在@ManyToMany关系中的表上的JPA 2.0 CriteriaQuery [英] JPA 2.0 CriteriaQuery on tables in @ManyToMany relationship

问题描述

我有两个处于@ManyToMany关系的实体.

I have two entities in a @ManyToMany relationship.

// Output has 4 other @ManyToOne relationships if that 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 this 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;

联接表在output和interval之间被称为output_has_interval.

The join table is called output_has_interval between output and interval.

我该怎么做CriteriaQuery?

SELECT `output`.`address`
FROM   `output`, `output_has_interval`, `interval`
WHERE  `output`.`address` = `output_has_interval`.`output_address`
AND    `interval`.`start` = `output_has_interval`.`interval_start`
AND    `interval`.`end` = `output_has_interval`.`interval_end`
AND    `interval`.`start` >= '2011-04-30'

如果我在MySQL中发布它，它将按预期工作.

This works as expected if I issue it in MySQL.

(我也有相应的静态元模型类，应要求我可以将其发布-没什么特别的.)

(I have the corresponding static meta model classes as well, on request I'll could 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 for '2011-04-30' */);
cq.where(pred);
TypedQuery<Output> tq = em.createQuery(cq);

但是tq.getResultList返回数据库中的每个output行.有什么主意吗?

However tq.getResultList returns every output row from my database. Any idea?

(附带说明:当我发出此查询时，Hibernate(我正在使用的提供程序)会生成许多select语句，对于每个关系Output都有一个，有时甚至更多.)

(On a side note: Hibernate (the provider I'm using) generates many select statements when I issue this query, one for every relationship Output has, sometimes more.)

编辑.:我写道:

tq.getResultList返回每个 我数据库中的output行

tq.getResultList returns every output row from my database

进行说明:它返回的不仅是数据库中每个output行.它实际上使用output和interval进行联接，但是使用谓词:

To clarify it: it returns more than just every output row from my database. It actually does a join using output and interval however the predicate:

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

不满意.

推荐答案

好，我将设法自己解决难题.

Ok, I'll managed to solve my riddle on my own.

首先:整个问题源于我是一个糟糕的程序员的事实.我遍历TypedQuery<Output>.getResultList()并以递归方式访问Output.intervals中的每个Interval，因此Hiberate延迟加载了所请求的对象，从而生成了少数select语句.

First of all: the whole problem originated from the fact that I'm a lousy programmer. I iterated over TypedQuery<Output>.getResultList() and accessed every Interval in Output.intervals in a recursive manner, thus Hiberate loaded lazily the requested objects generating a handful of select statements.

但是，我不得不以某种方式掌握这些Interval情况.我对CriteriaQuery的以下更改可以解决问题.

However I had to get a hold of those Interval instaces somehow. The following change to my CriteriaQuery did the trick.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery(); // or createQuery(Tuple.class)
Root<Output> root= cq.from(Output.class); // from 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 are the key to success!
cq.multiselect(addressPath, startPath); // select clause
Expression<Calendar> start = join.get(Interval_.start);
Predicate pred = cb.greaterThanOrEqualTo(start, /* calendar for '2011-04-30' */);
cq.where(pred); // where clause
TypedQuery<Tuple> tq = em.createQuery(cq); // holds Tuples
for (Tuple tuple : tq.getResultsList()) {
    String address = tuple.get(addressPath);
    Calendar start = tuple.get(startPath);
...

编辑

我刚刚意识到，当Path<T>扩展Expression<T>时，我可以使用Path<T>对象而不是Expression<T>对象(反之亦然).哦，好吧...

Edit

I've just realized that I could've used Path<T> objects instead Expression<T> objects (or vice versa) as Path<T> extends Expression<T>. Oh well...

这篇关于在@ManyToMany关系中的表上的JPA 2.0 CriteriaQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！