Hibernate index with DESC order

Question

is there a way to create multicolumn index with one column with DESC order? I've tried this (annotations from javax.persistence package):

@Table(name = some_table
  indexes = {
    @Index(name = "idx_multi_column", columnList = "column1, column2, column3"),
    @Index(name = "idx_multi_column", columnList = "column1, column2, column3 DESC")
  }
)

But it creates both indexes with column3 asc - DESC is ignored. I'm using:

Hibernate 4.3.10 Hibernate JPA 2.1 api

Thanks.

Answer 1

By definition - that is the JPA specification¹ document aka JSR 338 - there is a way to annotate the desired behaviour, exactly as you described in your question. Citing from the specification, section 11.1.23 Index Annotation, page 452:

---

The @Index annotation is used in schema generation. [..] the Index annotation may be used to specify the ordering of the columns in the index for the primary key.

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

The syntax of the columnList element is a column_list, as follows:

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

The persistence provider must observe the specified ordering of the columns.

If ASC or DESC is not specified, ASC (ascending order) is assumed.

---

Conclusion

Any persistence provider must (should) respect this requirement to be conform with the JPA spec. Therefore, I can imagine that Hibernate in version 4.3.10 did either (a) suffer from a bug, or (b) did not respect the spec in that particular point.

Ideas

• Try again with a more recent release, say Hibernate 5+, e.g. the current release 5.4.21
• Try with another JPA provider, say EclipseLink or OpenJPA.
• Check whether your compiled classes were run/tested with the correct syntax in place. Maybe, they might have been run/tested with an outdated version?

Hope it helps.

Footnotes

¹ In version 2.2 or the previous version 2.1/2.0