메서드 JPQL에 대한 쿼리를 검증하지 못했습니다.

메서드 JPQL에 대한 쿼리를 검증하지 못했습니다.

커스텀 오브젝트에 출력을 반환하는 쿼리를 구현하고 있습니다.구현에서 다음 오류가 발생합니다.

"public abstract java.vlic"org.degs.repository를 나열합니다.Consolidate Response Repository.transactions()!"

JPQL 저장소 코드:

@Query(value = "SELECT new org.degs.beans.ConsolidateResDB(DATE_FORMAT(ts, '%d-%m-%Y') AS transdate,IFNULL(COUNT(orderreqid),0),IFNULL(COUNT(failamount),0),IFNULL(SUM(amount),0),IFNULL(SUM(successamount),0),IFNULL(SUM(failamount),0),IFNULL(COUNT(successcount),0)) FROM Consolidateresponse GROUP BY DATE_FORMAT(ts, '%d-%m-%Y') ORDER BY ts DESC")
public List<ConsolidateResDB> transactions();

ConsolidateResDB콩:

public class ConsolidateResDB {
    String transdate;
    long total;
    long failcount;
    double totalamount; double successamount;
    double failamount;
    long successcount;
}

쿼리 자체에서 null 값을 이미 처리하여 mysql db 터미널에서 동일한 쿼리를 실행해도 정상 출력을 얻을 수 있지만 JPA 및 Spring Boot에 구현되어 있는 동안에도 여전히 수신하고 있습니다.NullPointerException에러:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'indexController': Unsatisfied dependency expressed through field 'crRepo'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'consolidateresponseRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List org.degs.repository.ConsolidateresponseRepository.transactions()!
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:588) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866) ~[spring-context-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) ~[spring-context-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:370) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1162) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1151) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.degs.SbipgMisApplication.main(SbipgMisApplication.java:16) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121]
at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_121]
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) [spring-boot-devtools-1.5.2.RELEASE.jar:1.5.2.RELEASE]

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'consolidateresponseRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List org.degs.repository.ConsolidateresponseRepository.transactions()!
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:585) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
... 24 common frames omitted

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List org.degs.repository.ConsolidateresponseRepository.transactions()!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:144) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:212) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:436) ~[spring-data-commons-1.13.1.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:221) ~[spring-data-commons-1.13.1.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:277) ~[spring-data-commons-1.13.1.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:263) ~[spring-data-commons-1.13.1.RELEASE.jar:na]
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:101) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
... 34 common frames omitted

Caused by: java.lang.NullPointerException: null
at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:418) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeColumnNames(SelectClause.java:267) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.tree.SelectClause.finishInitialization(SelectClause.java:257) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:252) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:920) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:688) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:673) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:309) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:257) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121]
at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_121]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347) ~[spring-orm-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at com.sun.proxy.$Proxy85.createQuery(Unknown Source) ~[na:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86) ~[spring-data-jpa-1.11.1.RELEASE.jar:na]
... 47 common frames omitted

스프링에게 그 쿼리를 네이티브로 처리하라고 말해야 합니다.그렇지 않으면 JPA 사양에 따라 검증이 시도됩니다.

시험:

@Query(value = "SELECT ...", nativeQuery = true)
public List<Object[]> transactions();

이 경우 에서는 NEW 연산자 구문을 사용할 수 없습니다.또한 결과는 다음 명령어의 배열로 처리해야 합니다.Object.

또는

결과를 POJO 클래스에 직접 매핑하려면 다음 작업을 수행해야 합니다(JPA 2.1+를 사용하는 것으로 가정).

1) 매핑을 정의합니다.

@SqlResultSetMapping(
    name="transactionsMapping",
    classes={
        @ConstructorResult(
            targetClass=ConsolidateResDB.class,
            columns={
                @ColumnResult(name="transdate"),
                @ColumnResult(name="orderreqid")
                // further mappings ...
            }
        )
    }
)

2) 네이티브 쿼리 정의

@NamedNativeQuery(name="transactions"
    , query="SELECT DATE_FORMAT(ts, '%d-%m-%Y') AS transdate, IFNULL(COUNT(orderreqid),0) ... ")

3) 에서 이 방법을 정의합니다.CrudRepository를 제외하고@Query주석:

public List<ConsolidateResDB> transactions();

스프링 데이터용2.0.5그 이상은

Maciej의 솔루션을 시도한 후 코드가 불만을 표시하기 시작했습니다."Got different size of tuples and aliases".최신 릴리스에서 이 문제를 해결하려면

@Query(<Entity.FunctionName>, nativeQuery=true). 

이것으로 내 코드는 e2e가 되었다.

나에게는 (정확히 같은 코드는 아니지만)

@Query("SELECT e FROM TheEntity e WHERE e.stuff = :stuff")
List<SomeEntity> findSomething(@Param("stuff") String someStr);

또한 엔티티도 잊지 마십시오.

@Entity(name = "TheEntity")

나는 같은 문제에 직면했고, 아래는 나를 위해 일했다.

@Query(value = "select NEW org.degs.beans.ConsolidateResDB(DATE_FORMAT(c.ts, '%d-%m-%Y') ,IFNULL(COUNT(c.orderreqid),0),IFNULL(COUNT(c.failamount),0),IFNULL(SUM(c.amount),0),IFNULL(SUM(c.successamount),0),IFNULL(SUM(c.failamount),0),IFNULL(COUNT(c.successcount),0)) FROM Consolidateresponse c GROUP BY DATE_FORMAT(c.ts, '%d-%m-%Y') ORDER BY c.ts DESC") 
public List<ConsolidateResDB> transactions();

동일한 오류가 발생하여 네이티브 쿼리를 사용하고 싶지 않습니다.

INSER JOIN 절을 삭제하여 해결.

  // Will not work if relations between entity classes are not defined properly.
  @Query(value = "SELECT d FROM Animal a "
      + "INNER JOIN Dog d ON a.id = d.animalId "
      + "WHERE d.name = ?1")

여기 작동 중인 것이 있습니다.

  // Works fine. (NO INNER JOIN)
  @Query(value = "SELECT d FROM Animal a, Dog d "
      + "WHERE a.id = d.animalId "
      + "WHERE d.name = ?1")

언급URL : https://stackoverflow.com/questions/44647630/validation-failed-for-query-for-method-jpql