Hibernate Best Practices – Parameterising Criteria Queries

Recently one of our database team pointed out some queries that were not running efficiently. We looked into the queries and they were Hibernate criteria queries. Consider a simple helper method that is creating a predicate for use in a criteria query:

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)
    );
}

There doesn’t seem anything wrong with this, it looks like a regular bit of Hibernate code, so what is the problem? Well, it takes a parameter – in this case policyTermId, but when it passes that into the criteria builder equal() method, it doesn’t explicitly create it as a parameterised expression. Why is this a problem? To understand, you need to understand how a database evaluates and runs a SQL statement. To evaluate a query, a database must do the following:

1. Construct one or more potential query plans
2. Choose a plan
3. 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 = 123

The 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 = 456

the 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();

Now the generated SQL will be parameterised, and the database will be able to cache the query plan.