Android - SQLite Database

Android provide SQLite database for an storage option. This post will provide a overview on how to use it.

SQLite Database

It is a private storage space for application. It is persistent, suitable for complex data structure and long term storage. The database will be removed when you uninstall the application.

SQLiteOpenHelper Overview

SQLiteOpenHelper is a helper class that manage SQLite database and version management. In order to use SQLite database, you need to extend this class for database management.

When extending this class, you will need to create a constructor and override 2 methods

Constructor - in the construct, you will need to call the super() method. This method is the primary method that allow you to define the database and database version

onCreate() - This method will be called if the database does not exist. It is a place where you can create your database schema

onUpgrade() - This method will be called if the database version is increased in the code. It is a place where you want to modify your database schema.

SQLiteDatabase Overview

SQLiteDatabase is a class that provide methods to perform SQL query

insert() - insert data into the table with the help of ContentValues class

query() - query the table and get the values. This method return a database cursor that allow user to iterate over the items

delete() - delete data from a table

execSQL() - execute an SQL statement such as CREATE that is not SELECT and does not return data

Creating the Database and Table

If the database does not exist, the framework will call onCreate(SQLiteDatabase database) method. You must use the method to create the new database and table. Inside onCreate, use execSQL() to create the table

Overall process is as follow

• Override the constructor and call super() that provide your database name and version number
• Override onCreate(SQLiteDatabase database), inside the method, call SQLiteDatabase.execSQL() to call CREATE SQL statement to create your database

Inserting Data into Table

Overall process is as follow

• Open and obtain database via SQLiteOpenHelper.getWritableDatabase()
• Put column name and value pair into ContentValues class
• Call SQLiteDatabase.insert() with ContentValue to add row into table
• Call SQLiteOpenHelper.close() to close the database

Query Data from Table

Overall process is as follow

• Open and obtain database via SQLiteOpenHelper.getWritableDatabase()
• Call SQLiteDatabase.query() to obtain a database Cursor
• Call Cursor.moveToFirst() to reset the cursor to first item
• Iterate the items with the help of Cursor.isAfterLast()
• Get the item value via Cursor.getXXX(index) where XXX can be String, Int, Long, etc.. and index is the table column index
• Move to next item via Cursor.moveToNext()
• After accessing all items, close the Cursor with Cursor.close

Sample Code