Revisiting JDBC (pt. 1)

About 6 or 7 years ago I was writing a lot of SQL and JDBC - I remember being particularly pleased when I developed an efficient implementation of Celko's Nested sets to represent hierarchical data in a Content Management project. At the time JDBC was a pretty neat way of interfacing with a relational database. However, we Enterprise Java developers have for the most part left JDBC behind in favor of excellent ORM frameworks such as Hibernate and although we are using JDBC more that ever, we do so with it operating under the covers - tucked away within our ORM framework. Sure there may be times when we have to step back to JDBC - how well would Hibernate handle hierarchical data? - but they are infrequent.

Now as I mentioned in previous posts, the main reason I found myself developing - well, debugging at first - on the iPhone platform was to investigate why a SQLite based feature of our application wasn't functioning as well as the original Java proof-of-concept. Fairly soon I was immersed in the world of SQLite's C/C++ interface - JDBC this was not. From a Java point of view it's low-level: Error conditions signaled by return value on almost every function and pointers aplenty. What I wanted was JDBC - or rather OBJCDBC - but in fact I wanted much more because in the JDBC world I had also become used to:

• Excellent connection and prepared statement pooling with the likes of DBCP.
• Concise utility methods that allowed me to avoid JDBC boiler-plate in the form of DbUtil.

I Googled a while to see if such a thing existed in the iPhone domain and thankfully found many likely candidates on the SQL Wiki (see: Objective-C section) ranging from simple wrappers to ORM frameworks. However, the simple wrappers were not as clean and object orientated as I was used to with JDBC and the ORM frameworks would not give me enough control to write some of the highly optimized SQL queries that our application demanded. I set about writing my own wrapper. One could argue that I was reinventing the wheel - but I'm always happy to learn more about wheels. My requirements were as follows:

• Option to pool connections and statements
• Check for every error condition that SQLite could possibly set and convert these into exceptions so we can adopt a try/catch/finally approach when accessing the database.
• Provide a clean and simple programmatic interface.
• Move all direct interactions with SQLite into a few sensible classes - C based SQLite code had previously been spread liberally throughout the application.

My core classes were to be as follows:

• PooledDataSource - A connection data source that also pools connections. Calling close on a connection actually returns it to the pool.
  
• Connection - Encapsulates a SQLite database handle - can also pool statements that have been prepared from this connection. Calling prepare on a connection might actually fetch and reuse a pooled PreparedStatement rather than creating a new instance. rovides methods to manage transactions.
• PreparedStatement - Prepares transient and non-transient statements, binds parameter values to statements, and executes statements. Returns ResultSets for SELECT queries. Calling close on a prepared statement returns it to the statement pool if it is not transient.
• ResultSet - An interface for stepping through a cursor and retrieving values from the row.

With these classes in place I could write some typical Data Access Object code:

    PreparedStatement statement;

    ResultSet results;

    NSNumber maxId = nil;

    @try {

        stmt = [con prepare:@"SELECT MAX(id) FROM foo WHERE bar = ?"];

        [con begin]; // OK - so we don't need this for a SELECT

        results = [stmt executeWithValues:@"dog", nil];

        if ([results next]) {

           maxId = [results getInt:1];

        }

        [con commit]; // for illustration only

    @catch (NSException* sqlX) {

        [con rollback]; // for illustration only

        // handle error

    } @finally {

        [DatabaseCommons closeQuietly:results, statement, con];

    }

I'll get more into the actual class implementation details in part 2.