如何处理具有实体关系的 Spring Boot/Spring Data 投影(嵌套投影) [英] How to handle Spring Boot/ Spring Data projections with entity relationships (nested projection)

问题描述

我正在尝试在 Spring Boot 中使用嵌套投影.我有 2 个实体，Parent 和 Child，而 Parent 与 Child 有单向的 @OneToMany 关系.

以下是类:(使用 Lombok-Annotations)

@Entity@Data @NoArgsConstructor公共类父{@Id@GeneratedValue私人长ID；私人字符串基本；私人字符串细节；@OneToMany(fetch = FetchType.EAGER)私人列表<孩子>儿童；公共父(字符串基本，字符串详细信息，列表<孩子>孩子){this.basic = 基本;this.detail = 细节；this.children = 儿童；}}

@Entity@Data @NoArgsConstructor公共类儿童{@Id@GeneratedValue(策略 = GenerationType.TABLE)私人长ID；私人字符串基本；私人字符串细节；公共子(字符串基本，字符串细节){this.basic = 基本;this.detail = 细节；}}

当我在没有投影的情况下获取数据时，我得到以下信息:

<预><代码>[{身份证":1，"basic": "parent-basic-1","detail": "parent-detail-1",孩子们":[{身份证":1，"basic": "child-basic-1","detail": "child-detail-1"},{身份证":2，"basic": "child-basic-2","detail": "child-detail-2"}]},{身份证":2，"basic": "parent-basic-2","detail": "parent-detail-2",孩子们":[{身份证":3，"basic": "child-basic-3","detail": "child-detail-3"},{身份证":4，"basic": "child-basic-4","detail": "child-detail-4"}]}

目标如下:

<代码> {身份证":1，"basic": "parent-basic-1",儿童":[1,2]},{身份证":2，"basic": "parent-basic-2",儿童":[3,4]}

然而，实现这一目标似乎完全不可能.

1. 到目前为止，我已经尝试过构造函数投影:

@Value公共类 ParentDto {长身份证；字符串基础；//在让 ChildDto 工作之前，首先想让它与 Child 而不是 ChildDto 一起工作集合<子>儿童；public ParentDto(long id, String basic, Collection children) {this.id = id;this.basic = 基本;this.children = 儿童；}}

//存储库中的构造函数投影@Query("select new whz.springbootdemo.application.constructor_projection.ParentDto(p.id, p.basic, p.children) from Parent p")列表findAllConstructorProjected();

但这会导致以下错误:

无法准备语句；SQL [选择 parent0_.id 作为 col_0_0_，parent0_.basic 作为 col_1_0_，.as col_2_0_ from parent parent0_inner join parent_children children1_ on parent0_.id=children1_.parent_id 内连接 child child2_ on children1_.children_id=child2_.id];嵌套异常是 org.hibernate.exception.SQLGrammarException: 无法准备语句

2. 尝试动态投影:

//存储库中的动态投影列表findAllDynamicProjectionBy();

导致以下错误:

org.hibernate.hql.internal.ast.QuerySyntaxException:无法在类 [whz.springbootdemo.application.constructor_projection.ParentDto] 上找到适当的构造函数.预期参数为:<b>long、java.lang.String、whz.springbootdemo.application.child.Child</b>[select new whz.springbootdemo.application.constructor_projection.ParentDto(generatedAlias0.id,generatedAlias0.basic,children) from whz.springbootdemo.application.parent.Parent asgeneratedAlias0 left joingeneratedAlias0.children as children];嵌套异常是 java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: 无法在类 [whz.springbootdemo.application.constructor_projection.ParentDto] 上找到适当的构造函数.预期参数是:long, java.lang.String, whz.springbootdemo.application.child.Child [select new whz.springbootdemo.application.constructor_projection.ParentDto(generatedAlias0.id,generatedAlias0.basic,children) from whz.springbootdemo.application.parent.Parent 作为generatedAlias0 左加入generatedAlias0.children 作为孩子]

这基本上告诉我执行了一个连接，但是值不是按父级的 id 分组的，因此产生了 x 行，其中 x 是父母拥有的孩子的数量，每个都有父母的基本信息和一个其孩子的信息.

3. 唯一有效"的是界面投影:

//Repository 中的接口投影列表findAllInterfaceProjectedBy();

public interface ParentDtoInterface {长 getId();字符串 getBasic();列表getChildren();}公共接口 ChildDtoInterface {长 getId();}

结果:

<预><代码>[{身份证":1，孩子们":[{身份证":1},{身份证":2}],"basic": "parent-basic-1"},{身份证":2，孩子们":[{身份证":3},{身份证":4}],"basic": "parent-basic-2"}]

现在我对 Interface-Projection 的问题是，它不仅会加载预期的属性，还会加载所有属性，但 jackson 只会序列化 Interface 提供的那些，因为它使用类/接口定义.

父加载:(sql日志；见第4行，加载详细信息)

 选择parent0_.id 为 id1_1_，parent0_.basic 为 basic2_1_，parent0_.detail 为 detail3_1_来自父母 parent0_

还有界面投影似乎真的很慢(见 这个 Stackoverflow 问题)，我仍然需要解开孩子，因为他们被赋予 [{id:1},{id:2}] 但我真的需要 [1,2].我知道我可以用 @JsonIdentityReference(alwaysAsId = true) 做到这一点，但这只是一种解决方法.

我也有点困惑为什么数据在 n+1 个查询中加载 - 1 个用于父母，另一个 n(其中 n 是父母的数量)每个父母孩子:

 选择parent0_.id 为 id1_1_，parent0_.basic 为 basic2_1_，parent0_.detail 为 detail3_1_来自父母 parent0_选择children0_.parent_id 为 parent_i1_2_0_，children0_.children_id 为 children2_2_0_，child1_.id 为 id1_0_1_，child1_.basic 为 basic2_0_1_，child1_.detail 作为 detail3_0_1_来自parent_children children0_内连接孩子孩子1_在 children0_.children_id=child1_.id哪里children0_.parent_id=?//... 省略进一步的子查询

我已经尝试过 @OneToMany(fetch=FetchType.LAZY) 和 @Fetch(FetchType.JOINED) - 两者都给出了与上面相同的结果.

所以主要问题是:有什么方法可以使用 Spring Boot 实现嵌套实体的投影，以便在尽可能少的查询中只加载所需的数据，并且在最好的情况下我可以调整它这样我就不必加载列表子项，而只需加载列表子项(也许通过 Jpa 查询按父 ID 对连接的行进行分组，然后从子项中提取所需的数据?).

我正在使用 Hibernate 和内存数据库.

感谢您提供任何答案或提示！

澄清:我不是想找到一种方法以所需格式序列化数据 - 这我已经可以实现了.主要的重点是只从数据库加载必要的信息.

解决方案

这将始终获取孩子，但可以给你你想要的结果.

公共接口 SimpleParentProjection {字符串 getBasic();字符串 getDetail();@Value("#{T(SimpleParentProjection).toId(target.getChildren())}")String[] getChildren();static String[] toId(Set childSet) {return childSet.stream().map(c -> String.valueOf(c.getId())).toArray(String[]::new);}}

I'm trying to get nested projections working in Spring Boot. I have 2 entities, Parent and Child, wheras Parent has a unidirectional @OneToMany relationship to Child.

Here are the classes: (using Lombok-Annotations)

@Entity
@Data @NoArgsConstructor
public class Parent {

    @Id
    @GeneratedValue
    private long id;
    private String basic;
    private String detail;

    @OneToMany(fetch = FetchType.EAGER)
    private List<Child> children;

    public Parent(String basic, String detail, List<Child> children) {
        this.basic = basic;
        this.detail = detail;
        this.children = children;
    }
}

@Entity
@Data @NoArgsConstructor
public class Child {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private long id;
    private String basic;
    private String detail;

    public Child(String basic, String detail) {
        this.basic = basic;
        this.detail = detail;
    }
}

When im fetching the data without projecting i get the following:

[
    {
        "id": 1,
        "basic": "parent-basic-1",
        "detail": "parent-detail-1",
        "children": [
            {
                "id": 1,
                "basic": "child-basic-1",
                "detail": "child-detail-1"
            },
            {
                "id": 2,
                "basic": "child-basic-2",
                "detail": "child-detail-2"
            }
        ]
    },
    {
        "id": 2,
        "basic": "parent-basic-2",
        "detail": "parent-detail-2",
        "children": [
            {
                "id": 3,
                "basic": "child-basic-3",
                "detail": "child-detail-3"
            },
            {
                "id": 4,
                "basic": "child-basic-4",
                "detail": "child-detail-4"
            }
        ]
    }

and the goal would be the following:

    {
        "id": 1,
        "basic": "parent-basic-1",
        "children": [1,2]
    },
    {
        "id": 2,
        "basic": "parent-basic-2",
        "children": [3,4]
    }

However it seems completly impossible to achive this.

1. So far I've tried Constructor Projection:

@Value
public class ParentDto {
    long id;
    String basic;
    // wanted to get it to work with just Child instead of ChildDto first, before getting ChildDto to work
    Collection<Child> children; 

    public ParentDto(long id, String basic, Collection<Child> children) {
        this.id = id;
        this.basic = basic;
        this.children = children;
    }
}

    // Constructor Projection in Repository
    @Query("select new whz.springbootdemo.application.constructor_projection.ParentDto(p.id, p.basic, p.children) from Parent p")
    List<ParentDto> findAllConstructorProjected();

but that leads to the following error:

could not prepare statement; SQL [select parent0_.id as col_0_0_, parent0_.basic as col_1_0_, . as col_2_0_ from parent parent0_ inner join parent_children children1_ on parent0_.id=children1_.parent_id inner join child child2_ on children1_.children_id=child2_.id]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

2. Trying Dynamic Projection:

    // Dynamic Projection in Repository
    List<ParentDto> findAllDynamicProjectionBy();

leads to the following error:

org.hibernate.hql.internal.ast.QuerySyntaxException:
Unable to locate appropriate constructor on class [whz.springbootdemo.application.constructor_projection.ParentDto].
Expected arguments are: <b>long, java.lang.String, whz.springbootdemo.application.child.Child</b>
[select new whz.springbootdemo.application.constructor_projection.ParentDto(generatedAlias0.id, generatedAlias0.basic, children) from whz.springbootdemo.application.parent.Parent as generatedAlias0 left join generatedAlias0.children as children]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [whz.springbootdemo.application.constructor_projection.ParentDto]. Expected arguments are: long, java.lang.String, whz.springbootdemo.application.child.Child [select new whz.springbootdemo.application.constructor_projection.ParentDto(generatedAlias0.id, generatedAlias0.basic, children) from whz.springbootdemo.application.parent.Parent as generatedAlias0 left join generatedAlias0.children as children]

which basically tells me that a join is executed, but the values arent grouped by the id of the parent, thus resulting in x rows, where x is the number of childs the parents has, each with the parents basic information and one of its childs information.

3. The only thing "working" is Interface Projection:

    // Interface Projection in Repository
    List<ParentDtoInterface> findAllInterfaceProjectedBy();

public interface ParentDtoInterface {
    long getId();
    String getBasic();
    List<ChildDtoInterface> getChildren();
}

public interface ChildDtoInterface {
    long getId();
}

It results in:

[
    {
        "id": 1,
        "children": [
            {
                "id": 1
            },
            {
                "id": 2
            }
        ],
        "basic": "parent-basic-1"
    },
    {
        "id": 2,
        "children": [
            {
                "id": 3
            },
            {
                "id": 4
            }
        ],
        "basic": "parent-basic-2"
    }
]

Now my problem with Interface-Projection is, that it will not just load the expected properties, but all properties, but jackson will only serialize those that the Interface provides, cause it uses the Class/Interface-Definition.

Parent loaded: (sql log; see line 4, detail information is loaded)

    select
        parent0_.id as id1_1_,
        parent0_.basic as basic2_1_,
        parent0_.detail as detail3_1_ 
    from
        parent parent0_

Also Interface Projection seems to be really slow (see this Stackoverflow question) and i still would have to unpack the children cause they are given as [{id:1},{id:2}] but i really need [1,2]. I know i can do this with @JsonIdentityReference(alwaysAsId = true) but thats just a workaround.

Also I'm abit confused why the data is loaded in n+1 queries - 1 for the parents, and another n (where n is the number of parents) for each parents childs:

    select
        parent0_.id as id1_1_,
        parent0_.basic as basic2_1_,
        parent0_.detail as detail3_1_ 
    from
        parent parent0_

   select
        children0_.parent_id as parent_i1_2_0_,
        children0_.children_id as children2_2_0_,
        child1_.id as id1_0_1_,
        child1_.basic as basic2_0_1_,
        child1_.detail as detail3_0_1_ 
    from
        parent_children children0_ 
    inner join
        child child1_ 
            on children0_.children_id=child1_.id 
    where
        children0_.parent_id=?

//... omitting further child queries

I have tried @OneToMany(fetch=FetchType.LAZY) and @Fetch(FetchType.JOINED) - both give the same result as above.

So the main question is: Is there any way to achive projection with Spring Boot for nested entities, so that only the needed data is loaded in as little as possible queries and in a best case scenario I can adjust it so that instead of having to load List children i can just load List childIds (maybe through a Jpa query that groups the joined rows by parentid and lets be extract needed data from the Child?).

Im using Hibernate and an In-Memory Database.

Thanks in regards for any answer or tip!

Edit: To clarify: I'm not trying to find a way to serialize the data in the wanted format - this i already can achive. The main focus is on only loading the neccessary information from the database.

解决方案

this will always fetch the children but could give you the result you want.

public interface SimpleParentProjection {

    String getBasic();

    String getDetail();

    @Value("#{T(SimpleParentProjection).toId(target.getChildren())}")
    String[] getChildren();

    static String[] toId(Set<Child> childSet) {
        return childSet.stream().map(c -> String.valueOf(c.getId())).toArray(String[]::new);
    }
}

这篇关于如何处理具有实体关系的 Spring Boot/Spring Data 投影(嵌套投影)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！