Working with SQLite Database (CRUD operations) in Android

We’ve covered data storage options like SharedPreferences and Internal/External Storage before. It’s time to look at another option which is basically SQLite databases. Most data-driven Android applications will need a solution to store structured (relational) data. If that’s the case then SQLite is an amazing option that Android supports out of the box. The SQLite database management classes/APIs are available in the android.database.sqlite package. We’ll learn how to create an SQLite database and then save data into it as well as retrieve, modify and delete data.

Note: This article will not cover the basics of database related concepts like tables, columns, rows, select/insert/update/delete queries or even relational data. I assume you’re already familiar with the basics of a RDBMS (relational database management system). If you’ve worked with MySQL, MariaDB, PostgreSQL, SQLite, Oracle, SQL Server or any other SQL based DB before then you’ll be familiar with these concepts already.

Creating a Database

A database is created by creating a subclass of SQLiteOpenHelper and executing an SQL command (query) in the onCreate() method. Let’s see some code to clarify this:

public class PostDatabase extends SQLiteOpenHelper {

    private static final String TAG = "PostDatabase";

    // Database Specific Details
    
    // If you change the database schema, you must increment the database version.
    private static final int DB_VERSION = 1;
    // DB Name, same is used to name the sqlite DB file
    private static final String DB_NAME = "test_db";

    // `posts` table details
    
    public static final String TABLE_POSTS = "posts";
    public static final String ID = "id";
    public static final String COL_TITLE = "title";
    public static final String COL_CONTENT = "content";

    private static final String CREATE_TABLE_POSTS =
            "CREATE TABLE " + TABLE_POSTS
            + " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COL_TITLE + " TEXT NOT NULL, "
            + COL_CONTENT + " TEXT NOT NULL);";


    public PostDatabase(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Called when the database is created for the
        // first time. This is where the creation of
        // tables and the initial population of the tables should happen.

        db.execSQL(CREATE_TABLE_POSTS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Called when the database needs to be upgraded.
        // The implementation should use this method to
        // drop tables, add tables, or do anything else
        // it needs to upgrade to the new schema version.

        Log.w(TAG, "Upgrading database. Existing contents will be lost. ["
                + oldVersion + "] -> [" + newVersion + "]");

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_POSTS);

        // Create after dropping
        onCreate(db);
    }
}

So we created a PostDatabase class that extends SQLiteOpenHelper. You’ll notice several constants defining database name, database version, table names, table fields (columns), table creation SQL query, etc. This might seem a little strange if you’re coming from web development or some other domain where such practises were not follower but this is what Android recommends. This way you can use the same constants across all the classes in the same package and change in the value/name of one constant in this class will propagate everywhere else. If you’re familiar with SQL and OOP then all this should be a piece of cake for your really.

The database and its tables (single table in our case) are created in the onCreate() method whereas if the DB_VERSION is changed (bumped to say 2) then onUpgrade() will be triggered next time some class tries to access the database.

Contract Class

This class that we coded is also known as a contract class. A contract class explicitly specifies the schema (how a database and its tables are organized) in a systematic and self-documenting way. It contains constants that define URIs, table names, column names, etc. These constants can be consistently use across all the classes in the same package.

There’s another good recommended way to organize a contract class. Let’s see an example:

public final class DatabaseContract {

    public static abstract class Post implements BaseColumns {
        public static final String TABLE_NAME = "posts";
        public static final String COL_TITLE = "title";
        public static final String COL_CONTENT = "content";
    }
}

So any details/definitions global to the database can be put in the root level whereas for every table an inner class is created listing its columns that can be used by your SQLiteOpenHelper implementation to generate the CREATE and DROP SQL queries. Infact these queries could also reside in the contract classes depending upon your usage.

If you notice we implemented the BaseColumns interface that makes Post inner class inherit a field called _ID which is the unique primary key field required by some Android classes like the cursor adapters. Hence this can help your DB to work seamlessly with the Android framework. So this basically means that instead of having your ID column’s name as id or something else, it should be _id which is the value of BaseColumns._ID.

