10.1: SQLite Database

Contents:

This chapter discusses the Android framework's SQLiteDatabase and SQLiteOpenHelper classes. It is not an introduction to SQLite or SQL databases.The chapter assumes that you are familiar with SQL databases in general, and basic SQL query building. Check out the SQL Primer chapter if you need a refresher.

Of the many storage options discussed, using a SQLite database is one of the most versatile, and straightforward to implement.

  • An SQLite database is a good storage solution when you have structured data that you need to store persistently and access, search, and change frequently.
  • You can use the database as the primary storage for user or app data, or you can use it to cache and make available data fetched from the cloud.
  • If you can represent your data as rows and columns, consider a SQLite database.
  • Content providers, which will be introduced in a later chapter, work excellently with SQLite databases.

When you use an SQLite database, represented as an SQLiteDatabase object, all interactions with the database are through an instance of the SQLiteOpenHelper class which executes your requests and manages your database for you. Your app should only interact with the SQLiteOpenHelper, which will be described below.

There are two data types associated with using SQLite databases in particular, Cursor and ContentValues.

Cursor

The SQLiteDatabase always presents the results as a Cursor in a table format that resembles that of a SQL database.

You can think of the data as an array of rows. A cursor is a pointer into one row of that structured data. The Cursor class provides methods for moving the cursor through the data structure, and methods to get the data from the fields in each row.

The Cursor class has a number of subclasses that implement cursors for specific types of data.

  • SQLiteCursor exposes results from a query on a SQLiteDatabase. SQLiteCursor is not internally synchronized, so code using a SQLiteCursor from multiple threads should perform its own synchronization when using the SQLiteCursor.
  • MatrixCursor is an all-rounder, a mutable cursor implementation backed by an array of objects that automatically expands internal capacity as needed.

Some common operations on cursor are:

  • getCount() returns the number of rows in the cursor.
  • getColumnNames() returns a string array holding the names of all of the columns in the result set in the order in which they were listed in the result.
  • getPosition() returns the current position of the cursor in the row set.
  • Getters are available for specific data types, such as getString(int column) and getInt(int column).
  • Operations such as moveToFirst() and moveToNext() move the cursor.
  • close() releases all resources and makes the cursor completely invalid. Remember to call close to free resources!

Processing cursors

When a method call returns a cursor, you iterate over the result, extract the data, do something with the data, and finally, you must close the cursor to release the memory. Failing to do so can crash your app when it runs out of memory.

The cursor starts before the first result row, so on the first iteration you move the cursor to the first result if it exists. If the cursor is empty, or the last row has already been processed, then the loop exits. Don't forget to close the cursor once you're done with it. (This cannot be repeated too often.)

// Perform a query and store the result in a Cursor
Cursor cursor = db.rawQuery(...);
try {
    while (cursor.moveToNext()) {
        // Do something with the data
     }
} finally {
    cursor.close();
}

When you use a SQL database, you can implement your SQLiteOpenHelper class to return the cursor to the calling activity or adapter, or you can convert the data to a format that is more suitable for the adapter. The advantage of the latter is that managing the cursor (and closing it) is handled by the open helper, and your user interface is independent of what happens at the backend. See the SQLite Database practical for an implementation example.

ContentValues

Similar to how extras stores data, an instance of ContentValues stores data as key-value pairs, where the key is the name of the column and the value is the value for the cell. One instance of ContentValues represents one row of a table.

The insert() method for the database requires that the values to fill a row are passed as an instance of ContentValues.

ContentValues values = new ContentValues();
// Insert one row. Use a loop to insert multiple rows.
values.put(KEY_WORD, "Android");
values.put(KEY_DEFINITION, "Mobile operating system.");

db.insert(WORD_LIST_TABLE, null, values);

Implementing an SQLite database

To implement a database for your Android app, you need to do the following.

  1. (Recommended) Create a data model.

  2. Subclass SQLiteOpenHelper

    1. Use constants for table names and database creation query

    2. Implement onCreate to create the SQLiteDatabase with tables for your data

    3. Implement onUpgrade()

    4. Implement optional methods

  3. Implement the query(), insert(), delete(), update(), count() methods in SQLiteOpenHelper.
  4. In your MainActivity, create an instance of SQLiteOpenHelper.
  5. Call methods of SQLiteOpenHelper to work with your database.

Caveats:

  • When you implement the methods, always put database operations into try/catch blocks.
  • The sample apps do not validate the user data. When you write an app for publication, always make sure user data is what you expect to avoid the injection of bad data or execution of malicious SQL commands into your database.

Data model

It is a good practice to create a class that represents your data with getters and setters.

For an SQLite database, an instance of this class could represent one record, and for a simple database, one row in a table.

public class WordItem {
    private int mId;
    private String mWord;
    private String mDefinition;
    // Getters and setters and more
}

Subclass SQLiteOpenHelper

Any open helper you create must extend SQLiteOpenHelper.

public class WordListOpenHelper extends SQLiteOpenHelper {

