17008oracle,Spring启动@Scheduled失败SQL错误：17008，SQLState：08003(Spring boot @Scheduled fails SQL Error: 1...

This is a similar kind of question raised in here again this looks to be a problem on @Schdeuled doesn't work with @Transaction. however, this is slightly different and might be a problem with transaction handled in my code.

I'm running a scheduler which starts every day (DailyScheduler.processDailyScheduler()) and fails with the below error

SQL Error: 17008, SQLState: 08003 javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not execute query at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) Caused by: java.sql.SQLRecoverableException: Closed Connection: next

And I also have a controller (DailyController.processDailyJob) which exactly performs the same job but that gets succeed when I run with the same input.

I don't understand why my scheduler doesn't pass and the controller works always.

Any help on this would be much appreciated! Thank you!

DailyScheduler:

import org.springframework.scheduling.annotation.Scheduled;

import org.springframework.stereotype.Component;

@Component

@Slf4j

public class DailyScheduler {

@Autowired

DailyService dailyService;

@Scheduled(cron = "${daily.scheduler.cron}")

public void processDailyScheduler() {

log.info("DailyScheduler starts");

dailyService.processDailyJob(new Date());

log.info("DailyScheduler finishes");

}

}

DailyController:

@Slf4j

@RestController

@ApiIgnore

public class DailyController {

@Autowired

DailyService dailyService;

@RequestMapping(value = {"/v1/daily/job/{jobDate}"}, method = RequestMethod.POST)

public ResponseEntity processDailyJob(@PathVariable("jobDate") String jobDate,

@RequestHeader(SIFAuthorization.AUTHORIZATION) String authorizationToken)

throws Exception {

SimpleDateFormat dateFormatter =new SimpleDateFormat("dd-MMM-yyyy");

Date targetDate =dateFormatter.parse(jobDate);

dailyService.processDailyJob(targetDate);

return new ResponseEntity<>("Daily Job Processed.", HttpStatus.OK);

}

}

DailyServiceImpl:

@Slf4j

@Service

public class DailyServiceImpl implements DailyService {

@Autowired

private UserServiceRepository userServiceRepository;

@Override

public Integer processDailyJob(Date targetDate) {

userServiceRepository.findUser(targetDate);

}

}

UserServiceRepository:

@Transactional(readOnly = true)

public interface UserServiceRepository extends JpaRepository {

UserDailySummary findUser(Date targetDate);

}

DBConfig:

@Configuration

@Profile("!test")

@EnableTransactionManagement

@EnableJpaRepositories(entityManagerFactoryRef = "dbEntityManagerFactory",

transactionManagerRef = "dbTransactionManager",

basePackages = {"com.org.dub.mmm"})

@EnableConfigurationProperties(DbProperties.class)

@Slf4j

public class DatabaseConfig {

@Autowired

private DbProperties dbProperties;

@Bean(name = "dataSource")

@Primary

public DataSource dataSource() {

PoolProperties poolProperties = new PoolProperties();

poolProperties.setUrl(dbProperties.getJdbcUrl());

poolProperties.setDriverClassName(dbProperties.getDriverClassName());

poolProperties.setUsername(dbProperties.getUser());

poolProperties.setPassword(dbProperties.getPassword());

// override the default value

poolProperties.setTestOnBorrow(true);

poolProperties.setTestWhileIdle(true);

poolProperties.setValidationQuery(dbProperties.getValidationQuery());

poolProperties.setRemoveAbandoned(true);

DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(poolProperties);

return dataSource;

}

@Bean

PlatformTransactionManager dbTransactionManager() {

return new JpaTransactionManager(dbEntityManagerFactory().getObject());

}

@Bean

@Primary

LocalContainerEntityManagerFactoryBean dbEntityManagerFactory() {

LocalContainerEntityManagerFactoryBean factoryBean = null;

HibernateJpaVendorAdapter vendorAdapter =

new HibernateJpaVendorAdapter();

vendorAdapter.setDatabase(Database.ORACLE);

factoryBean = new LocalContainerEntityManagerFactoryBean();

DataSource dataSource = dataSource();

factoryBean.setDataSource(dataSource);

factoryBean.setJpaVendorAdapter(vendorAdapter);

factoryBean.setJpaProperties(additionalJpaProperties());

factoryBean.setPackagesToScan(new String[]{"com.org.dub.mmm.domain"});

factoryBean.afterPropertiesSet();

return factoryBean;

}

Properties additionalJpaProperties() {

Properties properties = new Properties();

properties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");

properties.setProperty("hibernate.cache.use_second_level_cache", "true");

properties.setProperty("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.EhCacheRegionFactory");

return properties;

}

}