JAVA数据库批量操作

连线北海
连线北海
管理员
201
文章
评论字数 1234阅读4分6秒

1 Batch

Preparedstatements可以解决sql注入，可以大大提升性能，只编译一次。

2 JdbcTemplate

  1. As we know, it is meaningless to execute batch query，There is only batchUpdate provided in JdbcTemplate.
/** 
 * @param sql defining an array of SQL statements that will be executed. 
 */  
public int[] batchUpdate(final String[] sql);  
  
/** 
 * @param sql defining PreparedStatement that will be reused. 
 * @param pss object to set parameters on the PreparedStatement 
 */  
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss);  
  
/** 
 * @param sql the SQL statement to execute 
 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 
 */  
public int[] batchUpdate(String sql, List<Object[]> batchArgs);  
  
/** 
 * @param sql the SQL statement to execute. 
 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 
 * @param argTypes SQL types of the arguments 
 */  
public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes);  
  
/** 
 * @param sql the SQL statement to execute. 
 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 
 * @param batchSize batch size 
 * @param pss ParameterizedPreparedStatementSetter to use 
 */  
public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss);

package edu.xmu.jdbc.dao;  
  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.List;  
  
import org.springframework.jdbc.core.BatchPreparedStatementSetter;  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;  
import org.springframework.jdbc.core.support.JdbcDaoSupport;  
  
import edu.xmu.jdbc.bean.Student;  
  
public class BatchDao extends JdbcDaoSupport {  
  
    public int[] batchExecution() {  
    JdbcTemplate jdbcTemplate = getJdbcTemplate();  
  
    String sql = "update student set name='aaa' where id=1";  
    String sql2 = "update student set name='bbb' where id=2";  
    String sql3 = "update student set name='ccc' where id=3";  
    final String[] sqls = new String[] { sql, sql2, sql3 };  
  
    return jdbcTemplate.batchUpdate(sqls);  
    }  
  
    public int[] batchCreate(final List<Student> studentList) {  
    JdbcTemplate jdbcTemplate = getJdbcTemplate();  
    String sql = "insert into student(name, age) values(?, ?)";  
  
    int[] updateCounts = jdbcTemplate.batchUpdate(sql,  
        new BatchPreparedStatementSetter() {  
            public void setValues(PreparedStatement ps, int i) throws SQLException {  
              Student student = studentList.get(i);  
              ps.setString(1, student.getName());  
              ps.setInt(2, student.getAge());  
            }  
  
            public int getBatchSize() {  
              return studentList.size();  
            }  
        });  
  
    return updateCounts;  
    }  
  
    public int[] batchCreate2(final List<Student> studentList) {  
    JdbcTemplate jdbcTemplate = getJdbcTemplate();  
    String sql = "insert into student(name, age) values(?, ?)";  
  
    List<Object[]> batchArgs = new ArrayList<Object[]>();  
  
    for (Student student : studentList) {  
        String name = student.getName();  
        int age = student.getAge();  
  
        Object[] objects = new Object[] { name, age };  
        batchArgs.add(objects);  
    }  
  
    return jdbcTemplate.batchUpdate(sql, batchArgs);  
    }  
  
    public int[] batchCreate3(final List<Student> studentList) {  
    JdbcTemplate jdbcTemplate = getJdbcTemplate();  
    String sql = "insert into student(name, age) values(?, ?)";  
  
    List<Object[]> batchArgs = new ArrayList<Object[]>();  
  
    for (Student student : studentList) {  
        String name = student.getName();  
        int age = student.getAge();  
  
        Object[] objects = new Object[] { name, age };  
        batchArgs.add(objects);  
    }  
  
    return jdbcTemplate.batchUpdate(sql, batchArgs, new int[] {  
        java.sql.Types.VARCHAR, java.sql.Types.INTEGER });  
    }  
  
    public int[][] batchCreate4(final List<Student> studentList) {  
    JdbcTemplate jdbcTemplate = getJdbcTemplate();  
    String sql = "insert into student(name, age) values(?, ?)";  
  
    List<Object[]> batchArgs = new ArrayList<Object[]>();  
  
    for (Student student : studentList) {  
        String name = student.getName();  
        int age = student.getAge();  
  
        Object[] objects = new Object[] { name, age };  
        batchArgs.add(objects);  
    }  
  
    return jdbcTemplate.batchUpdate(sql, studentList, studentList.size(),  
        new ParameterizedPreparedStatementSetter<Student>() {  
            public void setValues(PreparedStatement ps, Student student)  
                throws SQLException {  
            ps.setString(1, student.getName());  
            ps.setInt(2, student.getAge());  
            }  
        });  
    }  
  
}  

package edu.xmu.jdbc.dao;  
  
import java.util.ArrayList;  
import java.util.List;  
  
import org.junit.After;  
import org.junit.Before;  
import org.junit.Test;  
import org.springframework.jdbc.datasource.DriverManagerDataSource;  
  
import edu.xmu.jdbc.bean.Student;  
  
public class BatchDaoTest {  
    private DriverManagerDataSource dataSource;  
    private String url = "jdbc:mysql://localhost:3306/jdbctest";  
    private String username = "root";  
    private String password = "root";  
  
    private BatchDao dao;  
  
    @Before  
    public void setUp() {  
    dataSource = new DriverManagerDataSource(url, username, password);  
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");  
  
    dao = new BatchDao();  
    dao.setDataSource(dataSource);  
    }  
  
