20160322 javaweb 之jdbc-- mysql 批處理

破玉發表於2016-04-08
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);
        }
    }
}

 

相關文章