How to create an index with JPA/hibernate and use fields from MappedSuperClass together with fields from concrete entity

Question

I have the @MappedSuperClass (simplified example):

@MappedSuperclass
public abstract class MySuperClass {

    @Id
    @GeneratedValue
    private long id;

    @Column(nullable = false)
    private Date creationDate;

    // ...
}

and a concrete Entity (simplified example):

@Entity
public class MyEntity extends MySuperClass {
    @Index(name = "IDX_MYINDEX")
    @Column(nullable = false)
    @Enumerated(EnumType.STRING)
    private MyType type;

    @Index(name = "IDX_MYINDEX")
    @Column(nullable = false)
    @Enumerated(EnumType.STRING)
    private MyResult status;

    // ...
}

Now I need an index including the columns MySuperClass.creationDate, MyEntity.status and MyEntity.type.

If I add @Index(name = "IDX_MYINDEX") to MySuperClass.creationDate hibernate adds an index of creationDate to every Entity inherited from MySuperClass.

I tried @AttributeOverride but it is not capable for indexes.

Any ideas?

Answer 1

If you are using JPA 2.1 then you can use class annotation @Table with its attribute indexes

@Table(indexes = { @Index(name = "IDX_MYIDX1", columnList = "id,name,surname") })

Please note that as documentation says

These are only used if table generation is in effect. Defaults to no additional indexes.

columnlist, as shown above, accepts column names list as a comma-delimited list.

If you don't use JPA 2.1 you can just use old Hibernates @Index annotation (note this is already deprecated). There's attribute columnNames where you can pass array of column names no matter above which field it is declared.

@Index(name = "IDX_MYIDX1", columnNames = { "id", "name", "surname"})

Answer 2

Use @Index annotation and use the parameter "columnList" to set which columns should be used to make your index. That list should be made of a comma-separated list of values of the column names.

Important: Don't forget to add the column name property (via Column annotation) to all properties that make this index, otherwise you'll get an error when starting up your container.

Answer 3

Make sure list column names that you've given in @Column's name attribute not fields' names.

Also enabling ddl-auto to update will create all the indexes.

spring.jpa.hibernate.ddl-auto=update

@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "ANSWER_OPTION", indexes = {@Index(name="SEC_INDEX",columnList = "ID,SEQUENCE,QUESTION_ID,OPTION_TITLE")})
public class AnswerOptionEntity {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "SEQUENCE")
private Long sequence;

@Column(name = "OPTION_TITLE")
private String optionTitle;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "QUESTION_ID", nullable = false)
@JsonIgnore
private QuestionEntity questionEntity;

@OneToMany(mappedBy = "answerOptionEntity", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
@JsonIgnore
private List<ResultEntity> resultEntityList = new ArrayList<>();

}