Issue
I am trying to get every formations that overlap with a given an interval of years from my postgresql database. However, i get a syntax error message for my query validation when my beans are being initialized.
antlr.NoViableAltException: unexpected token: OVERLAPS
Caused by this part of my JPA repository
@Query("SELECT f FROM formations f WHERE (SELECT (make_date(starting_year, 1, 1), make_date(ending_year, 1, 1)) OVERLAPS (make_date(:startingYear, 1, 1), make_date(:endingYear, 1, 1)))")
List<Formation> findAllMatchingFormations(@Param("startingYear") int startingYear, @Param("endingYear") int endingYear);
The thing is that the query works perfectly fine when i do it from inside the database.
There is the full stack trace
antlr.NoViableAltException: unexpected token: OVERLAPS
at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:2628) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1525) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1425) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1148) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:825) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:4356) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1060) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3960) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3891) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3767) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3393) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2998) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2824) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2783) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2693) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2653) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2331) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:2079) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:540) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:831) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:336) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:200) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
...
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65) ~[app.jar:0.0.1-SNAPSHOT]
2022-07-01 14:32:04.107 ERROR 1 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:163: unexpected token: ,
2022-07-01 14:32:04.108 ERROR 1 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:163: unexpected token: ,
antlr.NoViableAltException: unexpected token: ,
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:864) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:336) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:200) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
...
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65) ~[app.jar:0.0.1-SNAPSHOT]
2022-07-01 14:32:04.109 ERROR 1 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:192: expecting EOF, found ')'
2022-07-01 14:32:04.109 ERROR 1 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:192: expecting EOF, found ')'
antlr.MismatchedTokenException: expecting EOF, found ')'
at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar!/:na]
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:217) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
...
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65) ~[app.jar:0.0.1-SNAPSHOT]
2022-07-01 14:32:04.110 WARN 1 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'formationsController': Unsatisfied dependency expressed through field 'repository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'formationRepository' defined in FOC.FOC.Formations.FormationRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int); Reason: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!
2022-07-01 14:32:04.111 INFO 1 --- [ main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-07-01 14:32:04.113 INFO 1 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2022-07-01 14:32:04.115 INFO 1 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2022-07-01 14:32:04.117 INFO 1 --- [ main] o.apache.catalina.core.StandardService : Stopping service [Tomcat]
2022-07-01 14:32:04.127 INFO 1 --- [ main] ConditionEvaluationReportLoggingListener :
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2022-07-01 14:32:04.148 ERROR 1 --- [ main] o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'formationsController': Unsatisfied dependency expressed through field 'repository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'formationRepository' defined in FOC.FOC.Formations.FormationRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int); Reason: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:659) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:639) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:119) ~[spring-beans-5.3.21.jar!/:5.3.21]
...
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) ~[app.jar:0.0.1-SNAPSHOT]
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65) ~[app.jar:0.0.1-SNAPSHOT]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'formationRepository' defined in FOC.FOC.Formations.FormationRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int); Reason: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1391) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1311) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:656) ~[spring-beans-5.3.21.jar!/:5.3.21]
... 28 common frames omitted
Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int); Reason: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!
at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:101) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:107) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(QueryExecutorMethodInterceptor.java:95) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197) ~[na:na]
at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]
at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1061) ~[na:na]
at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[na:na]
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[na:na]
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682) ~[na:na]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:97) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:87) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at java.base/java.util.Optional.map(Optional.java:260) ~[na:na]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:87) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:365) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:323) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.util.Lazy.getNullable(Lazy.java:231) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.util.Lazy.get(Lazy.java:115) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:329) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:144) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.21.jar!/:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.21.jar!/:5.3.21]
... 38 common frames omitted
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List FOC.FOC.Formations.FormationRepository.findAllMatchingFormations(int,int)!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:96) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:51) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:169) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:253) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:93) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:103) ~[spring-data-commons-2.7.1.jar!/:2.7.1]
... 60 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: OVERLAPS near line 1, column 123 [SELECT f FROM FOC.FOC.Formations.Formation f WHERE (SELECT (make_date(starting_year, 1, 1), make_date(ending_year, 1, 1)) OVERLAPS (make_date(:startingYear, 1, 1), make_date(:endingYear, 1, 1)))]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:757) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:114) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.21.jar!/:5.3.21]
at jdk.proxy2/jdk.proxy2.$Proxy106.createQuery(Unknown Source) ~[na:na]
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:90) ~[spring-data-jpa-2.7.1.jar!/:2.7.1]
... 66 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: OVERLAPS near line 1, column 123 [SELECT f FROM FOC.FOC.Formations.Formation f WHERE (SELECT (make_date(starting_year, 1, 1), make_date(ending_year, 1, 1)) OVERLAPS (make_date(:startingYear, 1, 1), make_date(:endingYear, 1, 1)))]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:301) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) ~[hibernate-core-5.6.9.Final.jar!/:5.6.9.Final]
... 74 common frames omitted
I tried to do a native query with nativeQuery=true and a SqlResultSetMapping annotation, but it gave me more complications and I think I am just missing something there to make it work.
I'm using:
Spring boot 2.7.1
Postgresql 11
Solution
OVERLAPS is not supported by JPQL queries (you can find the list of supported functions in the Hibernate ORM documentation).
It should work if you use a native SQL query:
@Query(nativeQuery=true, value="... <The SQL query>")
If you return all the columns with select *, you won't need to map the result using SqlResultSetMapping. Hibernate should be able to map each row to Formation automatically.
Answered By - Davide D'Alto
Answer Checked By - David Goodson (JavaFixing Volunteer)
