Android Tutorial: SQLite Database

 ·  · 

Tutorial about how to use SQLite database in Android application.

 

1 What is SQLite

SQLite is an embedded SQL database engine. It is self-contained, serverless, zero-configuration, transactional and uses standard SQL language for executing queries.

Each Android application has its own SQLite database. This database is separated from other applications and saved in internal storage. The SQLite database of an Android application can only be accessed by classes in the application itself, but not others. So it is a good way to store complex private data of application using SQLite database.

Programmatically created SQLite database in an application is stored under path: /data/data/<YOUR_APP_PACKAGE_NAME>/databases/.

android_sqlite_database_save_path

 

2 How to use SQLite in Android

2.1 SQLiteOpenHelper and SQLiteDatabase

SQLiteOpenHelper is an abstract class which is responsible for creating or upgrading your SQLite databases and returning SQLiteDatabase object instance. You must create your own subclass of this abstract class.

SQLiteDatabase is the class which has APIs to handle database operations such as insert, query, update and delete.

2.2 Architecture overview

There are two ways of using database in Android application.

The first way is to call the SQLite database APIs directly wherever you need.

android_sqlite_architecture_01

The second design is to create an adapter or utility class to encapsulate database operations.

android_sqlite_architecture_02

2.3 Steps

Common steps of using SQLite database in Android.

  1. Create a subclass of SQLiteOpenHelper and implement two methods: onCreate() and onUpgrade(). This class will take care of opening the database if it exists, creating it if it doesn't, and upgrading it as necessary.
    • onCreate(): this will be called automatically if the database doesn't exist. And it won't be called any more if the database exists.
    • onUpgrade(): if current version of the database is higher than the old, this method will be called to perform upgrading operations.
  2. Obtain SQLiteDatabase object instance by calling getWritableDatabase() or getReadableDatabase() method of your subclass of SQLiteOpenHelper.
  3. Perform insert, query, update, delete operations in your application with the SQLiteDatabase instance.

 

3 APIs

3.1 Create database

Main step of creating a database is implementing a subclass of SQLiteOpenHelper and define database schema in its onCreate() method.

package com.codevoila.androidtutorial;

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

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    // database name
    private static final String DATABASE_NAME = "bank";

    // table name
    private static final String TABLE_NAME = "accounts";

    // database version
    private static final int DATABASE_VERSION = 1;

    // names of the table columns
    private static final String UID = "_id";
    private static final String NAME = "name";
    private static final String AMOUNT = "amount";

    // sql to create table
    private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
            + " ("
            + UID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + NAME + " VARCHAR(255), "
            + AMOUNT + " REAL"
            + ");";

    // sql to drop table
    private static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;

    // custom constructor
    public MySQLiteOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_TABLE);
        onCreate(db);
    }
}

onCreate() method will be called when the database is created for the first time. Creation of tables and initial data of tables should be put here.

onUpgrade() method will be called when the database needs to be upgraded. This method is used to drop tables, add tables, alter tables or do anything else it needs to upgrade to the new schema version.

3.2 Get database instance

You can call getWritableDatabase() or getReadableDatabase() method of SQLiteOpenHelper to get the SQLiteDatabase object instance. The returned SQLiteDatabase object by these two methods actually represents an instance of your SQLite database with which you can perform whatever operations you want.

Normally both getWritableDatabase() and getReadableDatabase() will create (if not exists) and open a database, then return the database object that can be used for reading and writing.

But if the disk storage is full or there is some permission error, calling of getWritableDatabase() may fail and cause error. By contrast, a read-only database object will be returned by getReadableDatabase() in that case.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getWritableDatabase();

// ...

db.close();

3.3 Insert

3.3.1 Use SQL statement

The first way to insert data is to execute a single SQL statement by execSQL() method of SQLiteDatabase.

public void execSQL(String sql, Object[] bindArgs)

For example, insert a new record into accounts table.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getWritableDatabase();
db.execSQL("INSERT INTO accounts(name, amount) VALUES(?, ?)", new Object[]{"Jack", 3000});
db.close();

Pay attention to return value of execSQL() method. It is always void, so you can't get any result of each calling.

3.3.2 Use ContentValues

The second way to insert data is to call insert() method of SQLiteDatabase and pass ContentValues object.

public long insert(String table, String nullColumnHack, ContentValues values)

Also an example of inserting a new record into account table.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getWritableDatabase();

ContentValues values = new ContentValues();
contentValues.put("name", "Lucy");
contentValues.put("amout", 4000);

long id = db.insert("account", null, values);

db.close();

The row ID of the newly inserted row will be returned, or -1 will be returned if an error occurred.

