How to create an Index on your Entity Beans?

An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.

When using plain SQL, to add an index for a column or a set of columns, you use the CREATE INDEX statement as follows:

CREATE INDEX index_name ON table_name (column_list)

When using JPA 2.1, you can use the javax.persistence.Index annotation to achieve the same result when your DB schema is generated by your Persistence Provider. Here is a sample Entity:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name = "country",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Country{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

} 

Besides standard indexes, you can also use Multicolumn indexes (also known as composite indexes). These are similar to standard indexes. They both store a sorted “table” of pointers to the main table. Multicolumn indexes however can store additional sorted pointers to other columns.

Here is the sample Entity using a Multicolumn index:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name = "country",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code,name", unique = true)})
public class Country{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

}

Finally bear in mind that Hibernate ORM provides a deprecated org.hibernate.annotations.Index annotation. We recommend to rely on JPA 2.1 approach if you want to have Jakarta EE compliant code.

Found the article helpful? if so please follow us on Socials