在使用 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 " +
" LEFT JOIN table2 prr prr.pile_run_code = t.pile_code " +
"WHERE " +
" prr.pile_state in (:value1) " +
"GROUP BY " +
" s.id " +
"ORDER BY " +
" s.id) as s")
Page<Entity数据库对应实体> sql(@Param("value1")Integer value1,,Pageable pageable);
二.自定义sql,对应自定义实体类
@Query(value = "SELECT t.value as value" +
" FROM " +
" ( SELECT * FROM table" +
"WHERE del = 0 " +
" LEFT JOIN table1 pp ON pp.pile_id = t.pile_id " +
" LEFT JOIN table2 prr ON prr.pile_run_code = t.pile_code " +
" WHERE " +
" prr.value IN ( :value ) ", nativeQuery = true)
List<Map<String,Object>> findAllByStationIdAndStatus(@Param("value ") String value );
List<VO实体类> vos =JSON.parseArray(JSON.toJSONString(list),VO实体类.class);
在使用 JpaRepository 自定义sql,自定义实体类时,经常会遇到ShardingResultSet.getString null问题,有时可以通过默认值解决,但不能适用所有情况。针对这种情况,建议使用Map<String,Object> 接收查询结果,在通过json转换为对应实体类. 目前这个问题我一直无法解决,所以使用Map接收在通过json转换绕过这个异常.