java - Springboot Hibernate & JPA and Native Queries (Group by PostgreSQL date_trunc) 可分页问题
问题描述
我在 SpringBoot 1.4.7 中遇到了这个 JPA 查询。问题是我在尝试处理本机查询时收到此错误。我已经测试了 SQL 查询并且都是有效的。
org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate named parameter [signals], expecting one of [toDate, from, device]; nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [signals], expecting one of [toDate, from, device]
查询定义为:
@Query(value = "select device, signal, avg(avg), min(min), max(max), date_trunc('day', timestamp) as timestamp from devicestatistics d "
+ "where timestamp between :from and :toDate and device like :device and signal IN (:signals) "
+ "group by d.device ,d.signal ,date_trunc('day' , d.timestamp ) order by device,date_trunc('day' ,d.timestamp) Desc \\n-- #pageable\\n",
countQuery = "SELECT count(1) as NumRows from "
+ "(select device, signal, avg(avg), min(min), max(max), date_trunc('day', :from) as timestamp from devicestatistics d "
+ "where 'time' between :from and :toDate and device like :device "
+ "group by device ,signal ,date_trunc('day' ,d.time ) order by device , date_trunc('day' ,d.time ), signal x"
,nativeQuery = true)
Page<DeviceStatistics> getDataDay(@Param("from") Date from, @Param("toDate") Date toDate, @Param("device") String device, @Param("signals") Collection<String> signals,Pageable page);
该表如下(我知道名称选择不明智)。
CREATE TABLE public.devicestatistics (
device text NOT NULL,
"TIMESTAMP" timestamp NOT NULL,
signal text NOT NULL,
avg float8 NULL,
max int8 NULL,
min int8 NULL,
CONSTRAINT devicestatistics_pkey PRIMARY KEY (device, "TIMESTAMP", signal)
);
实体定义为:
@Getter
@Setter
@Entity
@Table(name = "DEVICESTATISTICS")
@IdClass(DeviceStatisticsID.class)
public class DeviceStatistics implements Serializable {
@Id
@Column(name = "DEVICE")
private String device;
@Id
@Column(name = "SIGNAL")
private String signal;
@Column(name = "AVG")
private double average;
@Column(name = "MIN")
private double minimun;
@Column(name = "MAX")
private double maximun;
@Id
@Column(name = "TIMESTAMP")
@Temporal(TemporalType.TIMESTAMP)
private Date time;
有什么提示吗?
这个问题类似于这个问题,但我希望使用本机查询功能。
谢谢!
编辑:
如果我停止使用 Pageable 它可以工作,但是我无法使用 Pageable 使其工作......(我一直在 stackoverflow 中搜索解决方案,但没有工作)
@Query(value = "select device, signal, avg(avg), min(min), max(max), date_trunc('day', timestamp) as timestamp from devicestatistics d "
+ "where timestamp between :from and :toDate and device like :device and signal IN (:signals) "
+ "group by d.device ,d.signal ,date_trunc('day' , d.timestamp ) order by device,date_trunc('day' ,d.timestamp) Desc",
countQuery = "SELECT count(1) as NumRows from "
+ "(select device, signal, avg(avg), min(min), max(max), date_trunc('day', :from) as timestamp from devicestatistics d "
+ "where 'time' between :from and :toDate and device like :device "
+ "group by device ,signal ,date_trunc('day' ,d.time ) order by device , date_trunc('day' ,d.time ), signal x"
,nativeQuery = true)
List<DeviceStatistics> getDataDay(@Param("from") Date from, @Param("toDate") Date toDate, @Param("device") String device, @Param("signals") Collection<String> signals);
解决方案
最后我得到了它。如果有人需要通过分组进行这种原生查询,这可能会有所帮助。
@Query(value = "select device, signal, avg(avg), min(min), max(max), date_trunc('day', timestamp) as timestamp from devicestatistics d "
+ "where timestamp between :from and :toDate and device like :device and signal IN (:signals) "
+ "group by d.device ,d.signal ,date_trunc('day' , d.timestamp ) order by device,date_trunc('day' ,d.timestamp) Desc --#pageable\n" ,
countQuery = "SELECT count(1) as NumRows from "
+ "(select device, signal, avg(avg), min(min), max(max), date_trunc('day', timestamp) as timestamp from devicestatistics d "
+ "where timestamp between :from and :toDate and device like :device and signal IN (:signals) "
+ "group by device ,signal ,date_trunc('day' ,d.timestamp ) order by device , date_trunc('day' ,d.timestamp ) Desc) x"
,nativeQuery = true)
Page<DeviceStatistics> getDataDayIn(@Param("from") Date from, @Param("toDate") Date toDate, @Param("device") String device, @Param("signals") Collection<String> signals, Pageable page);
推荐阅读
- ibm-cloud - IBM Watson Assistant:尝试评估和比较多个上下文变量
- android - 是否可以在 Android TextView 或 Webview 中设置 div 类的样式?
- android - 结合使用 ScrollView Activity 和 adjustPan 时的空白
- c# - 如何将@RenderBody() 分成两部分
- common-lisp - Common Lisp 在宏中生成变量名
- reactjs - 如何测试使用反应上下文 API 的组件?
- javascript - 获取所有标题并递归创建树
- javascript - 使用 JavaScript,是否可以从传出的获取请求中捕获正文有效负载?
- node.js - Express 服务于 2 个不同的 Angular PWA
- java - 模型映射器中的延迟初始化异常