时间戳

首页 > 解决方案 > Sprind data JPA：多个OneToMany关系查询

hibernate - Sprind data JPA：多个OneToMany关系查询

问题描述

我无法弄清楚以下查询有什么问题（它不返回任何结果）：

public interface OrganizationRepository extends JpaRepository<Organization, Long> {
   // ...
   @Query("SELECT o FROM Organization o " +
            "JOIN o.establishments e " +
            "JOIN e.buildings b " +
            "WHERE b.id = ?1 ")
   Organization findOrganizationFromBuilding(Long buildingId);
   // ...
}

这些关系在相应的实体类中定义如下：

public class Organization extends AbstractEntity {
  // ...
  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  @JsonIgnore
  private Set<Establishment> establishments;
  // ...
}

public class Establishment extends AbstractEntity {
  // ...
  @ManyToMany(cascade =CascadeType.ALL)
  @JoinTable(
     name = "establishment_buildings",
     joinColumns = @JoinColumn(name = "establishment_id"),
     inverseJoinColumns = @JoinColumn(name = "buildings_id"))
  private Set<Building> buildings;

  @ManyToOne
  private Organization organization;
  // ...
}

public class Building extends AbstractEntity {
   // ...
   @ManyToMany(mappedBy = "buildings", cascade = CascadeType.ALL)
   private Set<Establishment> establishments;
   // ...
}

它应该按如下方式运行 RAW SQL 查询（返回唯一结果）并且可以工作：

SELECT o.* from organization o
inner join establishment e on o.id = e.organization_id
inner join establishment_buildings eb on e.id = eb.establishment_id
inner join building b on eb.buildings_id = b.id
where b.id = <some id>;

我在其中激活了 show-sql，application.properties这是生成的查询：

select organizati0_.id as id1_34_, ... other fields
 
 from organization organizati0_ 
 inner join organization_establishments establishm1_ on organizati0_.id=establishm1_.organization_id 
 inner join establishment establishm2_ on establishm1_.establishments_id=establishm2_.id 
 inner join establishment_buildings buildings3_ on establishm2_.id=buildings3_.establishment_id 
 inner join building building4_ on buildings3_.buildings_id=building4_.id where building4_.id=?

我不知道为什么INNER JOIN表上有一个额外的，-实体organization_establishments中没有声明关联......这个表确实存在于数据库中，但是看到它在没有任何声明或引用的情况下使用真的很奇怪实体类。Organizationorganization_establishents

更重要的是，我尝试将上述查询替换为原生查询，如下所示：

public interface OrganizationRepository extends JpaRepository<Organization, Long> {
    @Query(value = "SELECT " +
        "o.company_number as companyNumber " +
        "from organization o " +
        "inner join establishment e on o.id = e.organization_id " +
        "inner join establishment_buildings eb on e.id = eb.establishment_id " +
        "inner join building b on eb.buildings_id = b.id " +
        "where b.id = ?1",
        nativeQuery = true)
    OrganizationCompanyNumber findByBuildingId(Long buildingId);

   // ...
   interface OrganizationCompanyNumber {
      String getCompanyNumber();
   }
}

生成以下查询的原因：

SELECT o.company_number as companyNumber from organization o 
inner join establishment e on o.id = e.organization_id 
inner join establishment_buildings eb on e.id = eb.establishment_id 
inner join building b on eb.buildings_id = b.id where b.id = ?

但回来了NULL。尽管如此，在 PGAdmin 中运行相同的查询确实会返回一个结果。

那有什么问题？

标签： hibernatespring-data-jpa

解决方案

当您要使用双向 @OneToMany（组织 - 建立）时，您应该参考相反的一面mappedBy。

因此，尝试以这种方式更正您的映射：

@Entity
public class Organization extends AbstractEntity {
   // ...
   @OneToMany(cascade = CascadeType.ALL, mappedBy = "organization", orphanRemoval = true)
   @JsonIgnore
   private Set<Establishment> establishments;

   // 
}

@Entity
public class Establishment extends AbstractEntity {

   // ...

   @ManyToOne
   @JoinColumn(name = "organization_id")
   private Organization organization;

   // ...
}

推荐阅读