Tutorial: Android SQLite Database

SQLite is one of several ways that Android provide to store data. SQLite is already come with vanilla Android OS and it is very light weight to be used under mobile environment. Below is the tutorial on how to create classes to handle SQLite database operations.

For the sake of simplicity, I only uses one table with only 3 columns to store cars information. The three columns are id (INT), brand (TEXT), and color (TEXT) like the following structures:

Table name: car
+-------+------+-----+
| Field | Type | Key |
+-------+------+-----+
| id    | INT  | PRI |
| brand | TEXT |     |
| color | TEXT |     |
+-------+------+-----+

The first thing you have to do is creating a class that represent the car table, hence the following is the car class for the above table:

public class Car {

    public int id;
    public String brand;
    public String color;

    // constructor
    public Car(int id, String brand, String color) {
        this.id = id;
        this.brand = brand;
        this.color = color;
    }

    // 2nd constructor
    public Car(String brand, String color) {
        this.brand = brand;
        this.color = color;
    }

}

In order to handle all CRUD (Create, Read/Retrieve, Update, Delete) method and operations to SQLite database we need to create our database handler class which extends the SQLiteOpenHelper class.

public class CarHandler extends SQLiteOpenHelper {

	private String CAR_TABLE = "car";
	private String KEY_ID = "id";
	private String FIELD_BRAND = "brand";
	private String FIELD_COLOR = "color";

	private static String databaseName = "CarDB";
	private static int version = 1;

	public CarHandler(Context context) {
		super(context, databaseName, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_CAR_TABLE = "CREATE TABLE " + CAR_TABLE + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + FIELD_BRAND + " TEXT,"
                + FIELD_COLOR + " TEXT)";
                db.execSQL(CREATE_CAR_TABLE);
	}

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

The database handler class need to override two method from SQLiteOpenHelper class. The first one is onCreate(), required as you first creating your SQLite database. And the second is onUpgrade(), where you require changes or modification to previously created database then this method will be executed.

Database CRUD Operations

The usual minimum requirement in database operations is CRUD. So we have to set up our CRUD methods. For example, here I have 4 more methods to be added to the handler class: add(), delete(), get(), update() as it’s basic CRUD methods. But yours may be fewer or more, depends on your application requirements.

public void add(Car car){
	SQLiteDatabase db = this.getWritableDatabase();
	ContentValues values = new ContentValues();
	values.put(FIELD_BRAND, car.brand);
	values.put(FIELD_COLOR, car.color);

	// Inserting Row
	db.insert(CAR_TABLE, null, values);
	db.close(); // Closing database connection
}

public Car get(int id){
	SQLiteDatabase db = this.getReadableDatabase();
	Cursor cursor = db.query(CAR_TABLE, new String[] { KEY_ID,
            FIELD_BRAND, FIELD_COLOR }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, 
            null, // group by 
            null, // having
            null, // order by
            null); // limit

	if (cursor != null) cursor.moveToFirst();
	    Car car = new Car(
	    	Integer.parseInt(cursor.getString(0)),
	    	cursor.getString(1),
	    	cursor.getString(2));
	    cursor.close();
	    db.close();
	    return car;
	}
}

public int update(Car car) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(FIELD_BRAND, car.brand);
    values.put(FIELD_COLOR, car.color);

    // updating row
    return db.update(CAR_TABLE, values, KEY_ID + " = ?",
            new String[] { String.valueOf(car.id) });
}

public int delete(PushNotification notification) {

    SQLiteDatabase db = this.getWritableDatabase();
    // deleting row
    return db.delete(CAR_TABLE, KEY_ID + " = ?",
        new String[] { String.valueOf(notification.getId()) });

}

That is the CRUD methods required for handling car table in SQLite database. As an additional method I add one method to retrieve all tuples (rows) contained in the table. The method named getAll() like below:

public ArrayList getAll(){

  ArrayList listCar = new ArrayList();

  SQLiteDatabase db = this.getReadableDatabase();
  Cursor cursor = db.rawQuery("SELECT * FROM " + CAR_TABLE, null);   try{
    if (cursor != null){
      if(cursor.moveToFirst()){
        do {
  	  Car car = new Car(
            Integer.parseInt(cursor.getString(0)),
            cursor.getString(1),
            cursor.getString(2) );
            listCar.add(car);
        } while(cursor.moveToNext());
      }
    }
  } finally {
    cursor.close();
    db.close();
  }
  return listCar;
}

Please be noted that you have to close open cursor and db when you don’t need them as they more likely to create a database or memory leak.

Database Handler Usage

The usage of database handler class is easy, code below is the example:

CarHandler db = new CarHandler(this);
// this: Activity class
// Inserting Car data
Log.d("Insert: ", "Inserting ..");

db.add(new Car("Mustang", "red"));
db.add(new Car("Porsche", "silver"));
db.add(new Car("Jazz", "white"));
db.add(new Car("X5", "black"));
// Reads all cars
Log.d("Reading: ","Reading all cars...");
ArrayList<Car> cars = db.getAll();
for(Car c: cars) {
    String data = "ID: "+c.id+", Brand: "+c.brand+", Color: "+c.color;
    Log.d("Car: ", data);
}