You must be wondering that we just defined our schema and create/update operations but how or when is the database and its tables created ? We’ll cover that in the next section (hint: on calling getWritableDatabase()).

Reading to/Writing From the Database

Since our DB definition is sorted, now we need to look at how to start querying our DB and even writing to it. So what we have to basically do is, instantiate our SQLiteOpenHelper implementation and call getReadableDatabase() (read-only) or getWritableDatabase() (read and write) on it. When getWritableDatabase() is called for the first time, the database will be opened and onCreate(), onUpgrade() and/or onOpen() will be called.

// From MainActivity.onCreate()

PostDatabase DB = new PostDatabase(MainActivity.this);
SQLiteDatabase sqlDB = DB.getWritableDatabase();

// Execute queries...

sqlDB.close();

The call the getWritableDatabase() returns an object of SQLiteDatabase. This class has all the methods to interact (read/write) with the database.

The newly created database is private to the application and is created at DATA/data/APP_NAME/databases/FILENAME where DATA is what Environment.getDataDirectory() returns (generally /data), APP_NAME is your package name and FILENAME is the database name specified in the SQLiteOpenHelper implementation.

Create Data (INSERT)

Let’s write some code to see how we can create records in the Database.

ContentValues values = new ContentValues();
values.put(PostDatabase.COL_TITLE, "Test Title");
values.put(PostDatabase.COL_CONTENT, "Test Content");

sqlDB.insert(PostDatabase.TABLE_POSTS, null, values);

We used the insert() method to insert a single record. You can inspect the database file from ADB like this:

$ adb shell
root@vbox86p:/ # sqlite3 /data/data/com.pycitup.pyc/databases/test_db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
android_metadata  posts
sqlite> select * from posts;
1|Test Title|Test Content

Check the last two lines where I select all the records from the posts table and it prints the record that we had inserted. sqlite3 database tool ships with the Android SDK that lets you inspect and browse the contents of a database and its tables. It is a standalone command line program that lets you perform many other useful functions too. More information here.

You’ll notice that we used a class called ContentValues to set key/value pairs for insertion. It’s just a convenience class for inserting and updating entries in a database table.

Read/Retrieve Information (SELECT)

There are a couple of ways to fetch data from a particular table. Let’s see how:

// Insert a record

ContentValues values = new ContentValues();
values.put(PostDatabase.COL_TITLE, "Test Title");
values.put(PostDatabase.COL_CONTENT, "Test Content");

sqlDB.insert(PostDatabase.TABLE_POSTS, null, values);

// Raw Query
Cursor cursor = sqlDB.rawQuery("SELECT * FROM posts WHERE id = ?", new String[]{ "1" });

if (cursor != null && cursor.moveToFirst()) {
    String title = cursor.getString(cursor.getColumnIndex(PostDatabase.COL_TITLE));
    String content = cursor.getString(cursor.getColumnIndex(PostDatabase.COL_CONTENT));

    // Dumps "Title: Test Title Content: Test Content"
    Log.d(TAG, "Title: " + title + " Content: " + content);

    cursor.close();
}


// Another insertion (second)

values = new ContentValues();
values.put(PostDatabase.COL_TITLE, "Second Title");
values.put(PostDatabase.COL_CONTENT, "Second Content");

sqlDB.insert(PostDatabase.TABLE_POSTS, null, values);


// Now we'll use the `query()` method
// public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

cursor = sqlDB.query(PostDatabase.TABLE_POSTS,
        new String[] { PostDatabase.ID, PostDatabase.COL_TITLE, PostDatabase.COL_CONTENT },
        null, null, null, null, null);

if (cursor != null) {
    int idIndex = cursor.getColumnIndex(PostDatabase.ID);
    int titleIndex = cursor.getColumnIndex(PostDatabase.COL_TITLE);
    int contentIndex = cursor.getColumnIndex(PostDatabase.COL_CONTENT);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        int id = cursor.getInt(idIndex);
        String title = cursor.getString(titleIndex);
        String content = cursor.getString(contentIndex);

        // Dumps (2 rows):
        // ID: 1 Title: Test Title Content: Test Content
        // ID: 2 Title: Second Title Content: Second Content
        Log.d(TAG, "ID: " + id + " Title: " + title + " Content: " + content);

        cursor.moveToNext();
    }
}

