JPA where clause any

Question

Hi,

I'm new to JPA.

In JPA, the query is:

 Query query = entityManager.createQuery("select o from Product o WHERE o.category = :value");
query.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.

Answer 1

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

Answer 2

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.

Answer 3

To archive parameters become optional you can write a query in such a way 'select o from Product o WHERE :value is null or :value='' or o.category = :value'