Android SQLite Database Tutorial

Android provides several ways to store user and app data. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.

In this tutorial I am taking an example of storing user contacts in SQLite database. I am using a table called Contacts to store user contacts. This table contains three columns
id (INT), cname (TEXT), phone_number (TEXT).

Writing Contact Class
Before you go further you need to write your Contact class with all getter and setter methods to maintain single contact as an object.

package com.sqlitedemo;

public class Contact {

 // private variable
 private int id;
 private String cname, phone_number;
 
 public Contact() {}
 
 public Contact(int id, String cname, String phone_number) {
  this.id = id;
  this.cname = cname;
  this.phone_number = phone_number;
 }
 
 public Contact(String cname, String phone_number) {
  this.cname = cname;
  this.phone_number = phone_number;
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getCname() {
  return cname;
 }

 public void setCname(String cname) {
  this.cname = cname;
 }

 public String getPhone_number() {
  return phone_number;
 }

 public void setPhone_number(String phone_number) {
  this.phone_number = phone_number;
 }
}

Writing SQLite Database Handler Class
We need to write our own class to handle all database CRUD(Create, Read, Update and Delete) operations.

1. Create a new project by going to File ⇒ New Android Project.
2. Once the project is created, create a new class in your project src directory and name it asDatabaseHandler.java ( Right Click on src/package ⇒ New ⇒ Class)
3. Now extend your DatabaseHandler.java class from SQLiteOpenHelper.

package com.sqlitedemo;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

 // Database version
 private static final int DB_VERSION = 1;
 // Database Name
 private static final String DB_NAME = "demo";
 // Table Name
 private static final String TB_NAME = "contacts";
 
 // Table Columns names
 private static final String KEY_ID = "id";
 private static final String KEY_NAME = "cname";
 private static final String KEY_PH_NO = "phone_number";
 
 public DatabaseHandler(Context context) {
  super(context, DB_NAME, null, DB_VERSION);
 }
 
 @Override
 public void onCreate(SQLiteDatabase db) {
  String CREATE_TABLE = "CREATE TABLE " + TB_NAME + "(" + KEY_ID + " INTEGER PRIMARY KEY," +
    KEY_NAME + " TEXT," + KEY_PH_NO + " TEXT" + ")";
  db.execSQL(CREATE_TABLE);
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
  onCreate(db);
 }

 // Adding new contact
 public void addContact(Contact contact) {
  SQLiteDatabase db = this.getWritableDatabase();
  
  ContentValues values = new ContentValues();
  values.put(KEY_NAME, contact.getCname()); // Contact Name
  values.put(KEY_PH_NO, contact.getPhone_number()); // Contact Phone Number
  
  // Inserting Row
  db.insert(TB_NAME, null, values);
  db.close(); // Closing database connection
 }
 
 // Getting single contact 
 public Contact getContact(int id) {
  SQLiteDatabase db = this.getReadableDatabase();
  
  Cursor rs = db.query(TB_NAME, new String[] { KEY_ID, KEY_NAME, KEY_PH_NO }, KEY_ID + "=?", new String[]{ String.valueOf(id) }, null, null, null, null);
  if (rs != null)
   rs.moveToFirst();
  Contact con = new Contact(Integer.parseInt(rs.getString(0)), rs.getString(1), rs.getString(2));
  
  // return contact
  return con;
 }
 
 // Getting all contact
 public List getAllContacts() {
  List list = new ArrayList();
  
  // Select all query
  String query = "SELECT * FROM " + TB_NAME;
  
  SQLiteDatabase db = this.getWritableDatabase();
  Cursor rs = db.rawQuery(query, null);
  
  // Looping through all rows and adding to list
  if (rs.moveToFirst()) {
   do {
    Contact con = new Contact();
    con.setId(Integer.parseInt(rs.getString(0)));
    con.setCname(rs.getString(1));
    con.setPhone_number(rs.getString(2));
    
    // Adding contact to list
    list.add(con);
   } while (rs.moveToNext());
  }
  
  // return contact list
  return list;
 }
 
 // Getting contact count
 public int getContactsCount() {
  String query = "SELECT * FROM " + TB_NAME;
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor rs = db.rawQuery(query, null);
  rs.close();
  
  // return count
  return rs.getCount();
 }
 
 // Updating single contact
 public int updateContact(Contact contact) {
  SQLiteDatabase db = this.getWritableDatabase();
  
  ContentValues values = new ContentValues();
  values.put(KEY_NAME, contact.getCname());
  values.put(KEY_PH_NO, contact.getPhone_number());
  
  // updating row
  return db.update(TB_NAME, values, KEY_ID + " = ?", new String[]{ String.valueOf(contact.getId()) });
 }
 
 // Deleting single contact
 public void deleteContact(Contact contact) {
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TB_NAME, KEY_ID + " = ?", new String[]{ String.valueOf(contact.getId()) });
  db.close();
 }
}

Usage:
1. Open Class MainActivity:
Please input this code

package com.sqlitedemo;

import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        DatabaseHandler db = new DatabaseHandler(this);
        
        // Inserting Contacts
        Log.d("Insert: ", "Inserting...");
        db.addContact(new Contact("John Smith", "94504950459"));
        db.addContact(new Contact("Joey Keo", "94504950459"));
        
        // Reading all contacts
        Log.d("Reading: ", "Reading all contacts...");
        List contacts = db.getAllContacts();
        
        for (Contact cn : contacts) {
   String log = "Id: " + cn.getId() + ", Name: " + cn.getCname() + ", Phone: " + cn.getPhone_number();
   // Writing contacts to log
   Log.d("Name: ", log);
  }
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();
        if (id == R.id.action_settings) {
            return true;
        }
        return super.onOptionsItemSelected(item);
    }
}