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.
Unfortunately, while PostgreSQL is great for storing and comparing UUID data, it lacks capabilities for creating UUID values in its core. Instead, it relies on third-party modules to create UUIDs using specified techniques.
This annotation defines the Hibernate type mapping. Using “uuid-char” instructs Hibernate to store the UUID as a String, instead of the binary value. This way, it can be written to and read from a VARCHAR(36) column without the need for any pre-processing on our side.
You can use the Microsoft SQL Server Management Studio to connect your PostgreSQL data to an SQL Server instance. Linked Server is a tool of MS SQL Server that allows to execute distributed queries to refer tables stored on non-SQL Server datbase in a single query.
UUID stands for Universally Unique Identifier. UUID are standardized by the Open Software Foundation (OSF). It is a part of Distributive Computing Environment (DCE). A UUID is 36 characters long unique number.
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:
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)" );
}
}
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;
}
}
}
@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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With