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.

Coquette

of course, doing like the following is not possible. Before running the first loop, you have already built query with no any predicates applied. Inside loop you just apply predicates by query builder, not the query itself. So the first line must follow the first loop. And parameters are to be set by the second loop. – Thrower

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?

Thrower

Using criteriaBuilder.parameter() is not a pain :). It is when the parameters are supplied dynamically. For example, when we filter data held by an HTML table (there are filtered components like text box, calendar etc on each or some of column headers of that HTML table). There are no fixed number of parameters supplied. They may be 1, 2, 3... or even absent. How to use criteriaBuilder.parameter() then? – Coquette

P.S : It doesn't matter whether they are positional parameters or named parameters. I don't worry at all about it :) – Coquette

For example, you can use auxiliary ArrayList to store all parameter values together with building query predicate while iterating over filters.entrySet(). Then you write Query<Long> query = createQuery(criteriaQuery); and then iterate over your ArrayList and call query.setParameter(...) for each element. It's simple. – Thrower

But doubt if you really need parameters. I see two main reasons to use prepared query with parameters. The first reason is escaping parameters to fight against potential SQL injection attack. Not your case: integer parameters are trivial so Hibernate can escape then by itself. The second reason is speeding up query that is repeating with different input data (that is SQL server parses and builds query plan once). Not your case again, because you are performing very similar, but still different queries. – Thrower

Could you please check out the last edit? I cannot proceed from there. – Coquette

No, you should use two loops. The first loop creates predicates and fills in an ArrayList of parameters. Then, after the loop, you create typedQuery. And then the second loop calls setParameter. And only after that you do getSingleResult. But I again ask you question, that is still unanswered: why do you need pass parameters as a prepaired query parameters, not in SQL itself? – Thrower

Iterating over the same HashMap twice through a loop is plain clumsy that should not be necessary, especially, when a query to be executed has a number of parameters. Is there no any other way? "why do you need pass parameters as a prepared query parameters?" A prepared query is just a preference :). If it is really not essential, in this case then, I will avoid it. Is it really not needed in the cases like the one in the question? – Coquette

Yes, it is strange to have two loops for such thing. In JPA 1.0 there weren't criteria queries, so you were to deal with generating JPQL strings in StringBuilder in the first loop and filling in parameter in the second one. With JPA 2 you just do things like in your first example and don't pay attention to prepared statement parameters. They are not your case. – Thrower

Another reason to use parameters: Queries are cached and every distinct query (as in searching fora different Id) will fill up your cache. Common cause of memory leaks. – Choragus

+1 for proposing QueryDSL. It is much simpler and always generates named parameters (not only for text columns). – Catabolism