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

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