Android Database Design Considerations

There are several considerations specific to Android that you should consider when designing your databas

❑Files (such as bitmaps or audio files) are not usually stored within database tables. Instead, use a string to store a path to the file, preferably a fully qualified Content Provider UR

❑While not strictly a requirement, it’s strongly recommended that all tables include an auto-increment key field, to function as a unique index value for each row. It’s worth noting that if you plan to share your table using a Content Provider, this unique ID field is mandator

Querying Your Database

All database queries are returned as a Cursor to a result set. This lets Android manage resources more efficiently by retrieving and releasing row and column values on demand.

To execute a query on a database, use the query method on the database object, passing in:

❑ An optional Boolean that specifies if the result set should contain only unique values ❑ The name of the table to query

❑A projection, as an array of Strings, that lists the columns to include in the result set

❑A “where” clause that defines the rows to be returned. You can include ? wildcards that will be replaced by the values stored in the selection argument parameter.

❑ An array of selection argument strings that will replace the ?’s in the “where” clause ❑ A “group by” clause that defines how the resulting rows will be grouped

❑ A “having” filter that defines which row groups to include if you specified a “group by” clause ❑ A String that describes the order of the returned rows

❑An optional String that defines a limit to the returned rows

The following skeleton code shows snippets for returning some, and all, of the rows in a particular table:

Return all rows for columns one and three, no

duplicates String[] result_columns = new String[] {KEY_ID, KEY_COL1, KEY_COL3};

Cursor allRows = myDatabase.query(true, DATABASE_TABLE, result_columns,

null, null, null, null, null, null);

Return all columns for rows where column 3 equals a set value

and the rows are ordered by column 5.

String where = KEY_COL3 + “=” + requiredValue;

String order = KEY_COL5;

Cursor myResult = myDatabase.query(DATABASE_TABLE, null, where,

null, null, null, order);

In practice, it’s often useful to abstract these query commands within an adapter class to simplify data access.