sql语句转hibernate hql语句

Question

sql语句为select * from (select location_id,region_code,county_name,count(region_code) as countKKou from mon_location where longitude !=0.0 and region_code in ('370901') GROUP BY region_code) as teb LEFT JOIN mon_region as r on teb.region_code = r.code ;
求hql语句
其中sql
select location_id,region_code,county_name,count(region_code) as countKKou from mon_location where longitude !=0.0 and region_code in ('370901') GROUP BY region_code

已经转化为select new com.yisa.map.domain.LocationLocation(m.location_id,count(m.region_code) as countKKou,m.region_code,m.county_name) from LocationVo as m where m.longitude !=0.0 and m.region_code in ('370901') GROUP BY m.region_code

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

你可以使用Hibernate的map和join方法来将SQL查询转换为HQL查询。以下是一个例子：

@MappedStatement(
        statement = "mon_location",
        resultSetMapping = {
            @Result(property="location", column="location_id"),
            @Result(property="countRegionCode", column="region_code"),
            @Result(property="countRegionCount", column="count(region_code)")
        }
)
public class MonLocation {

    @Query(value = "SELECT new com.yisa.map.domain.LocationLocation(m.location_id,count(m.region_code) as countKKou,m.region_code,m.county_name) FROM LocationVo AS m WHERE m.longitude != 0.0 AND m.region_code IN ('370901') GROUP BY m.region_code", nativeQuery = true)
    public List<LocationLocation> getLocationByRegionCode(@Param("longitude") double longitude, @Param("regionCode") String regionCode);

    @Join("getLocationByRegionCode")
    private Join<MonLocation, MonRegion> join;
}

在这个例子中，我们首先创建了一个名为mon_location的映射，它返回一个包含三个属性的新对象：locationId、countRegionCode和countRegionCount。然后我们在getLocationByRegionCode方法中调用了这个映射，并传入了longitude和regionCode作为参数。

请注意，你需要根据你的数据库表结构调整mon_location和MonRegion类以及它们的字段名称。