Reading Data from the Database

There are many ways to read data from an SQL database, but they all come down to a basic sequence of operations:

1. Create an SQL statement that describes the data that you need to retrieve.

2. Execute that statement against the database.

3. Map the resulting SQL data into data structures that the language you're working in can understand.

This process can be very complex in the case of object-relational mapping software, or relatively simple when writing the queries directly into your application. The difference is fragility. Complex ORM tools shield your code from the complexities of database programming and object mapping by moving that complexity elsewhere. The result is that your code is more robust in the face of database changes, but at the cost of complex ORM setup and maintenance.

The simple approach of writing queries directly into your application works well only for very small projects that will not change much over time. Applications with database code in them are very fragile because as the database changes, any code that references those changes must be examined and potentially changed.

A common middle-ground approach is to sequester all of the database logic into a set of objects whose sole purpose is to translate application requests into database requests and deliver the results back to the application. This is the approach we have taken with the MicroJobs application; all of the database code is contained in a single class in the file MicroJobsDatabase.java.

Android gives us the ability to customize Cursors, and we use that ability to further reduce code dependencies by hiding all of the information about each specific database operation inside a custom cursor. Each custom cursor is a class within the MicroJobsDatabase class; the one that we'll look at in this chapter is the JobsCursor.

The interface to the caller in the getJobs method of MicroJobsDatabase appears first in the code that follows. The method's job is to return a JobsCursor filled with jobs from the database. The user can choose (through the single parameter passed to getJobs) to sort jobs by either the title column or the employer_name column: public class MicroJobsDatabase extends SQLiteOpenHelper {

/** Return a sorted JobsCursor * @param sortBy the sort criteria */

public JobsCursor getJobs(JobsCursor.SortBy sortBy) { String sql = JobsCursor.QUERY + sortBy.toString();© SQLiteDatabase d = getReadableDatabase();© JobsCursor c = (JobsCursor) d.rawQueryWithFactory( new JobsCursor.Factory(), sql, null, null); c.moveToFirst();0 return c;

public static class JobsCursor extends SQLiteCursor{ public static enum SortBy{ title, employer_name

private static final String QUERY =

"SELECT jobs._id, title, employer_name, latitude, longitude, status "+

"FROM jobs, employers "+

"WHERE jobs.employer_id = employers._id "+

private JobsCursor(SQLiteDatabase db, SQLiteCursorDriver driver, String editTable, SQLiteQuery query) { super(db, driver, editTable, query);

private static class Factory implements SQLiteDatabase.CursorFactory{ @Override public Cursor newCursor(SQLiteDatabase db,

SQLiteCursorDriver driver, String editTable, SQLiteQuery query) { return new JobsCursor(db, driver, editTable, query);

public long getColJobsId() {return getLong(getColumnIndexOrThrow("jobs._id"));}© public String getColTitle() {return getString(getColumnIndexOrThrow("title"));} public String getColEmployerName() {return getString(getColumnIndexOrThrow("employer_name"));} public long getColLatitude()

{return getLong(getColumnIndexOrThrow("latitude"));} public long getColLongitude()

{return getLong(getColumnIndexOrThrow("longitude"));} public long getColStatus(){return getLong(getColumnIndexOrThrow("status"));}

Here are some of the highlights of the code:

O Function that fashions a query based on the user's requested sort column (the sortBy parameter) and returns results as a cursor.

© Creates the query string. Most of the string is static (the QUERY variable), but this line tacks on the sort column. Even though QUERY is private, it is still available to the enclosing class. This is because the getJobs method and the the JobsCursor class are both within the MicroJobsDatabase class, which makes JobsCursor's private data members available to the getJobs method.

To get the text for the sort column, we just run toString on the enumerated value passed by the user. The enumeration is defined at item 8. We could have defined an associative array, which would give us more flexibility in naming variables, but this solution is simpler. Additionally, the names of the columns pop up quite nicely in Eclipse's autocompletion.

© Retrieves a handle to the database.

O Creates the JobsCursor cursor using the SQLiteDatabase object's rawQueryWith Factory method. This method lets us pass a factory method that Android will use to create the exact type of cursor we need. If we had used the simpler rawQuery method, we would get back a generic Cursor that lacked the special features of JobsCursor.

As a convenience to the caller, moves to the first row in the result. This way, the cursor is returned ready to use. A common mistake is forgetting the moveToFirst call and then pulling your hair out trying to figure out why the Cursor object is throwing exceptions.

© The cursor is the return value.

Q Class that creates the cursor returned by getJobs.

© Simple way to provide alternate sort criteria: store the names of columns in an enum. This variable is used in item 2.

© Constructor for the customized cursor. The final argument is the query passed by the caller.

© Factory class to create the cursor, embedded in the JobsCursor class.

(D Creates the cursor from the query passed by the caller.

© Returns the cursor to the enclosing JobsCursor class.

© Convenience functions that extract particular columns from the row under the cursor. For instance, getColTitle returns the value of the title column in the row currently referenced by the cursor. This separates the database implementation from the calling code and makes that code easier to read.

A sample use of the database follows. The code gets a cursor, sorted by title, through a call to getJobs. It then iterates through the jobs.

MicroJobsDatabase db = new MicroJobsDatabase(this);

JobsCursor cursor = db.getJobs(JobsCursor.SortBy.title);

for( int rowNum=0; rowNum<cursor.getCount(); rowNum++){ cursor.moveToPosition(rowNum); doSomethingWith(cursor.getColTitle());

Here are some of the highlights of the code:

O Creates a MicroJobsDatabase object. The argument, this, represents the context, as discussed previously.

© Creates the JobsCursor cursor, referring to the SortBy enumeration discussed earlier. © Uses generic Cursor methods to iterate through the cursor.

O Still within the loop, invokes one of the custom accessor methods provided by JobsCursor to "do something" chosen by the user with the value of each row's title column.

0 0

Post a comment