java - Hibernate:在Spring JPA存储库中按字段顺序进行额外的交叉联接

添加某种嵌套字段对象时，Spring JPARepository存在问题。

在控制器中，它被称为参数为Pageable的方法，其中包含sort =='parent.shortDescription ASC and shortDescription ASC'。 Pageable转到mapObjectDao.findByType(...)之后，休眠构造错误的sql查询（使用交叉连接）。

//控制器

public Page<MeteoInfoDto> list(MeteoInfoFilter filter, Pageable pageable) {
    mapObjectDao.findByType(MapObjectType.Meteo, pageable);
}

//存储库

@Query(value = "select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

// HQL

select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type order by m.parent.shortDescription asc, m.shortDescription asc

//休眠SQL输出

select
        mapobject0_.dbid as dbid1_27_0_,
        ....
        mapobject4_.parent_org_id as parent_15_27_4_
    from
        map_object mapobject0_
    left outer join
        map_object mapobject1_
            on mapobject0_.parent_id=mapobject1_.dbid
    left outer join
        map_object mapobject2_
            on mapobject0_.road_id=mapobject2_.dbid
    left outer join
        map_object mapobject3_
            on mapobject0_.parent_region_id=mapobject3_.dbid
    left outer join
        map_object mapobject4_
            on mapobject0_.parent_org_id=mapobject4_.dbid

    cross join map_object mapobject5_

    where
        mapobject0_.parent_id=mapobject5_.dbid
        and mapobject0_.type=?
    order by
        mapobject5_.short_description asc,
        mapobject0_.short_description asc limit ?

//模型

public class MapObject extends DomainObject {

    private UUID id;
    private MapObjectType type;
    ...
    private MapObject parent;
    private MapObject parentRegion;
    private MapObject parentOrganization;
    private MapObject road;
    ...
    private Set<MapObject> children;
    private Set<MapObject> childrenRegion;
    private Set<MapObject> childrenOrganization;
}

为什么要为排序cross join map_object mapobject5_添加parent.shortDescription？

最佳答案

解决了！

    @Query(value = "select m from MapObject as m left join m.parent as parent left join fetch m.parent left join fetch m.road left join fetch m.parentRegion left join fetch m.parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

交叉连接已删除，方法是添加带有别名且不提取的左连接。

 select
        mapobject0_.dbid as dbid1_27_0_,
        mapobject2_.dbid as dbid1_27_1_,
        mapobject3_.dbid as dbid1_27_2_,
        mapobject4_.dbid as dbid1_27_3_,
        mapobject5_.dbid as dbid1_27_4_,
        mapobject0_.id as id2_27_0_,

        mapobject0_.short_description as short_de3_27_0_,
        ...
        mapobject0_.parent_org_id as parent_15_27_0_,
        mapobject2_.id as id2_27_1_,
        ...
        mapobject2_.parent_org_id as parent_15_27_1_,
        mapobject3_.id as id2_27_2_,
        ....
        mapobject3_.parent_region_id as parent_14_27_2_,
        mapobject4_.short_description as short_de3_27_3_,
     ...
        mapobject4_.parent_org_id as parent_15_27_3_,
        mapobject5_.id as id2_27_4_,
        mapobject5_.short_description as short_de3_27_4_,
       ...
        mapobject5_.parent_org_id as parent_15_27_4_
    from
        map_object mapobject0_
    left outer join
        map_object mapobject1_
            on mapobject0_.parent_id=mapobject1_.dbid
    left outer join
        map_object mapobject2_
            on mapobject0_.parent_id=mapobject2_.dbid
    left outer join
        map_object mapobject3_
            on mapobject0_.road_id=mapobject3_.dbid
    left outer join
        map_object mapobject4_
            on mapobject0_.parent_region_id=mapobject4_.dbid
    left outer join
        map_object mapobject5_
            on mapobject0_.parent_org_id=mapobject5_.dbid
    where
        mapobject0_.type=?
    order by
        mapobject0_.short_description asc,
        mapobject1_.short_description asc limit ?`enter code here`