[Spring Batch] 배치 처리 : 엑셀 to 테이블

엑셀 파일 - read > 배치 어플리케이션 - write > 테이블

엑셀 접근 의존성

implementation 'org.apache.poi:poi-ooxml:5.3.0'

 

엑셀 생성

위 그림처럼 엑셀예제를 생성

 

AfterEntity

@Data
@Entity
public class AfterEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String username;
}

 

AfterRepository

@Repository
public interface AfterRepository extends JpaRepository<AfterEntity, Long> {
}

 

Batch 클래스, Job

@Configuration
@RequiredArgsConstructor
public class FourthBatch {

    private final JobRepository jobRepository;
    private final PlatformTransactionManager platformTransactionManager;
    private final AfterRepository afterRepository;

    @Bean
    public Job fourthJob() {

        System.out.println("fourth job");

        return new JobBuilder("fourthJob", jobRepository)
                .start(fourthStep())
                .build();
    }
}

 

Reader

@Bean
public ItemStreamReader<Row> excelReader() {
    try {
        return new ExcelRowReader("C:\\Users\\Jongwon.JONG-PC\\Desktop\\jongwon.xlsx");
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

 

Processor

@Bean
public ItemProcessor<Row, AfterEntity> fourthProcessor() {

    return new ItemProcessor<Row, AfterEntity>() {

        @Override
        public AfterEntity process(Row item) {

            AfterEntity afterEntity = new AfterEntity();
            afterEntity.setUsername(item.getCell(0).getStringCellValue());

            return afterEntity;
        }
    };
}

 

Writer

@Bean
public RepositoryItemWriter<AfterEntity> fourthAfterWriter() {

    return new RepositoryItemWriterBuilder<AfterEntity>()
            .repository(afterRepository)
            .methodName("save")
            .build();
}

 

Batch 클래스 전체 코드

@Configuration
@RequiredArgsConstructor
public class FourthBatch {

    private final JobRepository jobRepository;
    private final PlatformTransactionManager platformTransactionManager;
    private final AfterRepository afterRepository;

    @Bean
    public Job fourthJob() {

        System.out.println("fourth job");

        return new JobBuilder("fourthJob", jobRepository)
                .start(fourthStep())
                .build();
    }

    private Step fourthStep() {
        return new StepBuilder("fourthStep", jobRepository)
                .<Row, AfterEntity>chunk(10, platformTransactionManager)
                .reader(excelReader())
                .processor(fourthProcessor())
                .writer(fourthAfterWriter())
                .build();
    }

    @Bean
    public ItemStreamReader<Row> excelReader() {
        try {
            return new ExcelRowReader("C:\\Users\\Jongwon.JONG-PC\\Desktop\\jongwon.xlsx");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Bean
    public ItemProcessor<Row, AfterEntity> fourthProcessor() {

        return new ItemProcessor<Row, AfterEntity>() {

            @Override
            public AfterEntity process(Row item) {

                AfterEntity afterEntity = new AfterEntity();
                afterEntity.setUsername(item.getCell(0).getStringCellValue());

                return afterEntity;
            }
        };
    }

    @Bean
    public RepositoryItemWriter<AfterEntity> fourthAfterWriter() {

        return new RepositoryItemWriterBuilder<AfterEntity>()
                .repository(afterRepository)
                .methodName("save")
                .build();
    }

}

 

ExcelRowReader

@RequiredArgsConstructor
public class ExcelRowReader implements ItemStreamReader<Row> {

    private final String filePath;
    private FileInputStream fileInputStream;
    private Workbook workbook;
    private Iterator<Row> rowCursor;
    private int currentRowNumber;
    private final String CURRENT_ROW_KEY = "current.row.number";
}

 

filePath : 파일경로

rowCursor : 파일을 읽기 위한 커서

currentRowNumber : 현재 행

CURRENT_ROW_KEY : meta_db에서 현재 행 저장을 위한 변수

 

open

@Override
public void open(ExecutionContext executionContext) throws ItemStreamException {

    try {
        fileInputStream = new FileInputStream(filePath);
        workbook = WorkbookFactory.create(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        this.rowCursor = sheet.iterator();

        // 동일 배치 파라미터에 대해 특정 키 값 "current.row.number"의 값이 존재한다면 초기화
        if (executionContext.containsKey(CURRENT_ROW_KEY)) {
            currentRowNumber = executionContext.getInt(CURRENT_ROW_KEY);
        }

        // 위의 값을 가져와 이미 실행한 부분은 건너 뜀
        for (int i = 0; i < currentRowNumber && rowCursor.hasNext(); i++) {
            rowCursor.next();
        }

    } catch (IOException e) {
        throw new ItemStreamException(e);
    }
}

 

read

@Override
public Row read() {

    if (rowCursor != null && rowCursor.hasNext()) {
        currentRowNumber++;
        return rowCursor.next();
    } else {
        return null;
    }
}

 

update

@Override
public void update(ExecutionContext executionContext) throws ItemStreamException {
    executionContext.putInt(CURRENT_ROW_KEY, currentRowNumber);
}

 

close

@Override
public void close() throws ItemStreamException {

    try {
        if (workbook != null) {
            workbook.close();
        }
        if (fileInputStream != null) {
            fileInputStream.close();
        }
    } catch (IOException e) {
        throw new ItemStreamException(e);
    }
}

 

ExcelRowReader 전체 코드

@RequiredArgsConstructor
public class ExcelRowReader implements ItemStreamReader<Row> {

    private final String filePath;
    private FileInputStream fileInputStream;
    private Workbook workbook;
    private Iterator<Row> rowCursor;
    private int currentRowNumber;
    private final String CURRENT_ROW_KEY = "current.row.number";

    @Override
    public void open(ExecutionContext executionContext) throws ItemStreamException {

        try {
            fileInputStream = new FileInputStream(filePath);
            workbook = WorkbookFactory.create(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);
            this.rowCursor = sheet.iterator();

            // 동일 배치 파라미터에 대해 특정 키 값 "current.row.number"의 값이 존재한다면 초기화
            if (executionContext.containsKey(CURRENT_ROW_KEY)) {
                currentRowNumber = executionContext.getInt(CURRENT_ROW_KEY);
            }

            // 위의 값을 가져와 이미 실행한 부분은 건너 뜀
            for (int i = 0; i < currentRowNumber && rowCursor.hasNext(); i++) {
                rowCursor.next();
            }

        } catch (IOException e) {
            throw new ItemStreamException(e);
        }
    }

    @Override
    public Row read() {

        if (rowCursor != null && rowCursor.hasNext()) {
            currentRowNumber++;
            return rowCursor.next();
        } else {
            return null;
        }
    }

    @Override
    public void update(ExecutionContext executionContext) throws ItemStreamException {
        executionContext.putInt(CURRENT_ROW_KEY, currentRowNumber);
    }

    @Override
    public void close() throws ItemStreamException {

        try {
            if (workbook != null) {
                workbook.close();
            }
            if (fileInputStream != null) {
                fileInputStream.close();
            }
        } catch (IOException e) {
            throw new ItemStreamException(e);
        }
    }
}

 

controller를 통해 배치 실행

@GetMapping("/fourth")
public String fourthApi(@RequestParam("value") String value) throws Exception{

    JobParameters jobParameters = new JobParametersBuilder()
            .addString("date",value)
            .toJobParameters();

    jobLauncher.run(jobRegistry.getJob("fourthJob"), jobParameters);

    return "ok";
}

 

동작 확인

 

아래 writer 작업 수행

 

DB에서 값 확인