How create JPA query with dynamicly parameters?

Question

I have this method:

 public List<User> getReport(String name, int age, String sName, Date bd, 

     String address, String msg2, int first, int pageSize) {
     List<User> result = new ArrayList<>();
     result = em.createQuery("from User u, User.class)
                    .setFirstResult(first)
                    .setMaxResults(pageSize)
                    .getResultList();
     return result;
    }

Now, this method return all data. I need sett dynamicly parameters. I can make this:

result = em.createQuery("from User u WHERE u.age = :age", User.class)
                    .setParameter("age", age)
                    .setFirstResult(first)
                    .setMaxResults(pageSize)
                    .getResultList();

But if my age is null - I dont get result. And with another fields.

I need somethign like:

if(age != null){
em.createQuery("from User u WHERE u.age = :age", User.class)
                        .setParameter("age", age)
}

if(name!= null){
em.createQuery("from User u WHERE u.age = :age AND u.name= :name", User.class)
                        .setParameter("age", age)
}

I can format string query like:

String query = "from User u WHER";

and them concate this string

if(age != null){
query = query +"u.age = :age";
em.createQuery("from User u WHERE u.age = :age", User.class)
                        .setParameter("age", age)
}

But I thing it is bad practice.

JPQL queries start "SELECT " (or UPDATE/DELETE). Omitting that because one implementation allows you to is "bad practice" — Neil Stockton
– Neil Stockton, Commented Dec 16, 2015 at 7:43

Kajzer · Accepted Answer · 2022-01-11 20:09:39Z

All parameters are picking up the map, as I create a dynamic query.

You can try:

public List<User> getReport(String name, Integer age, String sName, Date bd, 

 String address, String msg2, int first, int pageSize) {

    Map<String, Object> paramaterMap = new HashMap<String, Object>();
    List<String> whereClause = new ArrayList<String>();

    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select u from User u ");

    if (!name.isEmpty()){
        whereClause.add(" u.name =:name ");
        paramaterMap.put("category", category);
    }
    if (age != null){
        whereClause.add(" u.age =:age ");
        paramaterMap.put("age", age);
    }
   if (!sName.isEmpty()){
        whereClause.add(" u.sName =:sName ");
        paramaterMap.put("sName", sName);
    }
    if (bd != null){
        whereClause.add(" u.bd =:bd ");
        paramaterMap.put("bd", bd);
    }
    if (!address.isEmpty()){
        whereClause.add(" u.address =:address ");
        paramaterMap.put("address", address);
    }
   if (!msg2.isEmpty()){
        whereClause.add(" u.msg2 =:msg2 ");
        paramaterMap.put("msg2", msg2);
    }

    queryBuilder.append(" where " + StringUtils.join(whereClause, " and "));
    Query jpaQuery = entityManager.createQuery(queryBuilder.toString());

    for(String key :paramaterMap.keySet()) {
            jpaQuery.setParameter(key, paramaterMap.get(key));
    }

    jpaQuery.setFirstResult(first)
    jpaQuery.setMaxResults(pageSize)

    return  jpaQuery.getResultList();
}

BetaRide · Accepted Answer · 2015-12-16 06:30:37Z

1

I guess that you mixed up the wording. By using something like u.age = :age you have a dynamic parameter, since you can change the value of age every time you use the query.

What you are likely looking for is dynamic queries. Although you can do this by just concatenating strings. This is nothing else than building your own (small) query builder.

JPA comes already with a full blown query builder called Criteria API. IMHO the API isn't self explaining or easy to learn but google easily reveals you a lot of examples which get you started.

answered Dec 16, 2015 at 6:30

BetaRide

16.9k32 gold badges108 silver badges192 bronze badges

Collectives™ on Stack Overflow

How create JPA query with dynamicly parameters?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related