Spring boot jdbctemplate example with mysql

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.candid.spring</groupId>
	<artifactId>SpringBoot_batch</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringBoot_batch Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.4.RELEASE</version>
	</parent>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-batch</artifactId>
		</dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
		<finalName>SpringBoot_batch</finalName>
	</build>
</project>

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/BatchDB1
spring.datasource.username=root
spring.datasource.password=root

schema-all.sql

DROP TABLE IF EXISTS Person;

CREATE TABLE Person (
    ID int NOT NULL AUTO_INCREMENT,
    first_name varchar(255) NOT NULL,
    last_name varchar(255),
    PRIMARY KEY (ID)
);

Application.java

package com.candidjava.spring;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration;
@SpringBootApplication
public class Application {
 public static void main(String[] args) {
  SpringApplication.run(Application.class, args);
 }
}

BatchConfiguration.java

package com.candidjava.spring.configuration;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
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.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.ItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.database.JpaItemWriter;
import org.springframework.batch.item.file.FlatFileItemReader;
import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper;
import org.springframework.batch.item.file.mapping.DefaultLineMapper;
import org.springframework.batch.item.file.transform.DelimitedLineTokenizer;
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 org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import com.candidjava.spring.model.Person;

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

 @Bean
 public ItemReader<Person> reader() {
  FlatFileItemReader<Person> reader = new FlatFileItemReader<Person> ();

  reader.setResource(new ClassPathResource("PersonDetail.csv"));
  reader.setLineMapper(new DefaultLineMapper<Person> () {
   {
    setLineTokenizer(new DelimitedLineTokenizer() {
     {
      setNames(new String[] {
       "first_name",
       "last_name",
      });
     }
    });
    setFieldSetMapper(new BeanWrapperFieldSetMapper<Person> () {
     {
      setTargetType(Person.class);
     }
    });
   }
  });
  return reader;
 }



 @Bean
 public ItemWriter<Person> writer(DataSource dataSource, NamedParameterJdbcTemplate jdbcTemplate) {
  JdbcBatchItemWriter<Person> databaseItemWriter = new JdbcBatchItemWriter<> ();
  databaseItemWriter.setDataSource(dataSource);
  databaseItemWriter.setJdbcTemplate(jdbcTemplate);
  databaseItemWriter.setSql("INSERT INTO Person (first_name, last_name) VALUES (:firstName, :lastName)");
  ItemSqlParameterSourceProvider<Person> sqlParameterSourceProvider = studentSqlParameterSourceProvider();
  databaseItemWriter.setItemSqlParameterSourceProvider(sqlParameterSourceProvider);
  return databaseItemWriter;
 }
 private ItemSqlParameterSourceProvider<Person> studentSqlParameterSourceProvider() {
  return new BeanPropertyItemSqlParameterSourceProvider<>();
 }

 @Bean
 public Job importUserJob(JobBuilderFactory jobs, Step step1) {
  return jobs.get("importUserJob")
   .incrementer(new RunIdIncrementer())
   .flow(step1)
   .end()
   .build();
 }

 @Bean
 public Step step1(StepBuilderFactory stepBuilderFactory, ItemWriter<Person> writer, ItemReader<Person> reader) {
  return stepBuilderFactory.get("step1")
   . < Person, Person > chunk(10)
   .reader(reader)
   .writer(writer)
   .build();
 }

}

Person.java

package com.candidjava.spring.model;

public class Person {

 private String lastName;
 private String firstName;

 public Person() {}

 public Person(String firstName, String lastName) {
  this.firstName = firstName;
  this.lastName = lastName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getFirstName() {
  return firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 @Override
 public String toString() {
  return "firstName: " + firstName + ", lastName: " + lastName;
 }

}

Download

Download source code from my github account Click here