java 批处理sql语句

Statement实现批处理：

优点：能够处理多种不同结构的sql语句

缺点：不能预处理，执行效率较差。对于参数不同的同一条sql语句需要多次调用addBatch()

package com.itheima.batch;

import java.sql.Connection;
import java.sql.Statement;

import org.junit.Test;

import com.itheima.util.DBUtil;


public class StatementBatch {

    /*  mysql数据库：
        create database batch;
        use batch;
        create table mybatch(
            id int primary key auto_increment,
            name varchar(50)
        );
        insert into mybatch values (null, '1');
        insert into mybatch values (null, '2');
        insert into mybatch values (null, '3');
        insert into mybatch values (null, '4');
     */
    @Test
    public void statementBatch() {
        Connection conn = null;
        Statement stat = null;
        try{
            conn = DBUtil.getConn();
            stat = conn.createStatement();
            stat.addBatch("create database batch");
            stat.addBatch("use batch");
            stat.addBatch("create table mybatch( id int primary key auto_increment, name varchar(50) )");
            stat.addBatch("insert into mybatch values (null, '1')");
            stat.addBatch("insert into mybatch values (null, '2')");
            stat.addBatch("insert into mybatch values (null, '3')");
            stat.addBatch("insert into mybatch values (null, '4')");
            stat.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stat, null);
        }
    }
}

PreparedStatement实现批处理：

优点：能够预处理，执行效率高；参数不同的同一条sql语句执行简便

缺点：只能批处理参数不同的同一条sql语句

[java] view plain copy

package com.itheima.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import com.itheima.util.DBUtil;

public class PrepBatch {

    @Test
    public void prepBatch() {

        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConn();
            ps = conn.prepareStatement("insert into mybatch value(null, ?)");
            for(int i = 0; i < 1000; i++) {
                ps.setString(1, "name" + i);
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }
}