spring - Spring引导数据jpa @Query本机查询的单元测试用例是否可能?
问题描述
我有一个配置了 JPA 的 Spring Boot 应用程序。在查询数据库时,我在 spring data @query 注释的帮助下运行本机 sql 查询。现在我打算使用 Spring Boot 测试框架编写测试用例。是否可以编写测试?
代码:
String searchQuery = "SELECT dh1.* FROM device_hist AS dh1 JOIN (" +
"SELECT rank() OVER (PARTITION BY dh.device_nm, dh.lst_log_in_user_id ORDER BY lst_chkin_ts DESC), " +
"dh.device_nm, dh.lst_log_in_user_id, dh.lst_chkin_ts FROM device_hist dh) AS T ON dh1.device_nm = T.device_nm " +
"AND dh1.lst_log_in_user_id = T.lst_log_in_user_id AND dh1.lst_chkin_ts = T.lst_chkin_ts WHERE T.rank = 1 AND " +
"dh1.lst_chkin_ts >= :lst_chkin_ts ";
String searchCountQuery = "SELECT count(*) FROM device_hist AS dh1 JOIN (" +
"SELECT rank() OVER (PARTITION BY dh.device_nm, dh.lst_log_in_user_id ORDER BY lst_chkin_ts DESC), " +
"dh.device_nm, dh.lst_log_in_user_id, dh.lst_chkin_ts FROM device_hist dh) AS T on h1.device_nm = T.device_nm " +
"AND dh1.lst_log_in_user_id = T.lst_log_in_user_id AND dh1.lst_chkin_ts = T.lst_chkin_ts WHERE T.rank = 1 " +
"AND dh1.lst_chkin_ts >= :lst_chkin_ts ";
@Query(value = searchQuery + "ORDER BY dh1.device_nm, dh1.lst_chkin_ts DESC",
countQuery = searchCountQuery,
nativeQuery = true)
Page<DeviceHistory> findAllLatestDeviceHistoryBylastCheckInTimeStamp(
@Param("lst_chkin_ts") Date lastCheckInTimeStamp, Pageable pageable);
测试用例:
@Test
void testfindAllLatestDeviceHistoryBylastCheckInTimeStamp() {
LocalDate localDateTime = LocalDate.now();
ZoneId defaultZoneId = ZoneId.systemDefault();
Instant instant = localDateTime.minusDays(10).atStartOfDay(defaultZoneId).toInstant();
Date timeStamp = Date.from(instant);
Page<DeviceHistory> devices = deviceHistoryRepository.findAllLatestDeviceHistoryBylastCheckInTimeStamp(timeStamp, pageable);
assertNotNull(devices.getContent());
}
错误:
无法提取结果集;SQL [不适用];嵌套异常是 org.hibernate.exception.SQLGrammarException: could not extract ResultSet org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [不适用];嵌套异常是 org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible (HibernateJpaDialect.java:255) 位于 org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) 的 org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)。
在 org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) 在 org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) 在 org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:186) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ... 96 更多原因:org.postgresql.util.PSQLException:错误:缺少 FROM 子句条目表“h1”
解决方案
我觉得你有问题
String searchCountQuery
异常告诉你你有一个叫做h1
table的问题
ERROR: missing FROM-clause entry for table "h1"
据我所见,该字符串包含h1
:“dh.device_nm, dh.lst_log_in_user_id, dh.lst_chkin_ts FROM device_hist dh) AS T on h1 .device_nm = T.device_nm ”。(我猜应该是dh1 )
通常,如果您面临,SQLGrammarException: could not extract ResultSet
那么它可能是您的 SQL 的问题。此外,堆栈跟踪的其余部分通常有助于澄清问题。
推荐阅读
- excel - 通过代码更改表单中的 textbox.name
- javascript - 从现有值重新排列 JSON 值
- reactjs - 在 Context.Consumer 创建的 useEffect 清理函数中取消所有订阅
- java - Java中对象与多态的交叉
- python - 是否有一个 linter 可以为 eslint 之类的 lint 故障提供自动修复?
- emacs - ess-rdired:自定义键绑定
- macos - Apache 站点在 Safari 中加载,但在 Chrome 或 Firefox 中不加载
- javascript - 如何在 html td id 中使用 javascript 变量
- excel - “列表框末尾的空白项”
- react-admin - 我该如何配置
忽略 location.search 参数?