23.PreparedStatement

执行计划

PreparedStatement

• Statement主要用于执行静态SQL语句，即内容固定不变的SQL语句

• Statement每执行一次都要对传入不同的SQL语句编译一次(编译为执行计划)，效率较差

• 某些情况下，SQL语句只是其中的参数有所不同，其余子句完全相同，适用于PreparedStatement，它能重用执行计划

• 更重要的是它能预防sql注入攻击

PreparedStatment原理

• PreparedStatement是接口，继承自Statement

• SQL语句提前编译，三种常用方法execute、executeQuery、executeUpdate已被更改，不再需要参数

• Prepared实例包含已事先编译的SQL语句

• SQL语句可有一个或多个IN参数

  • IN参数的值在SQL语句创建时未被指定，该语句为每个IN参数保留一个问号"?"，作为占位符

  • 每个问号的值必须在该语句执行之前，通过适当的setInt或者setString方法提供

• 由于PreparedStatement对象已预编译过，所以执行速度要快于Statement对象，因此，多次执行的SQL语句经常创建为PreparedStatement对象，以提高效率

• 它执行SQL语句，是批量执行

• 使用方式：

  

演示：PreparedStatement

package demo;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Demo01 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();
            //1.创建带参数的SQL语句
            String sql = "INSERT INTO demo(id,name) VALUES(?,?)";
            //2.将SQL发送到数据库，创建执行计划，返回值就代表执行计划
            PreparedStatement ps = conn.prepareStatement(sql);
            //替换执行计划中参数，按照序号发送参数
            ps.setInt(1, 8);
            ps.setString(2, "黄强");
            //执行“执行计划”
            int n = ps.executeUpdate();
            System.out.println(n);
            //再次重复使用执行计划
            ps.setInt(1,4);
            ps.setString(2,"老王");
            n = ps.executeUpdate();
            System.out.println(n);
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}

通过PreparedStatement提升性能

• 数据库具备缓存功能功能，可以对Statement的执行计划进行缓存，以免重复分析

• 缓存原理：

  • 使用statement本身作为key并将执行计划存入与statement对应的缓存中

  • 对曾经执行过的statements,再次运行时执行计划将重用

• 举例：

  • SELECT a,b FROM t WHERE c=1;

  • 再次发送相同的statement时，数据库会对先前使用过的执行计划进行重用，降低开销

  • SELECT a,b FROM t WHERE c=2;

  • 若发送这样的SQL语句，则执行计划不可重用

• PreparedStatement就能避免上述类似情况，降低开销

SQL Injection

• 场景：

  • String sql=“SELECT * FROM t WHERE username=’”+ name +"’ and password=’"+ passwd +"’";

• 若输入正确的参数后，数据库接受到的完整sql语句是：

  • SELECT * FROM t WHERE username=‘scott’ and password=‘admin’

• 若用户输入的passwd参数为：a’ OR ‘b’='b，则数据库收到的SQL语句将是：

  • SELECT * FROM t WHERE username=‘scott’ and password=‘a’ OR ‘b’=‘b’

  • 则此WHERE判断条件永远为true

• 用户输入了含有SQL成分的参数，参数在拼接SQL时候造成SQL语句的语义改变，改变了SQL语句的执行计划，最终的执行结果完全改变

通过PS防止SQL Injection

• 对JDBC而言，SQL注入攻击只对Statement有效，对PreparedStatement无效，因为PreparedStatement不允许在插入参数时，改变SQL语句的逻辑结构

• 使用预编译的语句对象，用户传入的任何数据不会和原SQL语句发生匹配关系，无需对输入的数据做过滤