How to use Hibernate Annotations to add an index on a Lob / Clob / tinyblob

Question

Hi,

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.

Answer 1

What you really want to do is to define your UUID property as a String rather than a byte array. That way Hibernate will map it to a character column in the database rather than a LOB, and I think it'll create the index you want.

Answer 2

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.