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();

         }

     }

 }