SQLite in Android

by Unknown Sunday, March 04, 2012 0 comments Android Tutorial

What is SQLite

SQLite is an Open Source Database which is embedded into Android.

SQLite supports standard relational database features like SQL syntax, transactions and prepared statements.

In addition it requires only little memory at runtime (approx. 250 KByte).

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java).

All other types must be converted into one of these fields before saving them in the database.

SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa. 

SQLite in Android - Brief

SQLite is available on every Android device.

Using an SQLite database in Android does not require any database setup or administration.

You only have to define the SQL statements for creating and updating the database.

Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the filesystem.

This can be slow.

Therefore it is recommended to perform database operations asynchronously, for example via the AsyncTask class. .

If your application creates a database, this database is saved in the directory DATA/data/APP_NAME/databases/DB_NAME. 

rawQuery() Example

Cursor cursor = getReadableDatabase().
rawQuery("select * from todo where _id = ?", new String[] { id });


query() Example

return database.query(DATABASE_TABLE,
new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
null, null, null, null, null);


What is Cursor?

A query returns a Cursor object . A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently;  as it does not have to load all data into memory. as it does not have to load all data into memory. To get the number of elements of the resulting query use the getCount() method. To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached. Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing. Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.