3

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, Calendar.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}
Mxyk
  • 10,678
  • 16
  • 57
  • 76
RonH
  • 140
  • 2
  • 14
  • I see the edit in the middle of your question. Is this question now obsolete? If it is, please either delete the question or answer it yourself, as this is confusing for other users that find your edited (and answered) question... – Lukas Eder Sep 14 '11 at 11:28
  • unfortunately the question isn't answered :(. i only fixed a thing, but the main problem with _dynamically_ filling up the `cq.multiselect()` still exists :( – RonH Sep 14 '11 at 12:49
  • OK, I didn't realise that... It looked as though that was the main problem at first glance – Lukas Eder Sep 14 '11 at 13:05
  • Can you answer your own question in a way that would help others? If you do, you can select yours as the correct answer. It may seem strange, but it is the preferred way of dealing with situations like this. –  Sep 14 '11 at 15:04

1 Answers1

4

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

RonH
  • 140
  • 2
  • 14