使用OneToMany的Spring Data Projection返回太多结果 [英] Spring Data Projection with OneToMany returns too many results

问题描述

我有一个具有onetomany关系的JPA实体（Person）（ContactInfo）。

I have a JPA entity (Person) with onetomany relation (ContactInfo).

@Entity
public class Person {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private String lastname;
    private String sshKey;
    @OneToMany(mappedBy = "personId")
    private List<ContactInfo> contactInfoList;
}

@Entity
public class ContactInfo {
    @Id
    @GeneratedValue
    private Integer id;
    private Integer personId;
    private String description;
}

我已经定义了一个投影界面，其中包含了如上所述的onetomany关系这里。

I've defined a projection interface that includes this onetomany relation as described here.

public interface PersonProjection {
    Integer getId();
    String getName();
    String getLastname();
    List<ContactInfo> getContactInfoList();
}

public interface PersonRepository extends JpaRepository<Person,Integer> {
    List<PersonProjection> findAllProjectedBy();
}

当我使用 findAllProjectedBy 检索数据时，结果包含太多行。看起来返回的数据是类似于以下内容的连接查询的结果：

When I retrieve the data with findAllProjectedBy the result contains too many rows. It looks like the returned data is the result of a join query similar to:

select p.id, p.name, p.lastname, ci.id, ci.person_id, ci.description 
from person p 
join contact_info ci on ci.person_id = p.id

例如，此数据集：

insert into person (id,name,lastname,ssh_key) values (1,'John','Wayne','SSH:KEY');

insert into contact_info (id, person_id, description) values (1,1,'[phone]'), (2,1,'[e-mail]');

findAllProjectedBy 方法返回2个对象（错误地）和标准 findAll 返回1个对象（正确）。

The findAllProjectedBy method returns 2 objects (incorrectly) and the standard findAll returns 1 object (correctly).

完整项目是这里

我做了一些调试，看来问题出在jpa查询上。
findAll方法使用此查询：

I've done some debugging and it seems that the problem is with the jpa query. The findAll method uses this query:

select generatedAlias0 from Person as generatedAlias0

findAllProjectedBy使用此查询：

The findAllProjectedBy uses this query:

select contactInfoList, generatedAlias0.id, generatedAlias0.name, generatedAlias0.lastname from Person as generatedAlias0 
left join generatedAlias0.contactInfoList as contactInfoList

有谁知道如何修复这种无效行为？

Does anyone know how to fix this invalid behaviour?

推荐答案

这个问题的快速解决方法是此处描述：
https://jira.spring.io/browse/DATAJPA-1173

A quick fix for this problem is described here: https://jira.spring.io/browse/DATAJPA-1173

您需要使用@Value注释描述其中一个投影属性。对于上面发布的示例，您最终会得到：

You need to describe one of the single projection attributes with a @Value annotation. For the example posted above you will end up with:

import java.util.List;
import org.springframework.beans.factory.annotation.Value;

public interface PersonProjection {
    @Value("#{target.id}")
    Integer getId();
    String getName();
    String getLastname();
    List<ContactInfo> getContactInfoList();
}

这篇关于使用OneToMany的Spring Data Projection返回太多结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！