Defining Indexes in JPA

1. Introduction

In this tutorial, we’ll discuss defining indexes using JPA’s @Index annotation. Through examples, we’ll learn how to define our first index using JPA and Hibernate. After that, we’re going to modify the definition showing additional ways to customize the index.

2. @Index Annotation

Let’s begin by making a quick recap. The database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space. Mostly, it’s a copy of selected columns of data from a single table. We should create indexes to increase performance on our persistence layer.

JPA allows us to achieve that by defining indexes from our code using @Index. This annotation is interpreted by the schema generation process, creating artifacts automatically. Note that it’s not necessary to specify any index for our entities.

Now, let’s take a look at the definition.

2.1. jakarta.persistence.Index

The index support has been finally added in the JPA 2.1 specification by jakarta.persistence.Index. This annotation let us define an index for our table and customize it accordingly:

@Target({})
@Retention(RUNTIME)
public @interface Index {
    String name() default "";
    String columnList();
    boolean unique() default false;
}

As we can see, only the columnList attribute is mandatory, which we have to define. We’ll take a better look at each of the parameters later, going through examples.

One aspect to note here is that the annotation doesn’t support changing the default indexing algorithm — btree.

2.2. JPA vs. Hibernate

We know that JPA is only a specification. To work correctly, we also need to specify a persistence provider. By default, the Hibernate Framework is JPA’s implementation delivered by Spring. More about it, you can read here.

We should remember the index support has been added to the JPA very late. Before that, many ORM Frameworks support indexes by introducing their own custom implementation, which might work differently. The Hibernate Framework also did it and introduced the org.hibernate.annotations.Index annotation. While working with that framework, we must be careful that it has been deprecated since the JPA 2.1 specification support, and we should use the JPA’s one.

Now when we have some technical background, we can go through examples and define our first index in JPA.

3. Defining the @Index

In this section, we’re implementing our index. Later, we’ll try to modify it, presenting different customization possibilities.

Before we start, we need to initialize our project properly and define a model.

Let’s implement a Student entity:

@Entity
@Table
public class Student implements Serializable {
    @Id
    @GeneratedValue
    private Long id;
    private String firstName;
    private String lastName;

    // getters, setters
}

When we have our model, let’s implement the first index. All we have to do is add an @Index annotation. We do that in the @Table annotation under the indexes attribute. Let’s remember to specify the name of the column:

@Table(indexes = @Index(columnList = "firstName"))

We’ve declared the very first index using the firstName column. When we execute the schema creation process, we can validate it:

[main] DEBUG org.hibernate.SQL -
  create index IDX2gdkcjo83j0c2svhvceabnnoh on Student (firstName)

Now, it’s time to modify our declaration showing additional features.

3.1. @Index Name

As we can see, our index must have a name. By default, if we don’t specify so, it’s a provider-generated value. When we want to have a custom label, we should simply add the name attribute:

@Index(name = "fn_index", columnList = "firstName")

This variant creates an index with a user-defined name:

[main] DEBUG org.hibernate.SQL -
  create index fn_index on Student (firstName)

Moreover, we can create our index in the different schema by specifying the schema’s name in the name:

@Index(name = "schema2.fn_index", columnList = "firstName")

3.2. Multicolumn @Index

Now, let’s take a closer look at the columnList syntax:

column ::= index_column [,index_column]*
index_column ::= column_name [ASC | DESC]

As we already know, we can specify the column names to be included in the index. Of course, we can specify multiple columns to the single index. We do that by separating the names by a comma:

@Index(name = "mulitIndex1", columnList = "firstName, lastName")

@Index(name = "mulitIndex2", columnList = "lastName, firstName")

[main] DEBUG org.hibernate.SQL -
  create index mulitIndex1 on Student (firstName, lastName)
   
[main] DEBUG org.hibernate.SQL -
  create index mulitIndex2 on Student (lastName, firstName)

Note that the persistence provider must observe the specified ordering of the columns. In our example, indexes are slightly different, even if they specify the same set of columns.

3.3. @Index Order

As we reviewed the syntax in the previous section, we also can specify ASC (ascending) and DESC (descending) values after the column_name. We use it to set the sort order of the values in the indexed column:

@Index(name = "mulitSortIndex", columnList = "firstName, lastName DESC")

[main] DEBUG org.hibernate.SQL -
  create index mulitSortIndex on Student (firstName, lastName desc)

We can specify the order for each column. If we don’t, the ascending order is assumed.

3.4. @Index Uniqueness

The last optional parameter is a unique attribute, which defines whether the index is unique. A unique index ensures that the indexed fields don’t store duplicate values. By default, it’s false. If we want to change it, we can declare:

@Index(name = "uniqueIndex", columnList = "firstName", unique = true)

[main] DEBUG org.hibernate.SQL -
  alter table Student add constraint uniqueIndex unique (firstName)

When we create an index in that way, we add a uniqueness constraint on our columns, similarly, how as a unique attribute on @Column annotation do. @Index has an advantage over @Column due to the possibility to declare multi-column unique constraint:

@Index(name = "uniqueMulitIndex", columnList = "firstName, lastName", unique = true)

3.5. Multiple @Index on a Single Entity

So far, we’ve implemented different variants of the index. Of course, we’re not limited to declaring a single index on the entity. Let’s collect our declarations and specify every single index at once. We do that by repeating @Index annotation in braces and separated by a comma:

@Entity
@Table(indexes = {
  @Index(columnList = "firstName"),
  @Index(name = "fn_index", columnList = "firstName"),
  @Index(name = "mulitIndex1", columnList = "firstName, lastName"),
  @Index(name = "mulitIndex2", columnList = "lastName, firstName"),
  @Index(name = "mulitSortIndex", columnList = "firstName, lastName DESC"),
  @Index(name = "uniqueIndex", columnList = "firstName", unique = true),
  @Index(name = "uniqueMulitIndex", columnList = "firstName, lastName", unique = true)
})
public class Student implements Serializable

What is more, we can also create multiple indexes for the same set of columns.

3.6. Primary Key

When we talk about indexes, we have to stop for a while at primary keys. As we know, every entity managed by the EntityManager must specify an identifier that is mapped into the primary key.

Generally, the primary key is a specific type of unique index. It’s worth adding that we don’t have to declare the definition of this key in the way presented before. Everything is done automatically by the @Id annotation.

3.7. Non-entity @Index

After we’ve learned different ways to implement indexes, we should mention that @Table isn’t the only place to specify them. In the same way, we can declare indexes in @SecondaryTable, @CollectionTable, @JoinTable, @TableGenerator annotations. Those examples aren’t covered in this article. For more details, please check the jakarta.persistence JavaDoc.

4. Conclusion

In this article, we discussed declaring indexes using JPA. We started by reviewing the general knowledge about them. Later we implemented our first index and, through examples, learned how to customize it by changing name, included columns, order, and uniqueness. In the end, we talked about primary keys and additional ways and places where we can declare them.