Spring-batch processing MySQL data and saving it to a CSV file

1 Introduction

With spring batch, we implemented a simple requirement to read the user table data from MySQL, and output the results to a CSV file based on the age of birthday calculation.

1.1 Preparing tables and data

user test;DROP TABLE IF EXISTS `test_user`;CREATE TABLE  `test_user` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(45) NOT NULL default '',  `birthday` datetime default NULL,  `age` int default 0,  PRIMARY KEY  (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO test_user(name, birthday, age) VALUES('小明', '1993-03-09', 0);INSERT INTO test_user(name, birthday, age) VALUES('Jack', '1973-03-09', 0);INSERT INTO test_user(name, birthday, age) VALUES('Tom', '1963-03-09', 0);INSERT INTO test_user(name, birthday, age) VALUES('齐天大圣', '1983-03-09', 0);INSERT INTO test_user(name, birthday, age) VALUES('星知', '2003-03-09', 0);

2 Implementing the 2.1 project directory

2.2 Entity Classes

publicclass TestUser {    private Integer id;    private String name;    private Date birthday;    private Integer age;    // set/get...}

2.3 Itemreader

Testuserconfig.java

@Bean PublicJdbccursoritemreader<testuser>Itemreader(){FinalDrivermanagerdatasource DataSource =New Drivermanagerdatasource(); DataSource.Setdriverclassname("Com.mysql.jdbc.Driver"); DataSource.SetUrl("Jdbc:mysql://mysql-server:3306/test"); DataSource.Setusername("r00t"); DataSource.SetPassword("r00t"); jdbccursoritemreader<testuser> reader =NewJdbccursoritemreader<testuser> (); Reader.Setdatasource(DataSource); Reader.SetSQL("SELECT ID, Name, birthday from Test_user"); Reader.Setrowmapper(New Userrowmapper());returnReader;}

2.4 Itemprocessor

Testuserconfig.java

@BeanpublicitemProcessor() {    returnnewTestUserItemProcessor();}

Testuseritemprocessor.java

publicclassimplements ItemProcessor<TestUser, TestUser> {    @Override    publicprocessthrows Exception {        Calendar cal1 = Calendar.getInstance();        Calendar cal2 = Calendar.getInstance();        cal1.setTime(testUser.getBirthday());        testUser.setAge(cal2.get(Calendar.YEAR) - cal1.get(Calendar.YEAR));        return testUser;    }}

2.5 Itemwriter

Testuserconfig.java

@Bean PublicFlatfileitemwriter<testuser>Itemwriter() {flatfileitemwriter<testuser> Itemwriter =NewFlatfileitemwriter<> (); String userhome = System.GetProperty("User.home"); Resource Outputresource =New Filesystemresource(Userhome +"/output/demo04/test_user.csv"); Itemwriter.Setresource(Outputresource); Itemwriter.Setlineaggregator(NewDelimitedlineaggregator<testuser> () {{Setdelimiter(",");Setfieldextractor(NewBeanwrapperfieldextractor<testuser> () {{Setnames(NewString[] {"id","Name","Age"});    }}); }});returnItemwriter;}

2.6 Job & Step

Testuserconfig.java

@Bean PublicStepStep1(jdbccursoritemreader<testuser> Itemreader, Testuseritemprocessor itemprocessor, FlatFileItemWriter< Testuser> itemwriter) {returnSteps.Get("Step1"). <testuser, testuser>Chunk(Ten)        .Reader(Itemreader).Processor(Itemprocessor).writer(Itemwriter).Build();}@Bean PublicJobJob1(Step Step1) {returnJobs.Get("Job1")        .Incrementer(New Runidincrementer())        .Flow(STEP1).End()        .Build();}

2.7 Testing

@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(classes = {TestUserConfig.class})publicclass TestUserTest {    @Autowired    private JobLauncherTestUtils jobLauncherTestUtils;    @Test    publicvoidgivenTaskletsJob_whenJobEnds_thenStatusCompletedthrows Exception {        JobExecution jobExecution = jobLauncherTestUtils.launchJob();        assertEquals(ExitStatus.COMPLETED, jobExecution.getExitStatus());    }}

2.8 Verification

Generated under the user directory C:\Users\{your-name}\output\demo04\test_user.csv , the contents of the file are as follows

6,小明,257,Jack,458,Tom,559,齐天大圣,3510,星知,15

3 Summary

This example allows you to learn about Java configuration sringbatch, read MySQL, and write CSV. This example is fully implemented Spring-batch

Spring-batch processing MySQL data and saving it to a CSV file