Android Database Helper

For every android application we mostly need to create a data layer which keeps the parametric data and some user related settings persisted. But it's not equivalent to the actual data layer that we have in our desktop based or web based application where we have an external database server. Because we can't keep such a huge application on our mobile devices.

In order to access stored data into our application, we can try two different approaches, one is to keep the data into a database server and access it via web service. But, for some user related settings why do we need such hassle. Because we don't need to access internet all the time in order to retrieve some basic user related information. So we will go for second approach which is to store a file on our local device and access it directly.

But, we want to make a structure in order to retrieve data and store data in some generic form. Yes, just like we do in databases. So, here in andorid SQLLite is a database which is stored in a file and can be accessed just like a database. I think it's fair enough for just a basic introduction, now we can jump into the code.

In order to keep my code generic and reusable by my other application I have created a separate class which I normally import in my every application which is related to database. Here, I am creating a class which is extending SQLiteOpenHelper which an android class available in package android.database.sqlite.

public class AppNameSQLiteOpenHelper extends SQLiteOpenHelper {

In this line we just need to mention the name of file that will be created in our application.

public static final String DB_NAME = "appname_db.sqllite";

Version is a necessary field that we just need to store into the application, this version will be updated whenever we just need to update the schema otherwise it will be considered the same schema and will never be replaced with a new file. For application development I think it's necessary to keep the version updated after every few changes so, that we can just have an updated schema whenever needed..

public static final int VERSION = 1;

In order to exposes methods to manage a SQLite database. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks. So, here we are just creating an instance of SQLiteDatabase which will be used later in our application.

public SQLiteDatabase DB;

The path of Database where we will have our database file stored on device.

public String DBPath;

Context is an interface to global information about an application environment. This is an abstract class whose implementation is provided by the Android system. It allows access to application-specific resources and classes, as well as up-calls for application-level operations such as launching activities, broadcasting and receiving intents, etc.

public static Context currentContext;

I am keeping the table name and fields in variables in order to avoid errors.

//Name of Table:
public static final String REGISTER_TABLE = "register";
//Fields:
public static final String USER_NAME = "username";
public static final String PASSWORD = "password";
public static final String E_MAIL = "email";
public static final String MOBILE_NUMBER = "mobilenumber";
public static final String KEY_MESSAGE = "keymessage";
public static final String SECURITY_NUMBER = "securityno";
// Columns:
private static final String[] COLUMNS = {SECURITY_NUMBER, USER_NAME, PASSWORD, E_MAIL, MOBILE_NUMBER, KEY_MESSAGE};

Here I am just creating an overloaded constructor to manage database creation and version management. This construcot will get a context of application as an argument and furhter call the parent class and after that the object will be used to create, open, and/or manage a database.

public TrackerSQLiteOpenHelper(Context context) {

Here context of the application using the database.

super(context, DB_NAME, null, VERSION);

On create method will only be called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen. This method expect a variable to hold the database instance,

@Override
public void onCreate(SQLiteDatabase db) {
createTable(db);
}

Create table will be called at the time of onCreate because we don't want to create table eachtime the application starts. Neither we want to create database everytime we start our application. So, here we are using a simple condition to avoid multiple databases creation.

private void createTable(SQLiteDatabase db) {

Here we have created a method checkDbExists that will insure the existence of database on our device.

boolean dbExists = checkDbExists();

checkDbExists method try to open the database with the variables defined for the creation of a new database. If database opens then it returns true else false

SQLiteDatabase checkDB = null;

try {
String myPath = DBPath + DB_NAME;

Here we are opening the database and assigning it to SQLiteDatabase instance, but if the instance is not null means the database doesn't exist.

checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

we must need to catch SQLiteException in catch block application will only move if the database will not exist.

} catch (SQLiteException e) {
// database does't exist yet.
}

As here we are just checking for the application so we must need to close the database instance that we have used to check the existence. Because we are not going to execute queries with this instance but, the other instance we have mentioned at the class level.

if (checkDB != null) {
checkDB.close();
}

Update the variable to validate the database.

dbExists = checkDB != null ? true : false;

if (!dbExists) {

This line is the most important line that will even open or create the database with the DB_NAME that we have mentioned in the start of our code.

DB = currentContext.openOrCreateDatabase(DB_NAME, 0, null);

Once, we are done with the craetion of database we will have to move to creation of table where our data will be persisted. So, here similar to SQL we are going to use a create table query.

String CREATE_REGISTER_TABLE = "create table register ( " +
SECURITY_NUMBER +" integer primary key autoincrement, " + USER_NAME + " text, " + PASSWORD +" text," +
E_MAIL +" text, " + MOBILE_NUMBER +" text, " + KEY_MESSAGE +" text )";

Here, we are going to use the database instance that we got from the createTable method and using the method execSQL which will create table for us..

db.execSQL(CREATE_REGISTER_TABLE);

This method will only be called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version. This method will only accept 3 variables one database instance, oldVersion and newVersion number which will be integer type of variable.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

In order to upgrade the schema we must need to Drop older tables if existed and the next line of code will do this for us.

db.execSQL("DROP TABLE IF EXISTS "+REGISTER_TABLE);

And after droping table we must need to create new tables for our database. So, once again we will give call to onCreate method that will create a new table set for us.

        this.onCreate(db);

Will update with the searching and insertion in my next blog.

If you find it helpful kindly click the donate button on the top of page. Also added an attachment of the Initial AndroidHelperClass.

DOWNLOAD SQL Helper Class