【Spring Data JPA】根据动态查询条件、根据经纬度距离查询

最新推荐文章于 2024-04-12 15:27:26 发布

Tellsea

最新推荐文章于 2024-04-12 15:27:26 发布

阅读量573

点赞数 9

分类专栏： Spring Data JPA 文章标签：数据库 java sql

本文链接：https://blog.csdn.net/qq_38762237/article/details/135481044

版权

Spring Data JPA 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

根据动态条件

    @Query(nativeQuery = true, value = "select A.* " +
            "from epidemic_case_info A where A.delete_flag = '0' " +
            "and (case " +
            "when right(:distCode, 4) = '0000' then A.dist_code like concat(substring(:distCode, 1, 2 ), '%')  " +
            "when right(:distCode, 2) = '00' then A.dist_code like concat(substring(:distCode, 1, 4 ), '%') " +
            "else A.dist_code = :distCode end) " +
            "and A.report_time >= :startDate and A.report_time <= :endDate " +
            "and (case when :name is not null and :name != '' then A.name = :name else 1 = 1 end) ")
    List<EpidemicCaseInfoPO> caseArea(@Param("distCode") String distCode, @Param("startDate") String startDate,
                                      @Param("endDate") String endDate, @Param("name") String name);

根据经纬度距离查询

    @Query(nativeQuery = true, value = "" +
            "select * from (select round(ST_Distance" +
            "(ST_Transform(ST_SetSRID(ST_MakePoint(:longitude,:latitude),4326),3857)" +
            ", ST_Transform(ST_SetSRID(ST_MakePoint(longitude\\:\\:float,latitude\\:\\:float),4326),3857))\\:\\:numeric, 2) as distance" +
            ",t.* from task_emer.task_storehouse_info t " +
            "where t.longitude is not null and t.longitude != '' and t.latitude is not null and t.latitude != ''" +
            " AND case when :storehouseName is not null and :storehouseName != '' then t.name like concat('%', :storehouseName, '%') else 1 = 1 end " +
            " AND case when :type is not null and :type != '' then t.type = :type else 1 = 1 end " +
            ") x where x.distance < :range " +
            "ORDER BY x.distance ")
    List<TaskStorehouseInfoPO> findTaskStorehouseByRange(@Param("longitude") float longitude, @Param("latitude") float latitude,
                                                         @Param("range") float range, @Param("storehouseName") String storehouseName,
                                                         @Param("type") String type);