【Spring Data Access】NamedParameterJdbcTemplate 批量插入

NamedParameterJdbcTemplate批量插入

NamedParameterJdbcTemplate类封装了JdbcTemplate，在此基础上提供了基于parameter的statement,

我们先了解一下NamedParameterJdbcTemplate提供有别于JdbcTemplate的批量操作接口(其实是实现了NamedParameterJdbcOperations接口)，下面是方法的定义

/**
 * Executes a batch using the supplied SQL statement with the batch of supplied arguments.
 * @param sql the SQL statement to execute
 * @param batchValues the array of Maps containing the batch of arguments for the query
 * @return an array containing the numbers of rows affected by each update in the batch
 */
int[] batchUpdate(String sql, Map<String, ?>[] batchValues);

/**
 * Execute a batch using the supplied SQL statement with the batch of supplied arguments.
 * @param sql the SQL statement to execute
 * @param batchArgs the array of {@link SqlParameterSource} containing the batch of arguments for the query
 * @return an array containing the numbers of rows affected by each update in the batch
 */
int[] batchUpdate(String sql, SqlParameterSource[] batchArgs);

上面的方法提供两种方式来给定parameters,这里我们演示第二个方法，对于SqlParameterSource对象的构造，我们可以用SqlParameterSourceUtils这个工具类来辅助生成对应的数组对象.

示例代码如下:

1 Config.java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

/**
 * @author jiangjian
 */
@Configuration
@ComponentScan
@PropertySource("classpath:jdbc.properties")
public class Config {
    @Autowired
    private Environment env;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        return dataSource;
    }

    /**
     * NOTE: 注意这里我们配置的是NamedParameterJdbcTemplate,而不是JdbcTemplate
     * */
    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(dataSource());
    }
}

2 jdbc.properties文件

spring.datasource.url=jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&autoReconnectForPools=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

3 User类

public class User {
    private Long id;
    private String name;

    public User() {
    }

    public User(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    //..省略get set方法
     
    
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

4 主类 NamedParameterJdbcTemplateBatchOperationSample

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

import java.util.Arrays;
import java.util.List;

/**
 * @author jiangjian
 */
public class NamedParameterJdbcTemplateBatchOperationSample {
    public static void main(String[] args) {
        ApplicationContext ac = new AnnotationConfigApplicationContext(Config.class);

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = ac.getBean(NamedParameterJdbcTemplate.class);

        namedParameterJdbcTemplate.getJdbcOperations().execute("drop table if exists user  ");
        namedParameterJdbcTemplate.getJdbcOperations().execute("create table user(id int auto_increment primary key, name varchar(40))");
        List<User> users = Arrays.asList(new User("Alice"), new User("Bob"));

        namedParameterJdbcTemplate.batchUpdate("insert into user(name) values (:name)", SqlParameterSourceUtils.createBatch(users));

        List<User> findUsers = namedParameterJdbcTemplate.query("select * from user", (rs, rowNum) -> new User(rs.getLong(1), rs.getString(2)));
        findUsers.forEach(System.out::println);

        namedParameterJdbcTemplate.getJdbcOperations().execute("drop table user");
    }
}