Android SQLite Database Tutorial


Android provides several ways to store user data and app data. SQLite database is one among them, it is a light weight database for storing user data which is inbuilt with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations with clear example programs. This post might look bit larger but explains everything very clearly.


SQLite  is used to store structured data. Android OS has complete support for SQLite database. But database table we create is accessible within the application. The tables can be accessed by the name to any class.
To create a new SQLite database we need to create a sub-class of SQLiteOpenHelper and override onCreate() method.

In my example program, I have used a table to store first name and last name of a person.
Table structure is;

Fields
Id
First name
Last name


Before we go further lets create the application and package structures as shown below.


Create value object/DTO class


We need to write Value Object class NameVO with all getter and setter methods to maintain a person’s name as an object.

 com.example.SQLiteDemo.vo.NameVO.java





/**
 * 
 */
package com.example.SQLiteDemo.vo;

/**
 * @author YY
 *
 */
public class NameVO {
 private int id;
 private String firstName;
 private String lastName;
 
 /**
  * Default constructor
  */
 public NameVO(){
  this.id=1;
  this.firstName="XX";
  this.lastName="YY";
 }
 
 /** 
  * Parameterized constructor
  * @param id
  * @param firstName
  * @param lastName
  */
 public NameVO(final int id,final String firstName,final String lastName){
  this.id=id;
  this.firstName=firstName;
  this.lastName=lastName;
 }

  /**
  * @return the id
  */
 public int getId() {
  return id;
 }

  /**
  * @param id the id to set
  */
 public void setId(int id) {
  this.id = id;
 }

  /**
  * @return the firstName
  */
 public String getFirstName() {
  return firstName;
 }

  /**
  * @param firstName the firstName to set
  */
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

  /**
  * @return the lastName
  */
 public String getLastName() {
  return lastName;
 }

  /**
  * @param lastName the lastName to set
  */
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
} 



Writing SQLite Database Handler Class

Create an interface for database operations (create, read,update,delete)

com.example.SQLiteDemo.db.CRUDOperations.java

/**
 * 
 */
package com.example.SQLiteDemo.db;


import java.util.List;


import com.example.SQLiteDemo.vo.NameVO;


/**
 * @author YY
 *
 */
public interface CRUDOperations {


 /**
 *  Adding new NameVO
 * @param name
 */
 public void addNameVO(NameVO name);


 /**
 * Getting single NameVO
 * @param id
 * @return
 */
 public NameVO getNameVO(int id);


 /**
 * Getting All NameVO
 * @return
 */
 public List<namevo> getAllNameVO();


 /**
 * Getting NameVO Count
 * @return
 */
 public int getNameVOCount();


 /**
 * Updating single NameVO
 * @param name
 * @return
 */
 public int updateNameVO(NameVO name);


 /**
 * Deleting single NameVO
 * @param name
 */
 public void deleteNameVO(NameVO name);
 
 /**
 * Deleting all NameVOs
 */
 public void deleteAllNames();
 
}


Implement the interface:

Implement the above interface and code the logic for database operations. This class should extend SQLiteOpenHelper of android to use SQLite database features as stated earlier. Then override two methods onCreate() and onUpgrade().

onCreate() – These is where we need to write table statements. This is called when database is created.
onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

com.example.SQLiteDemo.db.DBHandler.java

/**
 * 
 */
package com.example.SQLiteDemo.db;


import java.util.ArrayList;
import java.util.List;


import com.example.SQLiteDemo.vo.NameVO;


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


/**
 * @author YY
 *
 */
public class DBHandler extends SQLiteOpenHelper implements CRUDOperations{
 
 // Database Version
 private static final int DATABASE_VERSION = 1;
 
 // Database Name
 private static final String DATABASE_NAME = "demoDb";
 
 // Table name
 private static final String TABLE_NAME = "names";
 
 // Table Columns names
 private static final String KEY_ID = "id";
 private static final String KEY_FIRST_NAME = "firstName";
 private static final String KEY_LAST_NAME = "lastName";
 
 /**
 * @param context
 */
 public DBHandler(Context context) {
  super(context, DATABASE_NAME, null, DATABASE_VERSION);
 }


 /**
 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
 */
 @Override
 public void onCreate(SQLiteDatabase db) {
  String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + "("
    + KEY_ID + " INTEGER PRIMARY KEY," + KEY_FIRST_NAME + " TEXT,"
    + KEY_LAST_NAME + " TEXT" + ")";
  db.execSQL(CREATE_TABLE);
 }


