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

Navigation
0

Hello,

I have a question about Parameters of object Query on JPA .

I am implementing pagination of my tables using Primefaces with LazyDataModel and in the function that I use to perform pagination I do 2 searches in the database. The first one to filter the data and return only those of the range q I need. And the second to return the COUNT of the total records to the filter (without the 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] . The actually returning only the results I want from the base.

So, I'd like to be able to reuse the filter parameters of the first Query , in the second.

Something like this: 

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 have tried to do something like this, but the second Query does not execute with an error saying that the expected parameter was not found.

I look forward to your contributions, Thank you.

    
asked by anonymous 15.09.2017 / 16:42

2 answers

2

After investigating a bit more to understand the operation of Query and how this Object manages the parameters, I have identified that by setting the JPQL and creating the object instance, the parameter references that are stored in Set<Parameter<?>> are in the query (ex: where x = :valorX ) the parameter is added in Set before it even receives the value from the setParameter() method of Query . And the API also exposes the getParameterValue() method that can receive a Parameter<?> as a 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));
}

Finally, it is not as beautiful as it would like, you can simply grab and play Set<Parameter<?>> from one to another .. but it works.

I hope it helps someone who has the same difficulty.

    
15.09.2017 / 19:35
0

No, each Query must contain its own parameter. Because every Query is executed independently of the other, and therefore, it does not reuse the parameter from one to the other.

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

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

    
15.09.2017 / 17:19
Error No value specified for parameter 4 How to change the image when hovering over