更新时间:2022-10-20 10:09:12
这将始终获取孩子,但可以给你你想要的结果.
公共接口 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.
@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
// 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.
// 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 *** 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);
}
}