Mark Murphy - Beginning Android

ContentValues cv = new ContentValues();

cv. put(Constants.TITLE, "Gravity, Death Star I");

cv. put(Constants.VALUE, SensorManager.GRAVITY_DEATH_STAR_I);

db. insert("constants", getNullColumnHack(), cv);

The update()method takes the name of the table, a ContentValuesrepresenting the columns and replacement values to use, an optional WHEREclause, and an optional list of parameters to fill into the WHEREclause, to replace any embedded question marks ( ?). Since update()replaces only columns with fixed values, versus ones computed based on other information, you may need to use execSQL()to accomplish some ends.

The WHEREclause and parameter list work akin to the positional SQL parameters you may be used to from other SQL APIs. Consider this example:

// replacements is a ContentValues instance

String[] parms = newString[] {"snicklefritz"};

db. update("widgets", replacements, "name=?", parms);

The delete()method works akin to update(), taking the name of the table, the optional WHEREclause, and the corresponding parameters to fill into the WHEREclause.

As with INSERT, UPDATE, and DELETE, you have two main options for retrieving data from a SQLite database using SELECT:

• You can use rawQuery()to invoke a SELECTstatement directly.

• You can use query()to build up a query from its component parts.

Confounding matters is the SQLiteQueryBuilderclass and the issue of cursors and cursor factories. Let’s take all of this one piece at a time.

The simplest solution, at least in terms of the API, is rawQuery(). Simply call it with your SQL SELECTstatement. The SELECTstatement can include positional parameters; the array of these forms your second parameter to rawQuery(). So, we wind up with this:

Cursor c = db. rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='constants'", null);

In this example, we actually query a SQLite system table ( sqlite_master) to see if our constants table already exists. The return value is a Cursor, which contains methods for iterating over results (see the “Using Cursors” section).

If your queries are pretty much baked into your application, this is a very straightforward way to use them. However, it gets complicated if parts of the query are dynamic, beyond what positional parameters can really handle. For example, if the set of columns you need to retrieve is not known at compile time, puttering around concatenating column names into a comma-delimited list can be annoying — which is where query()comes in.

The query()method takes the discrete pieces of a SELECTstatement and builds the query from them. The pieces, in the order they appear as parameters to query(), are as follows:

1. The name of the table to query against

2. The list of columns to retrieve

3. The WHEREclause, optionally including positional parameters

4. The list of values to substitute in for those positional parameters

5. The GROUP BYclause, if any

6. The ORDER BYclause, if any

7. The HAVINGclause, if any

These can be nullwhen they are not needed (except the table name, of course):

String[] columns = {"ID", "inventory"};

String[] parms = {"snicklefritz"};

Cursor result = db. query("widgets", columns, "name=?",

parms, null, null, null);

Yet another option is to use SQLiteQueryBuilder, which offers much richer query-building options, particularly for nasty queries involving things like the union of multiple sub-query results. More importantly, the SQLiteQueryBuilderinterface dovetails nicely with the ContentProviderinterface for executing queries. Hence, a common pattern for your content provider’s query()implementation is to create a SQLiteQueryBuilder, fill in some defaults, then allow it to build up (and optionally execute) the full query combining the defaults with what is provided to the content provider on the query request.

For example, here is a snippet of code from a content provider using SQLiteQueryBuilder:

@Override

publicCursor query(Uri url, String[] projection, String selection,

String[] selectionArgs, String sort) {

SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

qb. setTables( getTableName());

if( isCollectionUri(url)) {

qb. setProjectionMap( getDefaultProjection());

} else{

qb. appendWhere( getIdColumnName() + "=" + url. getPathSegments(). get(1));

}

String orderBy;

if(TextUtils. isEmpty(sort)) {

orderBy = getDefaultSortOrder();

} else{

orderBy = sort;

}

Cursor c = qb. query(db, projection, selection, selectionArgs,

null, null, orderBy);

c. setNotificationUri( getContext(). getContentResolver(), url);

returnc;

}

Content providers are explained in greater detail in Part 5 of this book, so some of this you will have to take on faith until then. Here, we see the following:

1. A SQLiteQueryBuilderis constructed.

2. It is told the table to use for the query ( setTables(getTableName())).

3. It is either told the default set of columns to return ( setProjectionMap()), or is given a piece of a WHEREclause to identify a particular row in the table by an identifier extracted from the Uri supplied to the query()call ( appendWhere()).

4. Finally, it is told to execute the query, blending the preset values with those supplied on the call to query()( qb.query(db, projection, selection, selectionArgs, null, null, orderBy)).

Instead of having the SQLiteQueryBuilderexecute the query directly, we could have called buildQuery()to have it generate and return the SQL SELECTstatement we needed, which we could then execute ourselves.

No matter how you execute the query, you get a Cursorback. This is the Android/SQLite edition of the database cursor, a concept used in many database systems. With the cursor, you can do the following: