Install Redis on Oracle Linux 7+

Spring Batch - Read from Database and print to console


In this example, we will show you how to read from MySQL DB using JdbcPagingItemReader (You can also use JdbcCursorItemReader) and print to console. You can write data into the CSV, XML etc., target systems. 

I will show you very basic example here. However you can customized the example as per your need.

Technology used - 

1) Spring Boot 2.1.1.RELEASE
2) Spring Batch
3) Spring JDBC
4) Spring OXM
5) Lombok 

Create Database Table:

CREATE TABLE `test`.`customer` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(255) NULL,
  `lastName` VARCHAR(255) NULL,
  `birthdate` VARCHAR(255) NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT=1;

Sample Test data

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('1', 'John', 'Doe', '10-10-1952 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('2', 'Amy', 'Eugene', '05-07-1985 17:10:00');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('3', 'Laverne', 'Mann', '11-12-1988 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('4', 'Janice', 'Preston', '19-02-1960 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('5', 'Pauline', 'Rios', '29-08-1977 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('6', 'Perry', 'Burnside', '10-03-1981 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('7', 'Todd', 'Kinsey', '14-12-1998 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('8', 'Jacqueline', 'Hyde', '20-03-1983 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('9', 'Rico', 'Hale', '10-10-2000 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('10', 'Samuel', 'Lamm', '11-11-1999 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('11', 'Robert', 'Coster', '10-10-1972 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('12', 'Tamara', 'Soler', '02-01-1978 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('13', 'Justin', 'Kramer', '19-11-1951 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('14', 'Andrea', 'Law', '14-10-1959 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('15', 'Laura', 'Porter', '12-12-2010 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('16', 'Michael', 'Cantu', '11-04-1999 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('17', 'Andrew', 'Thomas', '04-05-1967 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('18', 'Jose', 'Hannah', '16-09-1950 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('19', 'Valerie', 'Hilbert', '13-06-1966 10:10:10');

INSERT INTO `test`.`customer` (`id`, `firstName`, `lastName`, `birthdate`) VALUES ('20', 'Patrick', 'Durham', '12-10-1978 10:10:10');

Customer.java

@AllArgsConstructor

@NoArgsConstructor

@Builder

@Data

public class Customer {

private Long id;

private String firstName;

private String lastName;

private LocalDateTime birthdate;

}

CustomerRowMapper.java

public class CustomerRowMapper implements RowMapper<Customer> {

private static final DateTimeFormatter DT_FORMAT = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss");

@Override

public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

//@// @formatter:off

return Customer.builder()

.id(rs.getLong("id"))

.firstName(rs.getString("firstName"))

.lastName(rs.getString("lastName"))

.birthdate(LocalDateTime.parse(rs.getString("birthdate"), DT_FORMAT))

.build(); 

// @formatter:on

}

}

JobConfiguration.java

@Configuration

public class JobConfiguration {

@Autowired

private JobBuilderFactory jobBuilderFactory;

@Autowired

private StepBuilderFactory stepBuilderFactory;

@Autowired

private DataSource dataSource;

/*@Bean

public JdbcCursorItemReader<Customer> cursorItemReader(){

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

reader.setSql("SELECT id, firstName, lastName, birthdate FROM customer ORDER BY lastName, firstName");

reader.setDataSource(dataSource);

reader.setFetchSize(100);

reader.setRowMapper(new CustomerRowMapper());

return reader;

}*/

// This is Thread-safe

@Bean

public JdbcPagingItemReader<Customer> pagingItemReader(){

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

reader.setDataSource(this.dataSource);

reader.setFetchSize(10);

reader.setRowMapper(new CustomerRowMapper());

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

sortKeys.put("id", Order.ASCENDING);

MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();

queryProvider.setSelectClause("select id, firstName, lastName, birthdate");

queryProvider.setFromClause("from customer");

queryProvider.setSortKeys(sortKeys);

reader.setQueryProvider(queryProvider);

return reader;

}

@Bean

public ItemWriter<Customer> customerItemWriter(){

return items -> {

for(Customer c : items) {

System.out.println(c.toString());

}

};

}

@Bean

public Step step1() {

return stepBuilderFactory.get("step1")

.<Customer, Customer>chunk(5)

//.reader(cursorItemReader())

.reader(pagingItemReader())

.writer(customerItemWriter())

.build();

}

@Bean

public Job job() {

return jobBuilderFactory.get("job")

.start(step1())

.build();

}

}

DatabaseinputApplication.java

@SpringBootApplication

@EnableBatchProcessing

public class DatabaseinputApplication implements CommandLineRunner{

@Autowired

private JobLauncher jobLauncher;

@Autowired

private Job job;

public static void main(String[] args) {

SpringApplication.run(DatabaseinputApplication.class, args);

}

@Override

public void run(String... args) throws Exception {

/* JobParameters jobParameters = new JobParametersBuilder()

.addString("time", String.valueOf(System.currentTimeMillis()))

.addDate("date", new Date())

.toJobParameters();*/

JobParameters jobParameters = new JobParametersBuilder()

                .addDate("date", new Date())

                .addLong("time",System.currentTimeMillis()).toJobParameters();

JobExecution execution = jobLauncher.run(job, jobParameters);

System.out.println("STATUS :: "+execution.getStatus());

}

}