- Manipulating Data in Android
- Transactions
- Running Queries
- Cursors
- CursorLoader
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Running Queries
Previous sections of this chapter discussed inserting, updating, and deleting data from a database. The last piece of database CRUD functionality is retrieving data from the database. As with the insert and update database operations, SQLiteDatabase contains multiple methods to support retrieving data. In addition to a series of query convenience methods, SQLiteDatabase includes a set of methods that support more free-form “raw” queries that can be generated via standard Java string manipulation methods. There is also an SQLiteQueryBuilder class that can further aid in developing complex queries such as joins.
Query Convenience Methods
The simplest way to issue a query to a database in Android is to use one of the query convenience methods located in SQLiteDatabase. These methods are the overloaded variations of SQLiteDatabase.query(). Each variant of the query() method takes a parameter list that includes the following:
String table: Indicates the table name of the query.
String[] columns: Lists the columns that should be included in the result set of the query.
String selection: Specifies the WHERE clause of the selection statement. This string can contain “?” characters that can be replaced by the selectionArgs parameter.
String[] selectionArgs: Contains the replacement values for the “?” of the selection parameter.
String groupBy: Controls how the result set is grouped. This parameter represents the GROUP BY clause in SQL.
String having: Contains the HAVING clause from an SQL SELECT statement. This clause specifies search parameters for grouping or aggregate SQL operators.
String orderBy: Controls how the results from the query are ordered. This defines the ORDER BY clause of the SELECT statement.
The table name, column list selection string, and selection arguments parameters operate in the same manner as other operations discussed earlier in the chapter. What is different about the query() methods is the inclusion of the GROUP BY, HAVING, and ORDER BY clauses. These clauses allow an app to specify additional query attributes in the same way that an SQL SELECT statement would.
Each query method returns a cursor object that contains the result set for the query. Listing 5.7 shows a query returning data from the people table used in previous listings.
Listing 5.7 Simple Query
SQLiteDatabase db = getDatabase();
Cursor result = db.query("people",
new String[] {"first_name", "last_name"},
"id = ?",
new String[] {"1"},
null,
null,
null);
Listing 5.7 returns the first_name and last_name columns for the row that has an id of 1. The query statement passes null values for the GROUP BY, HAVING, and ORDER BY clauses since the result set should be of size 1 and these clauses have no effect on a result set with size 1.
The query() method also supports passing a null value for the columns parameter which will cause the query to return all the table’s columns in the result set. It is usually better to specify the desired table columns rather than letting the Android SDK return all columns from a table and making the caller ignore the columns it does not need.
To return all the rows from a table, pass null values for the selection and selectionArgs parameters. A query returning all rows in a table is shown in Listing 5.8; the result set is sorted by ID in descending order.
Listing 5.8 Returning All Rows in a Table
SQLiteDatabase db = getDatabase();
Cursor result = db.query("people",
new String[] {"first_name", "last_name"},
null,
null,
null,
null,
"id DESC");
Raw Query Methods
If the query() convenience methods do not provide enough flexibility for a query that an app needs to run, the SQLiteDatabase.rawQuery() methods can be used instead. Like the convenience query methods, the rawQuery() methods are an overloaded set of methods. However, unlike the query() methods, the rawQuery() methods take two parameters as input: a String parameter representing the query to run, and a String[] to support query placeholder substitution. Listing 5.9 shows the same query as Listing 5.6 using the rawQuery() method instead of the query() convenience method.
Listing 5.9 Using the rawQuery() Method
SQLiteDatabase db = getDatabase();
Cursor result = db.rawQuery("SELECT first_name, last_name " +
"FROM people " +
"WHERE id = ?",
new String[] {"1"});
Like the query() method, rawQuery() returns a cursor containing the result set for the query. The caller can read and process the resulting cursor in the same way that it processes the result from the query() methods.
The rawQuery() method allows an app to have great flexibility and construct more complex queries using joins, sub-queries, unions, or any other SQL construct supported by SQLite. However, it also forces the app developer to build the query in Java code (or perhaps from reading a string resource), which can be cumbersome for really complex queries.
To aid in building more complex queries, the Android SDK contains the SQLiteQueryBuilder class. The SQLiteQueryBuilder class is discussed in more detail in the next chapter with the discussion of ContentProviders.