从NamedNativeQuery抛出的SqlResultSetMapping到POJO类“无法找到适当的构造函数”

Question

我创建了一个@NamedNativeQuery并将其附加到一个实体“Doctor”，在同一个实体上我附加了一个@SQLResultSetMapping，它获取查询结果的列并将它们映射到一个专门创建的POJO类的构造函数。该查询还连接到一个JPA方法，该方法驻留在同一实体的存储库中。

但是，我不断得到一个错误，即找不到合适的构造函数，好像@sqlresultsetmapping或POJO构造函数不同步。（堆栈跟踪在底部）

我的实体，@namednativeQuery和@sqlresultsetmapping:

我直接在数据库上尝试了查询，它给出了预期的结果，所以我只是编写了select子句

@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 {...}

我在@ConstructorResult中提到的'target class'下的POJO类有一个构造函数，其参数的顺序、数量和类型都是在'columns'下指定的。

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
}

@RepositoryRestResource
public interface DoctorRepository extends UserRepository<Doctor> {

    // JPA method mapped to the named native query above
    List<DoctorAvailabilityResponse> findFreeExaminationTimes(@Param("doctorId") Long doctorId);
}

我的测试：

@SpringBootTest
public class DoctorTests {

    @Autowired
    private DoctorRepository doctorRepository;

    private final Logger LOGGER = LoggerFactory.getLogger(this.getClass());

    @Test
    public void shouldReturnDoctorAvailability() {

        // Exception thrown here
        List<DoctorAvailabilityResponse> freeTimes = doctorRepository.findFreeExaminationTimes(4L);

        LOGGER.info(freeTimes.toString());
    }
}

我不明白为什么会这样。在维护JPA存储库方法的同时，有没有一种方法可以手动地将这个结果集映射到POJO？

堆栈跟踪：

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

健全性检查和替代方法

@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 {...}

@RepositoryRestResource
public interface DoctorRepository extends UserRepository<Doctor> {

    List<Object[]> findFreeExaminationTimes(@Param("doctorId") Long doctorId);
}

@SpringBootTest
public class DoctorTests {

    @Autowired
    private DoctorRepository doctorRepository;

    private final Logger LOGGER = LoggerFactory.getLogger(this.getClass());

    @Test
    public void shouldReturnDoctorAvailability() {

        // Exception thrown here
        List<DoctorAvailabilityResponse> freeTimes = doctorRepository.findFreeExaminationTimes(4L);

        freeTimes.stream().forEach((ft) -> {
            // 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());
    }
}

我必须更改@sqlresultsetmapping和POJO类构造函数中的类型。

已更改@sqlresultsetmapping

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

更改的POJO类构造函数

public DoctorAvailabilityResponse(String onDate, String fromTime, String toTime) {
        this.onDate = Date.valueOf(onDate);
        this.fromTime = Time.valueOf(fromTime); 
        this.toTime = Time.valueOf(toTime);
    }

@RepositoryRestResource
public interface DoctorRepository extends UserRepository<Doctor> {

    @Query(nativeQuery = true) // This is added
    List<DoctorAvailabilityResponse> findFreeExaminationTimes(@Param("doctorId") Long doctorId);
}

现在一切都正常了，但问题仍然是为什么@sqlresultsetmapping不首先将java.sql.*类型映射到构造函数。

Answer 1

@ConstructorResult不能很好地使用java.sql.date.class或java.sql.time.class类型。解决问题的一种方法是使用String.class，然后将字符串值转换为DoctorAvailabilityResponse构造函数中的日期/时间