JPA 2.1 Index annotation for columns with a function

Question

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 1

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.