Creating Indexes on DB with Hibernate @Index Annotation

Question

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.

Excoriate

Answer 1

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!

Excoriate

Yeah, I noticed this behavior also. +1 all around. – Benisch

But is that auto.create setting safe for production? https://mcmap.net/q/46599/-hibernate-hbm2ddl-auto-update-in-production if not,how do you handle indexes with hibernate? – Briefing

hibernate.atlassian.net/browse/HHH-1012 fixed, now works with update too. – Newtonnext

Answer 2

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

Ethe

just to back up this answer - updating the database to create indices is working for me using hibernate 4.1.3 and mysq. Indexes are created for me automatically without dropping and recreating tables if I add an @Index annotation to an entity. – Elsyelton

Answer 3

Better DB design means the schema is owned by a different user than the data itself. Hence I set hibernate.hbm2ddl.auto=none so there are no failures upon Hibernate start. I use a SchemaPrinter instead. The output of which can be run via my favorite SQL tool to recreate the schema when required.

import java.io.IOException;

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class SchemaPrinter {

    public static void main(String[] args) throws IOException {

        Configuration cfg = new AnnotationConfiguration()
            .addAnnotatedClass(MyClass1.class)
            .addAnnotatedClass(MyClass2.class)
            .setProperty(Environment.USER, "user")
            .setProperty(Environment.PASS, "password")
            .setProperty(Environment.URL, "jdbc:sybase:jndi:file://sql.ini?mydb")
            .setProperty(Environment.DIALECT, "org.hibernate.dialect.SybaseASE15Dialect")
            .setProperty(Environment.DRIVER, "com.sybase.jdbc4.jdbc.SybDriver")
            .setProperty(Environment.HBM2DDL_AUTO, "none")
        SchemaExport exp = new SchemaExport(cfg);
        exp.setOutputFile("schema.ddl");
        exp.create(true, false);
    }

}

Micrococcus

Answer 4

In Hibernate 3.5.6 using <property name="hibernate.hbm2ddl.auto">update</property> the indexes are created. So a proper answer now would be to upgrade. But I'm leaving this answer for those like me that have come across this question.

Bloodstained

Yes. hibernate.atlassian.net/browse/HHH-1012 fixed in 3.2.x, 3.3.x, 3.5.0-Beta-2 – Newtonnext