    public WordListOpenHelper(Context context) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
       Log.d(TAG, "Construct WordListOpenHelper");
    }
}

Define constants for table names

While not required, it is customary to declare your table, column, and row names as constants. This makes your code a lot more readable, makes it easier to change names, and your queries will end up looking a lot more like SQL. You can do this in the open helper class, or in a separate public class; you will learn more about this in the chapter about content providers.

    private static final int DATABASE_VERSION = 1;
  // has to be 1 first time or app will crash
  private static final String WORD_LIST_TABLE = "word_entries";
  private static final String DATABASE_NAME = "wordlist";

  // Column names...
  public static final String KEY_ID = "_id";
  public static final String KEY_WORD = "word";

  // ... and a string array of columns.
  private static final String[] COLUMNS = {KEY_ID, KEY_WORD};

Define query for creating database

You need a query that creates a table to create a database. This is also customarily defined as a string constant. This basic example creates one table with a column for an auto-incrementing id and a column to hold words.

    private static final String WORD_LIST_TABLE_CREATE =
            "CREATE TABLE " + WORD_LIST_TABLE + " (" +
             KEY_ID + " INTEGER PRIMARY KEY, " +
             // will auto-increment if no value passed
             KEY_WORD + " TEXT );";

Implement onCreate() and create the database

The onCreate method is only called if there is no database. Create your tables in the method, and optionally add initial data.

@Override
public void onCreate(SQLiteDatabase db) { // Creates new database
   db.execSQL(WORD_LIST_TABLE_CREATE); // Create the tables
   fillDatabaseWithData(db); // Add initial data
   // Cannot initialize mWritableDB and mReadableDB here, because
   // this creates an infinite loop of on Create()
   // being repeatedly called.
}

Implement onUpgrade()

This is a required method.

If your database acts only as a cache for data that is also stored online, you can drop the the tables and recreate them after the upgrade is complete.

Note: If your database is the main storage, you must preserve the user's data before you do this as this operation destroys all the data. See the chapter on Storing Data. 
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // SAVE USER DATA FIRST!!!
        Log.w(WordListOpenHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + WORD_LIST_TABLE);
        onCreate(db);
}

Optional methods

The open helper class provides additional methods that you can override as needed.

  • onDowngrade()—The default implementation rejects downgrades.
  • onConfigure()—called before onCreate. Use this only to call methods that configure the parameters of the database connection.
  • onOpen()—Any work other than configuration that needs to be done after the database is opened.

Database operations

While you can call your methods in the open helper anything you want and have them return anything you choose to the calling activity, it is a good idea to go with the standardized query(), insert(), delete(), update(), count() methods that match the API of the database and content providers. Using this format will make it easier to add a content provider or loader in the future, and it makes it easier for other people to understand your code.

The following diagram shows how the different API's should be designed for consistency and clarity. Design your API to be consistent using query

query()

The query method that you implement in your open helper class can take and return any data type that your user interface needs.

Since the open helper provides convenience methods for inserting, deleting, and updating rows, your query method does not need to be generic and support these operations.

In general, your query method should only allow queries that are needed by your app and not be general purpose.

The database provides two methods for sending queries: SQLiteDatabase.rawQuery() and SQLiteDatabase.query(), with several options for the arguments.

SQLiteDatabase.rawQuery()

The open helper query method can construct an SQL query and send it as a rawQuery to the database which returns a cursor. If your data is supplied by your app, and under your full control, you can use rawQuery().

rawQuery(String sql, String[] selectionArgs)
  • The first parameter to db.rawquery() is an SQLite query string.
  • The second parameter contains the arguments.
    cursor = mReadableDB.rawQuery(queryString, selectionArgs);

SQLiteDatabase.query()

If you are processing user-supplied data, even after validation, it is more secure to construct a query and use a version of the SQLiteDatabase.query() method for the database. The arguments are what you'd expect in SQL and are documented in the SQLiteDatabase documentation.

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

Here is a basic example:

String[] columns = new String[]{KEY_WORD};
String where =  KEY_WORD + " LIKE ?";
searchString = "%" + searchString + "%";
String[] whereArgs = new String[]{searchString};
cursor = mReadableDB.query(WORD_LIST_TABLE, columns, where, whereArgs, null, null, null);

Example of complete open helper query()

public WordItem query(int position) {
   String query = "SELECT  * FROM " + WORD_LIST_TABLE +
           " ORDER BY " + KEY_WORD + " ASC " +
           "LIMIT " + position + ",1";

   Cursor cursor = null;
   WordItem entry = new WordItem();

   try {
       if (mReadableDB == null) {mReadableDB = getReadableDatabase();}
       cursor = mReadableDB.rawQuery(query, null);
       cursor.moveToFirst();
       entry.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
       entry.setWord(cursor.getString(cursor.getColumnIndex(KEY_WORD)));
   } catch (Exception e) {
       Log.d(TAG, "EXCEPTION! " + e);
   } finally {
       // Must close cursor and db now that we are done with it.
       cursor.close();
       return entry;
   }
}

