Wednesday, November 05, 2008

Re-inventing ORM, annotations re-invented for databases!

I am working on a web application where one of the requirements is that I use an in-house Java database access framework. Personally I would prefer to use something that complements Spring and that makes my life easier such as Hibernate Annotations, iBATIS or even Spring JDBCTemplates but requirements are requirements.

One of the problems I face is that I need to re-invent a lot of ORM wheels. The framework is very similar in essence to iBATIS.All the SQL is stored in external XML configuration files. The framework conducts the mappings conversion from ResultSets to lists of beans (using commons-beanutils). As such, like iBATIS there is plenty of scope to generate most of the repetitive Java code (interfaces, implementations, facades) and configuration files (SQL CRUD). I have previously experimented with the Middlegen code generation with iBATIS. Middlegen code is a little long in the tooth, as a project it seems to have stagnated back in 2005 (I suppose iBATOR and Hibernate Tools have long since superseded it). That said, because Middlegen is based on Velocity templates it is very flexible and perfect for this kind of thing. I was able to port my previous iBATIS code generating templates to produce suitable Java code for use with the in-house framework.

I have managed to get some quite sophisticated code generated. Apparently "Lazy Loading is easy", so I am attempting to implement that using CGLIB in my DAO. I have also produced a method that is similar to Hibernates's SaveOrUpdate method. A given entity may have two kinds of relationship; target one (OneToOne, ManyToOne) or target many (OneToMany). I have no ManyToMany relationships and this is deliberate!

How my SaveOrUpdate works is derived from some code I found in iBATIS in Action (By Clinton Begin, Brandon Goodin and Larry Meadors, Published by Manning). In section 5.3.2 Updating or deleting child records the following method is suggested:

public void saveOrder(SqlMapClient sqlMapClient, Order order)
throws SQLException {
if (null == order.getOrderId()) {
sqlMapClient.insert("Order.insert", order);
} else {
sqlMapClient.update("Order.update", order);
sqlMapClient.delete("Order.deleteDetails", order);
for(int i=0;i<order.getOrderItems().size();i++) {
OrderItem oi = (OrderItem) order.getOrderItems().get(i);
sqlMapClient.insert("OrderItem.insert", oi);

The above method addresses the target many relationships but does not address the target one relationships. My method works more like this:

  1. Iterate through all the target one entities and SaveOrUpdate these. Inserting any generated ids into the current entity.
  2. Save the current entity data fields
  3. Iterate through all the target many relationships and SaveOrUpdate these.

At this point I got a little stuck. I found that relatively often my target one relationships should not be the subject of further SaveOrUpdates. The characteristic of these relationships is when the data is fairly static and mostly provides the conent of drop-down lists. When you make a selection from a such a list you do not usually need to update the list itself!

Essentially, Middlegen generates everything based on the contents of the database. For these relationships I needed some way to designate the code generation behaviour for certain database columns.

In Java Persistence API implementations, like Hibernate Annotations, this kind of behaviour is very easily represented by using @Column(insertable=false, updatable=false). I needed a way for Middlegen to acquire this information from the database. Then I had a brainwave, in the most recent version of Middlegen (from the CVS) you can "getRemarks" for tables and columns. Database remarks can be acquired from DatabaseMetaData. The unfortunate thing is that I am using an Oracle database which AFAICT uses a non-standard treatment of "remarks". It turns out that the default behaviour of Oracle drivers is not to populate the DatabaseMetaData with remarks. In Oracle remarks are contained inside two special tables; user_tab_comments and user_col_comments. You can override the default behaviour of Oracle driver to populate remarks, this can be done at the BasicDataSource (using addConnectionProperty("remarksReporting", "true")) or on a OracleConnection (using setRemarksReporting(true)).

Armed with this information I can "annotate" database columns, so I can use something like this:

COMMENT ON COLUMN <table>.<column> IS 'insertable=false, updatable=false';

I tweaked MiddlegenPopulator to allow it to acquire remarks for Oracle from the DatabaseMetaData. The database now contains all the information I need to generate my DAO code. I can now tweak my DAO generation templates to miss out the SaveOrUpdate code for target one relationships which match the column comment of 'insertable=false, updatable=false'.

Simple huh? Or maybe just plain scary... annotations with databases!