5

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. :-)

Improve this question

2 Answers 2

Reset to default
3

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.

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

8 Comments

Thanks, but why am I not able to see the indexes created in PGadmin? But when I use @Index from Hibernate on field, I can see them in Pgadmin.
I assume you generate your database schema with hbmddl, right?
No, By myself. I wrote the database table code myself and executed it in SQL console.
I think I will stick to field Indexes which are atleast getting generated. Thanks for your help.
If those are generated, it means you use hbmlddl=update, otherwise Hibernate cannot update your schema. Even so, I don;t see how it can make a diff, since that feature is used to update a Hibernate generated schema anyway. So, there is something missing from your question, if you say it's working.
|
2

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

Improve this answer

5 Comments

Thank you for your answer, Unfortunately whenever I search on Google, I am getting result back relevant to Hibernate-search. You documentation points me to Javadocs, and nothing specific in it, which makes it rather difficult to find. Can you please give some example or the actual API/method I would require.
I came up to the same link, to be honest, I really dont know what to do.
The @Index column is used to annotate a field which you want the index to be created. So if you want a database index to be created on the field username then you have to annotate the field. Optionally you can specify a name. Here is an example from Hibernate docs - docs.jboss.org/ejb3/app-server/HibernateAnnotations/reference/…
Multiple columns can also be used to create the index(usually at the type level) with the @Table annotation. For ex: @org.hibernate.annotations.Table( indexes = {@Index(name = "idx", columnNames = {"id_1", "Id2"}}) At the field level you can use something like this - @Index(name = "idx_1") private User user1;
Thanks, Your answer bought me closer, I am able to user @Index from hibernate-annotations on Field(Which IDE complains is deprecated), but when I use it on Entity, it says it is expecting a JPA annotation, but got Hibernate Annotation. Any idea why? I used it like this @Table(name="onlineuser", indexes){and stuff inside this.}).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.