Is it possible for unit test cases for Spring boot data jpa @Query native queries?

I have a spring boot application with JPA configured. While querying the database i am running native sql query with the help of spring data @query annotation. Now i am planning to write test cases using spring boot test framework. Is it possible to write tests or not?

Code:

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);

Testcase:

@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());
}

Error:

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is 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) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy138.findAllLatestDeviceHistoryBylastCheckInTimeStamp(Unknown Source) at com.aexp.de.crypto.wde.server.repository.DeviceHistoryRepositoryTest.testfindAllLatestDeviceHistoryBylastCheckInTimeStamp(DeviceHistoryRepositoryTest.java:158) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675) at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) ...

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ... 96 more Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "h1"

I think you have a problem in

String searchCountQuery 

Exception told you that you have problem with something called h1 table

ERROR: missing FROM-clause entry for table "h1"

As far as I can see this string contains 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 ". (should be dh1 I guess)

Usually if you are facing SQLGrammarException: could not extract ResultSet then its probably a problem with your SQL. Also the rest of a stack trace usally helps to clarify the issue.