1. Custom PreparedStatementSetter
Refer to my other blog for details:
Spring Batch reading database – JdbcCursorItemReader (35) – Programmer Sought
2. Project example
1. Project example
2. Code implementation
BatchMain.java:
package com.xj.demo29;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
* @Author : xjfu
* @Date : 2021/10/26 20:01
* @Description : Custom PreparedStatementSetter for demo29 JdbcCursorItemReader
*/
public class BatchMain {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("demo29/job/demo29-job.xml");
//Job launcher for Spring Batch,
JobLauncher launcher = (JobLauncher) context. getBean("jobLauncher");
//A job configured in batch.xml
Job job = (Job)context. getBean("billJob");
try {
//Start executing this job and get the processing result (job to be run, job parameter object)
JobExecution result = launcher.run(job, new JobParametersBuilder()
.addLong("time",System.currentTimeMillis())//Mainly to prevent "A job instance already exists and is complete for parameters" exception
.toJobParameters());
System.out.println(result.toString());
}catch (Exception e){
e.printStackTrace();
}
}
}
CreditBill.java:
package com.xj.demo29;
/**
* @Author : xjfu
* @Date : 2021/10/26 19:27
* @Description :
*/
public class CreditBill {
//Bank card account ID
private String accountID = "";
//Cardholder's Name
private String name = "";
//Amount of consumption
private double amount = 0;
// consumption date
private String date = "";
//Consumer sites
private String address = "";
public String getAccountID() {
return accountID;
}
public void setAccountID(String accountID) {
this.accountID = accountID;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getAmount() {
return amount;
}
public void setAmount(double amount) {
this.amount = amount;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return this.accountID + "," + this.name + "," + this.amount + "," + this.date + "," + this.address;
}
}
CreditBillPreparedStatementSetter.java:
package com.xj.demo29;
import org.springframework.jdbc.core.PreparedStatementSetter;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class CreditBillPreparedStatementSetter implements
PreparedStatementSetter {
public void setValues(PreparedStatement ps) throws SQLException {
//Set the first parameter here to "5"
ps.setString(1, "5");
}
}
CreditBillProcessor.java:
package com.xj.demo29;
import org.springframework.batch.item.ItemProcessor;
public class CreditBillProcessor implements
ItemProcessor<CreditBill, CreditBill> {
public CreditBill process(CreditBill bill) throws Exception {
System.out.println(bill.toString());
return bill;
}
}
CreditBillRowMapper.java:
package com.xj.demo29;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Author : xjfu
* @Date : 2023/7/17 01:31
* @Description : Custom RowMapper implements CreditBillRowMapper, converts the given result set ResultSet into a CreditBill object
*/
public class CreditBillRowMapper implements RowMapper<CreditBill> {
public CreditBill mapRow(ResultSet rs, int rowNum) throws SQLException {
CreditBill bill = new CreditBill();
bill.setAccountID(rs.getString("ACCOUNTID"));
bill.setAddress(rs.getString("ADDRESS"));
bill.setAmount(rs.getDouble("AMOUNT"));
bill.setDate(rs.getString("DATE"));
bill.setName(rs.getString("NAME"));
return bill;
}
}
DummyCreditItemWriter.java:
package com.xj.demo29;
import org.springframework.batch.item.ItemWriter;
import java.util.ArrayList;
import java.util.List;
public class DummyCreditItemWriter implements ItemWriter<CreditBill> {
public List<CreditBill> creditBills = new ArrayList<CreditBill>();
public void write(List<? extends CreditBill> items) throws Exception {
creditBills. addAll(items);
}
public List<CreditBill> getCredits() {
return credit Bills;
}
}
demo29-job.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:batch="http://www.springframework.org/schema/batch"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/ batch http://www.springframework.org/schema/batch/spring-batch.xsd">
<!--Import file-->
<import resource="classpath:demo29/job/demo29-jobContext.xml"/>
<!--Define a job named billJob-->
<batch:job id="billJob">
<!--Define a job step named billStep-->
<batch:step id="billStep">
<batch:tasklet transaction-manager="transactionManager">
<!--Define read, process, and write operations, and stipulate that every two pieces of data are processed, and a write operation is performed, which can improve the efficiency of writing -->
<batch:chunk reader="jdbcParameterItemReader" processor="creditBillProcessor" writer="creditItemWriter" commit-interval="2">
</batch:chunk>
</batch:tasklet>
</batch:step>
</batch:job>
</beans>
demo29-jobContext.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/ batch http://www.springframework.org/schema/batch/spring-batch.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context .xsd">
<!--Introduce configuration parameters-->
<context:property-placeholder location="classpath:/demo29/properties/demo29-batch-mysql.properties" />
<!--
data-source: Define the data source, default dataSource
transaction-manager: defines the transaction manager
isolation-level-for-create: Define the transaction isolation level when creating a Job Execution, to avoid multiple Job Executions executing a Job Instance, the default is SERIALIZABLE
table_prefix: The prefix of the database table used by the definition is BATCH_, the default BATCH_
max-varchar-length: Define the maximum length of varchar as 1000, the default value is 2500
-->
<batch: job-repository
id="jobRepository"
data-source="dataSource"
transaction-manager="transactionManager"
isolation-level-for-create="SERIALIZABLE"
table-prefix="BATCH_"
max-varchar-length="1000"/>
<!--Database transaction manager-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--Define the job scheduler to start the job-->
<bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
<!--Inject jobRepository-->
<property name="jobRepository" ref="jobRepository"/>
</bean>
<!--Data source-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>${datasource.driver}</value>
</property>
<property name="url">
<value>${datasource.url}</value>
</property>
<property name="username" value="${datasource.username}"></property>
<property name="password" value="${datasource.password}"></property>
</bean>
<!-- Parameterized read db -->
<bean id="jdbcParameterItemReader" scope="step"
class="org.springframework.batch.item.database.JdbcCursorItemReader">
<!--Specify the data source for access-->
<property name="dataSource" ref="dataSource"/>
<!--Specify the query SQL statement -->
<property name="sql" value="select ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS from t_credit where id between 1 and ? "/>
<property name="rowMapper" ref="custCreditRowMapper" />
<!--Use a custom PreparedStatementSetter to set the parameters required by the SQL statement -->
<property name="preparedStatementSetter" ref="custPreparedStatementSetter"/>
</bean>
<!--Custom PreparedStatementSetter-->
<bean id="custPreparedStatementSetter" class="com.xj.demo29.CreditBillPreparedStatementSetter"/>
<!--Custom RowMapper-->
<bean id="custCreditRowMapper" class="com.xj.demo29.CreditBillRowMapper"/>
<!--Processing class-->
<bean id="creditBillProcessor" scope="step" class="com.xj.demo29.CreditBillProcessor"/>
<!--Writing class-->
<bean id="creditItemWriter" class="com.xj.demo29.DummyCreditItemWriter"/>
</beans>
create-tables-mysql.sql:
DROP TABLE IF EXISTS t_credit;
DROP TABLE IF EXISTS t_destcredit;
CREATE TABLE t_credit
(ID VARCHAR(10),
ACCOUNTID VARCHAR(20),
NAME VARCHAR(10),
AMOUNT NUMERIC(10,2),
DATE VARCHAR(20),
ADDRESS VARCHAR(128),
primary key (ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t_destcredit
(ID VARCHAR(10),
ACCOUNTID VARCHAR(20),
NAME VARCHAR(10),
AMOUNT NUMERIC(10,2),
DATE VARCHAR(20),
ADDRESS VARCHAR(128),
primary key (ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t_credit(ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS) VALUES('1','4047390012345678','tom',100.00,'2013-2-2 12:00:08 ','Lu Jia Zui road');
INSERT INTO t_credit(ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS) VALUES('2','4047390012345678','tom',320.00,'2013-2-3 10:35:21 ','Lu Jia Zui road');
INSERT INTO t_credit(ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS) VALUES('3','4047390012345678','tom',674.70,'2013-2-6 16:26:49 ','South Linyi road');
INSERT INTO t_credit(ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS) VALUES('4','4047390012345678','tom',793.20,'2013-2-9 15:15:37 ','Longyang road');
INSERT INTO t_credit(ID,ACCOUNTID,NAME,AMOUNT,DATE,ADDRESS) VALUES('5','4047390012345678','tom',360.00,'2013-2-11 11:12:38 ','Longyang road');
demo29-batch-mysql.properties:
datasource.driver=com.mysql.jdbc.Driver datasource.url=jdbc:mysql://127.0.0.1:3306/spring_batch_demo1?serverTimezone=UTC datasource.username=root datasource.password=12345
3. Running results