So we learnt how to use the rawQuery() and query() method to query our database tables. This is the syntax of the query() method:

Cursor c = db.query(
    PostDatabase.TABLE_POSTS,                 // The table to query
    projection,                               // The columns to return
    selection,                                // The columns for the WHERE clause
    selectionArgs,                            // The values for the WHERE clause
    null,                                     // SQL GROUP BY (null = don't group)
    null,                                     // SQL HAVING (null = include all rows)
    sortOrder                                 // The sort order
);

One thing you’ll notice is that, a query operation returns a Cursor that basically points to all the rows returned by the query. Using a Cursor interface we can read from a resultset returned by a database query operation and also navigate through it.

To get a particular row’s data we first move the Cursor there before reading values. You’ll notice moveToFirst() method being used right after querying. That is done so that the cursor’s position is placed on the first entry of the result set. Then you can use various methods like getString() or getInt() to get a column’s value. These methods accept the column index which can be obtained by calling getColumnIndex() or getColumnIndexOrThrow() on the cursor object and passing the column/field string name to it.

You can create a model class for posts or any another table with getters and setters representing each rows if you want. You can then create an ArrayList of such models and show in a ListView for instance. This approach can prove to be really useful while coding applications where you take data from your database and show up in a particular format in the user interface.

Update Records (UPDATE)

Time to see how to update() records based on a WHERE condition.

// New value for one column
ContentValues values = new ContentValues();
values.put(PostDatabase.COL_TITLE, "Second title changed");

// Which row to update, based on the ID
String whereClause = PostDatabase.ID + " LIKE ?";
String[] whereArgs = { "2" };

int affectedRows = sqlDB.update(
                        PostDatabase.TABLE_POSTS,
                        values,
                        whereClause,
                        whereArgs
                    );

// Dumps 1
Log.d(TAG, String.valueOf(affectedRows));

Delete Records (DELETE)

Let’s see how to delete() records from a table based on a condition (WHERE clause).

// Define 'where' part of query.
String whereClause = PostDatabase.ID + " = ?";
// Specify arguments in placeholder order.
String[] whereArgs = { "1" };
// Issue SQL statement.
sqlDB.delete(PostDatabase.TABLE_POSTS, whereClause, whereArgs);

Passing null as the whereClause will delete all the rows in the table.

SQLiteQueryBuilder

SQLiteQueryBuilder is basically a convenience class for building complex queries that contains JOINs, unions, column aliases, multiple tables, subqueries, etc. and send them to SQLiteDatabase objects.

For instance it has a convenient method called appendWhere() that we can call separately to add a chunk to the WHERE clause of the query.

SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
queryBuilder.setTables(PostDatabase.TABLE_POSTS);
queryBuilder.appendWhere("id = 1");

// Dumps "SELECT * FROM posts WHERE (id = 1)"
Log.d(TAG, queryBuilder.buildQuery(null, null, null, null, null, null));

// queryBuilder.query(DB.getReadableDatabase(), projection, selection, selectionArgs, null, null, sortOrder)

Here’s how you could build a JOIN (between posts and comments table):

queryBuilder.setTables(
                PostDatabase.TABLE_POSTS
                + " INNER JOIN "
                + PostDatabase.TABLE_COMMENTS
                + " ON "
                + PostDatabase.ID
                + " = "
                + (PostDatabase.TABLE_COMMENTS + "." + PostDatabase.COMMENT_POST_ID)
);

// queryBuilder.query(...)

Wrapping Up

So we learnt how to do basic CRUD (Create, Read, Update, Delete) operations in Android with SQLite. Next we should learn about a concept called Content Providers in Android that uses what you learnt in this article as the base.

Author: Rishabh

Rishabh is a full stack web and mobile developer from India. Follow me on Twitter. View all posts by Rishabh