java - Hibernate UUID with PostgreSQL and SQL Server

Question

I have an application I would like to run on both PostgreSQL and SQL Server. I would like to use java.util.UUID as the IDs.

I have defined my columns in SQL Server as

id  UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE

I have defined my columns in PostgreSQL as

id  UUID NOT NULL

The columns are defined in my JPA Entities as

@Id
@Column(name = "id")
public UUID getId() {
    return id;
}

This works for PostgreSQL as it passes the UUID to the PostgreSQL JDBC driver. This sort of works for SQL Server, as Hibernate translates the UUID to its binary form before sending it to SQL Server. Unfortunately the binary format is slightly different, causing the string representation of the GUIDs (e.g. when looking at them using SSMS) to be different, which is at the very least confusing.

This can be remedied in SQL Server by changing the type of the column to uuid-char

@Id
@Type(type = "uuid-char")
@Column(name = "id")
public UUID getId() {
    return id;
}

However it then no longer works in PostgreSQL as there is no implicit mapping from varchar to uuid in Postgres.

Some people suggest a change in the generator to generate guids. This does not work in Postgres as there is no support for that in the PostgreSQL94Dialect.

What would be the most elegant solution to make this word for both databases? I was thinking about creating my own Dialect for SQLServer with a custom conversion from UUID to binary, but I'm not sure that's the way to go.

Answer 1

I had similar requirements, but I also wanted to use Hibernate DDL generation and make sure that SQL Server generated a uniqueidentifier type, and also wanted to support hsqldb for unit testing. To use UUIDs in SQL Server you definitely want to go through strings not binary, as the binary representation in SQL Server is for a GUID which is different from a UUID. See for example Different representation of UUID in Java Hibernate and SQL Server

You can create the correct mapping and generate the correct sql for SQL Server with:

@Type(type = "uuid-char")
@Column(columnDefinition="uniqueidentifier")
public UUID getUuid()

And this just works as is, but unfortunately there is no way in Hibernate to have different columnDefinitions for different databases, so this will fail on anything other than SQL Server.

So, you have to go the long way around. This is all for Hibernate 4.3:

1. Register a new GUID sql type in an overridden SQLServerDialect, and use this dialect instead of the base one

    public class SQLServer2008UnicodeDialect extends SQLServer2008Dialect
    {
        public SQLServer2008UnicodeDialect() 
        {
            // the const is from the MS JDBC driver, the value is -145
            registerColumnType( microsoft.sql.Types.GUID, "uniqueidentifier" ); 

            // etc. Bonus hint: I also remap all the varchar types to nvarchar while I'm at it, like so:
            registerColumnType( Types.CLOB, "nvarchar(MAX)" );
            registerColumnType( Types.LONGVARCHAR, "nvarchar(MAX)" );
            registerColumnType( Types.LONGNVARCHAR, "nvarchar(MAX)" );
            registerColumnType( Types.VARCHAR, "nvarchar(MAX)" );
            registerColumnType( Types.VARCHAR, 8000, "nvarchar($l)" );
        }
     }

2. Create a wrapper UUIDCustomType that behaves similar to the built in UUIDCharType but delegates depending on the database type. You need to call init(databaseType) before Hibernate configuration. Maybe the custom dialect could do it, but I call this in my Spring app startup.

DatabaseType was an enum I already had that's set based on system config, modify it to taste using a dialect class or string or whatever.

This is a variation on what's described at https://zorq.net/b/2012/04/21/switching-hibernates-uuid-type-mapping-per-database/

public enum DatabaseType
{
    hsqldb,
    sqlserver,
    mysql,
    postgres
}

public class UUIDCustomType extends AbstractSingleColumnStandardBasicType<UUID> implements LiteralType<UUID>
{
    private static final long serialVersionUID = 1L;

    private static SqlTypeDescriptor SQL_DESCRIPTOR;
    private static JavaTypeDescriptor<UUID> TYPE_DESCRIPTOR;

    public static void init( DatabaseType databaseType )
    {
        if ( databaseType == DatabaseType.sqlserver )
        {
            SQL_DESCRIPTOR = SqlServerUUIDTypeDescriptor.INSTANCE;
        }
        else if ( databaseType == DatabaseType.postgres  )
        {
            SQL_DESCRIPTOR = PostgresUUIDType.PostgresUUIDSqlTypeDescriptor.INSTANCE;
        }
        else
        {
            SQL_DESCRIPTOR = VarcharTypeDescriptor.INSTANCE;
        }

        TYPE_DESCRIPTOR = UUIDTypeDescriptor.INSTANCE;
    }

    public UUIDCustomType()
    {
        super( SQL_DESCRIPTOR, TYPE_DESCRIPTOR );
    }

    @Override
    public String getName()
    {
        return "uuid-custom";
    }

    @Override
    public String objectToSQLString( UUID value, Dialect dialect ) throws Exception
    {
        return StringType.INSTANCE.objectToSQLString( value.toString(), dialect );
    }

    public static class SqlServerUUIDTypeDescriptor extends VarcharTypeDescriptor
    {
        private static final long serialVersionUID = 1L;

        public static final SqlServerUUIDTypeDescriptor INSTANCE = new SqlServerUUIDTypeDescriptor();

        public SqlServerUUIDTypeDescriptor()
        {
        }

        @Override
        public int getSqlType()
        {
            return microsoft.sql.Types.GUID;
        }
    }
}

3. Register the custom type in a location that Hibernate will pick up (I have a common base class for all entities). I register it using defaultForType = UUID.class so that all UUIDs use it, which means I don't need to annotate UUID properties at all.

    @TypeDefs( {
            @TypeDef( name = "uuid-custom", typeClass = UUIDCustomType.class, defaultForType = UUID.class )
    } )
    public class BaseEntityWithId { 

Caveat: not actually tested with postgres, but working great for hsqldb and sql server on Hibernate 4.3.