如何使用Hibernate Annotations在Lob / Clob / tinyblob上添加索引

问题描述

我有一个使用Hibernate映射到数据库的bean。我使用Hibernate Annotations来指示我想要的映射，并创建索引。完全简化的代码如下所示。

我遇到的问题是我的byte []字段上的索引没有创建;特别是我的多字段索引sysUuid没有被创建（参见示例代码）。在Hibernate调试日志中，我甚至没有看到尝试创建索引！

我想指出uuid字段上的@Index注释也会不会导致数据库的索引。

我知道如何用MySQL创建索引：

  create index sysuuid on persons（system，`uuid`（8））; 
  

其中有趣的特性是uuid需要被转义（因为它是MySQL函数）需要在字段上给出一个长度（与文本字段一样）。

然而，我还没有找到一种方法来使用Hibernate Annotations给出索引长度字段，所以我不能测试是否是问题所在。然而，确定的是，在注释中命名字段uuid（8）不起作用。

  @Entity 
 // UniqueConstraints工作
 @Table（name =persons，
 uniqueConstraints = {@UniqueConstraint（columnNames = {uid，system}）}）
 //但这些不会生成索引
 @ org.hibernate.annotations.Table（applicableTo =persons，
 indexes = {@ Index（name =sysUuid，columnNames = {system， uuid}）}）
 public class Person {
 @Basic 
 @NotNull 
 private String uid; 
 
 @Basic 
 private int system; 
 
 //获取映射到tinyblob 
 @Basic 
 @Size（min = 16，max = 16）
 private byte [] uuid; 
 
 // getters and setters here 
} 
  

'd想问你：是否可以使用注释在一个lob上添加一个索引，如果是这样，如何？

编辑

对于我来说移动到一个基于字符串的UUID确实是可能的，但是我并不是那么舒服，因为uuid在概念上是一个16字节的标识符。

我非常喜欢Java类型来匹配问题域。

正如我所说 - 我确实有方便的SQL语句，以便我可以部署代码+ SQL脚本。我认为在任何可行的情况下最好都有自我记录的代码。

编辑&添加赏金

我相信我所需要的索引不能使用Hibernate Annotations创建（参见Matt Solnit的回答）。然而，我希望了解更多有关使用Hibernate Annotations创建索引的更多信息，这样最终的答案就是记录
API的局限性。

您可以使用Hibernate的支持，但不能使用注释完成： - （。

在您的示例中，它看起来像这样（为简洁起见，省略了大量内容）：

 < class name =Persontable =persons> 
<！ -  whatever  - > 
< database-object> 
< create> create index sysuuid on persons （system，`uuid`（8））< / create> 
< drop> drop index sysuuid< / drop> 
< dialect-scope name =org.hibernate.dialect.MySQL5InnoDBDialect /> 
< /数据库对象> 
< / class> 
  

对于缺乏基于注解的答案，我表示歉意:-(希望这会有所帮助。注意：如果你确实采用这种方法，请注意方言范围必须完全匹配 。例如，如果你的Hibernate配置要求使用 MySQL5InnoDBDialect ，那么你必须在< dialect-scope> 元素中使用这种方言使用 MySQLDialect 即使它是InnoDB方言的超类也不行。

I have a bean which I map to the database using Hibernate. I'm using Hibernate Annotations to indicate the mapping I want, and to create the indices. The thoroughly simplified code is shown below.

The problem I have is that the indices on my byte[] field are not created; specifically that my multi-field index sysUuid does not get created (see example code). In the Hibernate debug logs I do not even see an attempt to create an index!

I'd like to point out that an @Index annotation on the uuid field also does not result in an index on the database.

I do know how to create an index by hand using MySQL:

create index sysuuid on persons ( system, `uuid`(8) );

where the interesting features are that uuid needs to be escaped (as it is a MySQL function) and that a length needs to be given on the field (as with text fields).

I however have not found a way to give the index length field using Hibernate Annotations so I cannot test wether that is the problem. It is however certain that naming the field "uuid(8)" in the annotation does not work.

@Entity
// The UniqueConstraints work 
@Table(name = "persons", 
   uniqueConstraints = {@UniqueConstraint(columnNames = {"uid", "system"}) } )
// but these don't generate an index
@org.hibernate.annotations.Table(appliesTo="persons", 
   indexes={@Index(name="sysUuid",  columnNames={"system", "uuid"})  } )
public class Person  {
    @Basic 
    @NotNull
    private String uid;

    @Basic
    private int system;

    // Gets mapped to tinyblob
    @Basic
    @Size(min = 16, max = 16)
    private byte[] uuid;

    // getters and setters here 
}

What I'd like to ask you is: Is it possible to add an index on a lob using an annotation, and if so, how?

EDIT

It is indeed possible for me to move to a String-based UUID, but I'm not really comfortable with that as uuid is conceptually a 16-byte identifier.

I strongly prefer the Java types to match the problem domain.

And as I said - I do have an SQL statement handy so I can deploy the code + a SQL script. I just think it's better do have self-documenting code whenever feasible.

EDIT & Added Bounty

I believe the index I need cannot be created using Hibernate Annotations (re. Matt Solnit's answer).

I however would appreciate a bit more information about creating indices with Hibernate Annotations in general so the eventual answer ends up documenting the limitations of the API.

解决方案

You could do this using Hibernate's auxiliary objects support, but it cannot be done using annotations :-(.

In your example, it would look something like this (lots of stuff omitted for brevity):

<class name="Person" table="persons">
  <!-- whatever -->
  <database-object>
    <create>create index sysuuid on persons ( system, `uuid`(8) )</create>
    <drop>drop index sysuuid</drop>
    <dialect-scope name="org.hibernate.dialect.MySQL5InnoDBDialect" />
  </database-object>
</class>

I apologize for the lack of an annotation-based answer :-(. Hopefully this helps.

NOTE: If you do take this approach, be aware that the dialect scope has to match exactly. For example, if your Hibernate configuration says to use MySQL5InnoDBDialect, then you must have this dialect in the <dialect-scope> element as well. Using MySQLDialect will not work even though it is the super-class of the InnoDB dialect.

这篇关于如何使用Hibernate Annotations在Lob / Clob / tinyblob上添加索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！