Is it possible to reuse the parameters of a Query in JPA?

Question

Hello,

I have a question regarding Parameters of the object Query in the JPA.

I am implementing pagination of my tables using the Primefaces with LazyDataModel and in the function I use to make the pagination I do 2 searches in the database. The first to filter the data and return only those of the precise range q. And the second to return the COUNT of the total records for the filter (no range limit).

The only difference between these queries is that the first one has a maxResults = range[1] - range[0] and a firstResult = range[0]. Effectively returning only the results I want from the base.

Therefore, I would like to be able to reuse the filter parameters of the first Query, in the second.

Something like that:

Query queryUm  = em.createQuery("SELECT x FROM...")
                .setParameter("x", 1)
                .setParameter("x", 1)
                .setParameter("x", 1)
                .setMaxResults(range[1] - range[0])
                .setFirstResult(range[0]);

List<Entidade> list = queryUm.getResultList();

Query queryDois = em.createQuery("SELECT COUNT(x) From...");

//reutiliza os parametros aqui..
queryDois.getParameters().addAll(queryUm.getParameters());

Long totalRecords = queryDois.getSingleResult();

I’ve tried to do something like this. But the second one Query does not execute with an error saying that the expected parameter was not found.

I look forward to your contributions, thank you.

Answer 1

After investigating a little more to understand the functioning of Query and how this Object manages the parameters, I identified that when mounting the JPQL and create the object instance, is stored in the Set<Parameter<?>> the references of the parameters in the query (eg: where x = :valorX) the parameter is added in Set even before receiving the value from the method setParameter() of Query. And the API also exposes the method getParameterValue() that may receive a Parameter<?> as parameter and returns the value that was set in to be sent in the query.

Anyway, I can reuse the parameters of a Query as follows:

//Cria primeira query
Query q1 = em.createQuery("SELECT ...")
           .setParameter('a', 1)
           .setParameter('b', 2)
           ...;

List<Entity> resultado = q1.getResultList();

//Cria segunda query
Query q2 = em.createQuery("SELECT COUNT(o)...");

for(Parameter<?> p : q1.getParameters()) {
    //Pega o nome e valor do parâmetro na query anterior e seta na nova..
    q2.setParameter(p.getName, q1.getParameterValue(p));
}

Anyway, it’s not as beautiful as I’d like, being able to just pick up and play the Set<Parameter<?>> from one to the other. but it works.

I hope it helps someone who goes through the same difficulty.

Answer 2

No, every Query must contain its own parameter. For each Query is executed independently of each other, and therefore the parameter is not reused from each other.

But you can use a variable param (suggestive name) to pass as the value of the parameter if it repeats in its other Query.

Take a look in this post to see if it works for you.