Android SQlite Database Tutorial

SQLite is an Open-Source embedded SQL database engine. This provides relational database management structure for storing user defined records in the form of tables. SQLite is light weight when it comes to resource usage and it doesn’t need to have any server setup like other RDBMS systems. It is an cross platform and self-contained database. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS .

Android is shipped with SQLite version 3.4.0.

In this tutorial I am taking an example of storing Employee record in SQLite database. I am using a table called Employee to store Employee record. This table contains three columns emp_id (INTEGER), emp_name (TEXT), emp_phone_number (TEXT).

Step 1) Create Employee model class

write Employee class with all getter and setter methods to maintain single contact as an object.

package com.androidtutorialshub.sqlitedatabaseexample;

public class Employee {

    private int empId;
    private String empName;
    private String empPhoneNo;

    public Employee() {

    }

    public Employee(String empName, String empPhoneNo) {
        this.empName = empName;
        this.empPhoneNo = empPhoneNo;
    }

    public int getEmpId() {
        return empId;
    }

    public void setEmpId(int empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public String getEmpPhoneNo() {
        return empPhoneNo;
    }

    public void setEmpPhoneNo(String empPhoneNo) {
        this.empPhoneNo = empPhoneNo;
    }
}

Step 2) Create DatabaseHelper class

Create a class named DatabaseHelper and extends it with SQLiteOpenHelper .After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrade()
onCreate() – This is called when database is created and in this method we create the employee table
onUpgrade() – This is called when database is upgraded like modifying the table structure, adding constraints to database etc.

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "EmployeeManager";

    // Employee table name
    private static final String TABLE_EMPLOYEE = "employee";

    // Employee Table Columns names
    private static final String KEY_EMP_ID = "emp_id";
    private static final String KEY_EMP_NAME = "emp_name";
    private static final String KEY_EMP_PH_NO = "emp_phone_number";

    private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("
            + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT,"
            + KEY_EMP_PH_NO + " TEXT" + ")";

    private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + TABLE_EMPLOYEE;


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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_EMPLOYEE_TABLE);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        //Drop Employee Table if exist
        db.execSQL(DROP_EMPLOYEE_TABLE);

        // Create tables again
        onCreate(db);

    }
}

Step 3) Add All CRUD Operations (Create, Read, Update and Delete)

We need to write all operations that are required to read and write database .Add the following code snippets in DatabaseHelper class

-> Insterting New Employee Record

//Create employee record
    public void addEmployee(Employee employee){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_EMP_NAME, employee.getEmpName());
        values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());

        // Inserting Row
        db.insert(TABLE_EMPLOYEE, null, values);
        db.close();
    }

-> Reading All Employee Records

//Read employee record
    //Read employee record
    public ArrayList<Employee> getAllEmployee() {
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_EMPLOYEE;

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

        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Employee employee = new Employee();
                employee.setEmpId(Integer.parseInt(cursor.getString(0)));
                employee.setEmpName(cursor.getString(1));
                employee.setEmpPhoneNo(cursor.getString(2));
                // Adding employee record to list
                employeeList.add(employee);
            } while (cursor.moveToNext());
        }

        // return employee list
        return employeeList;
    }

-> Update Employee Record

//Update employee record
    public int updateEmployee(Employee employee) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_EMP_NAME, employee.getEmpName());
        values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());

        // updating row
        return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?",
                new String[] { String.valueOf(employee.getEmpId()) });
    }

-> Delete Employee Record

//Delete employee record
    public void deleteEmployee(Employee employee) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?",
                new String[]{String.valueOf(employee.getEmpId())});
        db.close();
    }

Complete DatabaseHelper Class

package com.androidtutorialshub.sqlitedatabaseexample;

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

import java.util.ArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "EmployeeManager";

    // Employee table name
    private static final String TABLE_EMPLOYEE = "employee";

    // Employee Table Columns names
    private static final String KEY_EMP_ID = "emp_id";
    private static final String KEY_EMP_NAME = "emp_name";
    private static final String KEY_EMP_PH_NO = "emp_phone_number";

    private String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMPLOYEE + "("
            + KEY_EMP_ID + " INTEGER PRIMARY KEY," + KEY_EMP_NAME + " TEXT,"
            + KEY_EMP_PH_NO + " TEXT" + ")";

    private String DROP_EMPLOYEE_TABLE = "DROP TABLE IF EXISTS " + CREATE_EMPLOYEE_TABLE;


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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_EMPLOYEE_TABLE);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        //Drop Employee Table if exist
        db.execSQL(DROP_EMPLOYEE_TABLE);

        // Create tables again
        onCreate(db);

    }

   //Create employee record
    public void addEmployee(Employee employee){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_EMP_NAME, employee.getEmpName());
        values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());

        // Inserting Row
        db.insert(TABLE_EMPLOYEE, null, values);
        db.close();
    }

   //Read employee record
    public ArrayList<Employee> getAllEmployee() {
        ArrayList<Employee> employeeList = new ArrayList<Employee>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_EMPLOYEE;

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

        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Employee employee = new Employee();
                employee.setEmpId(Integer.parseInt(cursor.getString(0)));
                employee.setEmpName(cursor.getString(1));
                employee.setEmpPhoneNo(cursor.getString(2));
                // Adding employee record to list
                employeeList.add(employee);
            } while (cursor.moveToNext());
        }

        // return employee list
        return employeeList;
    }

    //Update employee record
    public int updateEmployee(Employee employee) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_EMP_NAME, employee.getEmpName());
        values.put(KEY_EMP_PH_NO, employee.getEmpPhoneNo());

        // updating row
        return db.update(TABLE_EMPLOYEE, values, KEY_EMP_ID + " = ?",
                new String[] { String.valueOf(employee.getEmpId()) });
    }

    //Delete employee record
    public void deleteEmployee(Employee employee) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_EMPLOYEE, KEY_EMP_ID + " = ?",
                new String[]{String.valueOf(employee.getEmpId())});
        db.close();
    }


}

Step 4) Using DatabaseHelper in MainActivity.class

package com.androidtutorialshub.sqlitedatabaseexample;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;

import java.util.List;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseHelper db = new DatabaseHelper(this);

        // Inserting Contacts
        Log.d("Insert: ", "Inserting.");
        db.addEmployee(new Employee("Akanksha", "9988998899"));
        db.addEmployee(new Employee("Lalit", "888888888"));
        db.addEmployee(new Employee("Ayush", "9876543210"));
        db.addEmployee(new Employee("Nitul", "9977997799"));

        // Reading all contacts
        Log.d("Reading: ", "Reading all Employee Records ..");
        List<Employee> employees = db.getAllEmployee();

        for (Employee employee : employees) {
            String log = "Employee Id: " + employee.getEmpId() +
                    " ,Employee Name: " + employee.getEmpName() +
                    " ,Employee Phone: " + employee.getEmpPhoneNo();
            // Writing Contacts to log
            Log.d("Name: ", log);
        }
    }
}

Enjoy Coding and Share Knowledge