title: Is it possible for unit test cases for Spring boot data jpa @Query native queries?
body: <p>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?</p>
  <p>Code:</p>
  <pre><code>String searchQuery = &quot;SELECT dh1.* FROM device_hist AS dh1 JOIN (&quot; +
          &quot;SELECT rank() OVER (PARTITION BY dh.device_nm, dh.lst_log_in_user_id ORDER BY lst_chkin_ts DESC), &quot; +
          &quot;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 &quot; +
          &quot;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 &quot; +
          &quot;dh1.lst_chkin_ts &gt;= :lst_chkin_ts &quot;;
  
  String searchCountQuery = &quot;SELECT count(*) FROM device_hist AS dh1 JOIN (&quot; +
          &quot;SELECT rank() OVER (PARTITION BY dh.device_nm, dh.lst_log_in_user_id ORDER BY lst_chkin_ts DESC), &quot; +
          &quot;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 &quot; +
          &quot;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 &quot; +
          &quot;AND dh1.lst_chkin_ts &gt;= :lst_chkin_ts &quot;;
  
  @Query(value = searchQuery + &quot;ORDER BY dh1.device_nm, dh1.lst_chkin_ts DESC&quot;,
          countQuery = searchCountQuery,
          nativeQuery = true)
  Page&lt;DeviceHistory&gt; findAllLatestDeviceHistoryBylastCheckInTimeStamp(
          @Param(&quot;lst_chkin_ts&quot;) Date lastCheckInTimeStamp, Pageable pageable);
  </code></pre>
  <p>Testcase:</p>
  <pre><code>@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&lt;DeviceHistory&gt; devices = deviceHistoryRepository.findAllLatestDeviceHistoryBylastCheckInTimeStamp(timeStamp, pageable);
  
      assertNotNull(devices.getContent());
  }
  </code></pre>
  <p>Error:</p>
  <p>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)
  ...</p>
  <p>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 &quot;h1&quot;</p>