Hibernate: Create Index

Question

I want to create several Indexes in my DB. Unfortunately we have to change the persistence provider from EclipseLink to Hibernate, but nor the solution with javax.persistence.Index neither the solution with Hibernate works.

This is what the class looks like:

@Entity
@Table(name = "my_shop")
public class Shop extends BaseEntity {
    @Temporal(TemporalType.TIMESTAMP)
    @Column(nullable = false)
    private Calendar lastUpdate;
}

This should be the solution with javax.persistence.*:

import javax.persistence.Index;
import javax.persistence.Table;
@Table(name = "my_shop",
        indexes = @Index(columnList = "lastupdate")
)

The Hibernate annotations are deprecated, so there must be a reason not to use these annotations:

import org.hibernate.annotations.Index; // deprecated
import org.hibernate.annotations.Table;
@Table(...,
        indexes = @Index(columnNames = "lastupdate")
)

I use Glassfish 3.1.2.2, PostgreSQL 9.1, JPA 2.1 and hibernate-core 4.3.4.Final. If I look in the database, there are no indexes created on the specific field via psql "\d+".

This what my persistence.xml looks like:

...
    <property name="hibernate.hbm2ddl.auto" value="create"/>
    <property name="dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
...

Only EclipseLink can handle this easily:

import org.eclipse.persistence.annotations.Index;
@Entity
@Table(name = "my_shop")
public class Shop extends BaseEntity {
    @Index
    @Temporal(TemporalType.TIMESTAMP)
    @Column(nullable = false)
    private Calendar lastUpdate;
}

I tested the given solutions with all combinations "lastupdate", "lastUpdate" and additional "name" attributes in @Column and @Index, but nothing seems to work.

Update 1

Indeed this solution works:

@javax.persistence.Table(name = "my_shop")
@Table(appliesTo = "my_shop"
        ,indexes = {@Index(columnNames = "name", name = "name"),
                @Index(columnNames = "lastupdate", name = "lastupdate")}
)

But still org.hibernate.annotations.Index; is marked as deprecated. So is it good practice to use it or not? If not what's the alternative because apparently javax.persistence.Index doesn't work.

org.hibernate.annotations.Index; works with every value: create, update, ... javax.persistence.Index doesn't matter which value "hibernate.hbm2ddl.auto" has, doesn't work.

Solution 1

I use JPA 2.1 with Hibernate 4.3 and PostgreSQL 9.3. I have no problems with indexes

hibernate-commons-annotations-4.0.4.Final.jar
hibernate-core-4.3.1.Final.jar
hibernate-jpa-2.1-api-1.0.0.Final.jar
jandex-1.1.0.Final.jar
javassist-3.18.1-GA.jar
jboss-transaction-api_1.2_spec-1.0.0.Final.jar

Though my config has

<property name="hibernate.hbm2ddl.auto" value="update"/>

And that's my entity mapping

import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;
@Entity
@Table(name = "users", indexes = {
        @Index(columnList = "id", name = "user_id_hidx"),
        @Index(columnList = "current_city", name = "cbplayer_current_city_hidx")
})

PS. In fact, I have some problems with that annotations. I cannot specify tablespace for index and must create indecies for subclasses in parent class for SINGLE_TABLE hierarchy.

Solution 2

With Hibernate you need to enter the name attribute in the @Index annotation.

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;
@Entity
@Table(
        indexes = {
            @Index(columnList = "description", name = "product_description")
        })
public class Product implements Serializable {
     // ...
     private String description;
     // getters and setters
}

With EclipseLink is not required, it creates the name automatically.

Solution 3

• JAVAX

JAVAX package already included because of JPA or JERSEY or Spring Boot using Jersey so we should go with

 @Table(name = "userDetails", indexes={@Index(columnList="uid,name",name="Index_user")})

Benefits :

• We don't need to add any extra dependency
• We can also define unique key inside @Table
• Hibernate Same thing we can do with @Index, but for that, we need to add Hibernate dependency.

Solution 4

The @Index annotation only works with hibernate.hbm2ddl.auto=create-drop, see this entry in the Hibernate forums.

Just one word of caution is that this option drops the tables, but in general hibernate.hbm2ddl.auto is meant for development purposes only.

Solution 5

To sum up:

• JPA 2.1: javax.persistence.Index (see JSR-000338, p. 450, item 11.1.23)
• Hibernate ORM: org.hibernate.annotations.Index

View more solutions

Comments

[danbst]

updated my answer to show that I have everything working with JPA

[dedek]

Works for me! (just did not know that unique fields are indexed by default...)

[RobertG]

Your link is broken: jcp.org/aboutJava/communityprocess/final/jsr338/index.html

[gavenkoa]

I see another official statements: hibernate.atlassian.net/browse/HHH-1012 Request to propagate index with hibernate.hbm2ddl.auto=update resolved in 3.2.x, 3.3.x, 3.5.0-Beta-2