title: SqlResultSetMapping to POJO class from a NamedNativeQuery throwing 'could not locate appropriate constructor'
body: <p>I made a <code>@NamedNativeQuery</code> and attached it to an entity 'Doctor', on the same entity I attached a <code>@SqlResultSetMapping</code> which takes the columns of the query's result and maps them to a constructor of a specifically made POJO class. This query is also connected to a JPA method, which resides in the repository of the same entity.</p>
  
  <p>However I keep getting an error that the appropriate constructor could not be located, as if the <code>@SqlResultSetMapping</code> or the POJO constructors are not in sync. (stack trace is at the bottom)</p>
  
  <p>My entity, <code>@NamedNativeQuery</code> and <code>@SqlResultSetMapping</code>:</p>
  
  <p>I tried the query directly on the DB and it gave the expected result, so I am just writing the select clause</p>
  
  <pre><code>@Entity
  @NamedNativeQuery(
      name = 
          "Doctor.findFreeExaminationTimes", // name of the JPA method in entity's repository (definition below)
      query = 
          "SELECT on_date AS onDate, LAG(to_time, 1, '00:00') OVER mojWindow AS fromTime, from_time AS toTime " +
          "...",
          resultSetMapping = "freeTimesByDoctorId" // name of the mapping below
  )
  @SqlResultSetMapping(
      name = "freeTimesByDoctorId", // result set mapping name
      classes = @ConstructorResult(
          targetClass = DoctorAvailabilityResponse.class, // my POJO class (definition below)
          columns = { // order and types are the same as in the select clause above and the POJO constructor below
              @ColumnResult(name = "onDate", type = java.sql.Date.class),
              @ColumnResult(name = "fromTime", type = java.sql.Time.class),
              @ColumnResult(name = "toTime",type = java.sql.Time.class)
          }
      )
  )
  public class Doctor extends User {...}
  </code></pre>
  
  <p>The POJO class which I mention in the @ConstructorResult under 'targetClass' has a constructor with the exact order, number and type, of arguments, specified under 'columns'</p>
  
  <p>My POJO class which should be mapped to the query's result:</p>
  
  <pre><code>public class DoctorAvailabilityResponse {
  
      final private java.sql.Date onDate;
      final private java.sql.Time fromTime;
      final private java.sql.Time toTime;
  
      public DoctorAvailabilityResponse(java.sql.Date onDate, java.sql.Time fromTime, java.sql.Time toTime) {
          this.onDate = onDate;
          this.fromTime = fromTime;
          this.toTime = toTime;
      }
  
      // getters
  }
  </code></pre>
  
  <p>My repository:</p>
  
  <pre><code>@RepositoryRestResource
  public interface DoctorRepository extends UserRepository&lt;Doctor&gt; {
  
      // JPA method mapped to the named native query above
      List&lt;DoctorAvailabilityResponse&gt; findFreeExaminationTimes(@Param("doctorId") Long doctorId);
  }
  </code></pre>
  
  <p>However when testing this JPA method I get an exception with the message 'could not locate appropriate constructor'.</p>
  
  <p>My test:</p>
  
  <pre><code>@SpringBootTest
  public class DoctorTests {
  
      @Autowired
      private DoctorRepository doctorRepository;
  
      private final Logger LOGGER = LoggerFactory.getLogger(this.getClass());
  
      @Test
      public void shouldReturnDoctorAvailability() {
  
          // Exception thrown here
          List&lt;DoctorAvailabilityResponse&gt; freeTimes = doctorRepository.findFreeExaminationTimes(4L);
  
          LOGGER.info(freeTimes.toString());
      }
  }
  </code></pre>
  
  <p>I cannot understand why is this happening. Is there a way to manually map this result set to the POJO while maintaining the JPA repository method?</p>
  
  <p>Stack trace:</p>
  
  <pre><code>org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate appropriate constructor on class :     com.example.isaproj.isa_projekat_2019.Model.DTO.DoctorAvailabilityResponse; nested exception is java.lang.IllegalArgumentException: Could not locate appropriate constructor on class : com.example.isaproj.isa_projekat_2019.Model.DTO.DoctorAvailabilityResponse
      at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:256)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
  ...
  ...
  
  Caused by: java.lang.IllegalArgumentException: Could not locate appropriate constructor on class : com.example.isaproj.isa_projekat_2019.Model.DTO.DoctorAvailabilityResponse
      at org.hibernate.loader.custom.ConstructorResultColumnProcessor.resolveConstructor(ConstructorResultColumnProcessor.java:92)
      at org.hibernate.loader.custom.ConstructorResultColumnProcessor.performDiscovery(ConstructorResultColumnProcessor.java:45)
      at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:494)
      at org.hibernate.loader.Loader.processResultSet(Loader.java:2333)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:2289)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2045)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2007)
      at org.hibernate.loader.Loader.doQuery(Loader.java:953)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
      at org.hibernate.loader.Loader.doList(Loader.java:2810)
      at org.hibernate.loader.Loader.doList(Loader.java:2792)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2624)
      at org.hibernate.loader.Loader.list(Loader.java:2619)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
      at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2137)
      at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1134)
      at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173)
      at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526)
      at org.hibernate.query.Query.getResultList(Query.java:165)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:564)
      at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:409)
      at com.sun.proxy.$Proxy212.getResultList(Unknown Source)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:353)
      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)
      ... 73 more
  </code></pre>
  
  <p><strong>Sanity check and alternative approach</strong></p>
  
  <p>To make a sanity check I deleted the <code>@SqlResultSetMapping</code>, in which case the query is supposed to return a list of 'Object[]' values, and then I tested each individual value in that array to check its type, it showed me that the types are what I assumed them to be 'java.sql.Date' and 'java.sql.Time' twice, and all three of them were in the expected order, (Date, Time, Time), which matches the order of the constructor parameters of my POJO class.</p>
  
  <p>My entity and namedNativeQuery:</p>
  
  <pre><code>@Entity
  @NamedNativeQuery(
      name = 
          "Doctor.findFreeExaminationTimes",
      query = 
          "SELECT on_date AS onDate, LAG(to_time, 1, '00:00') OVER mojWindow AS fromTime, from_time AS toTime " +
          "..."
  )
  public class Doctor extends User {...}
  </code></pre>
  
  <p>My repository with a new return type:</p>
  
  <pre><code>@RepositoryRestResource
  public interface DoctorRepository extends UserRepository&lt;Doctor&gt; {
  
      List&lt;Object[]&gt; findFreeExaminationTimes(@Param("doctorId") Long doctorId);
  }
  </code></pre>
  
  <p>My test:</p>
  
  <pre><code>@SpringBootTest
  public class DoctorTests {
  
      @Autowired
      private DoctorRepository doctorRepository;
  
      private final Logger LOGGER = LoggerFactory.getLogger(this.getClass());
  
      @Test
      public void shouldReturnDoctorAvailability() {
  
          // Exception thrown here
          List&lt;DoctorAvailabilityResponse&gt; freeTimes = doctorRepository.findFreeExaminationTimes(4L);
  
          freeTimes.stream().forEach((ft) -&gt; {
              // Values are in expected order and of expected types
              String classNameOnDate = ft[0].getClass().toString(); // java.sql.Date
              String classNameFromTime = ft[1].getClass().toString(); // java.sql.Time
              String classNameToTime = ft[1].getClass().toString(); // java.sql.Time
  
              // I suppose the mapping mechanism is supposed to do something like this, but fails for some reason
              DoctorAvailabilityResponse dar = new DoctorAvailabilityResponse((Date)ft[0], (Time)ft[1], (Time)ft[2]);
          });
          LOGGER.info(freeTimes.toString());
      }
  }
  </code></pre>
  
  <p>Running this test works perfectly which supposedly shows that the problem is in the @SqlResultSetMapping or in POJO class.</p>
  
  <p>I would appreciate any feedback. Thanks!</p>
  
  <hr>
  
  <h1>SOLUTION</h1>
  
  <p>I had to change the types in the <code>@SqlResultSetMapping</code> and in the constructor of my POJO class.</p>
  
  <p>Changed <code>@SqlResultSetMapping</code></p>
  
  <pre><code>@SqlResultSetMapping(
      name = "freeTimesByDoctorId",
      classes = @ConstructorResult(
          targetClass = DoctorAvailabilityResponse.class,
          columns = {
              @ColumnResult(name = "onDate", type = String.class),
              @ColumnResult(name = "fromTime", type = String.class),
              @ColumnResult(name = "toTime",type = String.class)
          }
      )
  )
  </code></pre>
  
  <p>Changed POJO class constructor</p>
  
  <pre><code>public DoctorAvailabilityResponse(String onDate, String fromTime, String toTime) {
          this.onDate = Date.valueOf(onDate);
          this.fromTime = Time.valueOf(fromTime); 
          this.toTime = Time.valueOf(toTime);
      }
  </code></pre>
  
  <p>This alone did not solve my problem however as I got a hibernate exception as mentioned and solved <a href="https://stackoverflow.com/questions/49056084/got-different-size-of-tuples-and-aliases-exception-after-spring-boot-2-0-0-rel">in this SO question</a>. According to this answer I also changed my repository and added an additional annotation.</p>
  
  <p>Changed repository</p>
  
  <pre><code>@RepositoryRestResource
  public interface DoctorRepository extends UserRepository&lt;Doctor&gt; {
  
      @Query(nativeQuery = true) // This is added
      List&lt;DoctorAvailabilityResponse&gt; findFreeExaminationTimes(@Param("doctorId") Long doctorId);
  }
  </code></pre>
  
  <p>Now everything works, though the question remains why didn't <code>@SqlResultSetMapping</code> map <code>java.sql.*</code> types to the constructor in the first place.</p>