javajpajpql

Is it possible to create a dynamic JPQL query?


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?


Solution

  • 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();
    
    }