上一篇：17【JDBC基本操作】

下一篇：19【CallableStatement 接口】

目录：【MySQL零基础系列教程】

文章目录

18【PreparedStatement接口详细解析】
- 1.1 PreparedStatement接口

18【PreparedStatement接口详细解析】

1.1 PreparedStatement接口

1.1.1 SQL注入问题

1）需求案例

有一张用户表，添加几条用户记录

create table `user`(
    id int primary key auto_increment,
    `name` varchar(20),
    `password` varchar(20)
);

insert into user values(null, 'admin','123'),(null,'root','456');

select * from user;

-- 登录成功
select * from user where name='admin' and psd='123'

-- 登录失败
select * from user where name='root' and psd='999'

2）完成案例

package com.dfbz.demo;

import com.dfbz.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
public class Demo08_Login {

    public static void main(String[] args) throws Exception {
        //让用户输入用户名和密码
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入用户名：");
        String name = scanner.nextLine();

        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        // 使用工具类获取连接
        Connection conn = JdbcUtils.getConnection();

        //创建语句对象
        Statement stmt = conn.createStatement();

        //执行DQL查询，通过字符串拼接的方式得到SQL语句
        String sql = "select * from user where name='" + name + "' and password='" + password + "'";

        //得到结果集
        ResultSet rs = stmt.executeQuery(sql);

        System.out.println("输入的SQL语句: " + sql);

        //判断是否有记录
        if (rs.next()) {
            System.out.println("登录成功，" + name);
        } else {
            System.out.println("登录失败");
        }

        // 使用工具类释放资源
        JdbcUtils.close(conn, stmt, rs);
    }
}

失败：

成功：

3）问题引入

当我们输入以下密码，我们发现我们账号和密码都不对竟然登录成功了

abc' or '1'='1

问题分析：

select * from user where name='admin' and password='abc' or '1'='1';

select * from user where false and false or true;

select * from user where false or true;

-- 查询所有的记录
select * from user where true;

我们让用户输入的密码和SQL语句进行字符串拼接。用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义，以上问题称为SQL注入。要解决SQL注入就不能让用户输入的密码和我们的SQL语句进行简单的字符串拼接。

1.1.2 PreparedStatement 解决SQL注入

PreparedStatement接口中的方法：

方法	描述
boolean execute()	执行任何的SQL语句，如果SQL执行的SQL语句有返回值，则返回true，否则返回false
int executeUpdate()	执行DML，增删改的操作。 Tips：没有参数。 SQL语句在创建PreparedStatement对象的时候就已经提供了，所以执行的时候没有SQL语句返回：影响的行数
ResultSet executeQuery()	执行DQL，查询操作返回：结果集

案例代码：

package com.dfbz.demo;

import com.dfbz.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * @author lscl
 * @version 1.0
 * @intro: 使用PreparedStatement改写登录程序
 */
public class Demo09_Login_PreparedStatement {

    public static void main(String[] args) {
        //让用户输入用户名和密码
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = scanner.nextLine();

        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        //声明对象
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //访问数据库，创建连接
            conn = JdbcUtils.getConnection();

            //创建预编译的SQL语句
            ps = conn.prepareStatement("select * from user where name=? and password=?");

            //替换占位符
            ps.setString(1, name);
            ps.setString(2, password);

            //执行SQL语句，查询
            rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, ps, rs);
        }
    }
}

执行效果：

1.1.3 PreparedStatement 简介

PreparedStatement 是 Statement 的子类，也能执行Statement之前的所有操作，其中最主要的功能就是提供了占位符传参处理、预编译等功能；我们实际开发中PreparedStatement会使用的更多；

PreparedStatement 占位符参数处理：

PreparedStatement的方法	说明
void setXxx(int 参数1，参数2) Xxx数据类型	替换SQL语句中的占位符参数1：占位符的位置，第几个位置，从1开始参数2：用来替换占位符的真实的值

void setDouble(int parameterIndex, double x)	将指定参数设置为给定 Java double 值。
void setFloat(int parameterIndex, float x)	将指定参数设置为给定 Java REAL 值。
void setInt(int parameterIndex, int x)	将指定参数设置为给定 Java int 值。
void setLong(int parameterIndex, long x)	将指定参数设置为给定 Java long 值。
void setObject(int parameterIndex, Object x)	使用给定对象设置指定参数的值。
void setString(int parameterIndex, String x)	将指定参数设置为给定 Java String 值。

1）执行DML操作

示例代码：

package com.dfbz.demo;


