SQL example, simplified
@Query(value = "SELECT\n" +
"\tMAX( CASE WHEN type = 'ap' THEN num END ) AS 'apNum',\n" +
"\tMAX( CASE WHEN type = 'lo' THEN num END ) AS 'loNum',\n" +
"\tMAX( CASE WHEN type = 'pr' THEN num END ) AS 'prNum',\n" +
"\tMAX( CASE WHEN type = 'ac' THEN num END ) AS 'acNum',\n" +
"\tMAX( CASE WHEN type = 'co' THEN num END ) AS 'coNum',\n" +
"\tMAX( CASE WHEN type = 'co2' THEN num END ) AS 'coNum2' \n" +
" from order WHERE\n" +
"\tapply_date = ?1 \n"
,nativeQuery = true)
JSONObject findByNewData(String date);
SQL client execution results
The error message is as follows
Caused by: org.hibernate.InstantiationException: Cannot instantiate query result type 'com.alibaba.fastjson2.JSONObject' due to: Result class must have a single constructor with exactly 6 parameters 'com.alibaba.fastjson2.JSONObject'
at org.hibernate.jpa.spi.NativeQueryConstructorTransformer.transformTuple(NativeQueryConstructorTransformer.java:71)
at org.hibernate.sql.results.internal.RowTransformerTupleTransformerAdapter.transformRow(RowTransformerTupleTransformerAdapter.java:30)
at org.hibernate.sql.results.internal.StandardRowReader.readRow(StandardRowReader.java:160)
at org.hibernate.sql.results.spi.ListResultsConsumer.read(ListResultsConsumer.java:249)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:201)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:35)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:224)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:142)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:132)
at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:135)
at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:693)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:275)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:419)
at jdk.proxy2/jdk.proxy2.$Proxy379.getSingleResult(Unknown Source)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:224)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:93)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:152)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:140)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:170)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
... 139 common frames omitted
Caused by: org.hibernate.InstantiationException: Result class must have a single constructor with exactly 6 parameters 'com.alibaba.fastjson2.JSONObject'
at org.hibernate.jpa.spi.NativeQueryConstructorTransformer.constructor(NativeQueryConstructorTransformer.java:54)
at org.hibernate.jpa.spi.NativeQueryConstructorTransformer.transformTuple(NativeQueryConstructorTransformer.java:68)
... 175 common frames omitted
Comment From: BenEfrati
When using Class-based projection, types must declare a single constructor so that Spring Data can determine their input properties. If your class defines more than one constructor, then you cannot use the type without further hints for DTO projections. In such a case annotate the desired constructor with @PersistenceCreator as outlined below so that Spring Data can determine which properties to select:
public class NamesOnly {
private final String firstname;
private final String lastname;
protected NamesOnly() { }
@PersistenceCreator
public NamesOnly(String firstname, String lastname) {
this.firstname = firstname;
this.lastname = lastname;
}
}
https://github.com/spring-projects/spring-data-jpa/commit/7bcc96d84be802872052acc1b7b141d2fd1657e8