Mark Murphy - Beginning Android

This chapter will cover the basics of SQLite use in the context of working on Android. It by no means is a thorough coverage of SQLite as a whole. If you want to learn more about SQLite and how to use it in environments other than Android, a fine book is The Definitive Guide to SQLite [21] http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730 by Mike Owens (Apress, 2006).

Activities will typically access a database via a content provider or service. Therefore, this chapter does not have a full example. You will find a full example of a content provider that accesses a database in Chapter 28.

SQLite, as the name suggests, uses a dialect of SQL for queries ( SELECT), data manipulation (INSERT, et al), and data definition ( CREATE TABLE, et al). SQLite has a few places where it deviates from the SQL-92 standard, no different than most SQL databases. The good news is that SQLite is so space-efficient that the Android runtime can include all of SQLite, not some arbitrary subset to trim it down to size.

The biggest difference from other SQL databases you will encounter is probably the data typing. While you can specify the data types for columns in a CREATE TABLEstatement, and while SQLite will use those as a hint, that is as far as it goes. You can put whatever data you want in whatever column you want. Put a string in an INTEGER column? Sure! No problem! Vice versa? Works too! SQLite refers to this as “manifest typing,” as described in the documentation: [22] http://www.sqlite.org/different.html

In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column.

In addition, there is a handful of standard SQL features not supported in SQLite, notably FOREIGN KEYconstraints, nested transactions, RIGHT OUTER JOINand FULL OUTER JOIN, and some flavors of ALTER TABLE.

Beyond that, though, you get a full SQL system, complete with triggers, transactions, and the like. Stock SQL statements, like SELECT, work pretty much as you might expect.

If you are used to working with a major database, like Oracle, you may look upon SQLite as being a “toy” database. Please bear in mind that Oracle and SQLite are meant to solve different problems, and that you will not likely be seeing a full copy of Oracle on a phone any time soon.

No databases are automatically supplied to you by Android. If you want to use SQLite, you have to create your own database, then populate it with your own tables, indexes, and data.

To create and open a database, your best option is to craft a subclass of SQLiteOpenHelper. This class wraps up the logic to create and upgrade a database, per your specifications, as needed by your application. Your subclass of SQLiteOpenHelperwill need three methods:

• The constructor, chaining upward to the SQLiteOpenHelperconstructor. This takes the Context(e.g., an Activity), the name of the database, an optional cursor factory (typically, just pass null), and an integer representing the version of the database schema you are using.

• onCreate(), which passes you a SQLiteDatabaseobject that you need to populate with tables and initial data, as appropriate.

• onUpgrade(), which passes you a SQLiteDatabaseobject and the old and new version numbers, so you can figure out how best to convert the database from the old schema to the new one. The simplest, albeit least friendly, approach is to simply drop the old tables and create new ones. This is covered in greater detail in Chapter 28.

The rest of this chapter will discuss how you actually create tables, insert data, drop tables, etc., and will show sample code from a SQLiteOpenHelpersubclass.

To use your SQLiteOpenHelpersubclass, create an instance and ask it to getReadableDatabase()or getWriteableDatabase(), depending upon whether or not you will be changing its contents:

db = (new DatabaseHelper(getContext())).getWritableDatabase();

return (db == null) ? false : true;

This will return a SQLiteDatabaseinstance, which you can then use to query the database or modify its data.

When you are done with the database (e.g., your activity is being closed), simply call close()on the SQLiteDatabaseto release your connection.

For creating your tables and indexes, you will need to call execSQL()on your SQLiteDatabase, providing the DDL statement you wish to apply against the database. Barring a database error, this method returns nothing.

So, for example, you can use the following code:

db. execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);");

This will create a table, named constants, with a primary key column named _idthat is an auto-incremented integer (i.e., SQLite will assign the value for you when you insert rows), plus two data columns: title(text) and value(a float, or “real” in SQLite terms). SQLite will automatically create an index for you on your primary-key column — you could add other indices here via some CREATE INDEXstatements, if you so chose.

Most likely, you will create tables and indexes when you first create the database, or possibly when the database needs upgrading to accommodate a new release of your application. If you do not change your table schemas, you might never drop your tables or indexes, but if you do, just use execSQL()to invoke DROP INDEXand DROP TABLEstatements as needed.

Given that you have a database and one or more tables, you probably want to put some data in them and such. You have two major approaches for doing this.

You can always use execSQL(), just like you did for creating the tables. The execSQL()method works for any SQL that does not return results, so it can handle INSERT, UPDATE, DELETE, etc. just fine. So, for example you could use this code:

db. execSQL("INSERT INTO widgets (name, inventory)" +

VALUES ('Sprocket', 5));

Your alternative is to use the insert(), update(), and delete()methods on the SQLiteDatabaseobject. These are “builder” sorts of methods, in that they break down the SQL statements into discrete chunks, then take those chunks as parameters.

These methods make use of ContentValuesobjects, which implement a Map-esque interface, albeit one that has additional methods for working with SQLite types. For example, in addition to get()to retrieve a value by its key, you have getAsInteger(), getAsString(), and so forth.

The insert()method takes the name of the table, the name of one column as the null column hack , and a ContentValueswith the initial values you want put into this row. The null column hack is for the case where the ContentValuesinstance is empty — the column named as the null column hack will be explicitly assigned the value NULLin the SQL INSERTstatement generated by insert().