import com.dfbz.utils.JdbcUtils;
import org.junit.Test;

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

/**
 * @author lscl
 * @version 1.0
 * @intro: PreparedStatement实现增删改操作
 */
public class Demo10_PreparedStatement执行DML {

    /*
     preparedStatement向学生表中添加一条记录
     */
    @Test
    public void add() throws SQLException {
        //创建连接对象
        Connection conn = JdbcUtils.getConnection();
        //创建预编译语句对象
        PreparedStatement ps = conn.prepareStatement("INSERT into student VALUES (null,?,?,?,?)");

        //设置参数替换占位符
        ps.setString(1, "小陈");
        ps.setString(2, "女");
        ps.setDate(3, Date.valueOf("1987-02-10"));   //静态方法，将一个字符串转成日期类型
        ps.setString(4, "安徽宣城");

        //执行SQL语句
        int i = ps.executeUpdate();

        //关闭资源
        JdbcUtils.close(conn, ps);

        //输出影响的行数
        System.out.println("影响了" + i + "行");
    }

    /*
     将id为1的用户:
        1)姓名更新为"明明"
        2)性别换成女(改)
        3)出生日期改为"1994-03-19"
        4)地址改为"陕西汉中"
     */
    @Test
    public void update() throws SQLException {
        //创建连接对象
        Connection conn = JdbcUtils.getConnection();

        //创建预编译语句对象
        PreparedStatement ps = conn.prepareStatement("UPDATE student set name=?, gender=?,birthday=?,address=? where id=?");

        //设置参数替换占位符
        ps.setString(1, "明明");
        ps.setString(2, "女");
        ps.setDate(3, Date.valueOf("1994-03-19"));
        ps.setString(4, "陕西汉中");
        ps.setInt(5, 1);

        //执行SQL语句
        int i = ps.executeUpdate();
        //关闭资源
        JdbcUtils.close(conn, ps);
        //输出影响的行数
        System.out.println("影响了" + i + "行");
    }

    /**
     * 将id为4的学员删除
     */
    @Test
    public void delete() throws SQLException {
        //创建连接对象
        Connection conn = JdbcUtils.getConnection();
        //创建预编译语句对象
        PreparedStatement ps = conn.prepareStatement("DELETE FROM student where id=?");
        //设置参数替换占位符
        ps.setInt(1, 4);
        //执行SQL语句
        int i = ps.executeUpdate();
        //关闭资源
        JdbcUtils.close(conn, ps);
        //输出影响的行数
        System.out.println("影响了" + i + "行");
    }
}

2）执行DQL操作

表与类之间的关系

在实际开发中，我们查询出来的一条数据刚好对应的是Java中的一个对象，一张表对应Java中的一个类；当我们查询到一条数据时，应该创建一个对应的对象来接收这些数据；

【案例】：使用PreparedStatement查询id为1的一条学生数据，封装成一个学生Student对象

学生对象：

package com.dfbz.entity;

import java.util.Date;

/**
 * @author lscl
 * @version 1.0
 * @intro: 
 */
public class Student {
    private Integer id;
    private String name;
    private String gender;
    private Date birthday;
    private String address;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                '}';
    }

    public Student() {
    }

    public Student(Integer id, String name, String gender, Date birthday, String address) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.birthday = birthday;
        this.address = address;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

代码：

package com.dfbz.demo;


import com.dfbz.entity.Student;
import com.dfbz.utils.JdbcUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro: 
 */
public class Demo11_PreparedStatement执行DQL {
    
    /**
     * 查询id为1的一条记录封装成一个学生对象
     *
     * @throws SQLException
     */
    @Test
    public void query() throws SQLException {
        //得到连接对象
        Connection conn = JdbcUtils.getConnection();
        //得到预编译语句对象
        PreparedStatement ps = conn.prepareStatement("select * from student where id=?");
        //设置占位符
        ps.setInt(1, 1);
        //执行SQL语句
        ResultSet rs = ps.executeQuery();
        //创建一个学生对象
        Student student = new Student();
        //封装成一个学生对象
        if (rs.next()) {
            student.setId(rs.getInt("id"));    //从结果集中取出值，封装给学生对象
            student.setName(rs.getString("name"));
            student.setGender(rs.getString("gender"));
            student.setBirthday(rs.getDate("birthday"));
            student.setAddress(rs.getString("address"));
        }
        //关闭连接对象
        JdbcUtils.close(conn, ps, rs);
        System.out.println(student);
    }
}

