我正在尝试在Spring Boot中实现嵌套投影。 我有2个实体,即Parent
和Child
,其中Parent
具有单向@OneToMany
与Child
的关系。
以下是这些类:(使用Lombok注解)
@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;
}
}
当我在不进行投影的情况下获取数据时,我会得到以下结果:
[
{
"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"
}
]
}
我的目标是以下内容:
{
"id": 1,
"basic": "parent-basic-1",
"children": [1,2]
},
{
"id": 2,
"basic": "parent-basic-2",
"children": [3,4]
}
然而,似乎完全不可能实现这一点。
1. 到目前为止,我尝试过构造函数投影:
@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();
但这会导致以下错误:
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
尝试使用动态投影技术:
// Dynamic Projection in Repository
List<ParentDto> findAllDynamicProjectionBy();
导致以下错误:
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]
这基本上告诉我正在执行一个连接操作,但是值没有按父级的id进行分组,因此结果为x行,其中x是父项具有的子项数,每个子项都具有父项的基本信息和其一项子项的信息。
- 唯一“可用”的是接口投影:
// Interface Projection in Repository
List<ParentDtoInterface> findAllInterfaceProjectedBy();
public interface ParentDtoInterface {
long getId();
String getBasic();
List<ChildDtoInterface> getChildren();
}
public interface ChildDtoInterface {
long getId();
}
结果为:
[
{
"id": 1,
"children": [
{
"id": 1
},
{
"id": 2
}
],
"basic": "parent-basic-1"
},
{
"id": 2,
"children": [
{
"id": 3
},
{
"id": 4
}
],
"basic": "parent-basic-2"
}
]
现在我对接口投影的问题是,它不仅会加载期望的属性,还加载所有属性,但Jackson只会序列化接口提供的那些属性,因为它使用类/接口定义。
父类已加载:(SQL日志;请参见第4行,详细信息已加载)。
select
parent0_.id as id1_1_,
parent0_.basic as basic2_1_,
parent0_.detail as detail3_1_
from
parent parent0_
另外,界面投影似乎非常慢(参见这个Stackoverflow问题),而且我仍然需要解包子元素,因为它们以 [{id:1},{id:2}] 的形式给出,但我真正需要的是 [1,2]。我知道可以使用 @JsonIdentityReference(alwaysAsId = true)
来解决这个问题,但这只是一个变通办法。
此外,我有点困惑为什么数据要通过n+1个查询加载——1个查询用于父级,另外n个查询(其中n是父级数量)用于每个父级的子项:
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
我尝试了
@OneToMany(fetch=FetchType.LAZY)
和 @Fetch(FetchType.JOINED)
,但它们都产生了与上述相同的结果。所以主要问题是:有没有办法在Spring Boot中为嵌套实体实现投影,以便只加载必要的数据并尽可能少地查询次数,并且在最好的情况下我可以调整它,以便不必加载List children而是只加载List childIds(也许通过一个Jpa查询,将连接行按parentid分组,并从Child中提取所需的数据)。
我正在使用Hibernate和内存数据库。
感谢任何答案或提示!
编辑:为了澄清:我不是在寻找一种将数据序列化为所需格式的方法 - 我已经能够做到这一点。重点是仅从数据库加载必要的信息。