Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA where clause any

Tags:

java

jpa

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.

like image 643
Ke. Avatar asked Mar 13 '10 02:03

Ke.


People also ask

Where is JPQL?

JPQL WHERE Clause. The WHERE clause of a query consists of a conditional expression used to select objects or values that satisfy the expression. The WHERE clause restricts the result of a select statement or the scope of an update or delete operation.

How do you write a JPA query for in clause?

Let's follow the Spring Data JPA naming convention to write a query method for the IN clause for the Product entity class. Example: Consider the Product entity class and if we want to retrieve products with In clause then here is the Spring data JPA query method: List<Product> findByNameIn(List<String> names);

What is JpaSpecificationExecutor?

The JpaSpecificationExecutor<T> interface declares the methods that can be used to invoke database queries that use the JPA Criteria API. This interface has one type parameter T that describes the type of the queried entity.

How do we execute a normal SQL query in Spring Data JPA?

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.


1 Answers

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.

like image 56
Pascal Thivent Avatar answered Oct 17 '22 00:10

Pascal Thivent