Spring Data Jpa 返回完整对象时可以直接使用Entity实体类接收返回值,但是在开发过程中,可能不需要全部字段或者对字段重命名时,Entity对象就无法正常映射。
SELECT DATE_FORMAT(create_date, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS count from origin_image GROUP BY time ORDER BY time
这是我想查询的SQL
刚开始想到的是建一个实体类,属性名和字段重命名后保持一致。
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TimeCountDto {
private String time;
private Integer count;
}
@Query(value = "SELECT DATE_FORMAT(create_date, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS count from origin_image GROUP BY time ORDER BY time", nativeQuery = true)
List<TimeCountDto> findCountByTime();
测试后发现并不能直接映射正常接收。
看了一些博客之后好多都是直接在SQL中利用有参构造创建对象,但在我这不行,直接报SQL异常,把nativeQuery设为false,编译都不通过 = = ||
后来想到可以用Map接收,于是改成下面这种结构。
@Query(value = "SELECT DATE_FORMAT(create_date, ?1) AS time, COUNT(*) AS count from origin_image GROUP BY time ORDER BY time", nativeQuery = true)
List<Map<String, Object>> findCountByTime(String time);
结果可以正常返回
[{“time”:“2020-07-13 08:00:00”,“count”:2},{“count”:1,“time”:“2020-07-13 10:00:00”},{“time”:“2020-07-13 11:00:00”,“count”:1},{“time”:“2020-07-13 13:00:00”,“count”:1},{“count”:2,“time”:“2020-07-13 17:00:00”},{“count”:1,“time”:“2020-07-13 20:00:00”}]
感觉这种方式比自定义实体类要简单,而且避免了仅仅为了接收对象而建太多的实体类。
希望能帮到你。