使用JDBC连接MySQL数据库--典型案例分析(七)----批量插入员工信息

CREATE TABLE emp_seq ( seq VARCHAR(8));
INSERT INTO emp_seq VALUES('0');
UPDATE emp_seq SET seq = LAST_INSERT_ID(seq+1);
SELECT LAST_INSERT_ID();

package Account;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import dao.ConnectionSource;
public class Batch {
    public static void main(String [] args){}
    public void batchAdd(){
        Connection con=null;
        Statement stmt=null;
        String sql=null;
        try {
            con=ConnectionSource.getConnection();
            stmt=con.createStatement();
            //关闭自动提交
            con.setAutoCommit(false);
            // 提交
            con.commit();
        } catch (SQLException e) {
            System.out.println("数据库访问异常！");
            throw new RuntimeException(e);
        }finally{
            try {
                if(stmt!=null){
                    stmt.close();
                }
                if(con!=null){
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("释放资源时发生异常！");
            }
        }
    }
}

package Account;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import dao.ConnectionSource;
public class Batch {
    public static void main(String [] args){}
    public void batchAdd(){
        Connection con=null;
        Statement stmt=null;
        String sql=null;
        try {
            con=ConnectionSource.getConnection();
            stmt=con.createStatement();
            //关闭自动提交
            con.setAutoCommit(false);
            for(int i=0;i<100;i++){
                sql = "INSERT INTO Emp(empno, ename, sal) VALUES (emp_seq.nextval, 'name"+i+"', "+new Random().nextInt(10000)+")";
                stmt.addBatch(sql);
            }
            int[] results = stmt.executeBatch();
            // 提交
            con.commit();
        } catch (SQLException e) {
            System.out.println("数据库访问异常！");
            throw new RuntimeException(e);
        }finally{
            try {
                if(stmt!=null){
                    stmt.close();
                }
                if(con!=null){
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("释放资源时发生异常！");
            }
        }
    }
}

public static void main(String [] args){
    Batch batch=new Batch();
    batch.batchAdd();
}

emp_seq.nextval