[SpringBatch 실습] 37. DB 조회한 데이터를 .josn 파일로 생성하는 배치 (JsonFileItemWriter)

목표

Customer 테이블에 등록된 테스트 데이터 중 firstname 컬럼이 "A"로 시작하는 데이터를 찾아, 해당 데이터를 json 포맷으로 customer.json 파일을 생성해보자.

 

 

Job 생성

JsonConfiguration.java

package com.project.springbatch._58_writer_json;

import lombok.RequiredArgsConstructor;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.database.JdbcPagingItemReader;
import org.springframework.batch.item.database.Order;
import org.springframework.batch.item.database.support.MySqlPagingQueryProvider;
import org.springframework.batch.item.json.JacksonJsonObjectMarshaller;
import org.springframework.batch.item.json.JsonFileItemWriter;
import org.springframework.batch.item.json.builder.JsonFileItemWriterBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/*
--job.name=jsonWriterJob
*/
@RequiredArgsConstructor
@Configuration
public class JsonWriterConfiguration {

    private final JobBuilderFactory jobBuilderFactory;
    private final StepBuilderFactory stepBuilderFactory;
    private final DataSource dataSource;

    @Bean
    public Job jsonWriterJob() throws Exception {
        return jobBuilderFactory.get("jsonWriterJob")
                .incrementer(new RunIdIncrementer())
                .start(jsonWriterStep1())
                .build();
    }

    @Bean
    public Step jsonWriterStep1() throws Exception {
        return stepBuilderFactory.get("jsonWriterStep1")
                .<Customer, Customer>chunk(10)
                .reader(customJsonWriterItemReader())
                .writer(customJsonWriterItemWriter())
                .build();
    }

    @Bean
    public JdbcPagingItemReader<Customer> customJsonWriterItemReader() {

        JdbcPagingItemReader<Customer> reader = new JdbcPagingItemReader<>();

        reader.setDataSource(this.dataSource);
        reader.setFetchSize(10);
        reader.setRowMapper(new CustomerRowMapper());

        PostgresPagingQueryProvider queryProvider = new PostgresPagingQueryProvider();
        queryProvider.setSelectClause("id, firstName, lastName, birthdate");
        queryProvider.setFromClause("from customer");
        queryProvider.setWhereClause("where firstname like :firstname");

        Map<String, Order> sortKeys = new HashMap<>(1);

        sortKeys.put("id", Order.ASCENDING);
        queryProvider.setSortKeys(sortKeys);
        reader.setQueryProvider(queryProvider);

        HashMap<String, Object> parameters = new HashMap<>();
        parameters.put("firstname", "A%");

        reader.setParameterValues(parameters);

        return reader;
    }

    @Bean
    public JsonFileItemWriter<Customer> customJsonWriterItemWriter() {
        return new JsonFileItemWriterBuilder<Customer>()
                .jsonObjectMarshaller(new JacksonJsonObjectMarshaller<>())
                .resource(new ClassPathResource("customer.json"))
                .name("customerJsonFileItemWriter")
                .build();
    }
}

 

Customer.java

import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.Date;

@Data
@AllArgsConstructor
public class Customer {
    private final long id;
    private final String firstName;
    private final String lastName;
    private final Date birthdate;
}

 

CustomerRowMapper.java

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class CustomerRowMapper implements RowMapper<Customer> {
    @Override
    public Customer mapRow(ResultSet rs, int i) throws SQLException {
        return new Customer(rs.getLong("id"),
                rs.getString("firstName"),
                rs.getString("lastName"),
                rs.getDate("birthdate"));
    }
}

 

 

테스트 데이터 등록

create table customer
(
    id          serial constraint customer_pk primary key,
    "firstname" varchar,
    "lastname"  varchar,
    birthdate   varchar
);

INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (1,'Reed','Edwards','1952-08-16 12:34:53'),(2,'Hoyt','Park','1981-02-18 08:07:58'),(3,'Leila','Petty','1972-06-11 08:43:55'),(4,'Denton','Strong','1989-03-11 18:38:31'),(5,'Zoe','Romero','1990-10-02 13:06:31'),(6,'Rana','Compton','1957-06-09 12:51:11'),(7,'Velma','King','1988-02-02 05:52:25'),(8,'Uriah','Carter','1972-08-31 07:32:05'),(9,'Michael','Graves','1958-04-13 18:47:44'),(10,'Leigh','Stone','1967-06-23 23:41:43');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (11,'Iliana','Glenn','1965-02-27 14:33:56'),(12,'Harrison','Haley','1956-06-28 03:15:41'),(13,'Leonard','Zamora','1956-03-28 15:03:09'),(14,'Hiroko','Wyatt','1960-08-22 23:53:50'),(15,'Cameron','Carlson','1969-05-12 11:10:09'),(16,'Hunter','Avery','1953-11-19 12:52:42'),(17,'Aimee','Cox','1976-10-15 12:56:50'),(18,'Yen','Delgado','1990-02-06 10:25:36'),(19,'Gemma','Peterson','1989-04-02 23:42:09'),(20,'Lani','Faulkner','1970-09-18 17:22:14');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (21,'Iola','Cannon','1954-01-12 16:56:45'),(22,'Whitney','Shaffer','1951-03-19 01:27:18'),(23,'Jerome','Moran','1968-03-16 05:26:22'),(24,'Quinn','Wheeler','1979-06-19 16:24:22'),(25,'Mira','Wilder','1961-12-27 12:11:07'),(26,'Tobias','Holloway','1968-08-13 20:36:19'),(27,'Shaine','Schneider','1958-03-08 09:47:10'),(28,'Harding','Gonzales','1952-04-11 02:06:25'),(29,'Calista','Nieves','1970-02-17 13:29:59'),(30,'Duncan','Norman','1987-09-13 00:54:49');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (31,'Fatima','Hamilton','1961-06-16 14:29:11'),(32,'Ali','Browning','1979-03-27 17:09:37'),(33,'Erin','Sosa','1990-08-23 10:43:58'),(34,'Carol','Harmon','1972-01-14 07:19:39'),(35,'Illiana','Fitzgerald','1970-08-19 02:33:46'),(36,'Stephen','Riley','1954-06-05 08:34:03'),(37,'Hermione','Waller','1969-09-08 01:19:07'),(38,'Desiree','Flowers','1952-06-25 13:34:45'),(39,'Karyn','Blackburn','1977-03-30 13:08:02'),(40,'Briar','Carroll','1985-03-26 01:03:34');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (41,'Chaney','Green','1987-04-20 18:56:53'),(42,'Robert','Higgins','1985-09-26 11:25:10'),(43,'Lillith','House','1982-12-06 02:24:23'),(44,'Astra','Winters','1952-03-13 01:13:07'),(45,'Cherokee','Stephenson','1955-10-23 16:57:33'),(46,'Yuri','Shaw','1958-07-14 15:10:07'),(47,'Boris','Sparks','1982-01-01 10:56:34'),(48,'Wilma','Blake','1963-06-07 16:32:33'),(49,'Brynne','Morse','1964-09-21 01:05:25'),(50,'Ila','Conley','1953-11-02 05:12:57');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (51,'Sharon','Watts','1964-01-09 16:32:37'),(52,'Kareem','Vaughan','1952-04-18 15:37:10'),(53,'Eden','Barnes','1954-07-04 01:26:44'),(54,'Kenyon','Fulton','1975-08-23 22:17:52'),(55,'Mona','Ball','1972-02-11 04:15:45'),(56,'Moses','Cortez','1979-04-24 15:26:46'),(57,'Macy','Banks','1956-12-31 00:41:15'),(58,'Brenna','Mendez','1972-10-02 07:58:27'),(59,'Emerald','Ewing','1985-11-28 21:15:20'),(60,'Lev','Mcfarland','1951-05-20 14:30:07');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (61,'Norman','Tanner','1959-07-29 15:41:45'),(62,'Alexa','Walters','1977-12-06 16:41:17'),(63,'Dara','Hyde','1989-08-04 14:06:43'),(64,'Hu','Sampson','1978-11-01 17:10:23'),(65,'Jasmine','Cardenas','1969-02-15 20:08:06'),(66,'Julian','Bentley','1954-07-11 03:27:51'),(67,'Samson','Brown','1967-10-15 07:03:59'),(68,'Gisela','Hogan','1985-01-19 03:16:20'),(69,'Jeanette','Cummings','1986-09-07 18:25:52'),(70,'Galena','Perkins','1984-01-13 02:15:31');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (71,'Olga','Mays','1981-11-20 22:39:27'),(72,'Ferdinand','Austin','1956-08-08 09:08:02'),(73,'Zenia','Anthony','1964-08-21 05:45:16'),(74,'Hop','Hampton','1982-07-22 14:11:00'),(75,'Shaine','Vang','1970-08-13 15:58:28'),(76,'Ariana','Cochran','1959-12-04 01:18:36'),(77,'India','Paul','1963-10-10 05:24:03'),(78,'Karina','Doyle','1979-12-01 00:05:21'),(79,'Delilah','Johnston','1989-03-04 23:50:01'),(80,'Hilel','Hood','1959-08-22 06:40:48');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (81,'Kennedy','Hoffman','1963-10-14 20:18:35'),(82,'Kameko','Bell','1976-06-08 15:35:54'),(83,'Lunea','Gutierrez','1964-06-07 16:21:24'),(84,'William','Burris','1980-05-01 17:58:23'),(85,'Kiara','Walls','1955-12-27 18:57:15'),(86,'Latifah','Alexander','1980-06-19 10:39:50'),(87,'Keaton','Ward','1964-10-12 16:03:18'),(88,'Jasper','Clements','1970-03-05 00:29:49'),(89,'Claire','Brown','1972-02-11 00:43:58'),(90,'Noble','Morgan','1955-09-05 05:35:01');
INSERT INTO customer (id,firstname,lastname,birthdate) VALUES (91,'Evangeline','Horn','1952-12-28 14:06:27'),(92,'Jonah','Harrell','1951-06-25 17:37:35'),(93,'Mira','Espinoza','1982-03-26 06:01:16'),(94,'Brennan','Oneill','1979-04-23 08:49:02'),(95,'Dacey','Howe','1983-02-06 19:11:00'),(96,'Yoko','Pittman','1982-09-12 02:18:52'),(97,'Cody','Conway','1971-05-26 07:09:58'),(98,'Jordan','Knowles','1981-12-30 02:20:01'),(99,'Pearl','Boyer','1957-10-19 14:26:49'),(100,'Keely','Montoya','1985-03-24 01:18:09');

