Create a namedQuery to get filtered result

Question

How can I set a parameter as a list of values in a named query, so I can get a filtered resultSet from database?

For example: I want a resultSet which contains only data filtered by code 1, 2, and 3.

In SQL, the query is:

SELECT * FROM PontoEletronico WHERE prefDep IN (1, 2, 3);

As I read here, my namedQuery actually is:

@NamedQuery(name = "PontoEletronico.findAllEscritorios", query = "SELECT p FROM PontoEletronico p WHERE p.prefDep IN :myList");

The myList is set as below:

List<PrefDeps> myList;
private final PrefDeps p1 = new PrefDeps();
private final PrefDeps p2 = new PrefDeps();

    public List<T> findAllEscritorios() {
    p1.setPrefDep(9882);
    p2.setPrefDep(9517);
    myList.add(p1);
    myList.add(p2);
    return (List<T>) getEntityManager().createNamedQuery("PontoEletronico.findAllEscritorios", PontoEletronico.class).setParameter("prefDep", myList).getResultList();
}

Thanks in advance.

score 0 · Accepted Answer · answered Oct 02 '14 at 06:21

Just set "myList" as key of setParameter call::

return (List<T>) getEntityManager().createNamedQuery("PontoEletronico.findAllEscritorios", PontoEletronico.class).setParameter("myList", myList).getResultList();

From javax.persistence.Query:

/**
 * Bind an argument value to a named parameter.
 * @param name  parameter name
 * @param value  parameter value
 * @return the same query instance
 * @throws IllegalArgumentException if the parameter name does 
 *         not correspond to a parameter of the query or if
 *         the argument is of incorrect type
 */
Query setParameter(String name, Object value);

So, in your query called the pararameter :myList you should use the same key on setParameter.

Good luck!

Create a namedQuery to get filtered result

1 Answers1