Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to create a dynamic JPQL query?

Tags:

java

jpa

jpql

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?

like image 545
Kadzhaev Marat Avatar asked Jul 03 '15 15:07

Kadzhaev Marat


People also ask

How do you create a JPQL query?

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.

Which is the JPA method to create dynamic query?

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").

Which is faster JPQL or native 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 ...

How do you write a dynamic query in Java?

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.


1 Answers

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

}
like image 177
ooozguuur Avatar answered Oct 17 '22 07:10

ooozguuur