使用 jpa自定义sql分页，自定义实体类等

原创

已于 2024-05-14 10:16:37 修改 · 698 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #spring cloud #hibernate

于 2024-05-11 14:07:46 首次发布

在使用 JpaRepository 自定义sql，自定义实体类时，经常会遇到ShardingResultSet.getString null问题，有时可以通过默认值解决，但不能适用所有情况。针对这种情况，建议使用Map<String,Object> 接收查询结果，在通过json转换为对应实体类

一.自定义sql，对应表Entity
下面的代码可以同时实现自定义sql分页

@Query(value = " SELECT  " +
        "  s.*   " +
        "FROM  " +
        "  ( SELECT * FROM table WHERE del = 0 AND owner_id =  (:ownerId) AND provider_id = (:providerId) AND phase = 3 ) p  " +
        "  LEFT JOIN table1  s ON p.station_id = s.id  " +
        "  LEFT JOIN table2  prr ON prr.pile_id = p.pile_id   " +
        "WHERE  " +
        "  prr.pile_state in (:status)   " +
        "GROUP BY  " +
        "  s.id   " +
        "ORDER BY  " +
        "  s.id", nativeQuery = true,

        countQuery="SELECT count(*) FROM ( SELECT  " +
                "  s.*   " +
                "FROM  " +
                "  ( SELECT * FROM table WHERE del = 0 AND value = (:value1) ) p  " +
                "  LEFT JOIN table1  s ON p.station_id = s.id  " +
                "