首页 文章

Hibernate：Spring jpa存储库中按字段排序的额外交叉连接

提问于
浏览 次
1

当添加一些嵌套的字段对象时，我有一个Spring JPARepository的问题 .

在控制器中's called a method with param Pageable, contained sort == ' parent.shortDescription ASC and shortDescription ASC ' . 在Pageable之后转到 mapObjectDao.findByType(...) ，hibernate构造错误的sql-query（带交叉连接） .

//控制器

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

// hibernate 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;
}

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

java hibernate jpa spring-data

1 回答

  • 3

    解决了！

    @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`
    回复于

相关问题