I have a method to get education events from database. No clue why it fails to run. It seems that the query fails only in some cases, but couldn't really find out what are the input parameters that break it. I was unable to get any usage of the hint from the log about the syntax error at position 1853. Do you have any ideas what causes the problem or how to debug the method further?
public List<EE> findEEs(S s, List<EM> eMs, E e) {
TypedQuery<EE> query = entityManager.createQuery(
"select distinct ee from EE ee left join ee.lSE lse "
+ "where ee.eM in (:em) "
+ "and (ee.scope = :publicScope "
+ "or (ee.scope = :limitedScope and (ee.hE= :e or :e in lse)) "
+ "or (ee.scope = :localScope and ee.hE = :education)) "
+ "and ee.status = :status and ee.startDate > :now "
+ "and (ee.groupSize = 0 or (ee.groupSize > 0 "
+ " and ee.groupSize > (ee.numberOfStudents + ee.reservedSeats))) "
+ "and ee.id not in (select pee.eE.id from PEE pee "
+ "where studentStatus.s = :s and eE.eM in (:em))",
EE.class);
query.setParameter("s", s);
query.setParameter("em", eMs);
query.setParameter("publicScope", PUBLIC);
query.setParameter("limitedScope", LIMITED);
query.setParameter("localScope", LOCAL);
query.setParameter("e", e);
query.setParameter("status", CONFIRMED);
query.setParameter("now", new DateTime());
return query.getResultList();
The log shows this:
2017-10-19 14:46:32,673 [http-nio-8090-exec-10] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
2017-10-19 14:46:32,674 [http-nio-8090-exec-10] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near ")"
Position: 1853
lokakuuta 19, 2017 2:46:32 IP. org.springframework.web.servlet.handler.AbstractHandlerExceptionResolver logException
WARNING: Resolved exception caused by Handler execution: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Data access failure
Sorry, a problem occurred while accessing the database.
org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
com.sun.proxy.$Proxy435.findUnaddedEducationEvents(Unknown Source)