database

Android Cursor Example

In Android programming, storing data into a database is not a common practice, as we have other and most suitable methods to store our data, such as the SharedPreferences way. However, we do have a very strong tool, that can help our Android Application communicate with a database, and this is SQLite. SQLite is an Open Source Database for structured data in relational databases. It is embedded in Android, to you don’t have to do anything special to set up or administer an SQLite server.

So, when we want to interact with a database, inside an Android Application, we have to grant our Activity with the right to random read-write access to the contents of this database. For this reason, Android has the Cursor interface, that provides random read-write access to the result set returned by a database query.

In this example, we will see a very easy and common use of the Cursor class, in a SQLite example.
For our example will use the following tools in a Windows 64-bit or an OS X platform:

  • JDK 1.7
  • Eclipse 4.2 Juno
  • Android SDK 4.4.2

Let’s take a closer look:

1. Create a New Android Application Project

Tip
You may skip project creation and jump directly to the beginning of the example below.

Open Eclipse IDE and go to File → New → Project → Android Application Project.

Figure 2. Create a new Android project
Figure 1. Create a new Android project

Specify the name of the application, the project and the package and then click Next.

Figure 2. Create a new Android project name
Figure 2. Create a new Android project name

In the next window, the “Create Activity” option should be checked. The new created activity will be the main activity of your project. Then press Next button.

Create Activity
Figure 3. Configure the project

In “Configure Launcher Icon” window you should choose the icon you want to have in your app. We will use the default icon of android, so click Next.

Configure Launcher Icon
Figure 4. Configure the launcher icon

Select the “Blank Activity” option and press Next.

Blank Activity
Figure 5. Create the activity and select its type

You have to specify a name for the new Activity and a name for the layout description of your app. The .xml file for the layout will automatically be created in the res/layout folder. It will also be created a fragment layout xml, that we are not going to use in this project and you can remove it if you want. Then press Finish.

Figure 6. Create a new blank activity
Figure 6. Create a new blank activity

Here you can see, how will the structure of the project become when finished:

Figure 7. The tree of the project
Figure 7. The tree of the project

2. Creating the layout of the main AndroidCursorExample

We are going to make a very simple layout xml for the AndroidCursorExample.class, that only consists of a LinearLayout that contains the one ListView.

Open res/layout/activity_main.xml, go to the respective xml tab and paste the following:

activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#ffffff"
    android:orientation="vertical" >

    <ListView
        android:id="@android:id/list"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>

3. Creating the source code of the main AndroidCursorExample Activity

Open src/com.javacodegeeks.androidcursorexample/AndroidCursorExample.java file and paste the code below.

AndroidCursorExample.java

package com.javacodegeeks.androidcursorexample;

import java.util.ArrayList;
import java.util.List;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.Toast;

public class AndroidCursorExample extends ListActivity {

	private List bookTitles;
	// DB name
	private final String dbName = "Android";
	private static SQLiteDatabase sqliteDB = null;
	private ArrayAdapter myAdapter;

	// this is the table name
	private final String tableName = "BestSellers";

	// this is the bookTitle Array that will be entered in our SQLite Table
	private final String[] bookTitle = new String[] { "The Great Gatsby", "The Grapes of Wrath", "Invisible Man", "Gone with the Wind", "Pride and Prejudice", "Wuthering Heights", "The Color Purple", "Midnights Children", "Mrs Dalloway", "War and Peace" };

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);

		// set our main layout
		setContentView(R.layout.activity_main);

		// set the ArrayAdapter for the result list
		bookTitles = new ArrayList();
		myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, bookTitles);
		ArrayList results = new ArrayList();

		// Declare SQLiteDatabase object
		sqliteDB = null;

		try {
			// open or create the sqlite database
			sqliteDB = this.openOrCreateDatabase(dbName, MODE_PRIVATE, null);
			// execute the query
			sqliteDB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (bookTitle VARCHAR);");
			// insert bookTitle Array versions into table created
			for (String ver : bookTitle) {
				sqliteDB.execSQL("INSERT INTO " + tableName + " Values ('" + ver + "');");
			}

			// create Cursor in order to parse our sqlite results
			Cursor cursor = sqliteDB.rawQuery("SELECT bookTitle FROM " + tableName, null);
			// if Cursor is contains results
			if (cursor != null) {
				// move cursor to first row
				if (cursor.moveToFirst()) {
					do {
						// Get version from Cursor
						String bookName = cursor.getString(cursor.getColumnIndex("bookTitle"));

						// add the bookName into the bookTitles ArrayList
						bookTitles.add(bookName);
						// move to next row
					} while (cursor.moveToNext());
				}
			}
			// initiate the listadapter
			myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, bookTitles);

			// assign the list adapter
			setListAdapter(myAdapter);

		} catch (SQLiteException se) {
			Toast.makeText(getApplicationContext(), "Error creating the database", Toast.LENGTH_LONG).show();
		} finally {
			if (sqliteDB != null) {
				sqliteDB.execSQL("DELETE FROM " + tableName);
				sqliteDB.close();
			}
		}
	}
}

Let’s see in detail the code above.

We set the activity_main.xml layout and we initialize our main layout by:

setContentView(R.layout.activity_main);

Here, we instanciate the bookTitle Array that will be entered in our SQLite Table, and contains our data.

private final String[] bookTitle = new String[] { "The Great Gatsby", "The Grapes of Wrath", "Invisible Man", "Gone with the Wind", "Pride and Prejudice", "Wuthering Heights", "The Color Purple", "Midnights Children", "Mrs Dalloway", "War and Peace" };

This is how we set our ArrayAdapter that will fill the List with results in our layout.

bookTitles = new ArrayList();
myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, bookTitles);
ArrayList results = new ArrayList();

In this code snipped, we open or create the sqlite database, and we try to execute our query to the database. We have to insert every book from our given array into the table.

sqliteDB = this.openOrCreateDatabase(dbName, MODE_PRIVATE, null);
sqliteDB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (bookTitle VARCHAR);");
for (String ver : bookTitle) {
         sqliteDB.execSQL("INSERT INTO " + tableName + " Values ('" + ver + "');");
}

Here we create a cursor in order to parse our Sqlite results and if our cursor is contains results, we move the cursor to first row and we get the specific first result. Later we add this result row into the array that we will display in our layout. We continue the iteration, until all cursor results are parsed.

Cursor cursor = sqliteDB.rawQuery("SELECT bookTitle FROM " + tableName, null);
if (cursor != null) {
    if (cursor.moveToFirst()) {
        do {
            String bookName = cursor.getString(cursor.getColumnIndex("bookTitle"));
            bookTitles.add(bookName);
        } while (cursor.moveToNext());
     }
}

4. Android Manifest

The AndroidManifest.xml of our project is simple and basic:

AndroidManifest.xml

  <?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.javacodegeeks.androidcursorexample"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="19" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:name="com.javacodegeeks.androidcursorexample.AndroidCursorExample"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest>

5. Build, compile and run

When we build, compile and run our project, the main AndroidCursorExample should look like this:

Figure 8. Figure This is how the main Activity looks like
Figure 8. Figure This is how the main Activity looks like.

Download the Eclipse Project

This was an example of Android AndroidCursorExample.

Download
You can download the full source code of this example here: AndroidCursorExample

Chryssa Aliferi

Chryssa is a Computer Science graduate from Athens University of Economic and Business. During her studies, Chryssa carried out a great variety of projects ranging from networking to software engineering. She is very keen on front end development especially on mobile technologies and web applications. She has worked as a junior Software Engineer in the telecommunications area and currently works as an Android Developer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button