Home > Tutorials > SQLite on Android – Part II

SQLite on Android – Part II

In my previous, I’ve introduced you the idea and the basic mechanisms of SQLite on Android, so now, I will guide you to create the actual project.


E – Create the Project

Project Name: SQLite Study

Build Target: Android 2.3.3

Application Name: SQLite Study

Package Name: pete.android.study

Create Activity: MainActivity

Min SDK Version: 10


F – The Children Born: The Data Handling Couple

– First, let’s name them as: UserDbHelper (which inherits from SQLiteOpenHelper) and UserDbAdapter.

1. UserDbHelper: (UserDbHelper.java)

– What shall we do with this one? According to the Android documentation, we need to override two methods ‘onCreate’ and ‘onUpgrade()‘ for our own implemention. That’s all of it!

package pete.android.study;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class UserDbHelper extends SQLiteOpenHelper {

	// declare constants fields
	private static final String DB_NAME = "user_db";
	private static final int DB_VERSION = 1;

	// declared constant SQL Expression
	private static final String DB_CREATE =
		"CREATE TABLE tbl_info ( " +
		"name text NOT NULL, " +
		"age integer NOT NULL, " +
		"city text NOT NULL" +

	private static final String DB_DESTROY =
		"DROP TABLE IF EXISTS tbl_info";

	 * constructor
	public UserDbHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);

	 * (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	public void onCreate(SQLiteDatabase db) {

	 * (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


Note: the database is not actually created after ‘getReadableDatabase()‘ or ‘getWriteableDatabase()‘ is called.
2. UserDbAdapter: (UserDbAdapter.java)

The first things we need to implement is to call either ‘getWriteableDatabase()‘ or ‘getReadableDatabase()’ to create the actual database, in which will return a SQLiteDatabase object, that we will use directly to manipulate database.

So far so good, we need to have two field of SQLiteDatabase  and UserDbHelper objects at least. Also, we need a Context to define our UserDbHelper as described in its constructor.

Have a look at Android documentation on SQLiteDatabase class to find our needs.

– Let see what we need to do in this class. First, we need to provide some ways to

+ open the connection to database (SQLiteDatabase::getWriteableDatabase())

+ insert a new record to database (SQLiteDatabase::insert())

+ update a record in database (SQLiteDatabase::update())

+ delete a record in database (SQLiteDatabase::delete())

+ select one single record from database (SQLiteDatabase::query())

+ select all records from database (SQLiteDatabase::query())

+ close connection when done (SQLiteOpenHelper::close())

Since the connection to SQLiteDatabase is initiated through UserDbHelper, which inherits from SQLiteHelper, the UserDbHelper object is the one handling the connection, open or close.

Here the implementation:

package pete.android.study;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class UserDbAdapter {

	// declare database fields
	public static final String TBL_INFO = "tbl_info";
	public static final String COL_ID = "_id";
	public static final String COL_NAME = "name";
	public static final String COL_AGE = "age";
	public static final String COL_CITY = "city";

	// projection on all columns
	private static final String[] PROJECTION_ALL = new String[] {

	// query output type
	public static final int QUERY_TYPE_STRING_ARRAY = 0x01;
	public static final int QUERY_TYPE_USERINFO_OBJ = 0x02;

	// declared fields
	private Context mContext;
	private SQLiteDatabase mDb;
	private UserDbHelper mDbHelper;

	 * constructor
	public UserDbAdapter(Context c) {
		mContext = c;

	 * open database connection
	public UserDbAdapter open() throws SQLException {
		mDbHelper = new UserDbHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;

	 * close database connection
	public void close() {

	 * insert a record to db
	public long insertUser(String name, int age, String city) {
		return mDb.insert(TBL_INFO, null, createContentValues(name, age, city));

	 * update a record to db
	public long updateUser(int id, String name, int age, String city) {
		return mDb.update(TBL_INFO, createContentValues(name, age, city), COL_ID + "=" + id, null);

	 * delete a record from db
	public long deleteUser(int id) {
		return mDb.delete(TBL_INFO, COL_ID + "=" + id, null);

	 * query all records
	public List<UserInfo> fetchAllUsers() {
		// get query cursor
		Cursor queryCursor = mDb.query(TBL_INFO, PROJECTION_ALL, null, null, null, null, null);
		// just return null if cursor null
		if(queryCursor == null) {
			Log.d(MainActivity.TAG, "UserDbAdapter.fetchAllUsers(): queryCursor = null ");
			return null;
		// init list to hold user info
		List<UserInfo> listUsers = new ArrayList<UserInfo>();
		// set cursor to the first element
		// if cursor is not the last element
		while(queryCursor.isAfterLast() == false) {
			// add new user info
			listUsers.add(new UserInfo(
					// get user id from cursor
					// get user name from cursor
					// get user age from cursor
					// get user city from cursor
			// move cursor to next item
		// check if cursor is still opened and not null
		if(queryCursor != null && !queryCursor.isClosed()) {
			// close it to avoid memory leak
		Log.d(MainActivity.TAG, "UserDbAdapter.fetchAllUsers(): listUsers.size() = " + listUsers.size());
		// return user list
		return listUsers;

	 * query one record
	public Object fetchSingleUser(int id, int type) {
		// query a cursor on identified user
		Cursor c = mDb.query(true, TBL_INFO, PROJECTION_ALL, COL_ID + "=" + id, null, null, null, null, null);
		// return null if no record avaiable
		if(c == null) {
			return null;

		Object objOut = null;

			// create array to hold user info
			String[] user_info = new String[4];
			user_info[0] = String.valueOf(id);
			user_info[1] = c.getString(c.getColumnIndexOrThrow(COL_NAME));
			user_info[2] = c.getString(c.getColumnIndexOrThrow(COL_AGE));
			user_info[3] = c.getString(c.getColumnIndexOrThrow(COL_CITY));
			objOut = user_info;
		} else {
			// create UserInfo object
			UserInfo user_info = new UserInfo(
			objOut = user_info;
		// close cursor

		// return user info
		return objOut;

	 * create ContentValues object to use for db transaction
	private ContentValues createContentValues(String name, int age, String city) {
		// init a ContentValues object
		ContentValues cv = new ContentValues();
		// put data
		cv.put(COL_NAME, name);
		cv.put(COL_AGE, age);
		cv.put(COL_CITY, city);
		// return object
		return cv;

After reading above code, you may wonder:

+ Why do I return a list of UserInfo in fetchAllUser()? The answer is, as I mentioned in previous post (Part I), I will create a list view to display user information queried from database, so I create an entity class named ‘UserInfo‘ to hold every piece of user info. In order to avoid working around with database outside of this UserDbAdapter, I’ve just extracted all data from Cursor to create all user information and return into a list. Therefore, the other classes don’t need to worry about extracting data from Cursor. It’s very convenient!

+ Why do I return the Object in fetchSingleUser()? The answer is, in case the other class wants to have an array of String or prefer UserInfo object, then I just need to return Object to avoid the conversion between string array and UserInfo. Well, it’s flexible my way!

Just think it simple! Those methods, insert() – query() – delete() – update(), are wrappers around SQL query expressions, where you need to suitable input parameters to execute properly.

For insert() and update(), you need to use a ContentValues object to store all keys and values which would be passed as parameters. As you see in above code, I don’t pack column ‘_id‘ in ContentValues, because _id is created as AUTOINCREMENT, so I don’t need to. For convenience, I’ve created a private method ‘createContentValue()‘ to pack all things need to be inserted or updated.

When update(), you need to specify which record, therefore, the parameter ‘ COL_ID + “=” + id‘ is needed for.Similarly working while delete(), just specify row id to be deleted is enough.

The query() will return a Cursor object which point to a list structure (I guess @@) of results found. Before extracting data from a Cursor, remember to always call ‘moveToFirst()‘ to set its pointer at beginning of the list. Otherwise, you would receive unexpected results.Cursor object will point to each row of the table, you need to use proper methods to get data from each column of the row.Eg.: Cursor::getInt(), Cursor::getString(), Cursor::getExtras()

In order to get column index from column name, use ‘Cursor::getColumnIndex()‘ or ‘Cursor::getColumnIndexOrThrow()‘ if  you’re not sure about column name is correct or not.

You might want to refer to Android documentation on Cursor.

If Cursor points to many records, then just call ‘moveToNext()‘ to point to next row.


That’s done for part II!

Take some coffee and move around … wait for the final part!



Pete Houston

Categories: Tutorials Tags: , , ,
  1. May 20, 2014 at 10:22 pm

    Have you ever considered publishing an e-book or guest authoring
    on other blogs? I have a blog based upon on the same ideas you
    discuss and would love to have you share some stories/information.
    I know my audience would appreciate your work. If you are even remotely interested, feel free to send me an e-mail.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: