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 it is so, but how can I avoid this problem?
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();
}