【需求】：查询所有的学生类，封装成List<Student>返回

开发步骤：

创建一个集合用于封装所有的记录
得到连接对象
得到语句对象，SQL语句没有占位符
每次循环封装一个学生对象
把数据放到集合中
关闭连接
使用数据，循环输出学生对象

代码：

/**
 * 查询所有学生
 *
 * @throws SQLException
 */
@Test
public void findAll() throws SQLException {
    //创建集合
    List<Student> students = new ArrayList<>();
    //创建连接对象
    Connection conn = JdbcUtils.getConnection();
    //创建预编译语句对象
    PreparedStatement ps = conn.prepareStatement("select * from student");
    //查询
    ResultSet rs = ps.executeQuery();
    //使用while循环
    while (rs.next()) {
        //每次循环创建一个学生对象
        Student student = new Student();
        student.setId(rs.getInt("id"));    //从结果集中取出值，封装给学生对象
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setBirthday(rs.getDate("birthday"));
        student.setAddress(rs.getString("address"));
        //封装好的学生对象放在集合中
        students.add(student);
    }
    //关闭连接
    JdbcUtils.close(conn, ps, rs);

    for (Student student : students) {
        System.out.println(student);
    }
}

3）PreparesStatement 使用小结

功能	实现
得到PreparesStatement对象的方法	通过连接对象创建
设置占位符的方法	setXxx(占位符位置, 真实值)
执行DML的方法	executeUpdate()
执行DQL的方法	executeQuery()

1.1.4 PreparedStatement 参数处理原理

我们之前了解到：Statement在参数拼接上会出现SQL注入问题，我们的解决方案是将参数的传递换成了PreparedStatement的占位符，以此来解决SQL注入问题，那么PreparedStatement是如何解决SQL注入的呢？

我们来翻一翻PreparedStatement的源码：

1）打开PreparedStatement类的setString()方法：

setString方法全部源码：

public void setString(int parameterIndex, String x) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            // if the passed string is null, then set this column to null
            if (x == null) {
                setNull(parameterIndex, Types.CHAR);
            } else {
                checkClosed();

                int stringLength = x.length();

                if (this.connection.isNoBackslashEscapesSet()) {
                    // Scan for any nasty chars

                    boolean needsHexEscape = isEscapeNeededForString(x, stringLength);

                    if (!needsHexEscape) {
                        byte[] parameterAsBytes = null;

                        StringBuilder quotedString = new StringBuilder(x.length() + 2);
                        quotedString.append('\'');
                        quotedString.append(x);
                        quotedString.append('\'');

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding,
                                    this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString());
                        }

                        setInternal(parameterIndex, parameterAsBytes);
                    } else {
                        byte[] parameterAsBytes = null;

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                    this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(x);
                        }

                        setBytes(parameterIndex, parameterAsBytes);
                    }

                    return;
                }

                String parameterAsString = x;
                boolean needsQuoted = true;

                if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                    needsQuoted = false; // saves an allocation later

                    StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));

                    buf.append('\'');

                    //
                    // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
                    //

                    // 核心部分
                    for (int i = 0; i < stringLength; ++i) {
                        char c = x.charAt(i);

                        switch (c) {
                            case 0: /* Must be escaped for 'mysql' */
                                buf.append('\\');
                                buf.append('0');

                                break;

                            case '\n': /* Must be escaped for logs */
                                buf.append('\\');
                                buf.append('n');

                                break;

                            case '\r':
                                buf.append('\\');
                                buf.append('r');

                                break;

                            case '\\':
                                buf.append('\\');
                                buf.append('\\');

                                break;

                            case '\'':
                                buf.append('\\');
                                buf.append('\'');

                                break;

                            case '"': /* Better safe than sorry */
                                if (this.usingAnsiMode) {
                                    buf.append('\\');
                                }

                                buf.append('"');

                                break;

                            case '\032': /* This gives problems on Win32 */
                                buf.append('\\');
                                buf.append('Z');

                                break;

                            case '\u00a5':
                            case '\u20a9':
                                // escape characters interpreted as backslash by mysql
                                if (this.charsetEncoder != null) {
                                    CharBuffer cbuf = CharBuffer.allocate(1);
                                    ByteBuffer bbuf = ByteBuffer.allocate(1);
                                    cbuf.put(c);
                                    cbuf.position(0);
                                    this.charsetEncoder.encode(cbuf, bbuf, true);
                                    if (bbuf.get(0) == '\\') {
                                        buf.append('\\');
                                    }
                                }
                                buf.append(c);
                                break;

                            default:
                                buf.append(c);
                        }
                    }

                    buf.append('\'');

                    parameterAsString = buf.toString();
                }

                byte[] parameterAsBytes = null;

                if (!this.isLoadDataQuery) {
                    if (needsQuoted) {
                        parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding,
                                this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    } else {
                        parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    }
                } else {
                    // Send with platform character encoding
                    parameterAsBytes = StringUtils.getBytes(parameterAsString);
                }

                setInternal(parameterIndex, parameterAsBytes);

                this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
            }
        }
    }

