Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating Indexes on DB with Hibernate @Index Annotation

I have annotation-driven hibernate capabilies on my project.

Now I want to create an index over a column. My current column definition is

@NotNull
@Column(name = "hash")
private String hash;

and I add @Index annotation here.

@NotNull
@Column(name = "hash")
@Index(name="hashIndex")
private String hash;

and then DROP TABLE and restart Tomcat server. After the server is instantiated, the table is created but I can't see new index on following query.

SHOW INDEX FROM tableName

It is expected to construct table with new index. I am using InnoDB with MySQL.

like image 355
ahmet alp balkan Avatar asked Aug 20 '10 17:08

ahmet alp balkan


People also ask

Does Hibernate use index?

As the others already mentioned: Hibernated doesn't decide to use or not use an index. Your database does.

What is Hibernate indexing?

Indexing. The short answer is that indexing is automatic: Hibernate Search will transparently index every entity each time it's persisted, updated or removed through Hibernate ORM. Its mission is to keep the index and your database in sync, allowing you to forget about this problem.

What is @basic annotation in Hibernate?

We can use the @Basic annotation to mark a basic type property: @Entity public class Course { @Basic @Id private int id; @Basic private String name; ... } In other words, the @Basic annotation on a field or a property signifies that it's a basic type and Hibernate should use the standard mapping for its persistence.

What is the use of @entity annotation in Hibernate?

@Entity annotation marks this class as an entity. @Table annotation specifies the table name where data of this entity is to be persisted. If you don't use @Table annotation, hibernate will use the class name as the table name by default.


2 Answers

Interestingly, in my Hibernate configuration I was using hibernate.hbm2ddl.auto=update.

This one modifies an existing database. I was manually DROPping the table tableName and restarting Tomcat and the table had been constructed but index was not being created.

However, I made hibernate.hbm2ddl.auto=create which re-creates database upon each instantiation of webapp, it dropped all my database and rebuilt back and -hell yeah- my new index has been created!

like image 162
ahmet alp balkan Avatar answered Sep 22 '22 08:09

ahmet alp balkan


Index creation on schema update was intentionally disabled in Hibernate because it seemed inconsistent with the naming used in the schema export.

This is the commented code that you can find in class org.hibernate.cfg.Configuration.

//broken, 'cos we don't generate these with names in SchemaExport
subIter = table.getIndexIterator();
while ( subIter.hasNext() ) {
    Index index = (Index) subIter.next();
    if ( !index.isForeignKey() || !dialect.hasImplicitIndexForForeignKey() ) {
        if ( tableInfo==null || tableInfo.getIndexMetadata( index.getFilterName() ) == null ) {
            script.add( index.sqlCreateString(dialect, mapping) );
        }
    }
}
//broken, 'cos we don't generate these with names in SchemaExport
subIter = table.getUniqueKeyIterator();
while ( subIter.hasNext() ) {
    UniqueKey uk = (UniqueKey) subIter.next();
    if ( tableInfo==null || tableInfo.getIndexMetadata( uk.getFilterName() ) == null ) {
        script.add( uk.sqlCreateString(dialect, mapping) );
    }
}

Usually I remove that comment, recompile Hibernate.jar and have indexes created on schema update without any problem, at least with Oracle DB.

In recent versions of Hibernate the comment on the first part (table indexes) has been removed in the official version as well, while it's still commented the second one (indexes that implement unique keys). See the discussion at http://opensource.atlassian.com/projects/hibernate/browse/HHH-1012

like image 29
Stefano Travelli Avatar answered Sep 26 '22 08:09

Stefano Travelli