CODEWITHSUNDEEP
javaspringhibernatepostgresqlhibernate-mapping
We are Borg
We are Borg

Reputation: 5313

How to create indexes using Hibernate for PostgreSQL

I am working on a Spring-MVC application in which I am using Hibernate as the ORM tool with PostgreSQL. For some of the entities in the project Model, I would like to create Indexes for faster lookup. As I read, I found out it is possible to create indexes using Hibernate. Unfortunately, I am not having much luck. I only tried to create it on one Model class, but when I check in PGAdmin, I cannot see any index for that table.

When I try giving the @Index parameter to the @Table annotation, I get error. Can anyone tell me how I can annotate columns and entire table for auto-indexing by Hibernate. Thanks a lot.

Online User model : // This class I just used for testing

import org.hibernate.search.annotations.Indexed;

import javax.persistence.*;
@Entity
@Table(name="onlineusers" )
@Indexed(index = "onlineuserindex")
public class OnlineUsers {

  @Id
    @Column(name="onlineuserid")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "online_gen")
    @SequenceGenerator(name = "online_gen",sequenceName = "online_seq")
    private int onlineUserId;


    @Column(name = "onlineusername")
    private String personName;
}

Please note, when I try something like this below : 

@Indexed(index = "usernameindex");
@Column(name="username");
private String userName;

I get an error, @Indexed not applicatble to a field.

POM.xml :

 <properties>
        <java-version>1.8</java-version>
        <org.springframework-version>4.0.6.RELEASE </org.springframework-version>
        <org.aspectj-version>1.7.4</org.aspectj-version>
        <org.slf4j-version>1.7.5</org.slf4j-version>
        <hibernate.version>4.3.9.Final</hibernate.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

  <!-- Hibernate search dependencies -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-search-orm</artifactId>
            <version>5.2.0.Final</version>
        </dependency>

    <!--    <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-annotations</artifactId>
            <version>3.5.6-Final</version>
        </dependency>
-->

Kindly let me know what I am doing wrong. Thanks a lot. :-)

Upvotes: 5

Views: 10039

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154200

You can use the @Index JPA annotation:

@Entity
@Table(name = "onlineusers",
    indexes = {
        @Index(name = "usernameindex",  columnList="username", unique = true)   
    }
)
public class OnlineUsers {
   ...
}

This is only applied if you use the automatic hbmddl schema generation. If the database schema is generated externally (e.g. like when using Flyway), then this annotation won't have any effect.

When you delegate the database schema generation to an external process (a database migration tool or a manual script update procedure), then you need to include the indexes in the migration scripts.

So, you either generate the whole schema with Hibernate (which is not even recommended for production systems) or you rely on a database migration framework (e.g. Flyway) and the indexes are simply included in an incremental schema update script.

Upvotes: 3

jrao77
jrao77

Reputation: 142

The @Indexed annotation from Hibernate Search is only applicable to types. So you cannot use those on attributes.

Reading your question, it seems that you want to add a database index to the table? if so then you have to use Index Annotation

Upvotes: 2

Related Questions