JPA unable to find table in spring scheduler

Question

I am writing a Spring scheduler using @Scheduled annotation. I have autowired my repository in it and it gets autowired properly (no NPE). When I execute a method on that repository interface, it says "table not found". Although the same method of same JPA interface gets executed properly and returns data when written in a controller.

Scheduler Code

@Component
public class BACNotificationServiceScheduler implements NotificationServiceScheduler {

    private Logger logger = LoggerFactory.getLogger(BACNotificationServiceScheduler.class);

    private ApplicationContext applicationContext;

    @Autowired
    private NotificationRepository notificationRepository;

    @Autowired
    private NotificationProcessor notificationProcessor;

    @Override
    @Scheduled(fixedDelay = 30000)
    @Transactional(readOnly = false)
    public void processScheduledNotificationJob() {

        logger.logInfo("Started notification job");

        String tenantId = "24160344";

        List<NotificationData> notificationList = notificationRepository.findNotificationsByRetryCountAndStatus(0,
            tenantId, BACNotificationServiceContants.STATUS_TYPE_FAILURE);

        if (notificationList != null && !notificationList.isEmpty()) {
            notificationProcessor.processNotifications(notificationList, tenantId);
        }

        logger.logInfo("Finished notification job");
    }
}

Controller Code

@Controller
public class Abcd {

    @Autowired
    private NotificationRepository notificationRepository;

    @RequestMapping("/")
    public @ResponseBody List<NotificationData> print() {

        List<NotificationData> data = notificationRepository.findNotificationsByRetryCountAndStatus(0, "24160344", "SUCCESS");
        return data;
    }
}

Exception that i am getting in scheduler

13:03:47,606 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-14-thread-1) SQL Error: 0, SQLState: 42P01
13:03:47,606 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-14-thread-1) ERROR: relation "notification" does not exist
  Position: 705
13:03:47,843 ERROR [org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler] (pool-14-thread-1) Unexpected error occurred in scheduled task.: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242) [spring-orm-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225) [spring-orm-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:436) [spring-orm-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) [spring-data-jpa-1.10.2.RELEASE.jar:]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at com.sun.proxy.$Proxy138.findNotificationsByRetryCountAndStatus(Unknown Source)
    at com.mastercard.notificationservice.bac.scheduler.impl.BACNotificationServiceScheduler.processScheduledNotificationJob(BACNotificationServiceScheduler.java:38) [classes:]
    at com.mastercard.notificationservice.bac.scheduler.impl.BACNotificationServiceScheduler$$FastClassBySpringCGLIB$$13b61217.invoke(<generated>) [classes:]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [spring-core-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at com.mastercard.notificationservice.bac.scheduler.impl.BACNotificationServiceScheduler$$EnhancerBySpringCGLIB$$6251f9ec.processScheduledNotificationJob(<generated>) [classes:]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_73]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_73]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_73]
    at java.lang.reflect.Method.invoke(Method.java:497) [rt.jar:1.8.0_73]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) [spring-context-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_73]
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [rt.jar:1.8.0_73]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [rt.jar:1.8.0_73]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [rt.jar:1.8.0_73]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_73]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_73]
    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_73]
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2062) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1859) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:906) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:348) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2550) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2536) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2366) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2361) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:198) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1230) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:114) [spring-data-jpa-1.10.2.RELEASE.jar:]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78) [spring-data-jpa-1.10.2.RELEASE.jar:]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:102) [spring-data-jpa-1.10.2.RELEASE.jar:]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:92) [spring-data-jpa-1.10.2.RELEASE.jar:]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482) [spring-data-commons-1.12.2.RELEASE.jar:]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) [spring-data-commons-1.12.2.RELEASE.jar:]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) [spring-data-commons-1.12.2.RELEASE.jar:]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) [spring-tx-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    ... 31 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "notification" does not exist
  Position: 705
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:304)
    at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]
    ... 60 more

JPQL :

select notificati0_.tenantid as tenantid1_9_, notificati0_.id as id2_9_, notificati0_.CHANGETYPE as CHANGETY3_9_, notificati0_.CREATETIMESTAMP as CREATETI4_9_, notificati0_.CUSTOMERID as CUSTOMER5_9_, notificati0_.ENCODEDTOKEN as ENCODEDT6_9_, notificati0_.EVENTID as EVENTID7_9_, notificati0_.notificationType as notifica8_9_, notificati0_.requestBody as requestB9_9_, notificati0_.responseBody as respons10_9_, notificati0_.RETRYCOUNT as RETRYCO11_9_, notificati0_.STATUS as STATUS12_9_, notificati0_.tenantId as tenantId1_9_, notificati0_.TRIGGERPOINT as TRIGGER13_9_, notificati0_.updateTimeStamp as updateT14_9_, notificati0_.USERID as USERID15_9_, notificati0_.WALLETACCOUNTID as WALLETA16_9_ 
from NOTIFICATION notificati0_ 
where notificati0_.tenantId=? 
and notificati0_.STATUS=? 
and (notificati0_.RETRYCOUNT=? or notificati0_.RETRYCOUNT is null)

Check out [this](https://stackoverflow.com/questions/2034099/psqlexception-error-relation-table-name-does-not-exist) answer. — Abdullah Khan, Jun 06 '17 at 08:25
`relation "notification" does not exist`. So care to share with people WHAT JPQL you are invoking?! I see no "table not found" in the exception ... — Neil Stockton, Jun 06 '17 at 08:33
Hi @AbdullahKhan, The problem is it is working fine in controller but not in scheduler. Not sure why — user973179, Jun 06 '17 at 08:52
Nope. that is SQL. And JPQL relies on classes/fields, which you also would need to post for it to make sense! While at it you can point out what is character 705 of the SQL invoked (what the message tells you is the problem). — Neil Stockton, Jun 06 '17 at 08:53
I am not sure then what is JPQl and how to get it. The main issue is that how it works fine in a controller but not in a scheduler(exactly the same method call as in controller) — user973179, Jun 06 '17 at 08:55
@user973179 Try to create a separate service class with `findNotificationsByRetryCountAndStatus` method with `@Transactional` annotation and call it in `@Scheduled` method. It might be the problem with spring proxying. — Orest, Jun 06 '17 at 09:02
Possible duplicate of [Hibernate + PostgreSQL : relation does not exist - SQL Error: 0, SQLState: 42P01](https://stackoverflow.com/questions/2732878/hibernate-postgresql-relation-does-not-exist-sql-error-0-sqlstate-42p01) — Jens Schauder, Jun 07 '17 at 05:20
@JensSchauder As i said in my question, the same repository bean when autowired in controller works fine and returns the result. Problem is with my scheduler. I am not getting NPE for that bean but when queried, it throws table not found — user973179, Jun 07 '17 at 05:32
Verify that you are connected to the same database and schema, with the same user, as described in the answers to the linked question. — Jens Schauder, Jun 07 '17 at 06:59

JPA unable to find table in spring scheduler

0 Answers0