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 ( " +
		"_id integer PRIMARY KEY AUTOINCREMENT, " +
		"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)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DB_CREATE);
	}

	/*
	 * (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(DB_DESTROY);
		onCreate(db);
	}

}

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[] {
		COL_ID, COL_NAME, COL_AGE, COL_CITY
	};

	// 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() {
		mDbHelper.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
		queryCursor.moveToFirst();
		// if cursor is not the last element
		while(queryCursor.isAfterLast() == false) {
			// add new user info
			listUsers.add(new UserInfo(
					// get user id from cursor
					queryCursor.getInt(queryCursor.getColumnIndexOrThrow(COL_ID)),
					// get user name from cursor
					queryCursor.getString(queryCursor.getColumnIndexOrThrow(COL_NAME)),
					// get user age from cursor
					queryCursor.getInt(queryCursor.getColumnIndexOrThrow(COL_AGE)),
					// get user city from cursor
					queryCursor.getString(queryCursor.getColumnIndexOrThrow(COL_CITY))
				)
			);
			// move cursor to next item
			queryCursor.moveToNext();
		}
		// check if cursor is still opened and not null
		if(queryCursor != null && !queryCursor.isClosed()) {
			// close it to avoid memory leak
			queryCursor.close();
		}
		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;

		if(type == QUERY_TYPE_STRING_ARRAY) {
			// 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(
					id,
					c.getString(c.getColumnIndexOrThrow(COL_NAME)),
					c.getInt(c.getColumnIndexOrThrow(COL_AGE)),
					c.getString(c.getColumnIndexOrThrow(COL_CITY))
			);
			objOut = user_info;
		}
		// close cursor
		c.close();

		// 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!

 

Cheers,

Pete Houston

About these ads
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: