JPA where clause any

Question

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.

this guide has some examples using `Criteria`s as well as other more hardcoded alternatives. https://www.tutorialspoint.com/hibernate/hibernate_quick_guide.htm — Andrei-Niculae Petre, Nov 20 '18 at 23:02

score 18 · Answer 1 · answered Mar 13 '10 at 20:43

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.

Criteria is a better solution for dynamic queries. Expression.eq is deprecated, instead you can use Restrictions.eq — Gere, Feb 09 '16 at 19:23
this guide has some examples using `Criteria`s as well as other more hardcoded alternatives. https://www.tutorialspoint.com/hibernate/hibernate_quick_guide.htm — Andrei-Niculae Petre, Nov 20 '18 at 23:01

score 4 · Answer 2 · edited May 13 '16 at 11:29

4

To archive parameters become optional you can write a query without having to use the Criteria API:

select o from Product o WHERE :value is null or :value='' or o.category = :value

edited May 13 '16 at 11:29

Lukas Z.

165
1
5

answered Apr 27 '10 at 20:59

palmal

49
1

Hello, I'm wondering if is possible to make this kind of evaluation with collections like the query from this post http://stackoverflow.com/questions/570229/hibernate-hql-query-how-to-set-a-collection-as-a-named-parameter-of-a-query. So this query isn't working in my project as follow: FROM Foo WHERE Id = :id AND (:barlist is null or Bar in (:barlist)) – André Luís Tomaz Dionisio Nov 29 '16 at 11:48

score 2 · Answer 3 · answered May 30 '12 at 21:16

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 May 30 '12 at 21:16

changeme

620
2
11
35

score 1 · Answer 4 · answered Apr 10 '12 at 01:51

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 can set the category to "%". if (category == null) query.setParameter("category", "%"); else query.setParameter("category", category);

score 0 · Answer 5 · answered Mar 13 '10 at 02:40

0

SELECT * FROM PRODUCT WHERE CATEGORY=*

I think you are new to SQL, too.

WHERE CATEGORY = * does not mean "any category" (it is not even valid SQL).

In both SQL and JPA, you would just not have the WHERE clause at all if you want any category (or in SQL you could have WHERE CATEGORY IS NOT NULL).

answered Mar 13 '10 at 02:40

Thilo

241,635
91
474
626

sorry for the wrong SQL query:) Your solution "WHERE CATEGORY IS NOT NULL" is good, but I have to create another query. Is there any other way can save one query? – Ke. Mar 13 '10 at 02:54

score 0 · Answer 6 · answered Nov 23 '18 at 11:11

Pascal Thivent gave pretty good answer recommending using Criteria. However the Criteria in his answer is pure Hibernate criteria. Using JPA Criteria example:

private List<Result> getProduct(String category) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteria = builder.createQuery(Product.class);
    Root<Product> productRoot = criteria.from(Product.class);

    if (category != null)
        criteria.where(builder.equal(productRoot.get("category"), category))    
    }

    entityManager.createQuery(criteria).getResultList();
}

score 0 · Answer 7 · answered Jul 18 '19 at 05:21

The thing you are trying to achieve is counter intuitive in terms of design pattern. Let's think of the problems in SQL terms ignoring all JPA and other.

The corresponding SQL query of your JPQL looks like below

SELECT o.* FROM product o WHERE o.category = 'SOME_CAT';

Now if you pass null instead of category SOME_CAT the SQL would be like

SELECT o.* FROM product o WHERE o.category IS NULL;

There is no SQL standard to invert the result set altering param value unless your SQL looks like following

SELECT o.* FROM product o WHERE o.category IS NOT NULL;

The similar JPQL will look like

SELECT o FROM Product o WHERE o.category <> :param

You can see that we need to invert the logical operation instead of manipulating the param. There is no standard way to achive this behavior unless you build JPQL dynamically with if else condition.

I would prefer a separate method handler one for filtering with category and other for listing all regardless of category.

JPA where clause any

7 Answers7

Linked