How to use SQLite Db in the Android Development

SQLite is a self-contained i.e., having all the modules and dependencies with in it & does not depend much on external libraries. This is one of the important reasons why most of the embedded devices use SQLite as temporary storage. Well-known examples are Google, Mozilla Firefox, Apple, Adobe and Android devices. It has zero-configuration with out any separate process for installation or configuration.

Android has 2 main packages, which lets us to use SQLite in the application. android.database and android.database.sqllite has all the necessary api's for create/read/update and delete actions.

Also SQLiteOpenHelper class provides public methods like onCreate, onUpgrade, onOpen methods. onCreate is called whenever the database is created for first time, onOpen when the DB has been opened and onUpgrade when the databased needs to be upgraded.

A database handler class which implements the SQLiteOpenHelper should call the constructor to create helper object to create, open or manage database. Constructor takes database name & database version. See below.

public SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

As a first step to implement SQLite, the database handler class should extend SQLiteOpenHelper. In the constructor method call the super() method by passing the context, database name and version.

         public DatabaseHandler(Context context) {

             super(context, DATABASE_NAME, null, DATABASE_VERSION);

         }

Database handler class should also implement the onCreate & onUpgrade methods. onCreate method is called by framework when the DB is created for the first time and onUpgrade method is used whenever DB tables need to be updated.

ex:

         public void onCreate(SQLiteDatabase db) {

                  // Category table create query

                  String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT)";

                  db.execSQL(CREATE_CATEGORIES_TABLE);

         }

// Upgrading database

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

                  // Drop older table if existed

                  db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);

                  // Create tables again

                  onCreate(db);

         }

For any read/write operation, SQLiteOpenHelper class provides getReadableDatabase() & getWritableDatabase() respectively. These methods returns reference to SQLiteDatabase object which can be used to create, read, update and delete from tables. For ex, below example shows Create/Open database.

         SQLiteDatabase db = this.getReadableDatabase();

         // Create/open a database for read/write operation.

         SQLiteDatabase db = this.getWritableDatabase();

The db object returned from the getReadableDatabase & getWritableDatabase provides create(), insert(), delete(), update() methods.

         Ex: insert takes table name, optional null column and content values that need to be inserted into the table.

         // Inserting Row

                  db.insert(TABLE_LABELS, null, values);

We can write query in 2 ways, First is raw query like "select * from ", "insert into" etc. and second one is structured query. For this SQLiteDatabase provides 2 methods query() & rawQuery().

query() methods provides structured interface which takes table name, array of columns that needs to be fetched, filter column (for WHERE clause), groupBy, orderBy, having and limit. Example below.

         db.query(TABLE_NAME, String[] columnNames, String[] selection, group by, having, order by);

rawQuery() takes the raw sql just like any sql database. ex

         db.rawQuery("select * from TABLE_NAME", null);

Both query() & rawQuery() returns something called Cursor object. Cursor is not actual result set; rather it is an object that is positioned before the first row of the result set. The handler class should iterate through the cursor object to get through all rows.

ex:

                  Cursor cursor = db.rawQuery(selectQuery, null);

                  // Looping through all rows and adding to list

                  if (cursor.moveToFirst()) {

                           do {

                                    cursor.getString(1);

                           } while (cursor.moveToNext());

                  }

Cursor.get*(), methods are the way to access columns of each rows.

All the above methods insert(), read(), delete() etc. should implement close() method to close db & cursor operations.

                  // closing connection

                  cursor.close();

                  db.close();

Reference materials:

Android.com

sqlite.org
Dr Barbara Hecker