SQLite Database in Android

SQLite is a database embedded in Android OS, making it of course the default Android DB. The reason why it is a perfect choice for most mobile OSes including android is because its compact, configuration is not required, it’s serverless, thus making it a lightweight DB.

What are the things you should know about SQLite before we jump into its implementation on Android:

1. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements.
2. Only Three datatypes are supported in SQLite; Text, Integer and real (Double). All other type need to be converted to these types.
3. Data type integrity is not maintained in SQLite such that you can write an integer into a string column and vice versa.
4. Referential integrity is not maintained, meaning there is no Foreign Key constrains or Joins statements.
5. SQLite Full Unicode support is optional and not installed by default.

For demonstration purpose’s, am going to create simple students database application. My Application will contain two tables; Students Table and a Department table.

Student Table columns:

• StudentID
• FirstName
• SurName
• DateOfBirth
• DeptID

Department table columnts

• DeptID
• DeptName

 

SQLite on Android doesn’t have a management interface to create and manage databases from, so we are going to do this though code. To start with, we shall create a class to handle the Database and table creation. Here is the structure of the class:

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class StudentsDataHelper extends SQLiteOpenHelper  {
   
   

//Tables Constants
private static final String Table_Students="Table_Students";
private static final String Table_Dept="Table_Dept";

//Student table columns Constants
private static final String Col_Studentid="StudentID";
private static final String Col_FirstName="FirstName";
private static final String Col_Surname="Surname";
private static final String Col_DateOfBirth="DateOfBirth";
private static final String Col_Stud_DeptID="DeptID";

//Dept table columns Contants
private static final String Col_DeptID="DeptID";
private static final String Col_DeptName="DeptName";

    private static final String DATABASE_NAME = "Students.db";
    private static int DATABASE_VERSION=1;

    public StudentsDataHelper(Context context){
        super(context, DATABASE_NAME,  null, DATABASE_VERSION);

       
    }
   
     @Override
    public void onCreate(SQLiteDatabase db) {
        //TODO Create table Student TABLE
        //TODO Create table Department TABLE
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Drop the Student Table
        //Drop the Department Table
        onCreate(db);
    }
   
}

 

The StudentsDataHelper class Inherits the‘SQLiteOpenHelper’ class . This class provides us with two override methods; onCreate() which is called by the framework when the database doesn’t exist and onUpgrade() called when the database version is increased in the application code, This method allows one to upgrade the database schema.

In the constructor , the Super() method of ‘SQLiteOpenHelper’, requires the following parameters:

•  Context:-This is the context to which the database is attached.
•  DatabaseName:- The name of the Database.
• CursorFactory:- This can either be used as a tandard cursor which acts as a pointer to the results returned from the database. Or a Specialized Cursor done by extending the Cursor class to do additional operations or verifications on the database result.
• Version:- The version of the database schema. The constructor creates a new blank databse with the specified name and version.

 

OnCreate() Method

On our overriden onCreate() method we shall put the standard SQL statements to create out table and execute them against the Database: Here it is;

@Override
public void onCreate(SQLiteDatabase db) {
     String CREATE_STUDENT_TABLE=String.format("CREATE TABLE IF NOT EXISTS %s " +
             "(%s INTEGER PRIMARY KEY AUTOINCREMENT,%s TEXT,%s TEXT,%s TEXT,%s INTEGER)" ,Table_Students ,Col_Studentid, Col_FirstName, Col_Surname, Col_DateOfBirth,Col_Stud_DeptID);
    
     db.execSQL(CREATE_STUDENT_TABLE);
    
     String CREATE_DEPT_TABLE=String.format("CREATE TABLE IF NOT EXISTS %s (%s INTEGER PRIMARY KEY AUTOINCREMENT,%s TEXT)" ,Table_Dept ,Col_DeptID,Col_DeptName);
    
     db.execSQL(CREATE_DEPT_TABLE);
    
}

 

On this method you can create the tables, views and triggers. It is usually invoked only once when the database does not exist or the first time the application is ran. You will also realise later that I have invoked the method gain during the upgrade.

 

 onUpgrade() Method

Sometimes you want to change the Database schema; Add new table, Add columns, change datatypes. to do this you will require to override the onUpgrade() method:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + Table_Students);
    db.execSQL("DROP TABLE IF EXISTS " + Table_Dept);
   
    //Drop any view or triggers if any
    onCreate(db);
}

In our method, we drop all the tables and the call the method onCreate()  to recreate our schema again. For this method to be executed the database version in the constructor will have to change.

private static int DATABASE_VERSION=2;

For my simple case here, I have dropped all the table but what if there is data that you want to conserve in the tables? You can create temporary tables and preserve the data  then retrieve it after you are done with the upgrade.