20160322 javaweb 之jdbc-- mysql 批处理

package com.dzq.batch;

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

import com.itheima.util.JDBCUtils;

/*
    create database day10batch;
    use day10batch;
    create table batchDemo(
        id int primary key auto_increment,
        name varchar(20)
    );
    insert into batchDemo values(null,'aaaa');
    insert into batchDemo values(null,'bbb');
    insert into batchDemo values(null,'cc');
    insert into batchDemo values(null,'d');
 */
/*
    Statement方式执行批处理:
        优点:可以执行多条不同结构的sql语句
        缺点:没有使用预编译机制,效率低下,如果要执行多条结构相同仅仅参数不同的sql时,仍然需要写多次sql语句的主干
 */
public class StatementBatch {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stat = null;
        try{
            conn = JDBCUtils.getConn();
            stat = conn.createStatement();
            stat.addBatch("create database day10batch");
            stat.addBatch("use day10batch");
            stat.addBatch("create table batchDemo("+
                                "id int primary key auto_increment,"+
                                "name varchar(20)"+
                            ")");
            stat.addBatch("insert into batchDemo values(null,'aaaa')");
            stat.addBatch("insert into batchDemo values(null,'bbb')");
            stat.addBatch("insert into batchDemo values(null,'cc')");
            stat.addBatch("insert into batchDemo values(null,'d')");
            
            stat.executeBatch();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.close(null, stat, conn);
        }
    }
}

 

package com.dzq.batch;

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

import com.itheima.util.JDBCUtils;
/*
     create table psbatch(
         id int primary key auto_increment,
         name varchar(30)
     );
 */
/*
    prparedStatement 方式实现的批处理:
        优点:有预编译机制,效率比较高.执行多条结构相同,参数不同的sql时,不需要重复写sql的主干
        缺点:只能执行主干相同参数不同的sql,没有办法在一个批中加入结构不同的sql
 */
public class PSBatch {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement("insert into psbatch values(null,?)");
            
            for(int i=1;i<=100000;i++){
                ps.setString(1, "name"+i);
                ps.addBatch();
                
                if(i%1000==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.close(null, ps, conn);
        }
    }
}