private Predicate restrictToAcceptedTxWith(Long policyTermId, DateTime at, Root<PolicyTransaction> ptxRoot, CriteriaBuilder cb) {
return cb.and(
cb.isNotNull(ptxRoot.get("acceptedDate")),
cb.equal(ptxRoot.get("policyTerm").get("id"), policyTermId),
cb.greaterThanOrEqualTo(ptxRoot.get("transactionPeriodEndDate"), at),
cb.lessThanOrEqualTo(ptxRoot.get("transactionPeriodStartDate"), at)
);
}
- Construct one or more potential query plans
- Choose a plan
- Execute the query using the chosen plan
The reason SQL statements can result in multiple query plans is because SQL is a declarative language, rather than an imperative language like Java. You are telling the database conceptually what you want, without specifying exactly how it needs to be done. If you want to join tables A, B, C and D, you could do that by joining A to B, then the result of that to C, then the result of that to D. Or you could join A to D, then the result of that to B, then the result of that to C, and so on. In fact you could even join A to B, then separately join C to D, then join those two results together. (This is usually called a bushy query plan.) Choosing a query plan takes time, so once one is chosen, it is cached, so that if the same SQL query is run again, just with different parameters, the query plan choice does not have to be repeated.
This is the problem here. All databases understand the concept of parameterised SQL. Consider a simple query:
select * from policy where policy_number = ?The database constructs the query plan and caches it based on the parameterised SQL. However, if you send in SQL where a parameter has actually been inlined into the SQL, like this:
select * from policy where policy_number = 123The database has no understanding that “123” is meant to be a parameter, so the query plan (if cached at all) would be cached on the full query string. Then when you send in:
select * from policy where policy_number = 456the database will not pick up the cached plan. It has to reevaluate the query plan options.
In the case of the Hibernate criteria, it can be parameterised as follows:
private Predicate restrictToAcceptedTxWith(DateTime at, Root<PolicyTransaction> ptxRoot, CriteriaBuilder cb) {
final ParameterExpression<Long> policyTermIdParam = cb.parameter(Long.class, POLICY_TERM_ID_PARAM_NAME);
return cb.and(
cb.isNotNull(ptxRoot.get("acceptedDate")),
cb.equal(ptxRoot.get("policyTerm").get("id"), policyTermIdParam),
cb.greaterThanOrEqualTo(ptxRoot.get("transactionPeriodEndDate"), at),
cb.lessThanOrEqualTo(ptxRoot.get("transactionPeriodStartDate"), at)
);
}
// calling code then calls
getCurrentSession().createQuery(cq).setParameter(POLICY_TERM_ID_PARAM_NAME, policyTermId).list();