Switching Hibernate’s UUID Type Mapping per Database

JDBC doesn’t include UUIDs as one of its generic SQL types and so the different relational databases supported by Hibernate have different mechanisms to save and retrieve UUIDs. PostgreSQL’s JDBC driver, for example, requires that you use Types.OTHER as a surrogate for the missing UUID constant:

preparedStatement.setObject( index, uuid, Types.OTHER );

Whereas H2 requires the simpler:

preparedStatement.setObject( index, uuid );

Developers may also choose not to use a database’s native mechanism, preferring instead to stipulate storage using a 36 character string or as an ordinary binary number. The way of telling Hibernate which mechanism you wish to use is done using an @Type annotation on the column:

@Type("pg-uuid")
private UUID id;

Or an @TypeDef annotation on the package object:

@TypeDef(
        name = "pg-uuid",
        defaultForType = UUID.class,
        typeClass = PostgresUUIDType.class
)
package com.example.domain;

This works fine with one database, but causes problems when you wish to test your application with a different database. The solution is to create another Hibernate type definition which can delegate to a database specific type depending on some environmental setting.

The following implementation loads the database dialect in use from a properties file on the classpath. Strictly speaking, this information is already available in the persistence.xml file, but extracting that would entail much more effort. This is done just once when the class is loaded, so shouldn’t result in a performance impact.

/**
 * @author David Beaumont
 * @see org.hibernate.type.PostgresUUIDType
 */
public class UUIDCustomType extends AbstractSingleColumnStandardBasicType {
 
    private static final long serialVersionUID = 902830399800029445L;
 
    private static final SqlTypeDescriptor SQL_DESCRIPTOR;
    private static final JavaTypeDescriptor TYPE_DESCRIPTOR;
 
    static {
        Properties properties = new Properties();
        try {
            ClassLoader loader = Thread.currentThread().getContextClassLoader();
            properties.load(loader.getResourceAsStream("database.properties"));
        } catch (IOException e) {
            throw new RuntimeException("Could not load properties!", e);
        }
 
        String dialect = properties.getProperty("dialect");
        if(dialect.equals("org.hibernate.dialect.PostgreSQLDialect")) {
            SQL_DESCRIPTOR = PostgresUUIDType.PostgresUUIDSqlTypeDescriptor.INSTANCE;
        } else if(dialect.equals("org.hibernate.dialect.H2Dialect")) {
            SQL_DESCRIPTOR = VarcharTypeDescriptor.INSTANCE;
        } else {
            throw new UnsupportedOperationException("Unsupported database!");
        }
 
        TYPE_DESCRIPTOR = UUIDTypeDescriptor.INSTANCE;
    }
 
    public UUIDCustomType() {
        super(SQL_DESCRIPTOR, TYPE_DESCRIPTOR);
    }
 
    @Override
    public String getName() {
        return "uuid-custom";
    }
 
}

Now you just have to register this new type as detailed above using @Type("uuid-custom").

Adding a MySQL datasource to JBoss AS 7

This post assumes you are using a Unix like system with a bash shell and have already downloaded and extracted the JBoss AS 7 application server.

Retrieve the MySQL Connector

The connector can be downloaded from: http://dev.mysql.com/downloads/connector/j/. The current version at the time of writing is 5.1.17, further sections of this guide assume that version number.

Unzip the connector to your downloads folder and save the location of the main JAR in a variable which we can refer to later:

cd ~/Downloads
unzip mysql-connector-java-5.1.17.zip
export MYSQL_JAR=~/Downloads/mysql-connector-5.1.17/mysql-connector-java-5.1.17-bin.jar

Add a Module to AS 7

AS 7 uses a module system to provide isolation in class loading. We need to create a new module which contains the MySQL Connector J JAR. Move to the the AS installation directory and create the folder structure for the new module:

export JBOSS_HOME=~/Development/jboss-as-web-7.0.0.Final
cd $JBOSS_HOME
mkdir -p modules/com/mysql/main

Copy the driver jar to the new directory and move to that directory:

cp $MYSQL_JAR $JBOSS_HOME/modules/com/mysql/main
cd $JBOSS_HOME/modules/com/mysql/main

Define the module in XML. This is the key part of the process:

vi module.xml

If the version of the jar has changed, remember to update it here:

<?xml version="1.0" encoding="UTF-8"?>
 
<module xmlns="urn:jboss:module:1.0" name="com.mysql">
  <resources>
    <resource-root path="mysql-connector-java-5.1.17-bin.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
  </dependencies>
</module>

The new module directory should now have the following contents:

module.xml
mysql-connector-java-5.1.17-bin.jar

Create a Driver Reference

Now the module has been created, we need to make a reference to it from the main application server configuration file:

cd $JBOSS_HOME/standalone/configuration
vi standalone.xml

Find the ‘drivers’ element and add a new driver to it:

<drivers>
    <driver name="mysql" module="com.mysql"/>
    <driver name="h2" module="com.h2database.h2">
        <xa-datasource-class>
            org.h2.jdbcx.JdbcDataSource
        </xa-datasource-class>
    </driver>
</drivers>

The ‘h2’ driver is part of the default JBoss configuration. The new driver that needs adding is named ‘mysql’.

Add the Datasource

Go into the configuration directory and open the main configuration file:

cd $JBOSS_HOME/standalone/configuration
vi standalone.xml

Find the datasources element and add a new datasource inside:

<datasource
        jndi-name="java:/mydb" pool-name="my_pool"
        enabled="true" jta="true"
        use-java-context="true" use-ccm="true">
    <connection-url>
        jdbc:mysql://localhost:3306/mydb
    </connection-url>
    <driver>
        mysql
    </driver>
    <security>
        <user-name>
            root
        </user-name>
        <password>
 
        </password>
    </security>
    <statement>
        <prepared-statement-cache-size>
            100
        </prepared-statement-cache-size>
        <share-prepared-statements/>
    </statement>
</datasource>

Start the Application Server

Now try running the application server. Make sure you run initially as a user which has write access to the modules directory where you placed the MySQL connector JAR. This is because the appication server seems to generate an index of the directories inside the JAR.

cd $JBOSS_HOME
bin/standalone.xml

Hopefully, amongst the application server’s console output you should see the following:

20:31:33,843 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-1) Bound data source [java:/mydb]