【21-23】Statement和PreparedStatement

对比Statement和PreparedStatement

1. PreparedStatement 效率比 Statement 高，因为 PreparedStatement 预编译，编译一次执行 n 次，而 Statement 编译 n 次执行 n 次
2. PreparedStatement 会在编译阶段做类型检查，会更安全
3. PreparedStatement 不存在 SQL 注入，而 Statement 存在
4. 综上所以 PreparedStatement 使用较多
5. 当业务要求 SQL注入 那么使用 Statement，拼接SQL语句，支持 SQL注入

举例利用 Statement SQL注入

• 在浏览商品，例如价格升序降序等等，利用 PreparedStatement 中的 ‘?’ 并不能注入 ‘xxx desc’，所以利用 Statement
• 以下升序或者降序输出ename和sal
• 因为用 PreparedStatement ，在 处理sql字符串时，利用setString() 会让输入的 desc 加上单引号导致 sql 语句不合法，所以利用 Statement

  package _21_Statement和PreparedStatement;
  
  import java.sql.*;
  import java.util.Scanner;
  
  public class 演示Statement用途 {
      public static void main(String[] args) {
          Scanner scanner = new Scanner(System.in);
          System.out.printf("输入asc或者desc分别表示升序或降序：");
          String s = scanner.nextLine();
  
          if (!("asc".equals(s) || "desc".equals(s))){
              System.out.println("输入不合法!");
              return;
          }
  
          // 执行SQL
          Connection conn = null;
          Statement stmt = null;
          ResultSet res = null;
          try {
              // 1. register driver
              Class.forName("com.mysql.cj.jdbc.Driver");
              // 2. get connection
              conn = DriverManager.getConnection(
                      "jdbc:mysql://localhost:3306/sunlie",
                      "root",
                      "root"
              );
              // 3. create database operation object
              stmt = conn.createStatement();
              // 4. execute SQL statement
              String sql = "select ename, sal from emp order by sal " + s;
              res = stmt.executeQuery(sql);
              // 5. process result set
              while(res.next()){
                  System.out.println(res.getString("ename") + "\t" +
                          res.getString("sal"));
              }
  
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
          } catch (SQLException e) {
              e.printStackTrace();
          }finally {
              // 6. close resource
              if (res != null) {
                  try {
                      res.close();
                  } catch (SQLException e) {
                      e.printStackTrace();
                  }
              }
              if (stmt != null) {
                  try {
                      stmt.close();
                  } catch (SQLException e) {
                      e.printStackTrace();
                  }
              }
              if (conn != null) {
                  try {
                      conn.close();
                  } catch (SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
  
  
      }
  }

PrepareStatement 完成增删改

package _21_Statement和PreparedStatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PerparedStatement完成增删改 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 1. register Driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. get connection
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/sunlie",
                    "root",
                    "root"
            );
            // 3. get database operation object
            /* 3.1 insert statement
                String sql = "insert into dept(deptno,dname,loc) values(?,?,?)";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, 60);
                ps.setString(2, "SALES");
                ps.setString(3,"SHANGHAI");
            */

            /* 3.2 update statement
                String sql = "update dept set dname = ?, loc = ? where deptno = ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1, "RESEARCH");
                ps.setString(2, "BEIJING");
                ps.setInt(3, 60);
            */

            // 3.3 delete statement
            String sql = "delete from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,60);

            // 4. execute SQL statement
            int count = ps.executeUpdate();
            // 5. process result set
            System.out.println("count = " + count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 6. close resource
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}