Storing Structured Data Using SQLite Databases Android

For occasions when your application requires a more robust data storage mechanism, the Android file system includes support for application-specific relational databases using SQLite. SQLite databases are lightweight and file-based, making them ideally suited for embedded devices.

These databases and the data within them are private to the application. To share application data with other applications, you must expose the data you want to share by making your application a content provider (discussed later in this chapter).

The Android SDK includes a number of useful SQLite database management classes. Many of these classes are found in the android.database.sqlite package. Here you can find utility classes for managing database creation and versioning, database management, and query builder helper classes to help you format proper SQL statements and queries. The package also includes specialized Cursor objects for iterating query results. You can also find all the specialized exceptions associated with SQLite.

Here we focus on creating databases within our Android applications. For that, we use the built-in SQLite support to programmatically create and use a SQLite database to store application information. However, if your application works with a different sort of database, you can also find more generic database classes (within the android.database package) to help you work with data from other providers.

In addition to programmatically creating and using SQLite databases, developers can also interact directly with their application’s database using the sqlite3 command-line tool that’s accessible through the ADB shell interface. This can be an extremely helpful debugging tool for developers and quality assurance personnel, who might want to manage the database state (and content) for testing purposes.

Creating a SQLite Database

You can create a SQLite database for your Android application in several ways. To illustrate how to create and use a simple SQLite database, let’s create an Android project called SimpleDatabase.

Creating a SQLite Database Instance Using the Application Context

The simplest way to create a new SQLiteDatabase instance for your application is to use the openOrCreateDatabase() method of your application Context, like this:

Finding the Application’s Database File on the Device File System

Android applications store their databases (SQLite or otherwise) in a special application directory:

So, in this case, the path to the database would be

You can access your database using the sqlite3 command-line interface using this path.

Configuring the SQLite Database Properties

Now that you have a valid SQLiteDatabase instance, it’s time to configure it. Some important database configuration options include version, locale, and the thread-safe locking feature.

Creating Tables and Other SQLite Schema Objects

Creating tables and other SQLite schema objects is as simple as forming proper SQLite statements and executing them. The following is a valid CREATE TABLE SQL statement. This statement creates a table called tbl_authors. The table has three fields: a unique id number, which auto-increments with each record and acts as our primary key, and firstname and lastname text fields:

You can encapsulate this CREATE TABLE SQL statement in a static final String variable (called CREATE_AUTHOR_TABLE) and then execute it on your database using the execSQL() method:

The execSQL() method works for nonqueries. You can use it to execute any valid SQLite SQL statement. For example, you can use it to create, update, and delete tables, views, triggers, and other common SQL objects. In our application, we add another table called tbl_books. The schema for tbl_books looks like this:

Unfortunately, SQLite does not enforce foreign key constraints. Instead, we must enforce them ourselves using custom SQL triggers. So we create triggers, such as this one that enforces that books have valid authors:

We can then create the trigger simply by executing the CREATE TRIGGER SQL statement:

We need to add several more triggers to help enforce our link between the author and book tables, one for updating tbl_books and one for deleting records from tbl_authors.

Creating, Updating, and Deleting Database Records

Now that we have a database set up, we need to create some data. The SQLiteDatabase class includes three convenience methods to do that. They are, as you might expect, insert(), update(), and delete().

Inserting Records

We use the insert() method to add new data to our tables. We use the ContentValues object to pair the column names to the column values for the record we want to insert. For example, here we insert a record into tbl_authors for J.K. Rowling:

The insert() method returns the id of the newly created record. We use this author id to create book records for this author.

You might want to create simple classes (that is, class Author and class Book) to encapsulate your application record data when it is used programmatically.

Updating Records

You can modify records in the database using the update() method. The update() method takes four arguments:

  • The table to update records
  • A ContentValues object with the modified fields to update
  • An optional WHERE clause, in which ? identifies a WHERE clause argument
  • An array of WHERE clause arguments, each of which is substituted in place of the ?’s from the second parameter

Passing null to the WHERE clause modifies all records within the table, which can be useful for making sweeping changes to your database.

Most of the time, we want to modify individual records by their unique identifier. The following function takes two parameters: an updated book title and a bookId. We find the record in the table called tbl_books that corresponds with the id and update that book’s title. Again, we use the ContentValues object to bind our column names to our data values:

Because we are not updating the other fields, we do not need to include them in the ContentValues object. We include only the title field because it is the only field we change.

Deleting Records

You can remove records from the database using the remove() method. The remove() method takes three arguments:

  • The table to delete the record from
  • An optional WHERE clause, in which ? identifies a WHERE clause argument
  • An array of WHERE clause arguments, each of which is substituted in place of the ?’s from the second parameter

