How to use SQLite databases in Android

Before starting, let us have a brief introduction to SQLite. SQLite is a lightweight transactional database engine that occupies a small amount of disk storage and memory. These features make it a good choice for creating databases on many mobile operating systems such as Android. As we all know, Android default Database engine is Lite, which makes it perfect for Android.

Note: Before proceeding, let us look at few things which need to be considered while dealing with SQLite. They are as follows:

• First of all, data type integrity is not maintained in SQLite, so in that case, we can put a value of a certain data type in a column of another data type.
• Then, referential integrity is not maintained in SQLite as there are no FOREIGN KEY constraints or JOIN statements.
• Finally, SQLite Full Unicode support is optional and not installed by default.

Now, we will start by creating SQLite database. In this, we will create a class that handles all the operations required to deal with the database such as creating the database, creating tables, inserting and deleting records and so on.

So, the first step is to create the class that inherits from SQLiteOpenHelper class. This created class will provide us two methods to override to deal with the databases. They are as follows:

• onCreate: It is invoked when the database is created, this is where we can create tables and columns in addition to views or triggers.
• onUpgrade: It is invoked when we make a modification to the database such as altering, dropping , and creating new tables.

Listing 1: Shows the code for including the members of the class

public class DatabaseHelper extends SQLiteOpenHelper {

static final String dbName="demoDB";
static final String employeeTable="Employees";
static final String colID="EmployeeID";
static final String colName="EmployeeName";
static final String colAge="Age";
static final String colDept="Dept";

static final String deptTable="Dept";
static final String colDeptID="DeptID";
static final String colDeptName="DeptName";

static final String viewEmps="ViewEmps";

Now let’s go through the super class constructor parameters. They are as follows:

• Context con: It is the context attached to the database.
• dataBaseName: It is the name of the database.
• CursorFactory: Sometimes, we may use a class that extends the Cursor class to implement some extra validations or operations on the queries run on the database. In this case, we pass an instance ofCursorFactory to return a reference to our derived class to be used instead of the default cursor.
• Version: It is the version of the schema of the database. The constructor creates a new blank database with the specified name
• and version.

Listing 2: Shows the coding for a constructor

public DatabaseHelper(Context context) {
  super(context, dbName, null,33); 
  }

Now, we will create the database with the help of onCreate. The method creates tables with columns, a view and a trigger. The method is invoked when the database is created. So we create our table and specify the columns.

Listing 3: Shows the coding to create the class

public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
  
  db.execSQL("CREATE TABLE "+deptTable+" ("+colDeptID+ " INTEGER PRIMARY KEY , "+
    colDeptName+ " TEXT)");
  
  db.execSQL("CREATE TABLE "+employeeTable+" 
    ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
        colName+" TEXT, "+colAge+" Integer, "+colDept+" 
    INTEGER NOT NULL ,FOREIGN KEY ("+colDept+") REFERENCES 
    "+deptTable+" ("+colDeptID+"));");
  
  
  db.execSQL("CREATE TRIGGER fk_empdept_deptid " +
    " BEFORE INSERT "+
    " ON "+employeeTable+
    
    " FOR EACH ROW BEGIN"+
    " SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+" 
    WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+
    " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
    "  END;");
  
  db.execSQL("CREATE VIEW "+viewEmps+
    " AS SELECT "+employeeTable+"."+colID+" AS _id,"+
    " "+employeeTable+"."+colName+","+
    " "+employeeTable+"."+colAge+","+
    " "+deptTable+"."+colDeptName+""+
    " FROM "+employeeTable+" JOIN "+deptTable+
    " ON "+employeeTable+"."+colDept+" ="+deptTable+"."+colDeptID
    );
  //Inserts pre-defined departments
  InsertDepts(db);  
 }

Sometimes, there is a need to upgrade the database by changing the schema, add new tables or change column data types. So, this is done by overriding the onUpdate. This method is invoked when the version number specified in the constructor of the class changes.

