Is there an annotation to define a multi column index in jpa2

Question

Hibernate provides a mechanism to define multi column indexes via the Table. Is there a way to specify this is an ORM agnostic fashion via JPA or JPA2 (e.g. using the javax.persistence.* APIs)

Solution 1

No, as hinted in my answer to your previous question, there is no standardized way, you have to use provider extensions for that (when they exist).

Solution 2

Just came about the very same issue using Hibernate 4.3.8 with JPA 2.1 integration. It seems, mjaggard's answer is correct. However, the given example of usage looks like this:

@Index(name="EMP_NAME_INDEX", columnList={"F_NAME", "L_NAME"})

I don't know if this ever worked. I do know that in my case with JPA 2.1 the value of columnList is not an array but a String. So for me, the desired two-column index can be defined in the following way:

@Index(name="EMP_NAME_INDEX", columnList="F_NAME,L_NAME")

That is, just use a comma to separate the column names in a single string. This worked for me using a Postgres DBMS. I checked back and the index was successfully created over both columns.

Solution 3

It's possible to declare multi-column index in JPA 2.1 Here is a sample Entity class that demonstrates multi-column indexing.

@Entity
@Table(uniqueConstraints=@UniqueConstraint(columnNames={"product_id","store_id"}))
class MyEntity {
    @Column(name="product_id")
    private String productId;

    @Column(name="store_id")
    private Long storeId;
}

Please note that columnNames must be the name of the columns in DB, not the attribute name.

Solution 4

Yes, it is possible using JPA 2.1 as seen in the specification here:

http://download.oracle.com/otndocs/jcp/persistence-2_1-pfd-spec/index.html

on page 445 it states that

The Index annotation is used in schema generation

columnList (Required) The names of the columns to be included in the index.

An example of usage can be seen here:

http://java-persistence-performance.blogspot.co.uk/2013/03/but-what-if-im-not-querying-by-id.html

View more solutions

Comments

[user339108]

just curious to know, as per your earlier suggestion we are using hibernate annotations for this purpose. It makes sense to support this, when JPA already has support for single column Index annotation. Just my 2 cents

[Devanshu Mevada]

@user339108 I personally agree and I'd like JPA to support this too. Maybe in a future version.

[jaxvy]

@user339108 Can you provide an example to your statement "JPA already has support for single column Index annotation". I couldn't find any annotation allowing this other then the @Index annotation provided by Hibernate.

[Hollis Waite]

@Column(unique=true) imposes an index upon a column. Non-unique indexes are not supported by JPA 2.0. 2.1+ is much more versatile.

[Breina]

Unique constraints aren't necessarily indexed.