Passing null to the WHERE clause deletes all records within the table. For example, this function call deletes all records within the table called tbl_authors:

Most of the time, though, we want to delete individual records by their unique identifiers. The following function takes a parameter bookId and deletes the record corresponding to that unique id (primary key) within the table called tbl_books:

You need not use the primary key (id) to delete records; the WHERE clause is entirely up to you. For instance, the following function deletes all book records in the table tbl_books for a given author by the author’s unique id:

Working with Transactions

Often you have multiple database operations you want to happen all together or not at all. You can use SQL Transactions to group operations together; if any of the operations fails, you can handle the error and either recover or roll back all operations. If the operations all succeed, you can then commit them. Here we have the basic structure for a transaction:

Now let’s look at the transaction in a bit more detail. A transaction always begins with a call to beginTransaction() method and a try/catch block. If your operations are successful, you can commit your changes with a call to the setTransactionSuccessful() method. If you do not call this method, all your operations are rolled back and not committed. Finally, you end your transaction by calling endTransaction(). It’s as simple as that.

In some cases, you might recover from an exception and continue with the transaction. For example, if you have an exception for a read-only database, you can open the database and retry your operations.

Finally, note that transactions can be nested, with the outer transaction either committing or rolling back all inner transactions.

Querying SQLite Databases

Databases are great for storing data in any number of ways, but retrieving the data you want is what makes databases powerful. This is partly a matter of designing an appropriate database schema, and partly achieved by crafting SQL queries, most of which are SELECT statements.

Android provides many ways in which you can query your application database. You can run raw SQL query statements (strings), use a number of different SQL statement< builder utility classes to generate proper query statements from the ground up, and bind specific user interface controls such as container views to your backend database directly.

Working with Cursors

When results are returned from a SQL query, you often access them using a Cursor found in the android.database.Cursor class. Cursor objects are rather like file pointers; they allow random access to query results.

You can think of query results as a table, in which each row corresponds to a returned record. The Cursor object includes helpful methods for determining how many results were returned by the query the Cursor represents and methods for determining the column names (fields) for each returned record. The columns in the query results are defined by the query, not necessarily by the database columns. These might include calculated columns, column aliases, and composite columns.

Cursor objects are generally kept around for a time. If you do something simple (such as get a count of records or in cases when you know you retrieved only a single simple record), you can execute your query and quickly extract what you need; don’t forget to close the Cursor when you’re done, as shown here:

Managing Cursors as Part of the Application Lifecycle

When a Cursor returns multiple records, or you do something more intensive, you need to consider running this operation on a thread separate from the UI thread. You also need to manage your Cursor.

Cursor objects must be managed as part of the application lifecycle. When the application pauses or shuts down, the Cursor must be deactivated with a call to the deactivate () method, and when the application restarts, the Cursor should refresh its data using the requery() method. When the Cursor is no longer needed, a call to close() must be made to release its resources.

As the developer, you can handle this by implementing Cursor management calls within the various lifecycle callbacks, such as onPause(), onResume(), and onDestroy().

If you’re lazy, like us, and you don’t want to bother handling these lifecycle events, you can hand off the responsibility of managing Cursor objects to the parent Activity by using the Activity method called startManagingCursor().The Activity handles the rest, deactivating and reactivating the Cursor as necessary and destroying the Cursor when the Activity is destroyed. You can always begin manually managing the Cursor object again later by simply calling stopManagingCursor().

Here we perform the same simple query and then hand over Cursor management to the parent Activity:

Note that, generally, the managed Cursor is a member variable of the class, scope-wise.

Iterating Rows of Query Results and Extracting Specific Data

You can use the Cursor to iterate those results, one row at a time using various navigation methods such as moveToFirst(), moveToNext(), and isAfterLast().

On a specific row, you can use the Cursor to extract the data for a given column in the query results. Because SQLite is not strongly typed, you can always pull fields out as Strings using the getString() method, but you can also use the type-appropriate extraction utility function to enforce type safety in your application.

For example, the following method takes a valid Cursor object, prints the number of returned results, and then prints some column information (name and number of columns). Next, it iterates through the query results, printing each record.

The output to the LogCat for this function might look something like Figure

Executing Simple Queries

Your first stop for database queries should be the query() methods available in the SQLiteDatabase class. This method queries the database and returns any results as in a Cursor object.

Sample log output for the logCursorInfo() method.

Sample log output for the logCursorInfo() method.

The query() method we mainly use takes the following parameters:

  • [String]:The name of the table to compile the query against
  • [String Array]: List of specific column names to return (use null for all)
  • [String] The WHERE clause: Use null for all; might include selection args as ?’s
  • [String Array]: Any selection argument values to substitute in for the ?’s in the earlier parameter
  • [String] GROUP BY clause: null for no grouping
  • [String] HAVING clause: null unless GROUP BY clause requires one
  • [String] ORDER BY clause: If null, default ordering used
  • [String] LIMIT clause: If null, no limit

