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.

danbst · Accepted Answer · 2014-04-03 14:25:51Z

41

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.

edited Apr 3, 2014 at 14:25

answered Apr 3, 2014 at 6:07

danbst

3,70322 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

danbst Over a year ago

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

dedek Over a year ago

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

Douglas Nassif Roma Junior · Accepted Answer · 2016-04-22 15:02:30Z

12

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.

answered Apr 22, 2016 at 15:02

Douglas Nassif Roma Junior

2,1223 gold badges28 silver badges55 bronze badges

Comments

Angular University · Accepted Answer · 2014-04-03 09:17:12Z

5

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.

edited Apr 3, 2014 at 9:17

answered Apr 2, 2014 at 20:44

Angular University

43.2k15 gold badges79 silver badges81 bronze badges

1 Comment

gavenkoa Over a year ago

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

G_V · Accepted Answer · 2020-05-08 10:20:30Z

5

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.

edited May 8, 2020 at 10:20

G_V

2,4341 gold badge32 silver badges45 bronze badges

answered Jun 20, 2019 at 10:47

Tapan

611 silver badge3 bronze badges

Comments

naXa stands with Ukraine · Accepted Answer · 2015-12-10 13:54:03Z

3

To sum up:

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

edited Dec 10, 2015 at 13:54

answered Apr 15, 2015 at 12:59

naXa stands with Ukraine

38.6k25 gold badges208 silver badges279 bronze badges

1 Comment

RobertG Over a year ago

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

Collectives™ on Stack Overflow

Hibernate: Create Index

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related