Thursday, November 03, 2005

Spring and RIFE; Creating Database Tables Together

Two posts ago, I showed how RIFE could be used to generate CREATE TABLE SQL statements.

Let me digress for just a paragraph about why I am exploring this technique to create database tables:

  • I'm hoping to use the Constraints mechanism used by RIFE to automate some DAO validation.
  • Using RIFE lets me use the same code to generate SQL specific to each database product.
  • My ultimate goal is to create a bean that actually creates the database table not just the SQL - more on this in future posts.
  • I dislike SQL scripts which don't provide the flexibility and robustness that Java provide - for example, logging and exception handling.

Today, I demonstrate how to integrate Spring and RIFE. Here are the files that are involved:

  • DatabaseTableCreator.java - This interface specifies the methods used to generate SQL.
  • BaseDatabaseTableCreator.java - This abstract class provides the CreateTable attribute to subclasses.
  • BeerTableCreator.java - This concrete class specifies the contstraints needed for the Beer database table.
  • PlayCreateTable.java - This driver file loads the Spring configuration file and instantiates beans.
  • spring.xml - This configuration file specifies the database configuration and wires the beans together.

DatabaseTableCreator.java

package org.affy.play;

import com.uwyn.rife.database.queries.CreateTable;

public interface DatabaseTableCreator {
    public String getSql();
    public String getTableName();
    public void setCreateTable(final CreateTable _createTable);
    public CreateTable getCreateTable();
}

BaseDatabaseTableCreator.java

package org.affy.play;

import com.uwyn.rife.database.queries.CreateTable;

abstract public class BaseDatabaseTableCreator implements DatabaseTableCreator {
    
    private CreateTable createTable = null;

    public BaseDatabaseTableCreator() {
        super();
    }

    abstract public String getSql();

    abstract public String getTableName();
    
    public CreateTable getCreateTable() {
        return this.createTable;
    }

    public void setCreateTable(CreateTable _createTable) {
        this.createTable = _createTable;
    }

}

BeerTableCreator.java

package org.affy.play;

import org.activemapper.Beer;

import com.uwyn.rife.database.DbConnection;
import com.uwyn.rife.database.DbStatement;
import com.uwyn.rife.database.queries.CreateTable;

public class BeerTableCreator extends BaseDatabaseTableCreator {

    public BeerTableCreator() {
        super();
    }

    public String getSql() {
        getCreateTable().table(getTableName())
        .columns(Beer.class)
        .primaryKey("id")
        .precision("brand", 50)
        .nullable("brand", CreateTable.NOTNULL);
        
        return getCreateTable().getSql();li>
    }

    public String getTableName() {
        return "beer";
    }

}

PlayCreateTable.java

package org.affy.play;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class PlayCreateTable {

    /**
     * @param args
     */
    public static void main(String[] args) {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
        
        DatabaseTableCreator beerTableCreator = (DatabaseTableCreator)ctx.getBean("beerTableCreator");
        System.out.println(beerTableCreator.getSql());
    }

}

spring.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE beans PUBLIC '-//SPRING//DTD BEAN//EN' 'http://www.springframework.org/dtd/spring-beans.dtd'>
<beans>

  <bean id='rifeDatasource' class='com.uwyn.rife.database.Datasource' lazy-init='true'>
    <property name='driver'><value>org.hsqldb.jdbcDriver</value></property>
    <property name='url' value='jdbc:hsqldb:file:data/relationalDb'/>
    <property name='user' value='sa'/>
    <property name='password' value=''/>
  </bean>

  <bean id='createTable' class='com.uwyn.rife.database.queries.CreateTable' lazy-init='true'>
    <constructor-arg ref='rifeDatasource'/>
  </bean>

  <bean id='beerTableCreator' class='org.affy.play.BeerTableCreator' lazy-init='true'>
    <property name='createTable'><ref bean='createTable'/></property>
  </bean>

</beans>

I don't feel that I need to add any commentary, the code above speaks for itself. If you have questions, feel free to ask questions.

