开发者

building a criteria query with jpa 2.0 by using a dynamic list

开发者 https://www.devze.com 2023-04-04 23:54 出处:网络
I\'m a bit confused while creating a criteriaQuery with JPA 2.0. Prerequisites: I have a Gui, where the user can mark some checkboxes of (let us say) wheatherstations with some options like temperatu

I'm a bit confused while creating a criteriaQuery with JPA 2.0.

Prerequisites:

I have a Gui, where the user can mark some checkboxes of (let us say) wheatherstations with some options like temperature/wind/timeperiod/etc...

Now I want to set up a criteriaQuery to pick just the selected items from a sql database and return it as an object/Map/List for building some DataModels (this will be used for generating a few primefaces charts).

What i have so far:

// for presentation purposes just this mockup-data
Calendar start = new GregorianCalendar(2011, Calendar.APRIL, 1);
Calendar end = new GregorianCalendar(2011, Calend开发者_如何学JAVAar.MAY, 1);
List<String> selectedStations = new LinkedList<String>() {{
    add("PS1");
    add("PS2");
    add("PS3");
}};
Map<String, Object selectedOptions = new LinkedHashMap<String, Object>() {{
    put("opt1","val1");
    put("opt2","val2");
    put("opt3","val3");
}};
List<String> sel = new LinkedList<String>() {{
    add("selOpt1");
    add("selOpt2");
    add("selOpt3");
}};

criteriaBuilder, criteriaQuery and the mapping class:

// go for the criteriaBuilder
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<StationItem> r = cq.from(StationItem.class);

Setting up the predicates:

// ... where (name="PS1" or name="PS2" or name="PS3") ...
Predicate p1 = cb.disjunction();
for (String s : selectedStations) {
    p1 = cb.or(p1, cb.equal(r.get("name").as(String.class), s));
}
Predicate p2 = cb.between(r.get("fetchDate").as(Date.class),
    start.getTime(), end.getTime());
Predicate p3 = cb.conjunction();
for (Map.Entry<String, Object> param : selectedOptions.entrySet())
    p3 = cb.and(p3, cb.equal(r.get(param.getKey()), param.getValue()));

And the final step to run the query and fetching the results:

At this point I do not know what is the best approach to fill the multiselect criteria with my selections. I would like to insert all items/selections from the List sel to cq.multiselect() with some kind of a loop in a dynamic way...

Any idea is welcome!

// This is working but static :(
cq.multiselect(r.get(sel.get(0)), r.get(sel.get(1)), r.get(sel.get(2)));

// i would prefer to have something like 
for (int i=0;i<sel.size();i++) {
    cq.multiselect().add(r.get(sel.get(i)));
}

Concatenating my WHERE-clause and executing the query:

cq.where(cb.and(p1,p2,p3));

List<Tuple> res = em.createQuery(cq).getResultList();
for (Tuple t : res) {
    // do something ...
};
return <something useful>

Following a pseudo SQL query to sum up what I want to achieve:

SELECT {items from List<String> sel}
FROM MyStationDatabase
WHERE (name = selectedStation.get(0) OR ... OR name = selectedStation.get(last))
    AND {items from Map<String,Object> selectedOptions}


Well, sometimes it's too trivial to be true -.-

One way to fill the cq.multiselect() with my dynamic list is to just create a list of selections and pass this over to my multiselect-query.

List<Selection<?>> s = new LinkedList<Selection<?>>();

for (String item : sel) {
    s.add(r.get(item));
}

cq.multiselect(s);

easy, but maybe someone has the same struggles with this :)
and even if not, see it as an example for a criteriaQuery ;)

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号