Working with SQLite Database and Android
Introduction
SQLite is default database engine on many mobile operating systems such as Android, iOS.
1. Data type integrity is not maintained in SQLite
i.e. you can put string in integer and vice versa
2. Referential integrity is not maintained in SQLite
i.e. there is no FOREIGN KEY constraints or JOIN statements
In this tutorial, we will create a simple database application to store employee data. The DB has:
Tables
1. Employee
2. Employee Job Role
Columns for Tables
Employee
1. EMP_NO (PK)
2. USER_ID
3. PASSWORD
4. FIRST_NAME
5. LAST_NAME
6. EMAIL
7. JOB_CODE (FK)
Employee Job Role
1. JOB_CODE (PK)
2. JOB_ID
3. HOURLY_WAGE
4. OVERTIMEHOURLY_WAGE
Creating SQLite Database
SQLite on Android doesn't comes with a management interface or an application to create or maintain database from, so we're going to create the database ourselves by code.
Android Application
Step 1: Create GUI
main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:layout_margin="@dimen/Linearlayoutmargin"
android:orientation="vertical" >
<TextView
style="@style/textviewstyle"
android:text="@string/emp_id" />
<EditText
android:id="@+id/empidtext"
style="@style/edittextstyle"
android:inputType="textPersonName"
/>
<TextView
style="@style/textviewstyle"
android:text="@string/user_id" />
<EditText
android:id="@+id/useridtext"
style="@style/edittextstyle"
android:inputType="textPersonName"
/>
<TextView
style="@style/textviewstyle"
android:text="@string/password" />
<EditText
android:id="@+id/passwordtext"
style="@style/edittextstyle"
android:inputType="textPersonName"
/>
<TextView
style="@style/textviewstyle"
android:text="@string/email_id" />
<EditText
android:id="@+id/emailidtext"
style="@style/edittextstyle"
android:inputType="textEmailAddress"
/>
<TextView
style="@style/textviewstyle"
android:text="@string/first_name" />
<EditText
android:id="@+id/firstnametext"
style="@style/edittextstyle"
android:inputType="textPersonName"
/>
<TextView
style="@style/textviewstyle"
android:text="@string/last_name" />
<EditText
android:id="@+id/lastnametext"
style="@style/edittextstyle"
android:inputType="textPersonName"
/>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal" >
<Button
android:id="@+id/serachemployeebutton"
style="@style/buttonstyle"
android:layout_margin="1dp"
android:text="@string/serachemployee" />
<Button
android:id="@+id/newemployeebutton"
style="@style/buttonstyle"
android:layout_margin="1dp"
android:text="@string/newemployeebutton" />
<Button
android:id="@+id/saveemployeebutton"
style="@style/buttonstyle"
android:layout_margin="1dp"
android:text="@string/saveemployee" />
<Button
android:id="@+id/resetbutton"
style="@style/buttonstyle"
android:layout_margin="1dp"
android:text="@string/resetbutton" />
<Button
android:id="@+id/deletebutton"
style="@style/buttonstyle"
android:layout_margin="1dp"
android:text="@string/deletebutton" />
</LinearLayout>
</LinearLayout>
For this project I did use concept of Style and Theme.
<?xml version="1.0" encoding="utf-8"?>
<resources xmlns:android="http://schemas.android.com/apk/res/android">
<style name="textviewstyle">
<item name="android:layout_width">fill_parent</item>
<item name="android:layout_height">wrap_content</item>
<item name="android:textColor">#FF0000</item>
<item name="android:typeface">monospace</item>
<item name="android:textSize">20sp</item>
<item name="android:padding">5dp</item>
</style>
<style name="edittextstyle">
<item name="android:layout_width">wrap_content</item>
<item name="android:layout_height">wrap_content</item>
<item name="android:typeface">monospace</item>
<item name="android:padding">1dp</item>
</style>
<style name="buttonstyle">
<item name="android:layout_width">wrap_content</item>
<item name="android:layout_height">wrap_content</item>
<item name="android:typeface">monospace</item>
<item name="android:padding">5dp</item>
<item name="android:textSize">15sp</item>
<item name="android:background">#FF0000</item>
</style>
</resources>
You can also copy paste the content or code in order to get the working project. I am creating the blog at the same time my code is working.
Step 2: Create SQLite Database
First we will create a class that handles all the operations required to deal with the databases such as creating the databases, creating tables, inserting. updating and deleting records and so on. Because my application uses multiple tables and I was hoping not to have a single massive adapter class; I was able to find a workaround for it.
For each type of data that I needed to retrieve from the sqlite database and populate my application, I'll create an "adapter" class. These adapter class will holds all of the methods necessary for accessing the database for that piece of the information. For example, if I had two tables in my database:
1. Employee
2. Employee Job Roles
I would have two adapters. For this blog I will create one Employee adapter but Employee Job Roles adapter is more or less same.
public class Employee {
//Column Name
public static final String EMP_ID = "EMP_ID";
public static final String USER_ID = "USER_ID";
public static final String PASSWORD = "PASSWORD";
public static final String FIRST_NAME = "FIRST_NAME";
public static final String LAST_NAME = "LAST_NAME";
public static final String EMAIL = "EMAIL";
//Table Name
private static final String TABLE_NAME = "EMPLOYEE";
The name of the database
private DbHelper myDatabaseHelper;
The context attached to the database
private final Context myContext;
The version of the schema of the database.
private SQLiteDatabase myDatabase;
Create a class that inherits from SQLiteOpenHelper class.
//Class to build database
private static class DbHelper extends SQLiteOpenHelper{
The constructor creates a new blank databases with the specified name and version.
DbHelper(Context context) {
super(context, DBHelper.DB_NAME, null, DBHelper.DB_VERSION);
// TODO Auto-generated constructor stub
}
onCreate(SQLiteDatabase db): invoked when the databases is created, this is where we can create tables and columns to them, create views or triggers.
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion): invoked when we make a modification to the database such as altering, dropping, creating new tables.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
//set the context to current i.e. this class
public Employee(Context c){
myContext = c;
}
//open database connection
public Employee Open() throws SQLException {
myDatabaseHelper = new DbHelper(myContext);
myDatabase = myDatabaseHelper.getWritableDatabase();
return this;
}
//close database connection
public void close(){
myDatabaseHelper.close();
}
//insert data into sqlite database
public long createEmployee(String userid, String password, String firstname, String lastname, String email){
ContentValues content = new ContentValues();
content.put(USER_ID, userid);
content.put(PASSWORD, password);
content.put(FIRST_NAME, firstname);
content.put(LAST_NAME, lastname);
content.put(EMAIL, email);
return myDatabase.insert(TABLE_NAME, null, content);
}
//select employee : employee lookup
public String getEmployee(){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, null, null, null, null, null, null);
String result = "";
int iEMP_ID = c.getColumnIndex(EMP_ID);
int iUSER_ID = c.getColumnIndex(USER_ID);
int iPASSWORD = c.getColumnIndex(PASSWORD);
int iFIRST_NAME = c.getColumnIndex(FIRST_NAME);
int iLAST_NAME = c.getColumnIndex(LAST_NAME);
int iEMAIL = c.getColumnIndex(EMAIL);
for(c.moveToFirst();!c.isAfterLast();c.moveToNext()){
result = result + c.getString(iEMP_ID) + " " + c.getString(iUSER_ID) + " " +
c.getString(iPASSWORD) + " " + c.getString(iFIRST_NAME) + " " + c.getString(iLAST_NAME) +
c.getString(iEMAIL) + "\n";
}
return result;
}
//select individual employees
//get user id
public String getUserId(long empid){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, EMP_ID + "=" + empid , null, null, null, null, null);
if(c != null){
c.moveToFirst();
String userid = c.getString(1);
return userid;
}
return null;
}
//get password
public String getPassword(long empid){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, EMP_ID + "=" + empid , null, null, null, null, null);
if(c != null){
c.moveToFirst();
String password = c.getString(2);
return password;
}
return null;
}
//get first name
public String getFirstName(long empid){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, EMP_ID + "=" + empid , null, null, null, null, null);
if(c != null){
c.moveToFirst();
String firstname = c.getString(3);
return firstname;
}
return null;
}
//get last name
public String getLastName(long empid){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, EMP_ID + "=" + empid , null, null, null, null, null);
if(c != null){
c.moveToFirst();
String lastname = c.getString(4);
return lastname;
}
return null;
}
//get email address
public String getEmailId(long empid){
String[] columns = new String[]{EMP_ID, USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL};
Cursor c = myDatabase.query(TABLE_NAME, columns, EMP_ID + "=" + empid , null, null, null, null, null);
if(c != null){
c.moveToFirst();
String emailid = c.getString(5);
return emailid;
}
return null;
}
//update employee
public void updateEmployee (long empid, String username, String password, String firstname, String lastname, String emailid){
ContentValues cvUpdate = new ContentValues();
cvUpdate.put(USER_ID, username);
cvUpdate.put(PASSWORD, password);
cvUpdate.put(FIRST_NAME, firstname);
cvUpdate.put(LAST_NAME, lastname);
cvUpdate.put(EMAIL, emailid);
myDatabase.update(TABLE_NAME, cvUpdate, EMP_ID + "=" + empid, null);
}
//delete employee
public void deleteEmployee(long empid){
myDatabase.delete(TABLE_NAME, EMP_ID + "=" + empid, null);
}
}
So, I have one of this kind of classes for each table. So my main Database Adapter which is responsible for creating all of my tables in a single Database will looks like:
public class DBHelper{
//Create Database
public static final String DB_NAME = "Sample.db";
//Create Database Version
public static final int DB_VERSION = 1;
//Create Employee Table String
private static final String CREATE_TABLE_EMPLOYEE =
"CREATE TABLE EMPLOYEE (" + Employee.EMP_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
Employee.USER_ID + " TEXT NOT NULL, " +
Employee.PASSWORD + " TEXT NOT NULL, " +
Employee.FIRST_NAME + " TEXT NOT NULL, " +
Employee.LAST_NAME + " TEXT NOT NULL, " +
Employee.EMAIL + " TEXT NOT NULL);" ;
//Create Employee Job Code Table String
private static final String CREATE_TABLE_EMP_JOBROLES =
"CREATE TABLE EMP_JOBROLES (" + EMP_JOBROLES.JOB_CODE + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
EMP_JOBROLES.JOB_ID + " TEXT NOT NULL, " +
EMP_JOBROLES.HOURLY_WAGE + " TEXT NOT NULL, " +
EMP_JOBROLES.OVERTIMEHOURLY_WAGE + " TEXT NOT NULL, " );" ;
As I mentioned before that SQLite 3 by default does not support foreign key constraint, however, we can enforced constraint using TRIGGER.
//Create Trigger for Foreign Key Constraint : Employee and Employee Job Code Table
//Job Code Column in Employee Table is foreign key and its reference to Employee Job Code
private static final String fk_EmpJobCode_JobRollesJobCode =
"CREATE TRIGGER fk_EmpJobCode_JobRollesJobCode " +
" BEFORE INSERT " +
"ON " + Employee.TABLE_NAME +
" FOR EACH ROW BEGIN" +
" SELECT CASE WHEN ((SELECT " + Employee.JOB_CODE + " FROM " + Employee.TABLE_NAME + " " +
"WHERE " + Employee.JOB_CODE + "=new." + EMP_JOBROLES.JOB_CODE + " ) IS NULL)" +
" THEN RAISE (ABORT, 'FOREIGN KEY VIOLATION') END;" +
" END;";
private static final String TAG = DBHelper.class.getSimpleName();
private DatabaseHelper myHelper;
private SQLiteDatabase mySQLDb;
private final Context context;
public String sql;
public DBHelper(Context context) {
this.context = context;
this.myHelper = new DatabaseHelper(this.context);
}
private static class DatabaseHelper extends SQLiteOpenHelper{
This method creates tables with columns, views and trigger. The method is invoked when the database is created. So we create our table and specify the columns. This method is invoked when the database does not exist on the disk, it's executed only once on the same devices the first time the application is run on the device.
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(TAG,"onCreate sql");
db.execSQL(CREATE_TABLE_EMPLOYEE);
Log.d(TAG,"onCreate Employee Table Created");
db.execSQL(CREATE_TABLE_EMP_JOBROLES);
Log.d(TAG,"onCreate Employee Job Rolles Table Created");
db.execSQL(fk_EmpJobCode_JobRollesJobCode);
Log.d(TAG,"onCreate Trigger Created");
}
Sometimes, we want to upgrade the database by changing the schema, add new tables or change column data types. This is done by overriding the onUpdate method.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Drop the table if exists and run onCreate method
db.execSQL("DROP TABLE IF EXISTS EMPLOYEE");
This method is invoked when the version number specified in the constructor of the class changes. When you want to append a changes to your database, you must change the version number in the constructor of the class. In above code, if you change the DB_VERSION from 1 to 2 the application understands that you want to upgrade the database and onUpgrade method will be invoked. A typical implementation of this method is to drop the tables and create them again with the additional informations.
Log.d(TAG,"Table Drop");
this.onCreate(db);
}
}
//open database
public DBHelper open() throws SQLException{
this.mySQLDb = this.myHelper.getWritableDatabase();
return this;
}
//close database
public void close(){
this.myHelper.close();
}
}
Please follow Part II to understand how to program/call DBHelper class from main class.
