using a ParameterExpression versus a variable in JPA Criteria API

Question

When using the JPA Criteria API, what is the advantage of using a ParameterExpression over a variable directly? E.g. when I wish to search for a customer by name in a String variable, I could write something like

private List<Customer> findCustomer(String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
    Root<Customer> customer = criteriaQuery.from(Customer.class);
    criteriaQuery.select(customer).where(cb.equal(customer.get("name"), name));
    return em.createQuery(criteriaQuery).getResultList();
}

With parameters this becomes:

private List<Customer> findCustomerWithParam(String name) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
    Root<Customer> customer = criteriaQuery.from(Customer.class);
    ParameterExpression<String> nameParameter = cb.parameter(String.class, "name");
    criteriaQuery.select(customer).where(cb.equal(customer.get("name"), nameParameter));
    return em.createQuery(criteriaQuery).setParameter("name", name).getResultList();
}

For conciseness I would prefer the first way, especially when the query gets longer with optional parameters. Are there any disadvantages of using parameters like this, like SQL injection?

Solution

you can use ParameterExpression like this: assume that you have some input filter, an example could be this:

• in your query you have to check the value of a fiscal Code.

let's start: first of all create criteriaQuery and criteriaBuilder and root

        CriteriaBuilder cb = _em.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        Root<RootEntity> soggettoRoot = cq.from(RootEntity.class);

1) inizialize a predicateList(use for where clause) and a paramList(use for param)

Map<ParameterExpression,String> paramList = new HashMap();
List<Predicate> predicateList = new ArrayList<>();

2 )check if the input is null and create predicateList and param

if( input.getFilterCF() != null){
            //create ParameterExpression
            ParameterExpression<String> cf = cb.parameter(String.class);


           //if like clause
            predicateList.add(cb.like(root.<String>get("cf"), cf));
            paramList.put(cf , input.getFilterCF() + "%");

           //if equals clause
           //predicateList.add(cb.equal(root.get("cf"), cf));   
           //paramList.put(cf,input.getFilterCF()());
        }

3) create the where clause

 cq.where(cb.and(predicateList.toArray(new   Predicate[predicateList.size()])));
TypedQuery<Tuple> q = _em.createQuery(cq);

4) set param value

        for(Map.Entry<ParameterExpression,String> entry : paramList.entrySet())
        {
            q.setParameter(entry.getKey(), entry.getValue());
        }

Comments

[Henno Vermeulen]

That's a good thing to know. I prefer simpler code above optimized code until it is identified as a problem. The problem I have with using parameters for optional criteria in the where clause is that you need to have repeated code like "if (optionalParameter != null)" to declare and set the parameter. Your second answer confuses me. I always thought that (up to possible implementation bugs) parameters were guaranteed to not suffer from SQL injection attacks and I was wondering if my simpler first way can suffer from SQL injection. I am using OpenJPA by the way.

[Alex78191]

Will SQL injection be possible if parameters are not used?