Hibernate的UUID与PostgreSQL和SQL Server [英] Hibernate UUID with PostgreSQL and SQL Server

查看：341
本文介绍了Hibernate的UUID与PostgreSQL和SQL Server的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个应用程序，我想在PostgreSQL和SQL Server上运行。我想使用java.util.UUID作为ID。



我已经在SQL Server中将我的列定义为

  id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE

我在PostgreSQL中的列为

  id UUID不为空

这些列在我的JPA实体中定义为 

  @Id 
 @Column（name =id）
 public UUID getId（）{
 return id; 
 
 
 
 $ b 
这适用于PostgreSQL，因为它将UUID传递给PostgreSQL JDBC驱动程序。这种工作适用于SQL Server，因为Hibernate在将UUID发送到SQL Server之前将其转换为二进制形式。 不幸的是，二进制格式稍有不同，导致GUID的字符串表示（例如当使用SSMS查看它们时）是不同的，这至少是令人困惑的。
 
 
 
这可以是通过将列的类型更改为uuid-char来补救SQL Server中的错误
 
 $ p code @d 
 @Type（type = uuid-char）
 @Column（name =id）
 public UUID getId（）{
 return id; 
 
 $ / code>

然而，它不再适用于PostgreSQL，因为没有隐式映射varchar在Postgres中运行。

有人建议更改生成器以生成GUID。这在Postgres中不起作用，因为在PostgreSQL94Dialect中没有支持。



为这两个数据库创建这个词最优雅的解决方案是什么？我正在考虑使用从UUID到二进制的自定义转换来创建我自己的SQLServer方言，但我不确定这是要走的路。 解决方案

我有类似的需求，但我也想使用Hibernate DDL生成，并确保SQL Server生成了唯一标识符类型，并且还希望支持hsqldb进行单元测试。
要在SQL Server中使用UUID，您肯定希望通过字符串而不是二进制，因为SQL Server中的二进制表示是针对与UUID不同的GUID。请参阅 Java Hibernate和SQL中UUID的不同表示服务器



您可以创建正确的映射并为SQL Server生成正确的SQL： 

 @Type（type =uuid-char）
 @Column（columnDefinition =uniqueidentifier）
 public UUID getUuid（）

而且这样工作，但不幸的是Hibernate没有办法为不同的数据库使用不同的columnDefinitions，所以这将会失败除了SQL Server以外的其他任何东西。



所以，你必须走很长的路：


  1. 在重写的SQLServerDialect中注册一个新的GUID sql类型，并使用此方言而不是基本方式



  
 public class SQLServer2008UnicodeDialect extends SQLServer2008Dialect 
 {
 public SQLServer2008UnicodeDialect（）
 {
 //该const来自MS JDBC驱动程序，值为-145 
 registerColumnType（microsoft.sql.Types.GUID，uniqueidentifier）; 
 
 //等等。Bonus hint：我也将所有varchar类型重新映射到nvarchar，而我也是这样：
 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））;




  1. 创建一个包装UUIDCustomType，其行为类似于内置的UUIDCharType，但根据数据库类型进行委派。您需要在 Hibernate配置之前调用init（databaseType）。也许自定义方言可以这样做，但我在我的Spring应用程序启动时调用了它。

DatabaseType是我已经设置的枚举基于系统配置，使用方言类或字符串或其他方法对其进行修改。



这是 https://zorq.net/b/2012/04/21/ switch-hibernates -uuid -type-mapping-per-database / 

  public enum DatabaseType 
 { 
 hsqldb，
 sqlserver，
 mysql，
 postgres 
} 
 
 public class UUIDCustomType extends AbstractSingleColumnStandardBasicType< UUID>实现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（）
 {
 returnuuid-custom; 
 
 $ b @Override 
 public String objectToSQLString（UUID value，Dialect dialect）throws Exception 
 {
 return StringType.INSTANCE.objectToSQLString（value.toString （），方言）; 
} 
 
 public static class SqlServerUUIDTypeDescriptor extends VarcharTypeDescriptor 
 {
 private static final long serialVersionUID = 1L; 
 
 public static final SqlServerUUIDTypeDescriptor INSTANCE = new SqlServerUUIDTypeDescriptor（）; 
 $ b $ public SqlServerUUIDTypeDescriptor（）
 {
} 
 
 @Override 
 public int getSqlType（）
 {
返回microsoft.sql.Types.GUID; 
} 
} 
}




  1. 将自定义类型注册到Hibernate将会选择的位置（我为所有实体都有一个公共基类）。我使用defaultForType = UUID.class注册它，以便所有UUID都使用它，这意味着我不需要注释UUID属性。



  
 @TypeDefs（{
 @TypeDef（name =uuid-custom，typeClass = UUIDCustomType.class，defaultForType = UUID.class）$ b $警告：实际上没有用postgres进行过测试，但工作正常非常适合hsqldb和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 (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.
 解决方案 
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:
    

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





    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)" );
        }
     }




    

  1. 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.
    2. 

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;
        }
    }
}




    

  1. 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.
    2. 





    @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.


                        
这篇关于Hibernate的UUID与PostgreSQL和SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