insert()

The open helper's insert() method calls SQLiteDatabase.insert(), which is a SQLiteDatabase convenience method to insert a row into the database. (It's a convenience method, because you do not have to write the SQL query yourself.)

Format

long insert(String table, String nullColumnHack, ContentValues values)
  • The first argument is the table name.
  • The second argument is a String nullColumnHack. It's a workaround that allows you to insert empty rows. See the documentation for insert(). Use null.
  • The third argument must be a ContentValues container with values to fill the row. This sample only has one column; for tables with multiple columns, you add the values for each column to this container.
  • The database method returns the id of the newly inserted item, and you should pass that on to the application.

Example

newId = mWritableDB.insert(WORD_LIST_TABLE, null, values);

delete()

The open helper delete method calls the databases delete() method, which is a convenience method so that you do not have to write the full SQL query.

Format

int delete (String table, String whereClause, String[] whereArgs)
  • The first argument is the table name.
  • The second argument is a WHERE clause.
  • The third argument are the arguments to the WHERE clause.

You can delete using any criteria, and the method returns the number of items that were actually deleted, which the open helper should return also.

Example

deleted = mWritableDB.delete(WORD_LIST_TABLE,
                    KEY_ID + " =? ", new String[]{String.valueOf(id)});

update()

The open helper update method calls the database's update() method, which is a convenience method so that you do not have to write the full SQL query. The arguments are familiar from previous methods, and the onUpdate returns the number of rows updated.

Format

int update(String table, ContentValues values,
    String whereClause, String[] whereArgs)
  • The first argument is the table name.
  • The second argument must be a ContentValues with new values for the row.
  • The third argument is a WHERE clause.
  • The fourth argument are the arguments to the WHERE clause.

Example

ContentValues values = new ContentValues();
values.put(KEY_WORD, word);
mNumberOfRowsUpdated = mWritableDB.update(WORD_LIST_TABLE,
values, // new values to insert
KEY_ID + " = ?",
new String[]{String.valueOf(id)});

count()

The count() method returns the number of entries in the database. If you are using a RecyclerView.Adapter, it has to implement getItemCount(), which needs to get the number of rows from the open helper which needs to get it from the database.

In adapter

@Override
public int getItemCount() {
    return (int) mDB.count();
}

In the open helper

public long count(){
    if (mReadableDB == null) {mReadableDB = getReadableDatabase();}
    return DatabaseUtils.queryNumEntries(mReadableDB, WORD_LIST_TABLE);
}

queryNumEntries()) is a method in the public DatabaseUtils class, which provides many convenience methods for working with cursors, databases, and also content providers.

Instantiate Open Helper

To get a handle to the database, In MainActivity, in onCreate, call:

mDB = new WordListOpenHelper(this);

Working with the database

It is a common pattern to combine a SQLiteDatabase backend with a RecyclerView to display the data.
Common components used with an app that uses a SQLite database

For example:

  • Pressing the FAB could start an activity that gets input from the user and stores it into the database as a new or updated item.
  • Swiping an item might delete it after the user confirms deletion.

Transactions

Use transactions

  • when performing multiple operations that all need to complete to keep database consistent, for example, updating pricing of related items for a sale event.
  • to batch multiple independent operations to improve performance, such as mass inserts.

Transactions can be nested, and the SQLiteDatabase class provides additional methods to manage nested transactions. See SQLiteDatabase references documentation.

Transaction idiom

db.beginTransaction();
try {
  ...
  db.setTransactionSuccessful();
} finally {
  db.endTransaction();
}

Backing up databases

It is a good idea to back up your app's database.

You can do so using the Cloud Backup options discussed in the Storage Options chapter.

Shipping a database with your app

Sometimes you may want to include a populated database with your app. There are several ways in which to do that, and there are trade-offs for each.

  • Include the SQL commands with the application and have it create the database and insert the data on first use. This is basically what you will do in the practical for data storage. If the amount of data you want put in the database is small, just an example so that the user gets to see something, you can use this method.
  • Ship the data with the APK as a resource, and build the database when the user opens the app for the first time. This is similar to the first method, but instead of defining your data in your code, you put it in a resource, for example, in CSV format. You can then read the data with an input stream and add it to the database.
  • Build and pre-populate the SQLite database and include it in the APK. With this method you write an app that creates and populates a database. You can do this on the emulator. You then copy the file in which your database is actually stored ( "/data/data/YOUR_PACKAGE/databases/" directory) and include it as an asset with your app. When the app is started for the first time, you copy the database file back into the "/data/data/YOUR_PACKAGE/databases/" directory.

The SQLiteAssetHelper class, which you can download from Github, extends SQLiteOpenHelper to help you do this. And this Stackoverflow post discusses this topic in more detail.

Note that for a larger database, populating the database should be done in the background, and your app should not crash if there is no database yet, or the database is empty.

The related practical documentation is in Android Developer Fundamentals: Practicals.

Learn more

results matching ""

    No results matching ""