Listing 4: Shows the coding to upgrade a database

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
  
  db.execSQL("DROP TABLE IF EXISTS "+employeeTable);
  db.execSQL("DROP TABLE IF EXISTS "+deptTable);
  
  db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger");
  db.execSQL("DROP TRIGGER IF EXISTS dept_id_trigger22");
  db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");
  db.execSQL("DROP VIEW IF EXISTS "+viewEmps);
  onCreate(db);
 }

Note: When the user wants to append a change to your database, he/she must change the version number in the constructor of the class.

As we know, that SQLite 3 by default does not support foreign key constraint, however we can force such a constraint using Triggers. So, we will create a trigger that ensures that when a new Employee is inserted, his/her Deptvalue is present in the original Dept table.

Listing 5: Shows the code to create a trigger

CREATE TRIGGER fk_empdept_deptid Before INSERT ON Employees 
FOR EACH ROW BEGIN
    SELECT CASE WHEN ((SELECT DeptID FROM Dept WHERE DeptID =new.Dept ) IS NULL)
    THEN RAISE (ABORT,'Foreign Key Violation') END;
    END

Now, if the user wants to create the code using onCreate method, there will be a change in coding.

Listing 6: Shows the code for creating a trigger using onCreate method

db.execSQL("CREATE TRIGGER fk_empdept_deptid " +
    " BEFORE INSERT "+
    " ON "+employeeTable+
    
    " FOR EACH ROW BEGIN"+
    " SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+" _
    WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+
    " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
    "  END;");

After this, we need to execute the basic SQL statements. You can execute any SQL statement that is not a query (like insert, delete, or update) using db.execSQL method.

Listing 7: Shows the code for executing SQL statements

db.execSQL("CREATE TABLE "+deptTable+" ("+colDeptID+ " INTEGER PRIMARY KEY , "+
    colDeptName+ " TEXT)");

The above execution is done in the same way, as we did while creating the database tables. Now, let us insert records to the databases.

Listing 8: Shows the code to insert records to the database

SQLiteDatabase db=this.getWritableDatabase();
 ContentValues cv=new ContentValues();
   cv.put(colDeptID, 1);
   cv.put(colDeptName, "Sales");
   db.insert(deptTable, colDeptID, cv);

   cv.put(colDeptID, 2);
   cv.put(colDeptName, "IT");
   db.insert(deptTable, colDeptID, cv);
                     db.close();

Now, if we want to update statements, we can do it in two ways. They are as follows:

• To execute db.execSQL
• To execute db.update method

The update method has the following parameters:

• String Table: This is the table to update a value in.
• ContentValues cv: This is the content values object that has the new values.
• String where clause: This is the where clause to specify which record to update.
• String[] args: This is the arguments of the where clause.

Listing 9: Shows the code to update the values in the database

public int UpdateEmp(Employee emp)
  {
   SQLiteDatabase db=this.getWritableDatabase();
   ContentValues cv=new ContentValues();
   cv.put(colName, emp.getName());
   cv.put(colAge, emp.getAge());
   cv.put(colDept, emp.getDept());
   return db.update(employeeTable, cv, colID+"=?", 
    new String []{String.valueOf(emp.getID())});   
  }

Now, let us check the method to delete the rows. It can be done in two ways. They are as follows:

• To execute db.execSQL
• To execute db.delete method

Listing 10: Shows the code to delete the rows

public void DeleteEmp(Employee emp)
  {
   SQLiteDatabase db=this.getWritableDatabase();
   db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(emp.getID())});
   db.close();
  }

Finally, there is a need to execute the queries. For this, we can also use two methods. They are as follows:

• Execute db.rawQuery method
• Execute db.query method

Listing 11: Shows the code to execute the queries

Cursor getAllDepts()
  {
   SQLiteDatabase db=this.getReadableDatabase();
   Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, 
	"+colDeptName+" from "+deptTable,new String [] {});
   
   return cur;
  }

Conclusion

Finally, in this article, we have learnt about creating SQLite databases in Android. Hope you like reading this!