Specifying an Index (Non-Unique Key) Using JPA

datanucleus, hibernate, java, jpa, orm

Solution

With JPA 2.1 you should be able to do it.

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name = "region",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Region{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

}

Update: If you ever need to create and index with two or more columns you may use commas. For example:

@Entity
@Table(name    = "company__activity", 
       indexes = {@Index(name = "i_company_activity", columnList = "activity_id,company_id")})
public class CompanyActivity{

Problem

How do you define a field, eg `email` as having an index using JPA annotations. We need a non-unique key on `email` because there are literally millions of queries on this field per day, and its a bit slow without the key. ``` @Entity @Table(name="person", uniqueConstraints=@UniqueConstraint(columnNames={"code", "uid"})) public class Person { // Unique on code and uid public String code; public String uid; public String username; public String name; public String email; } ``` I have seen a hibernate specific annotation but I am trying to avoid vendor specific solutions as we are still deciding between hibernate and datanucleus. UPDATE: As of JPA 2.1, you can do this. See: The annotation @Index is disallowed for this location

Original source

Related problems