How to create an Index on your Entity Beans?

by

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.