Hibernate UUID with PostgreSQL and SQL Server
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 (eg 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.
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:
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.
I ended up writing my own Dialect and BasicType that links the Java type java.util.UUID to uuid-char by default.
This class is inspired by the PostgreSQLDialect
public class MySQLServerDialect extends SQLServer2012Dialect {
public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
super.contributeTypes( typeContributions, serviceRegistry );
typeContributions.contributeType( SQLServerUUIDType.INSTANCE );
}
}
This class is inspired by the UUIDCharType
public class SQLServerUUIDType extends AbstractSingleColumnStandardBasicType<UUID> implements LiteralType<UUID> {
public static final SQLServerUUIDType INSTANCE = new SQLServerUUIDType();
public SQLServerUUIDType() {
super( VarcharTypeDescriptor.INSTANCE, UUIDTypeDescriptor.INSTANCE );
}
@Override
protected boolean registerUnderJavaType() {
return true;
}
public String getName() {
return "my-uuid";
}
public String objectToSQLString(UUID value, Dialect dialect) throws Exception {
return StringType.INSTANCE.objectToSQLString( value.toString(), dialect );
}
}
I don't think it's the most elegant solution, but it works for now.
链接地址: http://www.djcxy.com/p/91454.html上一篇: 如何在Netbeans中生成Guid？