 /**
 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
 */
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // Drop older table if existed
  db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
  // Create tables again
  onCreate(db);
 }


 /**
 * Adding new name
 * @see com.example.SQLiteDemo.db.CRUDOperations#addNameVO(com.example.SQLiteDemo.vo.NameVO)
 * 
 * Method accepts NameVO object as parameter. 
 * We need to build parameters using NameVO object. 
 * Once we inserted data in database we need to close the database connection.
 */
 @Override
 public void addNameVO(NameVO name) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_FIRST_NAME, name.getFirstName()); // First Name
  values.put(KEY_LAST_NAME, name.getLastName()); // Last Name
  // Inserting Row
  db.insert(TABLE_NAME, null, values);
  // Closing database connection
  db.close(); 
 }


 /**
 * Getting single name
 * @see com.example.SQLiteDemo.db.CRUDOperations#getNameVO(int)
 * 
 * Will read single nameVO row. 
 * It accepts id as parameter and will return the matched row from the database.
 */
 @Override
 public NameVO getNameVO(int id) {
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor cursor = db.query(TABLE_NAME, new String[] { KEY_ID,
    KEY_FIRST_NAME, KEY_LAST_NAME }, KEY_ID + "=?",
    new String[] { String.valueOf(id) }, null, null, null, null);
  if (cursor != null)
   cursor.moveToFirst();
  NameVO nameVO = new NameVO(Integer.parseInt(cursor.getString(0)),
    cursor.getString(1), cursor.getString(2));
  // return nameVO
  return nameVO;
 }


 /**
 * Gettig all names
 * @see com.example.SQLiteDemo.db.CRUDOperations#getAllNameVO()
 * 
 * Will return all nameVOs from database in array list format of NameVO class type. 
 */
 @Override
 public List<namevo> getAllNameVO() {
  List<namevo> nameVoList = new ArrayList<namevo>();
  // Select All Query
  String selectQuery = "SELECT  * FROM " + TABLE_NAME;
  SQLiteDatabase db = this.getWritableDatabase();
  Cursor cursor = db.rawQuery(selectQuery, null);
  // looping through all rows and adding to list
  if (cursor.moveToFirst()) {
   do {
    NameVO nameVO = new NameVO();
    nameVO.setId(Integer.parseInt(cursor.getString(0)));
    nameVO.setFirstName(cursor.getString(1));
    nameVO.setLastName(cursor.getString(2));
    // Adding NameVO to list
    nameVoList.add(nameVO);
   } while (cursor.moveToNext());
  }
  // return NameVO list
  return nameVoList;
 }


 /**
 * Getting names Count
 * @see com.example.SQLiteDemo.db.CRUDOperations#getNameVOCount()
 * 
 * Will return total number of NameVOs in SQLite database.
 */
 @Override
 public int getNameVOCount() {
  String countQuery = "SELECT  * FROM " + TABLE_NAME;
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor cursor = db.rawQuery(countQuery, null);
  cursor.close();
  // return count
  return cursor.getCount();
 }


 /**
 * Updating single entry
 * @see com.example.SQLiteDemo.db.CRUDOperations#updateNameVO(com.example.SQLiteDemo.vo.NameVO)
 * 
 * Will update single nameVO in database. 
 * This method accepts NameVo class object as parameter.
 */
 @Override
 public int updateNameVO(NameVO name) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_FIRST_NAME, name.getFirstName());
  values.put(KEY_LAST_NAME, name.getLastName());
  // updating row
  return db.update(TABLE_NAME, values, KEY_ID + " = ?",
    new String[] { String.valueOf(name.getId()) });
 }


 /**
 * Deleting single entry 
 * @see com.example.SQLiteDemo.db.CRUDOperations#deleteNameVO(com.example.SQLiteDemo.vo.NameVO)
 * 
 * Will delete single nameVO from database.
 */
 @Override
 public void deleteNameVO(NameVO name) {
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TABLE_NAME, KEY_ID + " = ?",
    new String[] { String.valueOf(name.getId()) });
  db.close();
 }


 @Override
 public void deleteAllNames(){
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TABLE_NAME, null, null);
  db.close();
 }
}



Create an activity to add and display the values in a layout

com.example.SQLiteDemo.MainActivity.java


/**
 *
 */
package com.example.SQLiteDemo;


import java.util.List;


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


import com.example.SQLiteDemo.db.DBHandler;
import com.example.SQLiteDemo.vo.NameVO;
import com.example.SQLiteDemo.R;


public class MainActivity extends Activity {
 private TextView names;
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  StringBuffer details=new StringBuffer();
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  this.names= (TextView) findViewById(R.id.names);
  DBHandler handler=new DBHandler(this);
  
  //Deleting existing rows
  handler.deleteAllNames();
  
        // Inserting names
  handler.addNameVO(new NameVO(1,"X", "X"));
  handler.addNameVO(new NameVO(2,"Y", "Y"));
  handler.addNameVO(new NameVO(3,"Z", "Z"));
  handler.addNameVO(new NameVO(4,"W", "W"));


        // Reading all names
        List<namevo> nameVOList = handler.getAllNameVO();      


        for (NameVO name : nameVOList) {
         details.append("Id: "+name.getId()+" ,First Name: " + name.getFirstName() + " ,Last Name: " + name.getLastName()).append("\n");
        }
        names.setText(details.toString());
 }


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


}



Layout used in this app:

Layout I have used in this application is given below

/res/layout/activity_main.xml


<relativelayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity" >       
    <TextView
        android:id="@+id/names"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="10pt"
        android:textColor="#444444"
        android:layout_alignParentLeft="true"
        android:layout_marginRight="9dip"
        android:layout_marginTop="20dip"
        android:layout_marginLeft="100dip"/> 
</relativeLayout> 





We are done. Now run the application



Source code of this application
 SQLiteDemo.zip

 



Reactions:

3 comments :

  1. Replies
    1. Please find the source code zip above.

      Delete
  2. Hey I want to know something that,Can I run a program through the another application in Android..?in the same form in our main app..

    ReplyDelete