How to specify column order inside an index with multiple columns in Hibernate?

Question

I create an index with multiple columns as follows:

@Entity
public class Ranking extends Model {
    @ManyToOne
    @Index(name = "ranking_ix")
    public Rankable rankable;

    @ManyToOne
    @Index(name = "ranking_ix")
    public Criteria criteria;

    @Index(name = "ranking_ix")
    public double rank;
}

However, I don’t see how to control the order in which three columns are displayed in a composite index (which may be required to ensure optimal query performance). How can this be achieved?

Answer 1

Try using the notation

@Entity
@Table(
    name="Ranking",
    indexes = { @Index(name="ranking_ix", columnNames = { "Rankable", "Criteria" } ) }
    )
public class Ranking extends Model {
    @ManyToOne
    public Rankable rankable;

    @ManyToOne
    public Criteria criteria;

    @Index(name = "ranking_ix")
    public double rank;
}

Answer 2

This can be specified using the org.hibernate.annotations.Table annotation, which is used in addition to the javax.persistence.Table annotation:

@Entity
@javax.persistence.Table(name="Ranking")
@org.hibernate.annotations.Table(
    appliesTo="Ranking",
    indexes = { @Index(name="ranking_ix", columnNames = { "rankable", "criteria", "rank" } ) }
)
public class Ranking extends Model {
    @ManyToOne
    public Rankable rankable;

    @ManyToOne
    public Criteria criteria;

    @Index(name = "ranking_ix")
    public double rank;
}