Logo Icon Logo
A Crowd-sourced Cookbook on Writing Great Android® Apps
GitHub logo Twitter logo OReilly Book Cover Art

Retrieving data from a SQLite database.

Author: Rachee Singh
Published? true -- FormatLanguage: W

Problem:

Loading items from an existing SQLite database.

Solution:

Using a Cursor object to iterate over the database and storing them.

Discussion:

In order to iterate over items in a database, we require an object of the Cursor class. To query the database, we use the query method along with appropriate arguments, most importantly: the table name, the column names for which we are extracting values.

ArrayList<Food> foods = new ArrayList(this);
Cursor listCursor = database.query(TABLE_NAME, new String [] {ID, NAME}, null, null, null, null, String.format("%s", NAME));
listCursor.moveToFirst();
Food t;
if(! listCursor.isAfterLast()) {
	do {
		Long id = listCursor.getLong(0);
		String name= listCursor.getString(1);
		t = new Food(name);
		foods.add(t);
	} while (listCursor.moveToNext());
} 
listCursor.close();

There are several overloads of the query method; the arguments for the simplest are:

  • Table name;
  • "Projection" (column names you want; String[])
  • "Selection" - part of normal SELECT statement
  • selectionArgs - to be inserted into Selection; String[]
  • groupBy - grouping (usually null)
  • having - "having" - usually null
  • orderBy - for an "ORDER BY" clause

The moveToFirst() method starts from the first item in the database and moveToNext moves the cursor to the next item. We keep checking until we have reached the end of the database. Each item of the database is added to an ArrayList.

The Cursor has a variety of get() methods, e.g., getString(), getInteger(), and so on, depending on the type of data in the given column.

See Also:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

No records found.