CODEWITHSUNDEEP
javajpa
Ke.
Ke.

Reputation: 457

JPA where clause any

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.

Upvotes: 17

Views: 23290

Answers (7)

Rakib
Rakib

Reputation: 145

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.

Upvotes: 0

J-Alex
J-Alex

Reputation: 7097

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

Upvotes: 0

palmal
palmal

Reputation: 49

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

Upvotes: 4

changeme
changeme

Reputation: 640

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.

Upvotes: 2

Veer Muchandi
Veer Muchandi

Reputation: 277

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

Upvotes: 1

Pascal Thivent
Pascal Thivent

Reputation: 570295

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.

Upvotes: 19

Thilo
Thilo

Reputation: 262474

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

Upvotes: 0

Related Questions