    @Test  
    public void batchCreateTest() {  
    List<Student> studentList = new ArrayList<Student>();  
    Student student = new Student("Davy", 24);  
    studentList.add(student);  
    student = new Student("Jones", 25);  
    studentList.add(student);  
  
    int[] rowCounts = dao.batchCreate(studentList);  
  
    System.out.println("start batchCreateTest");  
    for (int i : rowCounts) {  
        System.out.println(i + " row affected.");  
     }       
    }  
  
    @Test  
    public void batchCreate2Test() {  
    List<Student> studentList = new ArrayList<Student>();  
    Student student = new Student("Davy", 24);  
    studentList.add(student);  
    student = new Student("Jones", 25);  
    studentList.add(student);  
  
    int[] rowCounts = dao.batchCreate2(studentList);  
  
    System.out.println("start batchCreate2Test");  
    for (int i : rowCounts) {  
        System.out.println(i + " row affected.");  
     }       
    }  
  
    @Test  
    public void batchCreate3Test() {  
    List<Student> studentList = new ArrayList<Student>();  
    Student student = new Student("Davy", 24);  
    studentList.add(student);  
    student = new Student("Jones", 25);  
    studentList.add(student);  
  
    int[] rowCounts = dao.batchCreate3(studentList);  
  
    System.out.println("start batchCreate3Test");  
    for (int i : rowCounts) {  
        System.out.println(i + " row affected.");  
     }        
    }  
  
    @Test  
    public void batchCreate4Test() {  
    List<Student> studentList = new ArrayList<Student>();  
    Student student = new Student("Davy", 24);  
    studentList.add(student);  
    student = new Student("Jones", 25);  
    studentList.add(student);  
  
    int[][] rowCounts = dao.batchCreate4(studentList);  
  
    System.out.println("start batchCreateTest");  
    for (int i = 0; i < rowCounts.length; i++) {  
        int[] list = rowCounts[i];  
        for (int j = 0; j < list.length; j++) {  
        int count = list[j];  
        System.out.println("Batch " + i + " execute. " + count + " row affected.");  
        }  
     }        
    }  
  
    @After  
    public void tearDown() {  
    }  
}

Comments:

    Still have some question about the ParameterizedPreparedStatementSetter approach.

    Why it will return int[][] instead of int[]?

 

  1. SimpleJdbcTemplate provided an additional  approach for batchUpdate

public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs);

 

As we can see, we can pass a list of SqlParameterSource as batch parameters.

And now since SimpleJdbcTemplate is depreciated, we still can not find alternative method in JdbcTemplate, And why?

package edu.xmu.jdbc.dao;  
  
import java.util.List;  
  
import org.springframework.jdbc.core.namedparam.SqlParameterSource;  
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;  
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;  
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;  
  
import edu.xmu.jdbc.bean.Student;  
  
@SuppressWarnings("deprecation")  
public class SimpleBatchDao extends SimpleJdbcDaoSupport {  
  
    public int[] batchCreate(List<Student> studentList) {  
    String sql = "insert into student(name, age) values(:name, :age)";  
  
    SimpleJdbcTemplate simpleJdbcTemplate = getSimpleJdbcTemplate();  
  
    SqlParameterSource[] paramSources = SqlParameterSourceUtils  
        .createBatch(studentList.toArray());  
  
    return simpleJdbcTemplate.batchUpdate(sql, paramSources);  
    }  
}  

package edu.xmu.jdbc.dao;  
  
import java.util.ArrayList;  
import java.util.List;  
  
import org.junit.After;  
import org.junit.Before;  
import org.junit.Test;  
import org.springframework.jdbc.datasource.DriverManagerDataSource;  
  
import edu.xmu.jdbc.bean.Student;  
  
public class SimpleBatchDaoTest {  
    private DriverManagerDataSource dataSource;  
    private String url = "jdbc:mysql://localhost:3306/jdbctest";  
    private String username = "root";  
    private String password = "root";  
  
    private SimpleBatchDao dao;  
  
    @Before  
    public void setUp() {  
    dataSource = new DriverManagerDataSource(url, username, password);  
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");  
  
    dao = new SimpleBatchDao();  
    dao.setDataSource(dataSource);  
    }  
  
    @Test  
    public void batchCreateTest() {  
    List<Student> studentList = new ArrayList<Student>();  
    Student student = new Student("Davy", 24);  
    studentList.add(student);  
    student = new Student("Jones", 25);  
    studentList.add(student);  
  
    int[] rowCounts = dao.batchCreate(studentList);  
  
    System.out.println("start batchCreateTest");  
    for (int i : rowCounts) {  
        System.out.println(i + " row affected.");  
     }       
    }  
  
    @After  
    public void tearDown() {  
    }  
}
  1. BatchUpdate and auto-generated keys.

Still, we may wonder that how can we get the auto-generated keys when execute batchUpdate?

Sadly, there is no provided solution for this.<See ref-link-2>

 

Reference Links:

1) http://examples.oreilly.com/0636920025405/justspring-data-src/src/main/java/com/madhusudhan/jsd/adv/JdbcTemplateBatchTest.java

2) http://stackoverflow.com/questions/6272272/batchsqlupdate-how-to-get-auto-generated-keys testify that there is no auto-generated key fetching solution. Also, it offers a good solution solving this.

继续阅读
weinxin
微信公众号
分享IT信息技术、北海生活的网站。提供北海本地化的信息技术服务。
 最后更新：2018-10-7
连线北海
  • 本文由 连线北海 发表于2018年9月3日 22:21:40
  • 除非特殊声明，本站文章均为原创，转载请务必保留本文链接
匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:
确定