Note: SQLite does not allow you insert a completely empty row. Following way is wrong!

// Wrong way
ContentValues values = new ContentValues();
long id = db.insert("account", null, values);

To achieve this purpose, you must explicitly specify parameter nullColumnHack with the name of any one column in your table that can be NULL.

// Correct way
ContentValues values = new ContentValues();
long id = db.insert("account", "amount", values);

3.4 Query or select

First of all, you couldn't use execSQL() to query data. Because execSQL() method has no return value.

Alternatively, you can use rawQuery() or query() method of SQLiteDatabase class to select and query data.

3.4.1 rawQuery()

Method signature of rawQuery() method:

public android.database.Cursor rawQuery(String sql, String[] selectionArgs)

The return value is a Cursor object which provides random read-write access to the result set returned by the query.

Cursor cursor = db.rawQuery("SELECT name, amount FROM accounts WHERE name = ?", new String[]{"Lucy"});
3.4.2 query()

The second method to query rows is query().

public Cursor query(String table, String[] columns, 
                    String selection, String[] selectionArgs, 
                    String groupBy, String having, 
                    String orderBy, String limit)

The second parameter columns is a list of which columns to return. Passing null will return all columns, which is equivalent to SELECT * in SQL statement and is discouraged to prevent reading redundant data that isn't going to be used.

For example, query people whose amounts are top three.

// SELECT * FROM accounts ORDER BY amount DESC LIMIT 3
Cursor cursor = db.query("accounts", null, 
                         null, null, 
                         null, null, 
                         "amount DESC", "3");
3.4.3 Using of Cursor: traverse query results

As mentioned above, Cursor object provides read-write access to the result set. And the Cursor is positioned before the first element of the result rows.

You can call moveToNext() method to move the Cursor position forward. If Cursor is not positioned at the bottom, moveToNext() returns true. Otherwise it returns false.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getReadableDatabase();

// SELECT * FROM accounts ORDER BY amount DESC LIMIT 3
Cursor cursor = db.query("accounts", null, null, null, null, null, "amount DESC", "3");

String msg = null;
StringBuffer buffer = new StringBuffer();

while(cursor.moveToNext()) {
    String name = cursor.getString(cursor.getColumnIndex("name"));
    float amount = cursor.getFloat(cursor.getColumnIndex("amount"));
    buffer.append(name + ", " + amount + "\n");
}
db.close();

if (buffer.toString().isEmpty()) {
    msg = "Query result is empty!";
} else {
    msg = buffer.toString();
}

Toast.makeText(this, msg, Toast.LENGTH_LONG).show();

3.5 Update

update() method also works along with ContentValues.

public int update(String table, ContentValues values, String whereClause, String[] whereArgs)

The returned value is the number of rows affected.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getReadableDatabase();

ContentValues values = new ContentValues();
values.put("amount", 5000);
int rows = db.update("accounts", values, "name = ?", new String[]{"Jack"});

db.close();

String msg = "Totoally " + rows + " updated!";
Toast.makeText(this, msg, Toast.LENGTH_LONG).show();

3.6 Delete

Using of delete() method is similar as update().

public int delete(String table, String whereClause, String[] whereArgs)

The returned value is the number of rows deleted. The parameter whereClause is optional. Passing null or "1" will delete all rows.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getReadableDatabase();

int rows = db.delete("accounts", "name = ?", new String[]{"Jack"});
db.close();

String msg = "Totoally " + rows + " rows deleted!";
Toast.makeText(this, msg, Toast.LENGTH_LONG).show();

3.7 Transactions

Following is a simple example about how to transfer money between two bank accounts using Android transactions.

MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
SQLiteDatabase db = mySQLiteOpenHelper.getReadableDatabase();

// Insert two new accounts
// Tom with $4000, Jerry with $1000
float moneyOfTom = 4000;
float moneyOfJerry = 1000;
db.execSQL("INSERT INTO accounts(name, amount) VALUES(?, ?)", new Object[]{"Tom", moneyOfTom});
db.execSQL("INSERT INTO accounts(name, amount) VALUES(?, ?)", new Object[]{"Jerry", moneyOfJerry});


try {
    // Begin transactions
    db.beginTransaction();

    // Transfer $1000 from Tom to Jary
    moneyOfTom -= 1000;
    moneyOfJerry += 1000;

    // Update Tom's account
    values = new ContentValues();
    values.put("amount", moneyOfTom);
    db.update("accounts", values, "name = ?", new String[]{"Tom"});

    values.clear();

    // Update Jerry's account
    values.put("amount", moneyOfJerry);
    db.update("accounts", values, "name = ?", new String[]{"Jerry"});

    // Mark the current transaction as successful.
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
    db.close();
}