Android provide various options for data persistence like Preferences, File and Database etc. This post we will introduce you to one of the option i.e. Android SQLite Database.

Let us take an example of Student database. i.e. student_database (DATABASE_NAME). We will create table tb_student (DATABASE_TABLE), with 3 columns i.e. _id (KEY_ROWID), name (KEY_NAME) and grade (KEY_GRADE). Columns _id is primary key and autoincrement, while name and garde are text .

We will 1st create a class called DatabaseUtil. DatabaseUtil is the main class that will used by other activities and will have various method to perform database operations like insert, delete etc. 

DatabaseUtil has private inner class DatabaseHelper, which is responsible for creating and updating database.

Below is the code for DatabaseUtil.

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 DatabaseUtil{

	private static final String TAG = "DatabaseUtil";

	/**
	 * Database Name
	 */
	private static final String DATABASE_NAME = "student_database";

	/**
	 * Database Version
	 */
	private static final int DATABASE_VERSION = 1;

	/**
	 * Table Name
	 */
	private static final String DATABASE_TABLE = "tb_student";

	/**
	 * Table columns
	 */
	public static final String KEY_NAME = "name";
	public static final String KEY_GRADE = "grade";
	public static final String KEY_ROWID = "_id";

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

	/**
	 * Context
	 */
	private final Context mCtx;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Inner private class. Database Helper class for creating and updating database.
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		/**
		 * onCreate method is called for the 1st time when database doesn't exists.
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "Creating DataBase: " + CREATE_STUDENT_TABLE);
			db.execSQL(CREATE_STUDENT_TABLE);
		}
		/**
		 * onUpgrade method is called when database version changes.
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 *
	 * @param ctx the Context within which to work
	 */
	public DatabaseUtil(Context ctx) {
		this.mCtx = ctx;
	}
	/**
	 * This method is used for creating/opening connection
	 * @return instance of DatabaseUtil
	 * @throws SQLException
	 */
	public DatabaseUtil open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	/**
	 * This method is used for closing the connection.
	 */
	public void close() {
		mDbHelper.close();
	}

	/**
	 * This method is used to create/insert new record Student record.
	 * @param name
	 * @param grade
	 * @return long
	 */
	public long createStudent(String name, String grade) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_GRADE, grade);
		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}
	/**
	 * This method will delete Student record.
	 * @param rowId
	 * @return boolean
	 */
	public boolean deleteStudent(long rowId) {
		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * This method will return Cursor holding all the Student records.
	 * @return Cursor
	 */
	public Cursor fetchAllStudents() {
		return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
				KEY_GRADE}, null, null, null, null, null);
	}

	/**
	 * This method will return Cursor holding the specific Student record.
	 * @param id
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor fetchStudent(long id) throws SQLException {
		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
					KEY_NAME, KEY_GRADE}, KEY_ROWID + "=" + id, null,
					null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * This method will update Student record.
	 * @param id
	 * @param name
	 * @param standard
	 * @return boolean
	 */
	public boolean updateStudent(int id, String name, String standard) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, name);
		args.put(KEY_GRADE, standard);
		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + id, null) > 0;
	}
}

In the above code as we can see DatabaseUtil have a private inner class DatabaseHelper which extends SQLiteOpenHelper. SQLiteOpenHelper have following methods:

  1. onCreate
  2. onUpgrade
  3. onOpen
  4. getWritableDatabase
  5. getReadableDatabase
  6. close

Out Of the above methods, onCreate and onUpgrade are two methods which we have overridden in subclass DatabaseHelper. Method onCreate is called for the 1st time when database is created/opened, and hence is used for creating table and populating them with predefined data if needed. Method onUpgrade is called when database needs to be upgarded in terms like, adding new tables, new colums to the existing tables etc based on the database version.

Database version for this example is set to 1 via DATABASE_VERSIONvariable.

Note: this class assumes monotonically increasing version numbers for upgrades. Also, there is no concept of a database downgrade; installing a new version of your app which uses a lower version number than a previously-installed version will result in undefined behavior.

Method onOpen is called when connection to database is opened. This method can be used for instance like checking read-only before updating database.

Methods getWritableDatabase and getReadableDatabase are responsible for creating or opening the database. The first time getWritableDatabasemethod is called it will call onCreate, onUpgrade and/or onOpen method is called.

Method close is used for closing the opened database.

Now let us look at the code snippet for creating/inserting  student record.

DatabaseUtil dbUtil = new DatabaseUtil(this);
dbUtil.open();
dbUtil.createStudent("Prashant Thakkar", "10th");
dbUtil.close();

As seen, first we created instance of DatabaseUtil class by passing the context of the class (Activity) in which this code is written. After creating instance call open method, which is responsible for opening database in writable mode (refer sample code above).  Now call createStudent method which accepts 2 parameters name and standard and create student record. Finally call close method to close the opened database.

After insertion, now let us look at the code snippet for fetching data from Students table.

DatabaseUtil dbUtil = new DatabaseUtil(this);
dbUtil.open();
Cursor cursor = dbUtil.fetchAllStudents();
if(cursor != null){
   while(cursor.moveToNext()){
	Log.i("Student", "Student Name: " + cursor.getString(1) +
               " Grade " + cursor.getString(2));
   }
}
dbUtil.close();

For fetching all students from student table we will call fetchAllStudents of DatabaseUtil class. Method fetchAllStudents return Cursor. We will check cursor for null and then iterate through it and print name and standard for each student.  Student name is fetched from cursor by calling cursor.getString(1), here 1 represent the position of column holding the Student Name in the String array passed while querying the database.

To know more about SQLite Datatypes.

Conclusion

This post introduces you to the basics of Android SQLite Database with sample code.