CODEWITHSUNDEEP
hibernatespring-dataspring-data-jpa
domi
domi

Reputation: 2257

How to influence Hibernate filter position in where clause

Given the following entity:

@FilterDef(name = "TENANT_FILTER", parameters = {
        @ParamDef(name = "tenantId", type = "long")
    })
@Filter(name = "TENANT_FILTER", condition = "tenant_id = :tenantId")
public class Pariticipant {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "tenant_id")
    private Long tenantId;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastName;
}

I activate the filter and use a spring data repository to query for entities:

Filter filter = entityManager.unwrap(Session.class).enableFilter("TENANT_FILTER");
filter.setParameter(fieldName, 1L);

// query using SpringData Repository
Pariticipant p = repository.getById(10L);

Hibernate generates the following query:

SELECT participant0_.id AS id2_97_,
       participant0_.tenant_id AS tenant_i3_97_,
       participant0_.firstname AS firstname_97_,
       participant0_.lastname AS lastname_97_
FROM participant participant0_
WHERE participant0_.tenant_id = 1
  AND participant0_.id=?

As id is the primary key, the clause would be a lot more efficient if the order of the where clause would be the other way around e.g. appended the filter instead of prepended to the where clause. e.g.:

SELECT participant0_.id AS id2_97_,
       participant0_.tenant_id AS tenant_i3_97_,
       participant0_.firstname AS firstname_97_,
       participant0_.lastname AS lastname_97_
FROM participant participant0_
WHERE participant0_.id=?
  AND participant0_.tenant_id = 1

This is a very simple example, but the problem gets a lot worse the more complex the query gets. In our case about every query would benefit if the filter would be appended instead of prepended.

Is there any way to influence the position where the filter is added in the where clause?

Upvotes: 2

Views: 635

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81990

There is no way to do that. And the assumption that the order of conditions has a measurable effect on execution speed is false for many (most? all?) serious modern databases.

Upvotes: 2

Related Questions