JDBC Statement 实例- 批量更新

这个例子介绍怎样使用JDBC Statement进行批量Update。 Batch Update 不仅可以用于Insert，也可以用于Update和Delete语句。

代码如下：

         conn = getDBConnection();
         stmt = conn.createStatement();

         conn.setAutoCommit(false);

            stmt.addBatch(sql1);
            stmt.addBatch(sql2);
            stmt.addBatch(sql3);

            stmt.executeBatch();
            conn.commit();

完整代码如下：

package org.hnrsc.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCMySQL {

    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/test";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "";


    public static void main(String[] args) {
        try{
            //createTable();
            //insertRecord();
            batchInsertRecords();
            selectRecords();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    private static void insertRecord() throws SQLException{
        Connection conn = null;
        Statement stmt = null;

        String sql = "INSERT INTO t_user(username,password) values"
            +"('Dendi','password')";

        try{
            conn = getDBConnection();
            stmt = conn.createStatement();
            System.out.println(sql);
            stmt.executeUpdate(sql);
            System.out.println("A Record has been inserted!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static void createTable() throws SQLException{
        Connection conn = null;
        Statement stmt = null;

        String sql = "CREATE TABLE t_user("
            +"id int auto_increment primary key,"
            +"username varchar(50) NOT NULL,"
            +"password varchar(50) NOT NULL"
            +")";

        try{
            conn = getDBConnection();
            stmt = conn.createStatement();
            System.out.println(sql);
            stmt.execute(sql);
            System.out.println("Table \"t_user\" is created!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }

    }

    private static void selectRecords() throws SQLException{
        Connection conn = null;
        Statement stmt = null;

        String sql = "SELECT id,username,password FROM t_user";
        try{
            conn = getDBConnection();
            stmt = conn.createStatement();

            System.out.println(sql);

            //execute select SQL statement

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                System.out.println("id: "+ id);
                System.out.println("username: "+ username);
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static void batchInsertRecords() throws SQLException{
        Connection conn = null;
        Statement stmt = null;

        String sql1 = "INSERT INTO t_user(username,password) values"
            +"('Dendi','password')";
        String sql2 = "INSERT INTO t_user(username,password) values"
            +"('Kevin','pass111')";
        String sql3 = "INSERT INTO t_user(username,password) values"
            +"('Lily','mima')";

        try{
            conn = getDBConnection();
            stmt = conn.createStatement();

            conn.setAutoCommit(false);

            stmt.addBatch(sql1);
            stmt.addBatch(sql2);
            stmt.addBatch(sql3);

            stmt.executeBatch();
            conn.commit();

            System.out.println("Batch Records have been inserted!");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally{
            if(stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }
    }
    private static Connection getDBConnection(){
        Connection conn = null;
        try{
            Class.forName(DB_DRIVER);
        }catch(ClassNotFoundException e){
            System.out.println(e.getMessage());
        }
        try{
            conn = DriverManager.getConnection(DB_CONNECTION,DB_USER, DB_PASSWORD);
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }

        return conn;
    }

}