위 데이터를 읽어와서, json 파일로 생성해보자.

 

 

Job 디버깅

1) customJsonWriterItemReader()

@Bean
public JdbcPagingItemReader<Customer> customJsonWriterItemReader() {

    JdbcPagingItemReader<Customer> reader = new JdbcPagingItemReader<>();

    reader.setDataSource(this.dataSource);
    reader.setFetchSize(10);
    reader.setRowMapper(new CustomerRowMapper());

    PostgresPagingQueryProvider queryProvider = new PostgresPagingQueryProvider();
    queryProvider.setSelectClause("id, firstName, lastName, birthdate");
    queryProvider.setFromClause("from customer");
    queryProvider.setWhereClause("where firstname like :firstname");

    Map<String, Order> sortKeys = new HashMap<>(1);

    sortKeys.put("id", Order.ASCENDING);
    queryProvider.setSortKeys(sortKeys);
    reader.setQueryProvider(queryProvider);

    HashMap<String, Object> parameters = new HashMap<>();
    parameters.put("firstname", "A%");

    reader.setParameterValues(parameters);

    return reader;
}

▶ CustomRowMapper.java 설정과 PostgresPagingQueryProvider 설정을 추가한 후의 JdbcPagingItemReader<Customer> 타입의 reader 객체의 현재 시점의 상태는 아래와 같다.

▶ 파라미터 셋팅한 후의 reader 객체에 paramterValues가 셋팅됨을 확인하였다.

 

2) CustomerRowMapper.java

@Override
public Customer mapRow(ResultSet rs, int i) throws SQLException {
    return new Customer(rs.getLong("id"),
            rs.getString("firstName"),
            rs.getString("lastName"),
            rs.getDate("birthdate"));
}

▶ 조회된 데이터를 Customer 인스턴스에 담아서 리턴해준다.

 

3) customJsonWriterItemWriter()

▶ customJsonWriterItemWriter

객체를 받아 JSON String 으로 변환하는 역할을 한다.

@Bean
public JsonFileItemWriter<Customer> customJsonWriterItemWriter() {
    return new JsonFileItemWriterBuilder<Customer>()
            .jsonObjectMarshaller(new JacksonJsonObjectMarshaller<>())
            .resource(new ClassPathResource("item58/customer.json"))
            .name("customerJsonFileItemWriter")
            .build();
}

▶ jsonFileItemWriter.build()

JacksonJsonObjectMarshaller로 설정되어있다.

 

 

결과

▶ 경로 : .resource(new ClassPathResource("customer.json"))

 

▶ customer.json