查询dsl将@one上的重复记录返回到多个关联（leftJoin vs leftJoin.fetch vs fetchAll） [英] Query dsl returning duplicate records on @one to many association(leftJoin vs leftJoin.fetch vs fetchAll)

问题描述

这是我的情景：我有个人实体，如下图所示。

  @Entity 
 public class Person {
 @OneToMany（cascade = CascadeType.ALL，fetch = FetchType .LAZY）
私人设置< PhoneNumber> phoneNumbers = new HashSet<>（0）; 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（name =AGENCY_ID）
私人代理机构; 
  

当我查询人员时，我无法检索正确的数据。
我遇到的问题：

  1。重复记录。 
 2.没有机构没有回来的人。 
 3.表现不佳
  

这是我试过的，看到上面问题的组合

1. query.from（qPerson）.leftJoin（qPerson.phoneNumbers，telecommNumber）.leftJoin（qPerson.agency，qAgency ）;

我有问题1：显而易见（在一对多关系中）和这可以通过使用distinct（）在直接休眠中解决。我在queryDsl中尝试了截然不同的功能。

2. 查询.from（qPerson）.leftJoin（qPerson.phoneNumbers，telecommNumber）.fetch（）。leftJoin（qPerson.agency，qAgency）.fetch（）;
   在这种情况下我有问题3 ：正确返回结果，但性能非常差（笛卡尔积问题，我猜）。
   

3. query.from（qPerson）。 fetchAll（）;

在这种情况下，我有问题2： ，但是当我尝试对代理字段进行排序时，例如，没有代理人返回人员。但是如果我不在查询中添加以下内容，则返回该人员。

  query.orderBy（person.agency.agencyIdentifierDescription.asc（） ）; 
  

我试图找到解决上述三个问题的解决方案。感谢您的帮助。

解决方案

好的，您应该如下定义您的实体：

在JPA中， ManyToOne 关系总是（几乎总是）需要定义 OneToMany 关系， ManyToOne 总是定义外键（ JoinColumn ）和 OneToMany 必须使用 mappedBy 来定义它的逆 ManyToOne 。

来自Wiki：

ManyToOne

OneToMany

例子：

  public class Person {
 
 @ID 
私人整数ID; 
 
 @OneToMany（mappedBy =person）
 private Set< PhoneNumber> = phoneNumbers; 
 
 @ManyToOne 
 @JoinTable（name =agency_person，joinColumns = {@ JoinColumn（name =person_id，referencedColumnName =id）}，inverseJoinColumns = {@ JoinColumn（name =agency_id，referencedColumnName =id）}）
私人代理机构; 
 
 // Getters& Setters 
} 
 
 // ---------------------------------- ----------------- 
 
 public class PhoneNumber {
 
 @ID 
 private Integer id; 
 
 @ManyToOne 
 @JoinTable（name =phonenumber_person，joinColumns = {@ JoinColumn（name =phone_id，referencedColumnName =id）}，inverseJoinColumns = {@ JoinColumn（name =person_id，referencedColumnName =id）}）
 private person person; 
 
 // Getters& Setters 
} 
 
 // ---------------------------------- ----------------- 
 
公共类代理{
 
 @ID 
私有整数id; 
 
 @OneToMany（mappedBy =agency）
 private Set< Person>者; 
 // Getters& Setters 
} 
  

Here is my scenario: i have person entity which looks like below.

@Entity     
    public class Person{
     @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
        private Set<PhoneNumber> phoneNumbers = new HashSet<>(0);
     @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "AGENCY_ID")
        private Agency agency;

I am unable to retrieve correct data,when i query for persons. Problems i have :

1. duplicate records.
2. person with no agency not returning .
3. Bad performance

Here is what i tried, and see combination of above problems

1. query.from(qPerson).leftJoin(qPerson.phoneNumbers, telecommNumber).leftJoin(qPerson.agency,qAgency);

I have problem 1: which is obvious(in one-to-many relationship) and this can be solved in direct hibernate by using distinct(). I tried distinct in queryDsl and that doesnt seem to work well.

2. query.from(qPerson).leftJoin(qPerson.phoneNumbers, telecommNumber).fetch().leftJoin(qPerson.agency,qAgency).fetch(); I have problem 3 in this case: returns results correctly but performance is really bad.(Cartesian product problem, i guess).

3. query.from(qPerson).fetchAll();

I have problem 2 in this case :This one performs well, but doesnt return person without agency when i try to sort on agency field for example. But returns that person if i dont add below to the query.

 query.orderBy(person.agency.agencyIdentifierDescription.asc());

I am trying to arrive at a solution that solves above three problems. Thanks for your help.

解决方案

Well, you should define your entities as following:

"In JPA a ManyToOne relationship is always (well almost always) required to define a OneToMany relationship, the ManyToOne always defines the foreign key (JoinColumn) and the OneToMany must use a mappedBy to define its inverse ManyToOne."

from Wiki:

ManyToOne

OneToMany

example:

public class Person {

    @ID
    private Integer id;

    @OneToMany(mappedBy = "person")
    private Set<PhoneNumber> = phoneNumbers;

    @ManyToOne
    @JoinTable(name="agency_person", joinColumns={@JoinColumn(name="person_id", referencedColumnName="id")}, inverseJoinColumns={@JoinColumn(name="agency_id", referencedColumnName="id")})
    private Agency agency;

    //Getters & Setters
}

//---------------------------------------------------

public class PhoneNumber {

    @ID
    private Integer id;

    @ManyToOne
    @JoinTable(name="phonenumber_person", joinColumns={@JoinColumn(name="phone_id", referencedColumnName="id")}, inverseJoinColumns={@JoinColumn(name="person_id", referencedColumnName="id")})
    private Person person;

    //Getters & Setters
}

//---------------------------------------------------

public class Agency {

    @ID
    private Integer id;

    @OneToMany(mappedBy = "agency")
    private Set<Person> persons;
    //Getters & Setters
}

这篇关于查询dsl将@one上的重复记录返回到多个关联（leftJoin vs leftJoin.fetch vs fetchAll）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！