How to define non-clustered index include clause in a JPA Entity

István Scheer 0
Viewed 26

I have an entity, like this:

@Entity
@Table(indexes = {
        @Index(name = "myModel_name_index", columnList = "name")
})
public class MyModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private boolean active;
    @Column(unique = true,nullable = false)
    private String value;
    @Column(nullable = false)
    private String name;
    private String description;
    private BigDecimal price;
}

When a query will be sent to Microsoft Sqlserver by Spring Data JPA like this:

select count(*) from myModell where name='anything';

then myModel_name_index will be used.

When a query will be sent to Microsoft Sqlserver by Spring Data JPA like this:

select name, active, description,price from myModell where name='anything';

then myModel_name_index will not be used!

The explain plan result told me, An index should be created as

create nonclustered index myModel_name_index on myModell(name) include(active, description,price)

My question: how can the Include clause be defined on entity? Which solution can be used?

include entity jpa hibernate indexing
1 Answers

It can't be done, and you also shouldn't rely on JPA/Hibernate for schema management. Use a tool like Liquibase or Flyway to manage your schema, which both allow you to take full control of the DDL.

Christian Beikov0
Related