0
votes

I am trying to write a batch processing application using spring batch with multi-thread step.this is simple application reading data from a table and writing to another table but data is large around 2 million record .

I am using RepositoryItemReader & RepositoryItemWriter for reading and writing data. But after processing some data it failing due to Unable to acquire JDBC Connection.

//Config.Java

 @Bean
    public TaskExecutor taskExecutor() {
        SimpleAsyncTaskExecutor taskExecutor = new SimpleAsyncTaskExecutor();
        taskExecutor.setConcurrencyLimit(10);
        return taskExecutor;
    }

    @Bean(name = "personJob")
    public Job personKeeperJob() {

        Step step = stepBuilderFactory.get("step-1")
                .<User, Person> chunk(1000)
                .reader(userReader)
                .processor(jpaProcessor)
                .writer(personWriter)
                .taskExecutor(taskExecutor())
                .throttleLimit(10)
                .build();

        Job job = jobBuilderFactory.get("person-job")
                .incrementer(new RunIdIncrementer())
                .listener(this)
                .start(step)
                .build();

        return job;
    }


//Processor.Java

@Override
    public Person process(User user) throws Exception {
        Optional<User> userFromDb = userRepo.findById(user.getUserId());
        Person person = new Person();

        if(userFromDb.isPresent()) {
            person.setName(userFromDb.get().getName());
            person.setUserId(userFromDb.get().getUserId());
            person.setDept(userFromDb.get().getDept());
        }

        return person;
    }


//Reader.Java

@Autowired
    public UserItemReader(final UserRepository repository) {
        super();
        this.repository = repository;
    }
    @PostConstruct
    protected void init() {
        final Map<String, Sort.Direction> sorts = new HashMap<>();
        sorts.put("userId", Direction.ASC);
        this.setRepository(this.repository);
        this.setSort(sorts);
        this.setMethodName("findAll");
    }

//Writer.Java

@PostConstruct
    protected void init() {
        this.setRepository(repository);
    }

    @Transactional
    public void write(List<? extends Person> persons) throws Exception {
        repository.saveAll(persons);
    }


application.properties

# Datasource

spring.datasource.platform=h2
spring.datasource.url=jdbc:h2:mem:batchdb
spring.main.allow-bean-definition-overriding=true
spring.datasource.hikari.maximum-pool-size=500

Error :

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:447)
  ......................
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)

............................

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30927ms.
1
what are the datasource configuration that you are using , please add the config here - Ashish Shetkar
You need to remove @Transactional on the write method, your writer will be called in the context of a transaction driven by Spring Batch. Since you use JPA, you need to configure Spring Batch to use a JpaTransactionManager. - Mahmoud Ben Hassine

1 Answers

0
votes

You run out of connections.

Try to set the Hikari Connection Pool to a bigger number:

spring.datasource.hikari.maximum-pool-size=20