Menu
I need to create a dynamic query. I tried to do it using JPQL, but could not. For example:
public List get(String category, String name, Integer priceMin, Integer priceMax){
List<Prod> list;
String query = "select p from Prod p where 1<2 ";
String queryCat="";
String queryName="";
String queryPriceMin="";
String queryPriceMax="";
String and = " and ";
if (!category.isEmpty()){
query+=and+"p.cat.name=:category ";
}
if (!name.isEmpty()){
query+=and+"p.name=:name ";
}
if (priceMin!=null){
query+=and+"p.price>=:priceMin ";
}
if (priceMax!=null){
query+=and+"p.price<=:priceMax ";
}
return list = entityManager.createQuery(query)
.setParameter("category", category)
.setParameter("name",name)
.setParameter("priceMin", priceMin)
.setParameter("priceMax", priceMax)
.getResultList();
}
If there are all the parameters, the query runs, but if there is no such parameter category I have Exception java.lang.IllegalArgumentException: Parameter with that name [category] did not exist and I understand why is it so, but how I can avoid this problem?
545
Creating Queries in JPQL This method creates dynamic queries that can be defined within business logic. Query createNamedQuery(String name) - The createNamedQuery() method of EntityManager interface is used to create an instance of Query interface for executing named queries.
Using EntityManager methods createQuery or createNativeQuery , you can create a Query object dynamically at run time (see "Using Java"). Using the Query methods getResultList , getSingleResult , or executeUpdate you can execute the query (see "Executing a Query").
In some cases it can happen Hibernate/JPA does not generate the most efficient statements, so then native SQL can be faster - but with native SQL your application loses the portability from one database to another, so normally is better to tune the Hibernate/JPA Query mapping and the HQL statement to generate more ...
You have to build your query dynamically, at the beginning of the method check whether id is null or equal 0 . To make it easier you can use trick in where clause with 1=1 so where clause can be included all the time in the query.
You can try.
public List<Prod> get(String category, String name, Integer priceMin, Integer priceMax){
Map<String, Object> paramaterMap = new HashMap<String, Object>();
List<String> whereCause = new ArrayList<String>();
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("select p from Prod p ");
if (!category.isEmpty()){
whereCause.add(" p.cat.name =:category ");
paramaterMap.put("category", category);
}
if (!name.isEmpty()){
whereCause.add(" p.name =:name ");
paramaterMap.put("name", name);
}
if (priceMin!=null){
whereCause.add(" p.price>=:priceMin ");
paramaterMap.put("priceMin", priceMin);
}
if (priceMax!=null){
whereCause.add("p.price<=:priceMax ");
paramaterMap.put("priceMax", priceMax);
}
//.................
queryBuilder.append(" where " + StringUtils.join(whereCause, " and "));
Query jpaQuery = entityManager.createQuery(queryBuilder.toString());
for(String key :paramaterMap.keySet()) {
jpaQuery.setParameter(key, paramaterMap.get(key));
}
return jpaQuery.getResultList();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
