How to read mdb file results using item reader in spring batch

Question

I want to read mdb file using spring batch Item Reader, I have managed to read the mdb file and the results I can see them in console but I'm not able to map this results into the reader object. Below is my sample code.

package config;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import model.Order;
import repository.OrderRepository;

@Configuration
@EnableBatchProcessing
public class SpringBatchConfig_ {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;
    
    @Autowired
    private StepBuilderFactory stepBuilderFactory;
    
    @Autowired
    private OrderRepository orderRepository;
    
    @Bean
    public JdbcCursorItemReader<Order> orderReader(@Value("#{jobParameters[fullPathFileName]}") String pathToFile) throws SQLException{
        String msAccDB = "jdbc:ucanaccess://" + pathToFile;
        Connection conn = DriverManager.getConnection(msAccDB);
        JdbcCursorItemReader<Order> orderReader = new JdbcCursorItemReader<>();
        
        //Fetching Orders Data from orders table in mdb file
        Statement orders = conn.createStatement();
        ResultSet orderResult = orders.executeQuery("SELECT * FROM tblOrder");
        
        List<Order> orderList = new ArrayList<>();
        while(orderResult.next()) { 
        Order orderData = new Order();
        orderData.setOrderId(orderResult.getString(2));
        orderData.setDescription(orderResult.getString(3));
        orderData.setQuantity(orderResult.getString(4));
        orderData.setOderDate(orderResult.getString(5));
        orderData.setOrderStatus(orderResult.getString(6));
        orderData.setOrderLocation(orderResult.getString(7));
        orderList.add(orderData);
        }
        system.out.print(orderList);
        return orderReader ;
    }
}

Any suggestions will be much appreciated.

Answer 1

Use the JdbcCursorItemReaderBuilder to configure the JdbcCursorItemReader (you could do it manually as well but using the builder is easier).

You need a DataSource for the JdbcCursorItemReader to work.

@Bean
@StepScope
public JdbcCursorItemReader<Order> orderReader(@Value("#{jobParameters[fullPathFileName]}") String pathToFile) {

  String url = "jdbc:ucanaccess://" + pathToFile;
  DriverManagerDataSource ds = new DriverManagerDataSource();
  ds.setUrl(url);

  return JdbcCursorItemReaderBuilder.
    datasource(ds)
    sql("SELECT * FROM tblOrder")
    rowMapper( (rs, c) -> {
      Order order = new Order();
      order.setOrderId(rs.getString(2));
      order.setDescription(rs.getString(3));
      order.setQuantity(rs.getString(4));
      order.setOderDate(rs.getString(5));
      order.setOrderStatus(rs.getString(6));
      order.setOrderLocation(rs.getString(7));
      return order;
      }).build();
}

Something like this will configure the itemreader for you.