QueryDSL投影中的集合 [英] Collections in QueryDSL projections

问题描述

我正在尝试使用投影从实体中获取与它具有某种关系的数据。然而。投影上的构造函数采用三个参数。一个集合，一个整数和另一个整数。如果我没有参数集，那么一切都很好，但是一旦添加了参数集，我就会开始收到SQL语法查询错误。

I am trying to use a projection to pull in data from an entity an some relations it has. However. The constructor on the projection takes three arguments; a set, integer and another integer. This all works fine if I don't have the set in there as an argument, but as soon as I add the set, I start getting SQL syntax query errors.

这是我正在使用的示例...

Here is an example of what I'm working with...

@Entity
public class Resource {
    private Long id;
    private String name;
    private String path;
    @ManyToOne
    @JoinColumn(name = "FK_RENDITION_ID")
    private Rendition rendition;
}

@Entity
public class Document {
    private Long id;
    private Integer pageCount;
    private String code;
}

@Entity
public class Rendition {
    Long id;
    @ManyToOne
    @JoinColumn(name="FK_DOCUMENT_ID")
    Document doc;
    @OneToMany(mappedBy="rendition")
    Set<Resource> resources;
}

public class Projection {        
    @QueryProjection
    public Projection(Set<Resource> resources, Integer pageCount, String code) {
    }
}

以下是我正在使用的查询（与这是不完全相同的

Here is the query like what I am using (not exactly the same as this is a simplified version of what I'm dealing with)....

QRendition rendition = QRendition.rendition;
Projection projection = from(rendition)
    .where(rendition.document().id.eq(documentId)
        .and(rendition.resources.isNotEmpty())
        .limit(1)
        .singleResult(
            new QProjection(rendition.resources, 
                rendition.document().pageCount,
                rendition.document().code));

只要我的投影类中没有rendition.resources，此查询就可以正常工作。并添加进去，我开始收到格式错误的SQL错误（它更改了输出sql，因此它以此开头。

This query works fine as long as my projection class does not have the rendition.resources in it. If I try and add that in, I start getting malformed SQL errors (it changes the output sql so that it starts with this.

select . as col_0_0_

所以，我想我的主要问题是如何将Set作为对象包含在其中

So, I guess my main question here is how do I include a Set as an object in a projection? Is it possible, or am I just doing something wrong here?

推荐答案

在JPA中使用投影中的集合是不可靠的。加入集合并汇总结果更安全

Using collections in projections is unreliable in JPA. It is safer to join the collection and aggregate the results instead.

Querydsl也可以用于结果汇总 http://www.querydsl.com/static/querydsl/3.2.0/reference/html/ch03s02.html#d0e1799

Querydsl can also be used for result aggregation http://www.querydsl.com/static/querydsl/3.2.0/reference/html/ch03s02.html#d0e1799

在您的情况下，是这样的

In your case something like this

QRendition rendition = QRendition.rendition;
Projection projection = from(rendition)
    .innerJoin(rendition.document, document) 
    .innerJoin(rendition.resources, resource)  
    .where(document.id.eq(documentId))
    .limit(1)
    .transform(
         groupBy(document.id).as(
            new QProjection(set(resources), 
                document.pageCount,
                document.code)));

这篇关于QueryDSL投影中的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！