JDBC 的 PreparedStatement 与 Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

public class StatmentExample {

    public static void main(String[] args) throws Exception {
        mysqlConnection3();
    }
    
    // mysql 获取自增id的值的方法
    public static void mysqlConnection1() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/test?useUnicode=true&&characterEncoding=UTF-8&autoReconnect=true";
        String user = "root";
        String password = "123456";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            stmt.executeUpdate("insert into dept (deptname) values ('市场部')", Statement.RETURN_GENERATED_KEYS);
            rs = stmt.getGeneratedKeys();// mysql 获取自增id的值的方法
            if (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        } catch (Exception e) {
            throw e;
        } finally {
            rs.close();
            stmt.close();
            conn.close();
        }
    }
    // 建议始终以 PreparedStatement 代替 Statement
    // 1.虽然代码多出几行，但可读性和可维护性得到提升
    // 2.防止SQL注入提高安全性，占位符中的内容都会被转义，['w' or '1' = '1']会被转义成[\'\\'w\\' or \\'1\\' = \\'1\\'\']
    // 3.虽然预编译要耗费时间，但sql编译后的执行代码被缓存下来，下次调用时就不需要编译，从而提升性能
    public static void mysqlConnection2() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/test?useUnicode=true&&characterEncoding=UTF-8&autoReconnect=true";
        String user = "root";
        String password = "123456";
        Connection conn = null;
        PreparedStatement perstmt2 = null;
        ResultSet rs2 = null;
        
        try {
            conn = DriverManager.getConnection(url, user, password);
            String sql2 = "select deptno,deptname from dept where deptno = ? ";// dept这张表有deptno，deptname字段
            perstmt2 = conn.prepareStatement(sql2);
            perstmt2.setInt(1,11);
            rs2 = perstmt2.executeQuery();
            while (rs2.next()) {
                System.out.print(rs2.getInt("deptno") + "    ");
                System.out.println(rs2.getString("deptname"));
            }
        } catch (Exception e) {
            throw e;
        } finally {
            // 不要只关闭conn，因为数据库那边的资源确实释放了，但是java这边的操作系统中的连接资源不会即时释放
            rs2.close();
            perstmt2.close();
            conn.close();
        }
    }
    // 使用PreparedStatement的AddBatch()方法一次性发送多个sql给数据库
    public static void mysqlConnection3() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/test?useUnicode=true&&characterEncoding=UTF-8&autoReconnect=true";
        String user = "root";
        String password = "123456";
        Connection conn = null;
        PreparedStatement perstmt2 = null;
        
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println((new Date()).getTime());
            perstmt2 = conn.prepareStatement("insert into dept (deptname) values (?)");  
            for (int n = 0; n < 1000; n++) {
                perstmt2.setString(1, "信息部" + n);
                perstmt2.addBatch();
            }
            perstmt2.executeBatch(); 
            System.out.println((new Date()).getTime());
        } catch (Exception e) {
            throw e;
        } finally {
            perstmt2.close();
            conn.close();
        }
    }
}