Persisting data to a database

Write App Reviews

Build your own Android App Dev Empire

Get Instant Access

One nice convenience that the Android platform provides is the fact that a relational database is built in. SQLite doesn't have all of the features of larger client/server database products, but it does cover just about anything you might need for local data storage, while being easy to deal with and quick.

In this section we are going to cover working with the built-in SQLite database system, from creating and querying a database to upgrading and working with the sqlite3 tool that is available in the Android Debug Bridge (adb) shell. Once again we will do this in the context of the WeatherReporter application we began in chapter 4. This application uses a database to store the user's saved locations and persists user preferences for each location. The screen shot shown in figure 5.4 displays this saved data for the user to select from; when the user selects a location, data is retrieved from the database and a location weather report is shown.

To see how this comes together we will begin with what it takes to create the database Weather-Reporter uses.

5.3.1 Building and accessing a database

To use SQLite you have to know a bit about SQL usage in general. If you need to brush up on the background of the basic commands—CREATE, INSERT, UPDATE, DELETE, and SELECT—then you may want to take a quick look at the SQLite documentation (http: //

For our purposes we are going tojump right in and build a database helper class that our application will use. We are creating a helper class so that the details concerning creating and upgrading our database, opening and closing connections, and running

through specific queries are all encapsulated in one place and not otherwise exposed or repeated in our application code. This is so our Activity and Service classes can later use simple get and insert methods, with specific bean objects representing our model, or Collections rather than database-specific abstractions (such as the Android Cursor object that represents a query result set). You can think of this class as a miniature Data Access Layer (DAL).

The first part of our DBHelper class, which includes a few inner classes you will learn about, is shown in listing 5.10.

Listing 5.10 Portion of the DBHelper class showing the DBOpenHelper inner class public class DBHelper {

public static final String DEVICE_ALERT_ENABLED_ZIP = "DAEZ99"; public static final String DB_NAME = "w_alert"; .

public static final String DB_TABLE = "w_alert_loc"; Use co n stants for O

public static final int db_version = 3; database properties private static final String CLASSNAME = DBHelper.class.getSimpleName(); private static final String [] COLS = new String []

{ "_id", "zip", "city", "region", "lastalert", "alertenabled" };

private SQLiteDatabase db; private final DBOpenHelper dbOpenHelper;

public static class Location { public long id; public long lastalert; public int alertenabled; public String zip; public String city; public String region;

Define inner Location bean

. Location constructors and toString omitted for brevity private static class DBOpenHelper extends SQLiteOpenHelper {

Define inner DBOpenHelper class private static final String DB_CREATE = "CREATE TABLE " + DBHelper .DB_TABLE

+ " (_id INTEGER PRIMARY KEY, zip TEXT UNIQUE NOT NULL,' + "city TEXT, region TEXT, lastalert INTEGER, " + "alertenabled INTEGER);";

Q Define SQL query for database creation public DBOpenHelper(Context context, String dbName, int version) { super (context, DBHelper . DB_NAME, null, DBHelper . DB_VERSION);

@Override public void onCreate(SQLiteDatabase db) { try {

db . execSQL (DBOpenHelper . DB_CREATE); } catch (SQLException e) {

F Override helper ,1_I callbacks

Log.e(Constants.LOGTAG, DBHelper.CLASSNAME, e) ;

@Override public void onOpen(SQLiteDatabase db) { super.onOpen(db);

@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS " + DBHelper.DB_TABLE); this.onCreate(db);

Within our DBHelper class we first have a series of constants that define important static values relating to the database we want to work with, such as database name, database version, and table name O. Then we show several of the most important parts of the database helper class that we have created for the WeatherReporter application, the inner classes.

The first inner class is a simple Location bean that is used to represent a user's selected location to save C. This class intentionally does not have accessors and mutators, because these add overhead and we don't really need them when we will use this bean only within our application (we won't expose it). The second inner class is a SQLiteOpenHelper implementation ©.

Our DBOpenHelper inner class extends SQLiteOpenHelper, which is a class that Android provides to help with creating, upgrading, and opening databases. Within this class we are including a String that represents the CREATE query we will use to build our database table; this shows the exact columns and types our table will have ©. The data types we are using are fairly self-explanatory; most of the time you will use INTEGER and TEXT types, as we have (if you need more information about the other types SQLite supports, please see the documentation: datatype3.html). Also within DBOpenHelper we are implementing several key SQLiteOpenHelper callback methods, notably onCreate and onUpgrade (onOpen is also supported, but we aren't using it) ©. We will explain how these callbacks come into play and why this class is so helpful in the second part of our DBHelper (the outer class), which is shown in listing 5.11.

© Override helper callbacks

Listing 5.11 Portion of the DBHelper class showing convenience methods public DBHelper (Context context) { <1-

this.dbOpenHelper = new DBOpenHelper(context, "WR_DATA", 1) ; this.establishDb();

} C Pr°v'dehnh DBOpenHelper private void establishDb () { <-1 establlshDb instance O

this.db = this.dbOpenHelper.getWritableDatabase();

public void cleanup() { <-1 provide cleanup if (this .db i = null) { D method this.db.close() ; this . db = null;

public void insert(Location location) { <-

ContentValues values = new ContentValues(); values.put("zip",; values.put("city",; values.put("region", location.region); values.put("lastalert", location.lastalert); values.put("alertenabled", location.alertenabled); this.db.insert(DBHelper,DB_TABLE, null, values);

public void update (Location location) { <1-

ContentValues values = new ContentValues(); _ . .

provide o values.put("zip",;

convenience values .put ("city", location. city) ; insert, Update, values .put ("region", location. region) ; delete get values.put("lastalert", location.lastalert); values.put("alertenabled", location.alertenabled);

this.db.update(DBHelper.DB_TABLE, values, "_id=" +, null);

this .db. delete (DBHelper ,DB_TABLE, "_id=" + id, null);

public void delete (String zip) { <1-

this .db. delete (DBHelper ,DB_TABLE, "zip='" + zip + , null);

public Location get (String zip) { <1-

Cursor c = null; Location location = null; try {

c = this.db.query(true, DBHelper.DB_TABLE, DBHelper.COLS,

"zip = ' " + zip + , null, null, null, null, null); if (c.getCount() > 0) { c.moveToFirst() ; location = new Location() ; = c.getLong(0) ; = c . getString(1) ; = c.getString(2) ; location.region = c.getString(3) ; location.lastalert = c.getLong(4); location.alertenabled = c.getInt(5);

Log.^(Constants.LOGTAG, DBHelper.CLASSNAME, e) ; } finally {

if (c ! = null && ! c . isClosed()) { c.close ();

return location; } F Provide additional

I get methods public List<Location> getAll() { <-'

ArrayList<Location> ret = new ArrayList<Location>(); Cursor c = null; try {

c = this .db. query (DBHelper ,DB_TABLE, DBHelper . COLS, null, null, null, null, null); int numRows = c.getCount(); c.moveToFirst() ;

Location location = new Location(); = c.getLong(0); = c.getString(1); = c.getString(2); location.region = c.getString(3); location.lastalert = c.getLong(4); location.alertenabled = c.getInt(5);

if (! location, zip .equals (DBHelper .DEVICE_ALERT_ENABLED_ZIP) ) { ret.add(location);


Log.v(Constants.LOGTAG, DBHelper.CLASSNAME, e) ; } finally {

if (c ! = null && !c.isClosed()) { c.close ();

return ret;

. . . getAllAlertEnabled omitted for brevity

Our DBHelper class contains a member-level variable reference to a SQLiteDatabase object, as we saw in listing 5.10 (the first half of this class). This object is the Android database workhorse. It is used to open database connections, to execute SQL statements, and more.

Then the DBOpenHelper inner class we also saw in the first part of the DBHelper class listing is instantiated inside the constructor O. From there the dbOpenHelper is used, inside the establishDb method if the db reference is null, to call openDatabase with the current Context, database name, and database version ©. This establishes db as an instance of SQLiteDatabase through DBOpenHelper.

Although you can also just open a database connection directly on your own, using the open helper in this way invokes the provided callbacks and makes the process easier. With this technique, when we try to open our database connection, it is automatically created or upgraded (orjust returned), if necessary, through our DBOpenHelper. While using a DBOpenHelper entails extra steps up front, once you have it in place it is extremely handy when you need to modify your table structure (you can simply increment your version and do what you need to do in the onUpgrade callback—without this you would have to manually alter and/or remove and re-create your existing structure).

Another important thing to provide in a helper class like this is a cleanup method D. This method is used by callers who can invoke it when they pause, in order to close connections and free up resources.

After the cleanup method we then see the raw SQL convenience methods that encapsulate the operations our helper provides. In this class we have methods to insert, update, delete and get data Q. We also have a few additional specialized get and get all methods Q. Within these methods you get a feel for how the db object is used to run queries. The SQLiteDatabase class itself has many convenience methods, such as insert, update, and delete—which we are wrapping—and it provides direct query access that returns a Cursor over a result set.

Databases are package private

Unlike the SharedPreferences we saw earlier, you can't make a database WORLD_READABLE. Each database is accessible only by the package in which it was created—this means accessible only to the process that created it. If you need to pass data across processes, you can use AIDL/Binder (as in chapter 4) or create a ContentProvider (as we will discuss next), but you can't use a database directly across the process/package boundary.

Typically you can get a lot of mileage and utility from basic steps relating to the SQLiteDatabase class, as we have here, and by using it you can create a very useful and fast data-storage mechanism for your Android applications. The final thing we need to discuss with regard to databases is the sqlite3 tool, which you can use to manipulate data outside your application.

5.3.2 Using the sqlite3 tool

When you create a database for an application in Android, the files for that database are created on the device in the /data/data/[PACKAGE_NAME]/database/ location. These files are SQLite proprietary, but there is a way to manipulate, dump, restore, and otherwise work with your databases through these files in the ADB shell—the sqlite3 tool.

This tool is accessible through the shell; you can get to it by issuing the following commands on the command line (remember to use your own package name; here we are using the package name for the WeatherReporter sample application):

cd [ANDROID_HOME]/tools adb shell sqlite3 /data/data/com.msi.manning.chapter4/databases/w_alert. db

Once you are in the shell prompt (you have the #), you can then issue sqlite3 commands; .help should get you started (if you need more, see the tool's documentation: From the tool you can issue basic commands, such as SELECT or INSERT, or you can go further and CREATE or ALTER tables. This tool comes in handy for basic poking around and troubleshooting and to .dump and .load data. As with many command-line SQL tools, it takes some time to get used to the format, but there is no better way to back up or load your data. (If you need that facil-ity—in most cases with mobile development you really shouldn't have a huge database. Keep in mind that this tool is available only through the development shell; it's not something you will be able to use to load a real application with data.)

Now that we have shown how to use the SQLite support provided in Android, from creating and accessing tables to store data, to investigating databases with the provided tools in the shell, the next thing we need to cover is the last aspect of handling data on the platform, and that is building and using a ContentProvider.

Was this article helpful?

0 0

Post a comment