Android Development: How to use sqlite in Android

One of nice part of Android is sqlite data access is very simple. You don”t worry about the path to the sqlite database file and there are a couple of functions to make your file easier.

Creating DB


SQLiteDatabase db = openOrCreateDatabase("mydb",MODE_PRIVATE,null);

This is a neat function which does open and create at the same time. You need to name your database (e.g. “mydb”) and specify the mode to open. In this example, I”m opening the database as private mode that allows only your app to touch the file.

Execute SQL


db.execSQL("CREATE TABLE IF NOT EXISTS mydata (first_name,last_name,age)");
db.execSQL("INSERT INTO mydata (first_name,last_name,age) VALUES("Jon","Doe",20)");

Since it”s already opened, executing CRUID operation is available via execSQL function.

Query data


Cursor cursor = db.rawQuery("SELECT * FROM mydata ORDER BY age",null);
if (cursor.moveToFirst()){
 do {
 Log.v("mylog", cursor.getString(0) "," cursor.getString(1) "," cursor.getString(2));
 }
 while(cursor.moveToNext());
}

To query data, you need two steps:

  1. Create a cursor from the result of your select statement

  2. Move the cursor and retrieve data via get… functions.

I often make a mistake to retrieve data from the cursor without calling moveToFirst() function. Let”s call it first and this returns if there is a row to return or not. After moving to the first row, keep calling moveToNext() function until you reach the end of rows.

Closing


cursor.close();
db.close();

Let”s make sure to close all connection to db. Avoid unnecessary memory usage.

That”s it. If you want to see a sample project (created by IntelliJ), please click here. You can browse the entire source code from here.

By: kiichi on: