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

Question

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"

it is possible. why not? what is the problem you face off? you can either mock, or for integration test, you can use h2, testcontainers..., so you can test your query on real db. — zlaval
I am using database for integration testing. I have edited the question with code and an error message. Can you please take a look? — Guraraj
missing FROM-clause entry for table "h1" Did you try to run the query from some sql manager? — zlaval

Aleksandr Tarasov Aleksandr Tarasov · Accepted Answer · 2020-06-24T15:59:16

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.

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

1 Answers