Java – SQLite Database Helper Class for Android

One of the most used features on the Android platform is being able to save and fetch your data from some sort of database, Android comes packaged with libraries to manage SQLite Databases. I have written a helper class to get you started using it, there are also examples showing how to read and write to the database using SQL and in-built methods.

DBHelper.java

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

public class DBHelper extends SQLiteOpenHelper {
	private static final String TAG = DBHelper.class.getSimpleName();
	public static final String DB_NAME = "test.db";
	public static final int DB_VERS = 1;
	public static final String TABLE = "test_table";
	public static final boolean Debug = false;
		
	public DBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERS);
		this.context = context;
	}
	
	public Cursor query(SQLiteDatabase db, String query) {
		Cursor cursor = db.rawQuery(query, null);
		if (Debug) {
			Log.d(TAG, "Executing Query: "+ query);
		}
		return cursor;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		/* Create table Logic, once the Application has ran for the first time. */
		String sql = String.format("CREATE TABLE %s (aid INTEGER PRIMARY KEY AUTOINCREMENT, test)", TABLE);
		db.execSQL(sql);
		if (Debug) {
			Log.d(TAG, "onCreate Called.");
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(String.format("DROP TABLE IF EXISTS %s", TABLE));
		if (Debug) {
			Log.d(TAG, "Upgrade: Dropping Table and Calling onCreate");
		}
		this.onCreate(db);
		
	}
}

With the above class you’ll notice it has some static variables, TABLE, DB_NAME, DB_VERS, DEBUG and TAG – The table field is the table you want to be working with, it doesn’t really matter if you use this in the class or simply add it when you perform the queries. The second is the Database name (DB_NAME) and can be called anything you wish, it’s probably best to keep a name that relates to your application.

The third field (DB_VERS) is used to manage the Database Version, if you already have the database running and increment the version, the onUpgrade method will be called and the above method will drop the tables and re-create them by calling onCreate again.

DEBUG and TAG are used for only debugging purposes, if DEBUG is set to true – you’ll see messages in the Console Log when executing queries and methods are running, the tag simply gets the current simple name of the class so it’s easy to set up a log filter.

Insert Usage

You can use the above helper in the following way (make sure to change the Activity in the below code to your own Activity name) The method below for the insert, uses Content Values instead of a raw query.

DBHelper dbHelper = new DBHelper(EFXActivity.this);
SQLiteDatabase db = dbHelper.getWritableDatabase();

//Setup new Content Values and assign some dummy content
ContentValues values = new ContentValues();
String AID = "AID";
String TEST = "test";
values.put(AID, 1);
values.put(TEST, "Test Value!");

//Perform the insert
db.insert(DBHelper.TABLE, null, values);

//Close the Database and the Helper
db.close();
dbHelper.close();

Select and Iterate Results

The following selects some data from the database using an SQL query and iterates over the returned Cursor object and prints the results to the Console. In practice you’ll probably want to do something useful with this data.

Cursor cursor = dbHelper.query(db, "SELECT aid, test FROM test_table");

cursor.moveToFirst();
while (cursor.isAfterLast() == false) {
	 Log.d(TAG, cursor.getString(0)); //Column 0 (AID)
	 cursor.moveToNext();
}
cursor.close();

The code above is basic as it gets, there is no error catching at all – so if you get the SQL wrong or anything alone those lines an exception will be thrown and the application will crash. You’ll probably want to add a Try { } Catch around anything to do with the database and deal with any errors that be thrown yourself.

That’s just about all there is to it, there are a few things I haven’t explained here – such as using Android’s ContentValues and Cursor, these are simple enough to work out just by reading the Android SDK Documentation.

About Steve

Web Developer, IT enthusiast & PC Gamer.
This entry was posted in Android and tagged , , , . Bookmark the permalink.

One Response to Java – SQLite Database Helper Class for Android

  1. Baha'a says:

    Very helpful , thank you alot , can you upload `test.db`
    and i can’t found where you use it in code can you help me

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>