最终传递的参数如下：

咱们在数据库中执行如下SQL语句（肯定是查询不到数据的）：

select * from user where username = 'abc\' or 1=1 -- '

如果把PreparedStatement加的那根"/"去掉呢？我们执行SQL试试：

select * from user where username = 'abc' or 1=1 -- '

小结：PreparedStatement之所以能够解决SQL注入的问题是因为PreparedStatement对占位符中的传递参数进行了特殊校验，如果发现一些特殊字符将会进行转义，达到不参与SQL语句生成的目的；

1.1.5 PreparedStatement 的预编译

PreparedStatement 提供了量大功能，第一个就是占位符参数处理，另一个就是SQL语句的预编译了；那什么是预编译呢？

1）预编译简介

通常我们发送一条SQL语句给MySQL服务器时，MySQL服务器每次都需要对这条SQL语句进行校验、解析等操作。但是有很多情况下，我们的一条SQL语句可能需要反复的执行，而SQL语句也只可能传递的参数不一样，类似于这样的SQL语句如果每次都需要进行校验、解析等操作，未免太过于浪费性能了，因此MySQL提出了SQL语句的预编译。

Tips：预编译的功能是MySQL的，PreparedStatement 只是开启MySQL的预编译功能；

所谓预编译就是将一些灵活的参数值以占位符?的形式给代替掉，我们把参数值给抽取出来，把SQL语句进行模板化。让MySQL服务器执行相同的SQL语句时，不需要在校验、解析SQL语句上面花费重复的时间，因此就是来提高我们的查询速度的；

2）开启预编译

PreparedStatement的预编译功能默认是关闭的，要让其生效，必须在JDBC连接的URL设置useServerPrepStmts=true，让其打开。

示例：

jdbc:mysql://localhost:3306/test?&useServerPrepStmts=true

预编译性能测试：

package com.dfbz.demo;


import org.junit.Test;

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

/**
 * @author lscl
 * @version 1.0
 * @intro: 预编译性能测试
 */
public class Demo13_PreparedStatement_预编译 {

    /**
     * 预编译性能测试
     * @throws SQLException
     */
    @Test
    public void test1() throws SQLException {
        
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true", "root", "admin");
        
        long startTime = System.currentTimeMillis();

        PreparedStatement ps =  connection.prepareStatement("select * from user where id = ?");
        
        for (int i = 0; i < 20000; i++) {

            ps.setString(1, "1");
            // 执行查询
            ps.executeQuery();
        }

        long endTime = System.currentTimeMillis();
        System.out.println(endTime - startTime);

        ps.close();
    }

}

下面是测试报告（2W次查询）：

次数	开启预编译	关闭预编译
第一次	1131ms	1240ms
第二次	1158ms	1244ms
第三次	1132ms	1300ms
第四次	1142ms	1216ms

通过上面表格可以看出，开启预编译之后查询性能能够得到一定的提升；

3）预编译的参数

另外，MySQL中的 max_prepared_stmt_count 变量用来控制全局最大存储的预编译语句数：

show variables like '%max_prepared_stmt_count%';

4）预编译的优缺点

对于频繁使用的语句，使用服务端 “预编译” 还是能够得到提升的。但是对于不频繁使用的语句，服务端预编译本身会增加额外的耗时，**还会增加MySQL的使用内存。**因此在实际开发中具体是否要开启预编译要根据情况而定；

例如下面测试案例：

@Test
public void test2() throws SQLException {

    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true", "root", "admin");

    long startTime = System.currentTimeMillis();
    PreparedStatement ps = null;

    for (int i = 0; i < 10000; i++) {
        // 每次都预编译
        ps = connection.prepareStatement("select * from user where id = ?");
        
        ps.setString(1, "1");
        // 执行查询
        ps.executeQuery();
    }

    long endTime = System.currentTimeMillis();
    System.out.println(endTime - startTime);

    ps.close();
}