rosenthal rosenthal - 9 months ago 57
Java Question

JPA 2.1 Index annotation for columns with a function

In my Hibernate project, I add indices like so:

@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

@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
wrong, or there could be a field not described in the documentation I'm missing.

Answer Source

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.