CODEWITHSUNDEEP
javahibernatepostgresqljpa
Rooky
Rooky

Reputation: 840

Hibernate: Create Index

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.

Upvotes: 27

Views: 80552

Answers (5)

Tapan
Tapan

Reputation: 61

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

Upvotes: 5

Douglas Nassif Roma Junior
Douglas Nassif Roma Junior

Reputation: 2102

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.

Upvotes: 11

naXa stands with Ukraine
naXa stands with Ukraine

Reputation: 37926

To sum up:

Upvotes: 3

danbst
danbst

Reputation: 3613

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.

Upvotes: 40

Angular University
Angular University

Reputation: 43087

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.

Upvotes: 5

Related Questions