SQLite In Android

There are three ways to persist data locally in android application:-

  • Shared Preference
  • Saving Files
  • Saving Data in SQL Databases(SQLite)

In this article, we will discuss what is SQLite Database and how we use SQLite Database in Android application.

SQLite in Android

SQLite in Android

SQLite is a lightweight open source database for android devices. It requires very less memory as compare to conventional databases we have in web.

SQLite supports normal SQL features like Prepared Statement, SQL syntax and Transaction.

SQLite supports data types like Text (similar to String in java), Integer (similar to int) and Real (similar to double in java).

In order to use SQLite Database in android we have heavily rely on two classes.

  • SQLiteOpenHelper – It is a helper class to manage database creation and version management. You need to extend this class in order to create database in android application. This class has two abstract methods.
    • onCreate – which is called when the database is created for the first time. It is the place you define your table creation query.
    • onUpgrade – this method is get called, whenever your database is updated from the previous version.
  • SQLiteDatabase – We can create, update, and manage database with the help of SQLiteOpenHelper. Now we need to have some way through which we can perform normal SQL queries to play around with data. SQLiteDatabase exposes methods to mange the database.
    • It has methods to create, delete, execute SQL commands and perform other common database task.

Now we have enough theory to get start with SQLite Database in android. Lets try to make one Simple database application. For demo purpose I will not design activity class, in fact we will use Log in order to track the database operations.

Sample will be having Student database, where we have only one Student Table.

Lets begin our Coding by creating a StudentContract, this class will be having all constant related to student database. It will also have one inner class name StudentTable, which have constants, related with student table. The more tables you have the more classes you can create.
We prevent instantiate of this class, as it is database contract.

This contract class explicitly specifies the layout of your schema in a systematic and self-documenting way.

A Contract class is container for constants that defines names for URIs, tables and columns. This lets you to define all constant at one place, which will be propagated throughout your code
A good to define contract database is to define global definition at the root level of the class and inner class for each table that enumerates its columns, table name.

Inner class ideally implements the BaseColumns interface, that way you can inherit _ID primary key field, which works well with android framework. In fact Android classes like cursor adaptors expect this column to be present.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import android.provider.BaseColumns;
public class StudentContract {
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "student.db";
    public static final String TEXT_TYPE = " TEXT ";
    public static final String INTEGER_TYPE = " INTEGER ";
    public static final String COMMA_SEP = " , ";
 
    private StudentContract(){}
 
    public static class StudentTable implements BaseColumns{
        public static final String TABLE_NAME       = "Student";
        public static final String COLUMN_ROLL_NO = "ROLLNUMBER";
        public static final String COLUMN_NAME = "NAME";
        public static final String COLUMN_MARKS = "MARKS";
 
        public static final String CREATE_TABLE = "CREATE TABLE "+
                TABLE_NAME + " ( " +
                _ID + " INTEGER PRIMARY KEY AUTOINCREMENT " + COMMA_SEP +
                COLUMN_ROLL_NO + INTEGER_TYPE + COMMA_SEP +
                COLUMN_NAME + TEXT_TYPE + COMMA_SEP +
                COLUMN_MARKS + INTEGER_TYPE + " );";
 
 
        public static final String DELETE_TABLE = "DROP TABLE IF EXISTS " +TABLE_NAME;
 
    }
 
}

Creating a StudentHelper class in order to setup the student database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package com.createappfaster.dbhandler;
 
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
import com.createappfaster.dbcontract.StudentContract;
 
public class StudentHelper extends SQLiteOpenHelper {
 
    private final String TAG = StudentHelper.class.getSimpleName();
 
    public StudentHelper(Context context){
        super(context, StudentContract.DATABASE_NAME,null, StudentContract.DATABASE_VERSION);
    }
 
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(StudentContract.StudentTable.CREATE_TABLE);
        Log.d(TAG,StudentContract.StudentTable.CREATE_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        sqLiteDatabase.execSQL(StudentContract.StudentTable.DELETE_TABLE);
        onCreate(sqLiteDatabase);
        Log.d(TAG, StudentContract.StudentTable.DELETE_TABLE);
    }
}

Android stores your database in private disk location accessible only to your application.

Now lets create a class, which will put information into Student Database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package com.createappfaster.dbhandler.datasource;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
 
import com.createappfaster.dbcontract.StudentContract;
import com.createappfaster.dbhandler.StudentHelper;
import com.createappfaster.model.Student;
 
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentDataSource {
 
    private SQLiteDatabase sqLiteDatabase;
    private StudentHelper studentHelper = null;
 
   public StudentDataSource(Context context){
       studentHelper = new StudentHelper(context);
   }
 
    public void openConnection() throws SQLException {
        sqLiteDatabase = studentHelper.getWritableDatabase();
    }
 
    public void closeConnection() throws SQLException {
        if (sqLiteDatabase.isOpen()) {
            sqLiteDatabase.close();
        }
    }
 
    public void addStudent(Student student) throws SQLException {
        ContentValues contentValues = new ContentValues();
        contentValues.put(StudentContract.StudentTable.COLUMN_ROLL_NO, student.getRollNumber());
        contentValues.put(StudentContract.StudentTable.COLUMN_NAME, student.getName());
        contentValues.put(StudentContract.StudentTable.COLUMN_MARKS, student.getMarks());
        sqLiteDatabase.insertOrThrow(StudentContract.StudentTable.TABLE_NAME, null, contentValues);
    }
 
    public void deleteStudent(final int studentId) throws SQLException {
        sqLiteDatabase.delete(StudentContract.StudentTable.TABLE_NAME, StudentContract.StudentTable._ID + " = " + studentId, null);
    }
 
    public List getAllStudents() throws SQLException {
        List students = new ArrayList();
 
        String getAllStudentQuery = "Select * from " + StudentContract.StudentTable.TABLE_NAME;
        Cursor cursor = sqLiteDatabase.rawQuery(getAllStudentQuery, null);
 
        if (cursor.moveToFirst()) {
            do {
                Student student = new Student();
                student.setStudentId(cursor.getInt(cursor.getColumnIndex(StudentContract.StudentTable._ID)));
                student.setName(cursor.getString(cursor.getColumnIndex(StudentContract.StudentTable.COLUMN_NAME)));
                student.setMarks(cursor.getInt(cursor.getColumnIndex(StudentContract.StudentTable.COLUMN_MARKS)));
                student.setRollNumber(cursor.getInt(cursor.getColumnIndex(StudentContract.StudentTable.COLUMN_ROLL_NO)));
                students.add(student);
            } while (cursor.moveToNext());
        }
        return students;
    }
 
    public void updateStudentRecords(Student student) throws SQLException {
        ContentValues contentValues = new ContentValues();
        int studentId = student.getStudentId();
        int marks = student.getMarks();
        contentValues.put(StudentContract.StudentTable.COLUMN_MARKS, marks);
        sqLiteDatabase.update(StudentContract.StudentTable.TABLE_NAME, contentValues, StudentContract.StudentTable._ID + " = " + studentId, null);
    }
}

You must have observed one thing that i am throwing SQLException in every api. The Simple reason for this is, i want caller to handle the application behaviour in case of any SQLException occurs.

For Example, user is trying to update the student records, and some SQLException occurs. Then in that case i want my application to show a Toast message or anything to notify the application user saying – ” Update failed,try again “.

One more important thing here, as performing SQL operations are time consuming task, it is advisable to perform SQL operations in worker thread.Instead of blocking Main thread.

Complete Code can be download from here.

Leave a Reply

Your email address will not be published.