使用Hibernate @Index注释在DB上创建索引

I have annotation-driven hibernate capabilies on my project.

我的项目中有注释驱动的hibernate功能。

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.

我在这里添加@Index注释。

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

然后DROP TABLE并重启Tomcat服务器。在实例化服务器之后，会创建表，但我无法在后续查询中看到新索引。

SHOW INDEX FROM tableName

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

期望用新索引构造表。我正在使用带有MySQL的InnoDB。

4 个解决方案

#1

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

有趣的是，在我的Hibernate配置中，我使用的是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.

这个修改了现有的数据库。我手动DROPping表tableName并重新启动Tomcat并且表已经构建但是没有创建索引。

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!

但是，我做了hibernate.hbm2ddl.auto = create，它在webapp的每个实例化时重新创建数据库，它删除了我的所有数据库并重新生成了-hell是的 - 我的新索引已经创建了！

#2

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

在Hibernate中故意禁用模式更新上的索引创建，因为它似乎与模式导出中使用的命名不一致。

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

这是您可以在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.

通常我删除该注释，重新编译Hibernate.jar并在架构更新上创建索引而没有任何问题，至少对于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

在最新版本的Hibernate中，第一部分（表索引）的注释也已在官方版本中删除，而它仍然评论第二部分（实现唯一键的索引）。请参阅http://opensource.atlassian.com/projects/hibernate/browse/HHH-1012上的讨论

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

更好的数据库设计意味着模式由与数据本身不同的用户拥有。因此我设置了hibernate.hbm2ddl.auto = none，因此在Hibernate启动时没有失败。我使用SchemaPrinter代替。可以通过我最喜欢的SQL工具运行其输出，以在需要时重新创建模式。

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

}

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

在Hibernate 3.5.6中使用 update 创建索引。所以现在一个正确的答案就是升级。但是，对于像我这样遇到过这个问题的人，我会留下这个答案。

#1