Android with SQLite database

1. SQLite and Android

1.1. What is SQLite?

SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

1.2. SQLite in Android

SQLite is embedded into every Android device. Using an SQLite database in Android does not require a setup procedure or administration of the database.

You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the file system. This can be slow. Therefore it is recommended to perform database operations asynchronously.

If your application creates a database, this database is by default saved in the directoryDATA/data/APP_NAME/databases/FILENAME.

The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.

2. SQLite architecture

2.1. Packages

The android.database package contains all necessary classes for working with databases. Theandroid.database.sqlite package contains the SQLite specific classes.

2.2. Creating and updating database with SQLiteOpenHelper

To create and upgrade a database in your Android application you create a subclass of theSQLiteOpenHelper class. In the constructor of your subclass you call the super() method ofSQLiteOpenHelper, specifying the database name and the current database version.

In this class you need to override the following methods to create and update your database.

onCreate() – is called by the framework, if the database is accessed but not yet created.

onUpgrade() – called, if the database version is increased in your application code. This method allows you to update an existing database schema or to drop the existing database and recreate it via the onCreate() method.

Both methods receive an SQLiteDatabase object as parameter which is the Java representation of the database.

The SQLiteOpenHelper class provides the getReadableDatabase() andgetWriteableDatabase() methods to get access to an SQLiteDatabase object; either in read or write mode.

The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

2.3. SQLiteDatabase

SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.

More specifically SQLiteDatabase provides the insert(), update() and delete() methods.

In addition it provides the execSQL() method, which allows to execute an SQL statement directly.

The object ContentValues allows to define key/values. The key represents the table column identifier and the value represents the content for the table record in this column.ContentValues can be used for inserts and updates of database entries.

Queries can be created via the rawQuery() and query() methods or via theSQLiteQueryBuilder class .

rawQuery() directly accepts an SQL select statement as input.

query() provides a structured interface for specifying the SQL query.

SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

A list of which table columns to return. Passing “null” will return all columns.

String whereClause

Where-clause, i.e. filter for the selection of data, null will select all data.

String[] selectionArgs

You may include ?s in the “whereClause””. These placeholders will get replaced by the values from the selectionArgs array.

String[] groupBy

A filter declaring how to group rows, null will cause the rows to not be grouped.

String[] having

Filter for the groups, null means no filter.

String[] orderBy

Table columns which will be used to order the data, null means no ordering.

If a condition is not required you can pass null, e.g. for the group by clause.

The “whereClause” is specified without the word “where”, for example a “where” statement might look like: “_id=19 and summary=?”.

If you specify placeholder values in the where clause via ?, you pass them as the selectionArgs parameter to the query.

2.6. Cursor

A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.

To get the number of elements of the resulting query use the getCount() method.

To move between individual data rows, you can use the moveToFirst() and moveToNext()methods. The

isAfterLast() method allows to check if the end of the query result has been reached.

Cursor provides typed get*() methods, e.g. getLong(columnIndex),getString(columnIndex) to access the column data for the current position of the result. The “columnIndex” is the number of the column you are accessing.

Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.

A Cursor needs to be closed with the close() method call.

2.7. ListViews, ListActivities and SimpleCursorAdapter

ListViews are Views which allow to display a list of elements.

ListActivities are specialized activities which make the usage of ListViews easier.

To work with databases and ListViews you can use the SimpleCursorAdapter. TheSimpleCursorAdapter allows to set a layout for each row of the ListViews.

You also define an array which contains the column names and another array which contains the IDs of Views which should be filled with the data.

The SimpleCursorAdapter class will map the columns to the Views based on the Cursorpassed to it.

o obtain the Cursor you should use the Loader class.

3. Tutorial: Using SQLite

3.1. Introduction to the project

The following demonstrates how to work with an SQLite database. We will use a data access object (DAO) to manage the data for us. The DAO is responsible for handling the database connection and for accessing and modifying the data. It will also convert the database objects into real Java Objects, so that our user interface code does not have to deal with the persistence layer.

The resulting application will look like the following.

Using a DAO is not always the right approach. A DAO creates Java model objects; using a database directly or via a ContentProvider is typically more resource efficient as you can avoid the creation of model objects.

I still demonstrate the usage of the DAO in this example to have a relatively simple example to begin with. Use the latest version of Android 4.0. This is currently API Level 15. Otherwise I would have to introduce the Loader class, which should be used as of Android 3.0 for managing a database Cursor. And this class introduces additional complexity.

3.2. Create Project

Create the new Android project with the name de.vogella.android.sqlite.first and anactivity called TestDatabaseActivity.

3.3. Database and Data Model

Create the MySQLiteHelper class. This class is responsible for creating the database. TheonUpgrade() method will simply delete all existing data and re-create the table. It also defines several constants for the table name and the table columns.

3.4. User Interface

Change your main.xml layout file in the <filename class=”directory”>res/layout_ folder to the following. This layout has two buttons for adding and deleting comments and a ListView which will be used to display the existing comments. The comment text will be generated later in theactivity by a small random generator.

3.5. Running the apps

Install your application and use the Add and Delete button. Restart your application to validate that the data is still there.

4. Content provider and sharing data

4.1. What is a content provider?

If you want to share data with other applications you can use a content provider (short provider). Provider offer data encapsulation based on URI’s. Any URI which starts with content:// points to a resources which can be accessed via a provider. A URI for a resource may allow to perform the basic CRUD operations (Create, Read, Update, Delete) on the resource via the content provider.

A provider allows applications to access data. The data can be stored in an SQlite database, on the file system, in flat files or on a remote server.

While a content provider can be used within an application to access data, its is typically used to share data with other application. As application data is by default private, a content provider is a convenient to share you data with other application based on a structured interface.

A content provider must be declared in the manifest file for the application.

4.2. Base URI of the content provider

The base URI to access a content provider is defined via the combination of the content://schema and the name space of the provider. This name space is defined

in the manifest file via the android:authorities attribute of the receiver registration. This can for example be: content://test/

The base URI represents a collection of resources. If the base URI is combined with an instance identifier, e,g., content://test/2, it represents a single instance.

4.3. Accessing a content provider

As it is required to know the URIs of a provider to access it, it is good practice to provide public constants for the URIs to document them to other developers.

4.4. Custom content provider

To create your custom content provider you have to define a class which extendsandroid.content.ContentProvider. You must declare this class as content provider in the Android manifest file. The corresponding entry must specify the android:authorities attribute which allows identifying the content provider. This authority is the basis for the URI to access data and must be unique.

Your content provider must implement several methods, e.g. query(), insert(), update(),delete(), getType() and onCreate(). In case you do not support certain methods its good practice to throw an UnsupportedOperationException().

The query() method must return a Cursor object.

4.5. Security and content provider

Until Android version 4.2 a content provider is by default available to other Android applications. As of Android 4.2 a content provider must be explicitly exported.

To set the visibility of your content provider use the android:exported=false|true parameter in the declaration of your content provider in the AndroidManifest.xml file.

It is good practice to always set the android:exported parameter to ensure correct behavior across Android versions.

4.6. Thread Safety

If you work directly with databases and have multiple writers from different threads you may run into concurrency issues.

A content provider can be accessed from several programs at the same time, therefore you must implement the access thread-safe. The easiest way is to use the keyword synchronized in front of all methods of the provider, so that only one thread can access these methods at the same time.

If you do not require that Android synchronizes data access to the provider, set theandroid:multiprocess=true attribute in your <provider> definition in the AndroidManifest.xmlfile. This permits an instance of the provider to be created in each client process, eliminating the need to perform interprocess communication (IPC).