Background on Spring-JDBC decision
Having decided to move away from Hibernate, the two most promising closer-to-the-SQL candidates appeared to be Spring JDBC and Apache Cayenne.
Cayenne looks like a fine lightweight ORM, but its current production release doesn't apply persistence to POJOs. Instead, each domain object needs to be a subclass of a Cayenne-specific class, which seems overly intrusive compared to Hibernate or Spring-JDBC. Cayenne 3.0 will take care of this, though, and introduces so many other significant improvements that I decided it would be better to hold off further involvement until 3.0 was closer to final release.
Since all of our local experiences with Spring-JDBC have been positive, and Spring 2.0 and 2.5 (not yet available in Sakai) have made it even easier to use, I took that route.
To my surprise, though, I didn't find much of a community or existing code samples for handling cross-platform Spring-JDBC. Apparently most of its users focus on one or two database vendors rather than on Sakai-style multiple-vendor support. This meant taking on the labor of inventing our own idioms and helpers (source code). The project post-mortem will tell whether this additional cost was worthwhile.
DDL creation
Like most other ORM frameworks, Hibernate supports automatic generation of vendor-specific database table definitions from a generic description of the data. For the most part, it did a pretty good job of bootstrapping and the DB schema could be tuned by DBAs later.
At first I assumed I'd use an Eclipse plug-in (such as Azzurri Clay) or an ORM helper to handle initial schema generation. So far, however, I seem not to be slowed down by hand-coding DDL based on existing examples.
One immediate advantage of handcrafted DDL over our older Hibernate-generated DDL is the ability to push Oracle's idiosyncratic key generation out of Java code and into the DB schema itself, via a "TRIGGER" that strongly associates the data table with the sequence table.
Generating DB tables
No ORM tool is in place to automatically create the actual DB schema based on the DDL, and I didn't want to drag in the whole of Sakai's legacy "SqlService" for this one task. It was easy enough to find a sample script parser, and easy enough to write a script runner bean to run it. Past that, it was a matter of working out some vendor-selection conventions.
The only tricky aspect turned out to be support for the more complex DDL needed by Oracle. The sample parsing code assumed ";" as a SQL statement delimiter, which doesn't work for embedded PL/SQL. I added primitive support for Oracle's "SET SQLTERMINATOR" statement to work around that.
Easy testing
Since no ORM tool is in place to automatically generate more-or-less accurate vendor-specific SQL based on generic queries, the Spring-JDBC developer (i.e., me) has to take responsibility for any dialect-specific problems. This makes it essential to run database-vendor-specific tests very frequently, which in turn means making them cheap to write and run.
By using some conventions, I'm able to share a single suite of DB-defining properties (basically an Oracle DB, a MySQL DB, and an hsqldb DB) between my normal deployment environments, my Sakai integration tests, and my lightweight Sakai-mocked automatically-run test code. Quickly testing the service against all three databases is a matter of entering these three lines from the top-level project directory:
mvn clean test # Test against in-memory hsqldb mvn -Dtest.sakai.home=C:/java/sakaisettings/oracle-sakai/ clean test mvn -Dtest.sakai.home=C:/java/sakaisettings/mysql-sakai/ clean test
Any of these tests can also be run inside Eclipse with a JUnit "Run" configuration that includes the appropriate "-D" flag in "Arguments : VM arguments:" and the appropriate JDBC libraries in "Classpath : User Entries".
Native generated keys
Each DB vendor has its own preferred way(s) to automatically generate identifier keys for new records. Hibernate provided a great labor-saver by papering over that difference. Out-of-the-box, Spring-JDBC doesn't have anything similar.
After a little tinkering though, it looked like we could avoid vendor-specific code for each table by subclassing JdbcTemplate and adding a new method modeled around JDBC 3.0's generated key support. I've got one working variation for hsqldb, and I believe the same approach would easily fit older versions of MySQL and Oracle. (The more recent versions of MySQL and Oracle I'm using support the new JDBC 3.0 standard.)