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.
</ul> </p>

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.

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.