Previously in the chapter, we called the query() method with only one parameter set to the table name.

This is equivalent to the SQL query

Add a WHERE clause to your query, so you can retrieve one record at a time:

This is equivalent to the SQL query

Selecting all results might be fine for tiny databases, but it is not terribly efficient. You should always tailor your SQL queries to return only the results you require with no extraneous information included. Use the powerful language of SQL to do the heavy lifting for you whenever possible, instead of programmatically processing results yourself. For example, if you need only the titles of each book in the book table, you might use the following call to the query() method:

This is equivalent to the SQL query

Executing More Complex Queries Using SQLiteQueryBuilder

As your queries get more complex and involve multiple tables, you should leverage the SQLiteQueryBuilder convenience class, which can build complex queries (such as joins) programmatically.

When more than one table is involved, you need to make sure you refer to columns within a table by their fully qualified names. For example, the title column within the tbl_books table is tbl_books.title. Here we use a SQLiteQueryBuilder to build and execute a simple INNER JOIN between two tables to get a list of books with their authors:

First, we instantiate a new SQLiteQueryBuilder object. Then we can set the tables involved as part of our JOIN and the WHERE clause that determines how the JOIN occurs. Then, we call the query() method of the SQLiteQueryBuilder that is similar to the query() method we have been using, except we supply the SQLiteDatabase instance instead of the table name. The earlier query built by the SQLiteQueryBuilder is equivalent to the SQL query:

Executing Raw Queries Without Builders and Column-Mapping

All these helpful Android query utilities can sometimes make building and performing a nonstandard or complex query too verbose. In this case, you might want to consider the rawQuery() method.The rawQuery() method simply takes a SQL statement String (with optional selection arguments if you include ?’s) and returns a Cursor of results. If you know your SQL and you don’t want to bother learning the ins and outs of all the different SQL query building utilities, this is the method for you.

For example, let’s say we have a UNION query. These types of queries are feasible with the QueryBuilder, but their implementation is cumbersome when you start using column aliases and the like.

Let’s say we want to execute the following SQL UNION query, which returns a list of all book titles and authors whose name contains the substring ow (that is Hallows, Rowling), as in the following:

We can easily execute this by making a string that looks much like the original query and executing the rawQuery() method.

We make the substrings (ow) into selection arguments, so we can use this same code to look for other substrings searches).

Closing and Deleting a SQLite Database

Although you should always close a database when you are not using it, you might on occasion also want to modify and delete tables and delete your database.

Deleting Tables and Other SQLite Objects

You delete tables and other SQLite objects in exactly the same way you create them. Format the appropriate SQLite statements and execute them. For example, to drop our tables and triggers,we can execute three SQL statements:

Closing a SQLite Database

You should close your database when you are not using it. You can close the database using the close() method of your SQLiteDatabase instance, like this:

Deleting a SQLite Database Instance Using the Application Context

The simplest way to delete a SQLiteDatabase is to use the deleteDatabase() method of your application Context. You delete databases by name and the deletion is permanent. You lose all data and schema information.

Designing Persistent Databases

Generally speaking, an application creates a database and uses it for the rest of the application’s lifetime—by which we mean until the application is uninstalled from the phone. So far, we’ve talked about the basics of creating a database, using it, and then deleting it.

In reality, most mobile applications do not create a database on-the-fly, use them, and then delete them. Instead, they create a database the first time they need it and then use it. The Android SDK provides a helper class called SQLiteOpenHelper to help you manage your application’s database.

To create a SQLite database for your Android application using the SQLiteOpenHelper, you need to extend that class and then instantiate an instance of it as a member variable for use within your application. To illustrate how to do this, let’s create a new Android project called PetTracker.

Keeping Track of Database Field Names

You’ve probably realized by now that it is time to start organizing your database fields programmatically to avoid typos and such in your SQL queries. One easy way you do this is to make a class to encapsulate your database schema in a class, such as PetDatabase, shown here:

By implementing the BaseColumns interface, we begin to set up the underpinnings for using database-friendly user interface controls in the future, which often require a specially named column called _id to function properly. We rely on this column as our primary key.

Extending the SQLiteOpenHelper Class

To extend the SQLiteOpenHelper class, we must implement several important methods, which help manage the database versioning. The methods to override are onCreate(), onUpgrade(), and onOpen().We use our newly defined PetDatabase class to generate appropriate SQL statements, as shown here:

Now we can create a member variable for our database like this:

Now, whenever our application needs to interact with its database,we request a valid database object. We can request a read-only database or a database that we can also write to. We can also close the database. For example, here we get a database we can write data to:

Binding Data to the Application User Interface

