Spring JdbcTemplate.batchUpdate()

在此页面上，我们将学习使用Spring JdbcTemplate.batchUpdate()方法。batchUpdate()方法使用批处理发出多个 SQL。batchUpdate()接受参数如下。
1.

int[] batchUpdate(String... sql) throws DataAccessException 

使用批处理在单个 JDBC 语句上发出多个 SQL 更新。如果 JDBC 驱动程序不支持批处理更新，则该方法将回退到单个语句上的单独更新。
2.

int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException 

使用批量更新和BatchPreparedStatementSetter 设置值在单个PreparedStatement上发出多个更新语句。如果 JDBC 驱动程序不支持批量更新，该方法将回退到单个PreparedStatement上的单独更新。
3.

<T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException 

使用提供的 SQL 语句和提供的参数集合执行多个批处理。每批应具有“批次大小”中标明的大小。
4.

int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException 

使用提供的 SQL 语句和提供的参数批处理执行批处理。
5.

int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException 

使用提供的 SQL 语句和提供的参数批处理执行批处理。

参数：
sql定义批处理操作中使用的 SQL 查询。
pss是设置参数的对象。
batchArgs是包含查询参数批处理的对象数组列表。
batchSize 是批量大小。

返回：
该方法返回受每个语句影响的行数的数组。

抛出：
如果发布更新时出现任何问题，该方法将抛出DataAccessException。
 

使用Spring Boot的示例

PersonDAO.java

package com.concretepage;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Repository;

@Repository
public class PersonDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // batchUpdate(String... sql)
    public void addPersonsUsingBatch1(List<Person> list) {
        String sql1 = "insert into person (name, age) values ('" + list.get(0).getName() + "', " + list.get(0).getAge() + ")";
        String sql2 = "insert into person (name, age) values ('" + list.get(1).getName() + "', " + list.get(1).getAge() + ")";
        int[] insertedRows = jdbcTemplate.batchUpdate(sql1, sql2);
    }

    // batchUpdate(String sql, BatchPreparedStatementSetter pss)
    public void addPersonsUsingBatch2(List<Person> list) {
        String sql = "insert into person (name, age) values (?, ?)";
        int[] insertedRows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, list.get(i).getName());
                ps.setInt(2, list.get(i).getAge());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
    }

    // batchUpdate(String sql, Collection&lt;T&gt; batchArgs, int batchSize, ParameterizedPreparedStatementSetter&lt;T&gt; pss)
    public void addPersonsUsingBatch3(List<Person> list) {
        String sql = "insert into person (name, age) values (?, ?)";
        int[][] insertedRows = jdbcTemplate.batchUpdate(sql, list, 2,
                new ParameterizedPreparedStatementSetter<Person>() {
            @Override
            public void setValues(PreparedStatement ps, Person p) throws SQLException {
                ps.setString(1, p.getName());
                ps.setInt(2, p.getAge());
            }
        });
    }

    // batchUpdate(String sql, List<Object[]> batchArgs)
    public void addPersonsUsingBatch4(List<Person> list) {
        String sql = "insert into person (name, age) values (?, ?)";
        Object[] ob1 = {list.get(0).getName(), list.get(0).getAge()};
        Object[] ob2 = {list.get(1).getName(), list.get(1).getAge()};
        int[] insertedRows = jdbcTemplate.batchUpdate(sql, Arrays.asList(ob1, ob2));
    }

    // batchUpdate(String sql, List&lt;Object[]&gt; batchArgs, int[] argTypes)
    public void addPersonsUsingBatch5(List<Person> list) {
        String sql = "insert into person (name, age) values (?, ?)";
        Object[] ob1 = {list.get(0).getName(), list.get(0).getAge()};
        Object[] ob2 = {list.get(1).getName(), list.get(1).getAge()};
        int[] argTypes = {java.sql.Types.CHAR, java.sql.Types.INTEGER};
        int[] insertedRows = jdbcTemplate.batchUpdate(sql, Arrays.asList(ob1, ob2), argTypes);
    }
}

Person.java

package com.concretepage;

public class Person {

    private int id;
    private String name;
    private int age;

    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }

    @Override
    public String toString() {
        return id + " - " + name + " - " + age;
    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/concretepage?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MySQL 表：person

CREATE TABLE `person` (
	`id` INT(5) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`age` INT(3) NOT NULL,
	PRIMARY KEY (`id`)
) 

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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.concretepage</groupId>
    <artifactId>soap-ws</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>spring-demo</name>
    <description>Spring Demo Application</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.4</version>
        <relativePath />
    </parent>
    <properties>
        <java.version>16</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

MyApplication.java

package com.concretepage;

import java.util.Arrays;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

@SpringBootApplication
public class MyApplication {

    public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(MyApplication.class, args);
        PersonDAO personDAO = ctx.getBean(PersonDAO.class);
        personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 1", 21), new Person("Shiva 1", 31)));
        personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 2", 22), new Person("Shiva 2", 32)));
        personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 3", 23), new Person("Shiva 3", 33)));
        personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 4", 24), new Person("Shiva 4", 34)));
        personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 5", 25), new Person("Shiva 5", 35)));
    }
}

以下行将插入到数据库中。

参考

类 Jdbc 模板

下载源代码

spring-jdbctemplate-batchupdate.zip