java - JPA 2.0 CriteriaQuery on tables in @ManyToMany relationship -

i have 2 entities in @manytomany relationship.

// output has 4 other @manytoone relationships if matters     @entity @table public class output {     @id public string address;     @manytomany(targetentity = interval.class,                 cascade = cascadetype.all,                 fetch = fetchtype.lazy)     @jointable(name = "output_has_interval",                joincolumns = {@joincolumn(name = "output_address",                                            referencedcolumnname = "address")},         inversejoincolumns = {@joincolumn(name = "interval_start",                                           referencedcolumnname = "start"),                               @joincolumn(name = "interval_end",                                            referencedcolumnname = "end")})     collection<interval> intervals;  @idclass(intervalpk.class) // i'll omit one. @entity @table public class interval {     @id public calendar start;     @id public calendar start;     @manytomany(targetentity = output.class,                 mappedby = "intervals",                 cascade = cascadetype.all,                 fetch = fetchtype.lazy)     public collection<output> outputs; 

the join table called output_has_interval between output , interval.

how do criteriaquery this?

select `output`.`address`   `output`, `output_has_interval`, `interval`  `output`.`address` = `output_has_interval`.`output_address` ,    `interval`.`start` = `output_has_interval`.`interval_start` ,    `interval`.`end` = `output_has_interval`.`interval_end` ,    `interval`.`start` >= '2011-04-30' 

this works expected if issue in mysql.

(i have corresponding static meta model classes well, on request i'll post them - nothing fancy tho'.)

criteriabuilder cb = em.getcriteriabuilder(); criteriaquery<output> cq = cb.createquery(output.class); root<output> root= cq.from(output.class); collectionjoin<output, interval> join = root.join(output_.intervals); expression<calendar> start = join.get(interval_.start); predicate pred = cb.greaterthanorequalto(start, /* calendar '2011-04-30' */); cq.where(pred); typedquery<output> tq = em.createquery(cq); 

however tq.getresultlist returns every output row database. idea?

(on side note: hibernate (the provider i'm using) generates many select statements when issue query, 1 every relationship output has, more.)

edit.: wrote:

tq.getresultlist returns every output row database

to clarify it: returns more every output row database. join using output , interval predicate:

`interval`.`start` >= '2011-04-30' 

doesn't satisfied.

ok, i'll managed solve riddle on own.

first of all: whole problem originated fact i'm lousy programmer. iterated on typedquery<output>.getresultlist() , accessed every interval in output.intervals in recursive manner, hiberate loaded lazily requested objects generating handful of select statements.

however had hold of interval instaces somehow. following change criteriaquery did trick.

criteriabuilder cb = em.getcriteriabuilder(); criteriaquery<tuple> cq = cb.createtuplequery(); // or createquery(tuple.class) root<output> root= cq.from(output.class); // clause collectionjoin<output, interval> join = root.join(output_.intervals); path<string> addresspath = root.get(output_.address); // mind these path objects path<calendar> startpath = join.get(interval_.start); // these key success! cq.multiselect(addresspath, startpath); // select clause expression<calendar> start = join.get(interval_.start); predicate pred = cb.greaterthanorequalto(start, /* calendar '2011-04-30' */); cq.where(pred); // clause typedquery<tuple> tq = em.createquery(cq); // holds tuples (tuple tuple : tq.getresultslist()) {     string address = tuple.get(addresspath);     calendar start = tuple.get(startpath); ... 

edit

i've realized could've used path<t> objects instead expression<t> objects (or vice versa) path<t> extends expression<t>. oh well...