Skip to main content

Dynamic JPA 2.0 query using Criteria API

5 replies [Last post]
Anonymous

Hello all,

I am a bit stucked constructing a dynamic query using the
CriteriaBuilder of JPA 2.0.

I have quite a common use case I guess: User supplies a arbitrary amount
of search parameters X to be and / or concatenated: like :

select e from Foo where (name = X1 or name = X2 .. or name = Xn )

The Method or of CriteriaBuilder is not dynamic:

Predicate or(Predicate... restrictions)

Ideas? Samples?

Rgds Ben

[att1.html]

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
ievans
Offline
Joined: 2003-06-10
Points: 0

Store the parameters in a List (or a Map) and iterate through them in a loop.

Something like:
[code]
public void arbitraryParameterMethod(List parameters) {
// list to store query results
List results = new ArrayList();

//set up the Criteria query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Foo.class);
Root foo= cq.from(Foo.class);

// loop through the list of parameters
Iterator i = parameters.iterator();
while (i.hasNext()) {
String parameter = i.next();
cq.where(cb.equal(foo.get(Foo_.name), parameter));
}
// finish and execute the query
cq.select(foo);
TypedQuery q = em.createQuery(cq);
results = q.getResultList();
}
[/code]

mdebac
Offline
Joined: 2007-07-10
Points: 0

Where in this solution is used disjunction of predicates?

Predicate or(Predicate... restrictions)

chris_delahunt
Offline
Joined: 2005-07-06
Points: 0

Are you looking for an example? It wouldn't be difficult to modify the example given to use 'or' in the loop instead. Also note, that the example is incorrect - each subsequent loop calling cq.where will replace the previous where clause, so that only the last in the loop is used.

Or takes two predicates or an array of predicates. Assuming you have an array of Predicates:
Predicate a = restrictions[0];
for (int i = 1; i < max; ++i){
a = criteriaBuilder.or(a, restrictions[i]);
}
gives a, which is the same as:
Predicate a = criteriaBuilder.or(restrictions);
You would then add them to the selection criteria via the where call outside the loop:
cq.where(a);

So modifying the previous example could be something like

public void arbitraryParameterMethod(List parameters) {
// list to store query results
List results = new ArrayList();

//set up the Criteria query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Foo.class);
Root foo= cq.from(Foo.class);

// loop through the list of parameters
Iterator i = parameters.iterator();
Predicate a = null;
if (i.hasNext())
a = cb.equal(foo.get(Foo_.name), i.next());
while (i.hasNext()) {
String parameter = i.next();
Predicate a = cb.or(cb.equal(foo.get(Foo_.name), parameter));
}
cq.where(a);
// finish and execute the query
cq.select(foo);
TypedQuery q = em.createQuery(cq);
results = q.getResultList();
}

atomicknight
Offline
Joined: 2010-03-14
Points: 0

Have you tried using the other form of or?

[url]http://java.sun.com/javaee/6/docs/api/javax/persistence/criteria/CriteriaBuilder.html#or(javax.persistence.criteria.Expression, javax.persistence.criteria.Expression)[/url]

saturon

Hi

yes maybe this would be the solution, but I frankly do not know how to
use it. Have not found an explanation of what this Interface Expression
is supposed to do.

Thanks, Ben

-----Original Message-----
From: glassfish@javadesktop.org [mailto:glassfish@javadesktop.org]
Sent: Donnerstag, 25. März 2010 01:01
To: users@glassfish.dev.java.net
Subject: Re: Dynamic JPA 2.0 query using Criteria API

Have you tried using the other form of or?

http://java.sun.com/javaee/6/docs/api/javax/persistence/criteria/Criteri
aBuilder.html#or(javax.persistence.criteria.Expression,
javax.persistence.criteria.Expression)
[Message sent by forum member 'atomicknight']

http://forums.java.net/jive/thread.jspa?messageID=393597

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net