How to set positional/named parameters dynamically to JPA criteria query?

Question

Hibernate provider does not generate prepared statement for non-string type parameters unless they are set to entityManager.createQuery(criteriaQuery).setParameter(Parameter p, T t); as done by EclipseLink, by default.

What is the way to set such parameters, if they are supplied dynamically at run time. For example,

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Long>criteriaQuery=criteriaBuilder.createQuery(Long.class);
Metamodel metamodel=entityManager.getMetamodel();
EntityType<Product> entityType = metamodel.entity(Product.class);
Root<Product> root = criteriaQuery.from(entityType);
criteriaQuery.select(criteriaBuilder.count(root));

List<Predicate>predicates=new ArrayList<Predicate>();

for (Map.Entry<String, String> entry : filters.entrySet()) {
    if (entry.getKey().equalsIgnoreCase("prodId")) {
        predicates.add(criteriaBuilder.equal(root.get(Product_.prodId), Long.parseLong(entry.getValue().trim())));
    } else if (entry.getKey().equalsIgnoreCase("prodName")) {
        predicates.add(criteriaBuilder.like(root.get(Product_.prodName), "%" + entry.getValue().trim() + "%"));
    } else if (entry.getKey().equalsIgnoreCase("marketPrice")) {
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(Product_.marketPrice), new BigDecimal(entry.getValue().trim())));
    } else if (entry.getKey().equalsIgnoreCase("salePrice")) {
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(Product_.salePrice), new BigDecimal(entry.getValue().trim())));
    } else if (entry.getKey().equalsIgnoreCase("quantity")) {
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(Product_.quantity), Integer.valueOf(entry.getValue().trim())));
    }
}

if (!predicates.isEmpty()) {
    criteriaQuery.where(predicates.toArray(new Predicate[0]));
}

Long count = entityManager.createQuery(criteriaQuery).getSingleResult();

In this case, the parameter to prodName is a String type parameter. Hence, it is automatically bound to a positional parameter ?. The rest (non-string type) are however not. They all are just replaced by their values.

They are required to be set to createQuery() by using, for example,

ParameterExpression<BigDecimal> exp=criteriaBuilder.parameter(BigDecimal.class);
entityManager.createQuery(criteriaQuery).setParameter(exp, new BigDecimal(1000));

How to set such dynamic parameters so that a prepared query can be generated?

---

This is indeed not required in EclipseLink where they are automatically mapped to positional parameters.

Can this be done with Hibernate provider?

I'm using JPA 2.0 provided by Hibernate 4.2.7 final.

---

If all the parameters are set then, the statement generated by above criteria query would be like as shown below.

SELECT count(product0_.prod_id) AS col_0_0_ 
FROM   projectdb.product product0_ 
WHERE  product0_.market_price >= 1000 
       AND ( product0_.prod_name LIKE ? ) 
       AND product0_.prod_id = 1 
       AND product0_.quantity >= 1 
       AND product0_.sale_price >= 1000 

---

I have just run the above criteria query under EclipseLink (2.3.2) that resulted in producing the following SQL statement.

SELECT count(prod_id) 
FROM   projectdb.product 
WHERE  ( ( ( ( ( market_price >= ? ) 
           AND prod_name LIKE ? ) 
         AND ( prod_id = ? ) ) 
       AND ( quantity >= ? ) ) 
     AND ( sale_price >= ? ) ) 

bind => [1000, %product1%, 1, 1, 1000]

i.e a parameterized query.

---

Doing like the following is not possible.

//...

TypedQuery<Long> typedQuery = entityManager.createQuery(criteriaQuery);

for (Map.Entry<String, String> entry : filters.entrySet()) {
    if (entry.getKey().equalsIgnoreCase("discountId")) {
        ParameterExpression<Long> parameterExpression = criteriaBuilder.parameter(Long.class);
        predicates.add(criteriaBuilder.equal(root.get(Discount_.discountId), parameterExpression));
        typedQuery.setParameter(parameterExpression, Long.parseLong(entry.getValue().trim()));
    }

    //...The rest of the if-else-if ladder.
}

//...
//...
//...
Long count = typedQuery.setFirstResult(first).setMaxResults(pageSize).getSingleResult();

Doing so would cause the java.lang.IllegalArgumentException: Name of parameter to locate cannot be null exception to be thrown.

Answer 1

Why don't use CriteriaBuilder.parameter? And why do you want parameters to be inserted like positional parameters in the generated SQL? Do you want optimize you query by prepairing it on SQL server and then sending parameters only? I suppose in this case it is better to explicitly specify CriteriaBuilder.parameter.

And BTW did you see QueryDSL?