At times we are in need to store some data locally for faster retrieving and processing. One of the ways to store data locally is to write it in the file, and read it whenever needed. But there is another approach of using Database. Advantages of using database are like one doesn’t need to read entire file, load it in memory for getting some small chuck of data, also from developer perspective retrieve data from database would be easier.

In this article we will look at how to use android’s inbuilt supported sqlite database.

Let us look at the example of employee_database with employee table having columns _id, emp_name, emp_designation. With _id set as integer, primary key and autoincrement and emp_name and emp_designation to string/text.

package com.dbexample;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class EmployeeTable{

	public static final String KEY_NAME = "emp_name";
	public static final String KEY_DESIGNATION = "emp_designation";
	public static final String KEY_ROWID = "_id";

	private static final String TAG = "EmployeeTable";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DATABASE_NAME = "employee_database";
	private static final String DATABASE_TABLE = "employee";
	private static final int DATABASE_VERSION = 3;

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE =
		"create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
		+ KEY_NAME +" text not null, " + KEY_DESIGNATION + " text not null);";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "Creating DataBase: " + DATABASE_CREATE);
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 *
	 * @param ctx the Context within which to work
	 */
	public EmployeeTable(Context ctx) {
		this.mCtx = ctx;
	}

	public EmployeeTable open() throws SQLException {
		Log.i(TAG, "OPening DataBase Connection....");
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long createEmployee(String empName, String empDesignation) {
		Log.i(TAG, "Inserting record...");
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, empName);
		initialValues.put(KEY_DESIGNATION, empDesignation);

		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}

	public boolean deleteEmployee(long rowId) {

		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public Cursor fetchAllEmployee() {

		return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
				KEY_DESIGNATION}, null, null, null, null, null);
	}

	public Cursor fetchEmployee(long empId) throws SQLException {

		Cursor mCursor =

			mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
					KEY_NAME, KEY_DESIGNATION}, KEY_ROWID + "=" + empId, null,
					null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	public boolean updateEmployee(int empId, String empName, String empDesignation) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, empName);
		args.put(KEY_DESIGNATION, empDesignation);

		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + empId, null) > 0;
	}

}

In above example, EmployeeTable class has a private inner class DatabaseHelper which extends SQLiteOpenHelper class. SQLiteOpenHelper calss is responsible for calling onCreate and onUpgrade method whenever database is opened for either read or writes operation. onCreate method will check whether the table exist if not will create it, similarly onUpgrade method. Beside inner class EmployeeTable contains various other methods to createEmployee, deleteEmployee etc.

Snippet for Inserting Employee

EmployeeTable employeeTable = new EmployeeTable(this);
    	employeeTable.open();
    	employeeTable.createEmployee("Prashant Thakkar", "Tech Lead");
    	employeeTable.close();

Create instance of EmployeeTable and call it’s open() method. open method of EmployeeTable will create instance of DatabaseHelper and will open database connection with write permission. After open method is called, now call createEmployee method which accepts 2 parameter employee name and designation, this will insert a record in table employee. Since emp_id is set to autoincrement while creating database it will be set appropriately. Similarly close() method of EmployeeTable will close the database connection.
Snippet for retrieving Employee

EmployeeTable employeeTable = new EmployeeTable(this);
    	employeeTable.open();
    	Cursor c = employeeTable.fetchEmployee(1);
    	Toast.makeText(this, “Name: ” + c.getString(1) + “ Designation: “+ c.getString(2), Toast.LENGTH_LONG).show();
    	employeeTable.close();

fetchEmployee method of EmployeeTable accepts empId as parameter and returns cursor pointing to employee.
CursorAdapter
Let us look at how we can directly use cursor returned, for populating ListView.

EmployeeTable employeeTable = new EmployeeTable(this);
employeeTable.open();
Cursor c = employeeTable.fetchAllEmployee();
if (c != null){
SimpleCursorAdapter adapter2 = new SimpleCursorAdapter(this,
		R.layout.listview,
		c, // Give the cursor to the list adapter
	new String[] {c.getColumnName(1),c.getColumnName(2)}, // Map the column in the
		// employee database to...
new int[] {R.id.EmployeeName, R.id.EmployeeDesignation}); // The view defined in the XML template
		ListView empListView = (ListView)findViewById(R.id.Employee);
		empListView.setAdapter(adapter2);
		}
		employeeTable.close();

listview.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:layout_width="fill_parent" android:layout_height="wrap_content" android:id="@+id/MainLayout">
	<TextView android:id="@+id/EmployeeName" android:layout_alignLeft="@id/MainLayout"
		android:layout_width="wrap_content" android:layout_height="wrap_content" />
	<TextView android:id="@+id/EmployeeDesignation" android:layout_alignParentRight="true" android:layout_toRightOf="@id/EmployeeName"
		android:layout_width="wrap_content" android:layout_height="wrap_content" />
</RelativeLayout>

Point to remember is for using cursor for this kind of data binding operations we need to make sure that respective table have one column as _id
Conclusion
In this article introduces basics of Android SQLite database, created database and table, fetched records from database and also learned how to use SimpleCursorAdapter .

Prashant Thakkar– Team member – Xoriant Mobile Center of Excellence.

Related posts:

1. Find Nth maximum record in Database While working on a project, I came across a requirement wherein I had to write a query to retrieve the Nth maximum (salary) of the records in a database. Sounds...
2. How to publish Android Apps into an Android Market After having invested your time and efforts in developing the most creative Android application, you surely want to make it available to a larger audience. Go ahead and read this...