rosenthal rosenthal - 6 days ago 5
Java Question

JPA 2.1 Index annotation for columns with a function

In my Hibernate project, I add indices like so:

@Entity
@Table(name="MY_TABLE", indexes = {
@Index(name = "idx_user_name", columnList = "name"),
@Index(name = "idx_user_email", columnList = "email")
})


Which works just fine, but what happens when I want to make an index like this:

CREATE INDEX ON my_table (lower(name));


If I use the following annotation I get an
AnnotationException
:

@Index(name = "idx_lower_name", columnList = "lower(name)")


I wish to ship my project with the indices made via Hibernate + JPA 2.1. Is it possible to apply functions via the Index annotation?

Or should I just run an SQL script on deployment to add these indices myself via SQL?

I could just be formatting the
columnList
wrong, or there could be a field not described in the documentation I'm missing.

Answer

It is not possible to use a function based index in JPA 2.1.

JPA provides means to abstract from a specific database. Every database has its own implementation of FBI which can vary greatly. Hence, it is not part of the standard.

I peeked into the Hibernate code to see whether there might be an undocumented feature for a FBI. Unfortunately, only column based indexes are supported.

You should run a SQL script on deployment to create your index.

Comments