Indexes (non-unique)

From the viewpoint of software modelling non-unique indexes are not part of the business logic but refer to database optimization. Consequently JPA has no support for non-unique indexes.

On the other hand performance matters. Hibernate and other persistence providers offer vendor specific JPA extensions. We may find it useful to access hibintro.v5.User instances having a specific cname quickly. This can be achieved by adding a Hibernate specific org.hibernate.annotations.org.hibernate.annotations.Table index generating annotation ❶ which works on top of JPA's javax.persistence.javax.persistence.Table:

Java
package hibintro.v7;
...
@Entity
@Table(uniqueConstraints={@UniqueConstraint(name="uidKey", columnNames={"uid"})},
      indexes = {@Index(name = "byCname", columnList="cname", unique = false)} ❶
)
public class User {

  int uidNumber;
  @Id
  public int getUidNumber() { return uidNumber; }
  public void setUidNumber(int uidNumber) { this.uidNumber = uidNumber; }

  String uid;
  /**
   * @return The user's unique login name e.g. "goik"
   */
  @Column(nullable=false)
  public String getUid() { return uid; }

  /**
   * @param uid See {@link #getUid()}.
   */
  public void setUid(String uid) { this.uid = uid; }
   ...
}
Sql
CREATE TABLE User (
  uidNumber INT NOT NULL PRIMARY KEY,
  cname VARCHAR(255) NOT NULL,
  uid VARCHAR(255) NOT NULL UNIQUE
);

CREATE INDEX findCname ON User (cname ASC);