In many cases with application databases, you want to couple your user interface with the data in your database. You might want to fill drop-down lists with values from a database table, or fill out form values, or display only certain results. There are various ways to bind database data to your user interface. You, as the developer, can decide whether to use builtin data-binding functionality provided with certain user interface controls, or you can build your own user interfaces from the ground up.

Working with Database Data Like Any Other Data

If you peruse the PetTracker application provided on the book website, you notice that its functionality includes no magical data-binding features, yet the application clearly uses the database as part of the user interface.

Specifically, the database is leveraged:

  • When you fill out the Pet Type field, the AutoComplete feature is seeded with pet types already in listed in the table_pettypes table (left).

The PetTracker application: Entry Screen (left, middle) and Pet Listing Screen (right).

The PetTracker application: Entry Screen (left, middle) and Pet Listing Screen (right).

  • When you save new records using the Pet Entry Form (middle).
  • n When you display the Pet List screen, you query for all pets and use a Cursor to programmatically build a TableLayout on-the-fly (right).

This might work for small amounts of data; however, there are various drawbacks to this method. For example, all the work is done on the main thread, so the more records you add, the slower your application response time becomes. Second, there’s quite a bit of custom code involved to map the database results to the individual user interface components. If you decide you want to use a different control to display your data, you have quite a lot of rework to do. Third, we constantly requery the database for fresh results, and we might be requerying far more than necessary.

Yes, we really named our pet bunnies after data structures and computer terminology. We are that geeky. Null, for example, is a rambunctious little black bunny. Shane enjoys pointing at him and calling himself a Null pointer.

Binding Data to Controls Using Data Adapters

Ideally, you’d like to bind your data to user interface controls and let them take care of the data display. For example, we can use a fancy ListView to display the pets instead of building a TableLayout from scratch. We can spin through our Cursor and generate ListView child items manually, or even better, we can simply create a data adapter to map the Cursor results to each TextView child within the ListView.

We included a project called PetTracker2 on the book website that does this. It behaves much like the PetTracker sample application, except that it uses the SimpleCursorAdapter with ListView and an ArrayAdapter to handle AutoCompleteTextView features.

The source code for subsequent upgrades to the PetTracker application (for example, Pet-Tracker2, PetTracker3, and so on) is provided for download on the book website.

Binding Data Using SimpleCursorAdapter

Let’s now look at how we can create a data adapter to mimic our Pet Listing screen, with each pet’s name and species listed. We also want to continue to have the ability to delete records from the list.

Remember from Chapter “Designing User Interfaces with Layouts,” that the ListView container can contain children such as TextView objects. In this case, we want to display each Pet’s name and type. We therefore create a layout file called pet_item.xml that becomes our ListView item template:

Next, in our main layout file for the Pet List, we place our ListView in the appropriate place on the overall screen. The ListView portion of the layout file might look something like this:

Now to programmatically fill our ListView,we must take the following steps:

  1. Perform our query and return a valid Cursor (a member variable).
  2. Create a data adapter that maps the Cursor columns to the appropriate TextView controls within our pet_item.xml layout template.
  3. Attach the adapter to the ListView.

In the following code,we perform these steps:

Notice that the _id column as well as the expected name and type columns appears in the query. This is required for the adapter and ListView to work properly.

Using a ListView instead of a custom user interface enables us to take advantage of the ListView control’s built-in features, such as scrolling when the list becomes longer, and the ability to provide context menus as needed. The _id column is used as the unique identifier for each ListView child node. If we choose a specific item on the list, we can act on it using this identifier, for example, to delete the item.

The PetTracker2 application: Pet Listing Screen ListView (left) with Delete feature (right).

The PetTracker2 application: Pet Listing Screen ListView (left) with Delete feature (right)

Now we re-implement the Delete functionality by listening for onItemClick() events and providing a Delete Confirmation dialog (right):

You can see what this would look like on the screen in Figure.

Note that within the PetTracker2 sample application, we also use an ArrayAdapter to bind the data in the pet_types table to the AutoCompleteTextView on the Pet Entry screen. Although our next example shows you how to do this in a preferred manner, we left this code in the PetTracker sample to show you that you can always intercept the data your Cursor provides and do what you want with it. In this case, we create a String array for the AutoText options by hand. We use a built-in Android layout resource called android.R.layout.simple_dropdown_item_1line to specify what each individual item within the AutoText listing looks like. You can find the built-in layout resources provided within your appropriate Android SDK version’s resource subdirectory.

Storing Nonprimitive Types (Such as Images) in the Database

Because SQLite is a single file, it makes little sense to try to store binary data within the database. Instead store the location of data, as a file path or a URI in the database, and access it appropriately. We show an example of storing image URIs in the database in the next chapter.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Android Topics