Alex Rivera | Logout

JPA where clause any

Asked 2010-03-13T02:34:54.120
17

In JPA, the query is:

Query q = entityManager.createQuery("select o from Product o WHERE o.category = :value");
q.setParameter("category", category);

How can I set category to any category in JPA? So if the null category passed, I simple ignore the category parameter, select all products.

Edit
Report

2 Answers

19

How can I set category to any category in JPA? So if the null category passed, I simple ignore the category parameter, select all products.

You'll have to build the query dynamically here. With HQL (this is a simplified example):

Map<String, Object> params = new HashMap<String, Object>();
StringBuffer hql = new StringBuffer("from Product p");
boolean first = true;

if (category != null) {
    hql.append(first ? " where " : " and ");
    hql.append("p.category = :category");
    params.put("category", category);
}

// And so on...

Query query = session.createQuery(hql.toString());

Iterator<String> iter = params.keySet().iterator();
while (iter.hasNext()) {
    String name = iter.next();
    Object value = params.get(name);
    query.setParameter(name, value);
}

List results = query.list()

But, actually, my recommendation would be to use the Criteria API here:

Criteria criteria = session.createCriteria(Product.class);
if (category != null) {
    criteria.add(Expression.eq("category", category);
}
// And so on...
List results = criteria.list();

Much simpler for complicated dynamic queries.

answered 2010-03-13T20:43:37.610
2

You are right almost with small change.

Query query = entityManager.createQuery("select o from Product o WHERE o.category = :value");
query.setParameter("value", category);

in setParamater "value" (exact text) should match with ":value" in query.

answered 2012-05-30T21:16:56.690

Your Answer