Working with SQLite Database in Android

Now a day, there are lots of android applications of different categories, and lots of them needs to store some data. For that purpose, it is a common requirement to use a system to store data in our application. There are several systems which perform this data storing task. Out of these systems, we will discuss about the SQLite database system in this tutorial.

Why SQLite database:

Most of us know about some familiar database systems like : MSSQL, MySQL, PostgreSQL etc. The question comes, why are we going to introduce SQLite database system in our project? The answer is Because of its better privileges to some extent than the other systems.The convenient attributes of this system are:

• Simple
• Lightweight
• Compact
• Embeddable with project
• No extra overhead of managing and configuring
• Perfect for single user and daily use

In this tutorial we will cover how to create a SQLite database and tables in an android project and then insert data and retrieve data from tables in an application using an Employee database management example.

Employee Database Example:

Let’s consider an employee database. An employee database can be used to store information (i.e id, name, address, designation, salary) of the employees of any organization.
So, we are going to create an employee database named employeeDb.
For simplicity, we will consider some general information like id, name and designation of an employee. So we will create a table which will store this information of an employee. The table name is: employeeInfo.

Table structure:

Think about a situation: Two employees are there with same name and same designation in an organization, how can we differentiate between them? For that purpose, every employee will be given a unique id, which will be the differentiator for the above case. So our table structure will be like this:



EmployeeInfo Class:

We need a class to store and retrieve employee information. Now we will create a class named EmployeeInfo which will meet our purpose. Here is a detail of the class.

Variables:



Constructor:

This class contains a constructor with this signature:

Code:

public EmployeeInfo(int id, String name, String designation)

This constructor takes an int and two string values on initialization and stores these values in its class variables.

Functions:

EmployeeInfo class contains following functions:



EmployeeInfo Class:

Here is the code block for EmployeeInfo class:

Code:

package com.example.demosqlite;

public class EmployeeInfo {
    
    int employeeId;
    String employeeName;
    String employeeDesignation;
    
    public EmployeeInfo(int id, String name, String designation){
        this.employeeId = id;
        this.employeeName = name;
        this.employeeDesignation = designation;
    }
    
    public void setEmployeeId(int id)
    {
        this.employeeId = id;
    }
    
    public void setEmployeeName(String name)
    {
        this.employeeName = name;
    }
    
    public void setEmployeeDesignation(String designation)
    {
        this.employeeDesignation = designation;
    }
    
    public int getEmployeeId(){
        return this.employeeId;
    }

    public String getEmployeeName(){
        return this.employeeName;
    }
    public String getEmployeeDesignation(){
        return this.employeeDesignation;
    }    
}

DbHandler Class:

Now we are going to create a class which will be responsible to perform the database operations. This class extends SQLiteOpenHelper class.

Here, we will define the following items:

• Database Version
• Database Name
• Table Name
• Fields of the table

In OnCreate function, we will execute a SQLite command to create our table in the database. To create a table, the command structure is :

Code:

Create table Table Name (Field1 name, Field1 data type, Field2 name, Field2 data type)

In place of table name, field name and field data type, we have to specify our desired value. If the table contains any primary or foreign key, you must specify along with the table field. To create our expected table, we have to execute the following command:

Create table employeeInfo(id integer primary key, name text, designation text).

The above command is used to create a table name employeeInfo, which has one integer field named id and it is the primary key of the table, one text field named name and one text field named designation.

OnUpgrade function is used to upgrade the database with a new version. In this tutorial, we are not going to make use of it.

addInfo function takes a EmployeeInfo object as parameter. It performs following operations:

• Extract id, name and designation information from the EmployeeInfo object.
• Create a Contentvalues object.
• Put extracted information in the Contentvalue object.
• Create a SQLiteDatabase object.
• Insert the values using Contentvalues object into the desired table.

Notice a thing: addInfo function is being called in from OnCreate function, not from the event where user enters data and submits it for storing. This is not very practical to add information in OnCreate function. But as we are in the very beginning step, we are using this example to learn how to use SQLite database.

getEmployeeList function returns the list of the employees from the database. It performs following operations:

• Create a List of EmployeeInfo
• Take a SQLiteDatabase object
• Use Cursor object to perform selection query
• Get values of individual row entry of the table and add it in the list until the end of the table is reached.
• Return the List to the caller function.

The note provided for the addInfo function is also applicable for the getEmployeeList function.
Here is the code block for our DbHandler class:

Code:

package com.example.demosqlite;

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

public class DbHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "employeeDb";

    private static final String TABLE_EMPLOYEE = "employeeInfo";

    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_DESIGNATION = "designation";
    
    public DbHandler(Context context) {
        
        // TODO Auto-generated constructor stub
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_DESIGNATION + " TEXT" + ")";
        db.execSQL(EMPLOYEE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEE);
        onCreate(db);
    }
    
    void addInfo(EmployeeInfo info) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        
        values.put(KEY_ID, info.getEmployeeId()); 
        values.put(KEY_NAME, info.getEmployeeName()); 
        values.put(KEY_DESIGNATION, info.getEmployeeDesignation()); 
        db.insert(TABLE_EMPLOYEE, null, values);
        db.close(); 
    }
    
        public List<EmployeeInfo> getEmployeeList() {
        List<EmployeeInfo> employeeList = new ArrayList<EmployeeInfo>();

        String query = "SELECT  * FROM " + TABLE_EMPLOYEE;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                EmployeeInfo employee = new EmployeeInfo();
                employee.setEmployeeId(Integer.parseInt(cursor.getString(0)));
                employee.setEmployeeName(cursor.getString(1));
                employee.setEmployeeDesignation(cursor.getString(2));
                employeeList.add(employee);
            } while (cursor.moveToNext());
        }
        return employeeList;
    }
}

SQLiteActivity Class:

This is our activity class. From this class, we will use the EmployeeInfo class to store and retrieve data of the employees and also use DbHandler class to perform database operations.
The tasks here performed are:

• Create a DbHandler class.
• Create EmployeeInfo object with the attribute value.
• Pass the employee information to DbHandler class through addInfo function, which later will be inserted into database by addInfo function.
• Get Employee list from database using getEmployeeList function of the DbHandler class and display the employee information in the log.

Here is the code block for our SQLiteActivity class

Code:

package com.example.demosqlite;

import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;

public class SQLiteActivity extends Activity {

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sql_lite);
        
        DbHandler handler = new DbHandler(this);
        
        handler.addInfo(new EmployeeInfo(1058,"John", "SE"));
        handler.addInfo(new EmployeeInfo(1089,"Jack", "SSE"));


            List<EmployeeInfo> employees = handler.getEmployeeList();       
        
        for (EmployeeInfo info : employees) {
            String employeeDetails = "Name    "+ info.getEmployeeName() + "    Designation    " + info.getEmployeeDesignation();
        Log.d("Employee information ", employeeDetails);
            
        }
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_sql_lite, menu);
        return true;
    }   
}