Tuesday, November 01, 2005

Using UUID Values With RIFE CreateTable

As I mentioned in other entries, I like to use UUID values. The current version of RIFE doesn't handle UUID properties correctly. However, a few minutes digging into the source code showed me that the changes needed for support where trivial.

I created a com.uwyn.rife.database.types.databasedrivers package in my own project and then copied the org_hsqldb_jdbcDriver.java into it.Then I updated the getSqlType() method by added the highlighted code shown below immediately after the test for the character class.

  else if (type == java.util.UUID.class) {
    return "VARCHAR(36)";
  }

I'd love for this change to be propagated to the rest of the driver files and slipped into an upcoming release.

Using RIFE CreateTable to Generate CREATE TABLE Sql

The RIFE framework has functionality which abstracts the Sql Create Table statement into Java classes. The fundamental reason to use an abstraction instead of writing the Sql directly is that the underlying framework generates Sql specific to the targeted database. Why is this important? Frequently I use an open-source database like Hypersonic for development but a commercial product like Oracle for production.

The following code demonstrates the technique:

  String driverClassname = "org.hsqldb.jdbcDriver";
  String url = "jdbc:hsqldb:hsql://localhost:9101/test";
  String username = "sa";
  String password = "";
  String poolSize = 5;

  Datasource ds = new Datasource(driverClassname, url, username, password, poolSize);
        
  CreateTable create = new CreateTable(ds);
         
  create.table("beer")
    .columns(Beer.class)
    .primaryKey("id")
    .precision("brand", 50)
    .nullable("brand", CreateTable.NOTNULL);

  String createSql = create.getSql();

When executed, the generated SQL looks like this:

CREATE TABLE beer (brand VARCHAR(50) NOT NULL, id INTEGER NOT NULL, price NUMERIC, PRIMARY KEY (id))

For completeness, here are the relevant parts of Beer.java

public class Beer {

    private String brand = null;

    private BigDecimal price = null;

    private int id = 0;

    // ... snipped out getters and setters.
}

ActiveMapper: Using UUID Values for Primary Keys

I am a big fan of using UUID values as primary keys but I won't use this blog to articulate why. Other people in the blogosphere have argued back and forth on the subject of primary keys - you don't need me to repeat the arguments.

If you do want to use UUID values, then ActiveMapper will not be useful in its current version. The good news is that adding support is trivally easy.

In order to use UUID values with ActiveMapper you need to do three things:

  1. Update the ActiveMapper.assignNewId() method as shown below - just add the two highlighted lines.
        protected Object assignNewId(Object o) {
            PersistentField pf = (PersistentField) persistentObject.getPersistentFields().get("id");
            Object newId = null;
            if (pf.getJavaType() == (java.lang.Long.class))
                newId = new Long(persistentObject.
    getIncrementer().nextLongValue());
            else if (pf.getJavaType() == UUID.class)
                newId = UUID.randomUUID();
            else if (pf.getJavaType() == (java.lang.Integer.class))
                newId = new Integer(persistentObject.getIncrementer().
    nextIntValue());
            try {
                Method m = o.getClass().getMethod(ActiveMapperUtils.
    setterName(pf.getFieldName()), new Class[] { newId.getClass() });
                m.invoke(o, new Object[] { newId });
            } catch (NoSuchMethodException e1) {
                e1.printStackTrace();
            } catch (IllegalAccessException e1) {
                e1.printStackTrace();
            } catch (InvocationTargetException e1) {
                e1.printStackTrace();
            }
            return newId;
        }
    
  2. Use the following instance variable in your domain objects:
    private UUID id = null;
    
  3. Use the following field definition when creating SQL tables:
    id char(36) not null
    

ActiveMapper; Making Java DAO with Reflection and Spring

I just learned about Thomas Risberg's ActiveMapper set of Java classes. They were inspired by Ruby on Rails and us reflection to perform some housekeeping to make persistence easier. I encourage you to visit his blog at http://jroller.com/page/buggybean.