Spring boot data jpa@Query本机查询的单元测试用例是否可能？

Question

我有一个配置了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());
}

错误：

在org.springframework.transaction.interceptor.transactionaspectsupport.java:366)在

Answer 1

我想你在

String searchCountQuery 

异常告诉您，您有一个名为h1表的问题

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的一个问题。此外，堆栈跟踪的其余部分通常有助于澄清问题。