How to set positional/named parameters dynamically to JPA criteria query?
criteria, eclipselink, hibernate, jpa, prepared-statement
Solution
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?
Problem
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.