Android SQLite Database – Part 1/2

When it comes to save data in an Android device then there are three ways to do it. Depending on the nature of requirement data could be saved in-

1. SQLite Database – A light weight database already available with Android.
2. Shared Preferences – An XML file used privately by the application which created it, and
3. File – Writing a file and saving it on SD card.

In this post we are going to create essential classes to perform database operations.

MyDatabase.java: This class contains a private inner class of SQLiteOpenHelper type. And, it is used for creating database. It also provides methods to perform CRUD operations. Note that it does not overrides methods, instead here we are providing a method with similar types of arguments to ease our operations.

package pcsalt.example.databasedemoapp;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MyDatabase implements DBConsts {
	private final String TAG = getClass().getSimpleName().toString();
	private final String DB_NAME = "dbname.db";	// .db is not compulsory
	private final int DB_VERSION = 1; 		// database version must be greater than or equal to 1
							// Increment DB_VERSION whenever database table is updated.
	private SQLiteDatabase mSQLiteDatabase;
	private MySQLiteOpenHelper mOpenHelper;
	public MyDatabase(Context context) {
		mOpenHelper = new MySQLiteOpenHelper(context, DB_NAME, null, DB_VERSION);
	}
	public MyDatabase open() {
		mSQLiteDatabase = mOpenHelper.getWritableDatabase();
		return this;
	}
	public void close() {
		mOpenHelper.close();
	}
	/*
	 * Always pass table name and values as arguments.
	 * It would be useful when there is more than one table in application.
	 *
	 * insert function returns the id of inserted row and -1 if it was not successful.
	 * */
	public long insert(String table, ContentValues values) {
		long id = mSQLiteDatabase.insert(table, null, values);
		return id;
	}
	/*
	 * Create different versions of select function : overload select() to match your requirement
	 * */
	public Cursor select(String table, String[] columns, String selection, String[] selectionArgs) {
		return select(table, columns, selection, selectionArgs, null, null, null);
	}
	public Cursor select(String table, String[] columns, String selection, String[] selectionArgs,
			String groupBy, String having, String orderBy) {
		Cursor cur = mSQLiteDatabase.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
		return cur;
	}
	/*
	 * Update() returns the number of updated rows.
	 * */
	public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
		int updated_rows = mSQLiteDatabase.update(table, values, whereClause, whereArgs);
		return updated_rows;
	}
	public int delete(String table, String whereClause, String[] whereArgs) {
		int deleted_rows = mSQLiteDatabase.delete(table, whereClause, whereArgs);
		return deleted_rows;
	}
	private class MySQLiteOpenHelper extends SQLiteOpenHelper {
		public MySQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			// Create tables here
			String query = "CREATE TABLE " + IF_NOT_EXISTS + TBL_CONTACT + " ("
							+ ID + " INTEGER PRIMARY KEY,"
							+ NAME + " VARCHAR(30), "
							+ CONTACT + " VARCHAR(30))";
			try {
				db.execSQL(query);
			} catch(Exception e) {
				// in case of any exception : application must not crash
				Log.e(TAG, "Exception: " + e.getMessage());
			}
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// When database is upgraded this method is called
			// call onCreate explicitly to modify the database with updated table(s).
			onCreate(db);
		}
	}
}

This database class uses some constants defined in following DBConsts interface.

DBConts.java: This interface is used to provided a consistent name and prevent typing errors while performing operations. It is a good practice to use constants to handle the key strings.

package pcsalt.example.databasedemoapp;
/*
 * DBConsts: contains all column name, table name and other database
 * variables used for Database.
 * */
public interface DBConsts {
	public static String TBL_CONTACT = "tblContact";
	public static String ID = "_id";
	public static String NAME = "name";
	public static String CONTACT = "contact";
	// mind the spaces
	public static String IF_NOT_EXISTS = " if not exists ";
	public static String AND = " and ";
}

Source code is available for download in next part, after completion of tutorial.

Video tutorial