JDBC之PreparedStatement的理解

前言：怀着疑惑和痛苦的心情写下这篇文章，其中疑惑之一是预编译对象，会把编译之后的整个语句保存在对象中，如果保存保存在哪里呢？
注：本文以mysql数据库为基础展开

一、基础知识

1、SQL语句在数据库中的执行过程

一条SQL语句从客户端（如: java 程序、navicat工具、cmd命令行）发送到数据库管理系统后，要经历以下过程：

1. 词法和语义的解析
2. 优化SQL语句，制定执行计划
3. 执行并返回结果

2、预编译语句

     形如 select * from user where id = 1，这种普通语句叫做 Immediate Statement，执行时要经历完整的SQL执行过程

     形如select * from user where id = ?, 使用占位符代替语句中字段的值，这种语句叫做Prepared Statement,执行时，首先要进行预编译，之后每次执行都省去了解析优化等过程。

     预编译语句适用场景：一条SQL语句需要反复执行多次并且只有字段的值可能存在改变（如query的where子句值不同，update的set子句值不同,insert的values值不同）

二、MySQ数据库的预编译功能

1、开启数据库日志功能

为了更好的了解SQL语句的执行，需要开启mysql的查询日志功能。本文通过设置mysql全局变量方式开启，mysql服务重启后配置失效。

第一步：通过命令行，进入mysql客户端，输入命令： show variables like "general_%"; general_log = OFF 表明日志功能没有开启。

第二步：开启日志功能，输入命令： set global general_log = "ON";                                            修改保存日志的文件（非必要操作），输入命令： set global general_log_file = "D:\\mysql\\data\query01.log";
第三步： 查看是否开启成功，输入命令 show variables like "general_%";

2、执行mysql预编译命令

在执行相关操作前，需要创建一张表，作为操作对象。建表语句如下：

create table user(
  id int auto_increment primary key,
  username varchar(50),
  password int(11)
)

(1) 编译
语法：PREPARE stmt_name FROM preparable_stmt
stmt_name: 个人理解，它和编译后的语句建立了映射，通过stmt_name可以找到要执行的语句
preparable_stmt: 要进行预编译的SQL语句

输入命令： prepare insert_user from "insert into user(username, password) values(?,?)";

（2）设置变量
语法： SET @var_name value

输入命令： set @username ="明月几时有";

                   set @password=10010;

(3)执行
语法：EXECUTE stmt_name [USING @var_name [, @var_name] …]

输入命令： execute insert_user using @username, @password;

查看执行结果：

查看日志文件：

（4）释放
语法：{DEALLOCATE | DROP} PREPARE stmt_name
即删除预编译语句。
预编译语句是会话级别的，会话一般可以理解为一次连接，当连接关闭后，预编译语句也会被释放

二、MySQL 驱动程序的预编译功能

     首先，我们要知道Mysql驱动程序实现预编译功能有两种方式：驱动程序直接对sql语句进行预编译和驱动程序使用mysql数据库提供的预编译功能。（注意MySQL的老版本（4.1之前）是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译）。接下来将使用图解和源码分析mysql驱动的预编译功能的实现

1、部分继承关系图

如上图所示，当使用驱动器直接对sql语句进行编译（下文称此方式为：客户端预编译），编译后的sql语句保存在ParseInfo内部类中；当驱动程序使用mysql数据库提供的预编译功能（下文称此方式为：服务器端预编译），将服务器端预编译语句的ID保存在statementID中（前文中：prepare stmt_name FROM preparable_stmt ， 这里的ID相当于stmt_name, 也就是通过ID可以映射到服务器端的预编译语句）
在mysql中，使用连接对象的prepareStatement()方法实际返回的对象时JDBC42ServerPreparedStatement.

二、客户端与服务器端预编译及缓存

两个超级重要的参数（用在数据库连接URL中）：

useServerPrepStmts ：控制是否使用服务器端预编译功能，默认值false,即不使用服务器端预编译。
cachePrepStmts : 控制是否缓存预编译的相关信息,默认值false,即不使用缓存。

使用上述参数不同取值，预编译对象的获取流程基本如下

测试参数的使用代码如下：

DBUtils.java 用于获取连接,根据getConnection的参数：boolean useServerPrepStmts,和boolean cachePrepStmts决定是否使用服务器端预编译和缓存

public class DBUtils {

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://localhost:3306/imooc";
    private static final Properties props = new Properties();

    static {
        props.setProperty("user", "root");
        props.setProperty("password", "password");
        props.setProperty("useUnicode", "true");
        props.setProperty("characterEncoding", "UTF-8");
    }
    
    public static Connection getConnection(boolean useServerPrepStmts, boolean cachePrepStmts) {
        Connection conn = null;
        try {
            Class.forName(driver);

            if (useServerPrepStmts)
                props.setProperty("useServerPrepStmts", Boolean.toString(useServerPrepStmts));
            if (cachePrepStmts)
                props.setProperty("cachePrepStmts", Boolean.toString(cachePrepStmts));
            conn = DriverManager.getConnection(url, props);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

DatabaseProgram.java 测试程序，向前文中提到的user用户表中插入两条数据

public class DatabaseProgram {
    
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DBUtils.getConnection(true, true);) {
            insert(conn, "中国移动", 10086);
            insert(conn, "中国联通", 10010);
        }
    }
    private static void insert(Connection conn, String username, int password) throws SQLException {
        String sql = "insert into user(username, password)" +
                "values(?,?)";
        try (PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setString(1, username);
            statement.setInt(2, password);
            statement.executeUpdate();
        }
    }
}

1、使用客户端预编译

useServerPrepStmts参数为false;cachePrepStmts参数为false;
查看mysql执行日志文件，如下图所示，可以看出没有出现我们在第二节mysql数据库预编译功能日志中的prepare和execute命令

2、使用服务器端预编译

useServerPrepStmts参数为true;cachePrepStmts参数为false;
查看mysql执行日志文件，如下图所示，可以看出使用了服务器端预编
并且，我们注意到，由于没有使用缓存，虽然是相同的sql语句，这里进行了两次prepare(编译）,增加了不必要的开销。这里的没有缓存不是指数据库没有缓存预编译的sql语句，只有不手动释放（删除）或者是会话不关闭，预编译的语句就存在。在服务器端，问题是一个PreparedStatement对象对应一条服务器端预编译语句（statementID建立的映射关系），这里不缓存是指，在使用完PreparedStatement对象后没有把它放入缓存对象中，而是关闭了（close stmt可以看出）。那么进行第二次插入时，必须创建新的PreparedStatement对象，因此也要重新去预编译（prepare）

3、使用服务器端预编译+缓存

useServerPrepStmts参数为true;cachePrepStmts参数为true;
查看mysql执行日志文件，如下图所示，解决了要重新预编译的问题。

四、效率测试

接下来要测试客户端、服务器端和缓存组合的预编译对象PreparedStatement以及Statement向数据库中插入1000条数据3次，使用的平均时间。
注：测试时最好关闭数据库日志功能，记录执行日志比较耗时。
测试代码如下：

public class Main {

    public static final int executeCount = 3;

    public static long test(int insertCount,boolean useServerPrepStmts, boolean cachePrepStmts) throws SQLException{
        long result = 0;
        String sql = "insert into user(username, password) values(?,?)";

        try (Connection conn = DBUtils.getConnection(useServerPrepStmts,cachePrepStmts);
             PreparedStatement pstat = conn.prepareStatement(sql)) {
            long start = System.currentTimeMillis();

            for (int i = 0; i < insertCount; i ++){
                pstat.setString(1, "明月几时有");
                pstat.setInt(2, 123456);
                pstat.executeUpdate();
            }
            long end = System.currentTimeMillis();
            result = Math.abs(end - start);
        }
        return result;
    }

    public static void main(String[] args) throws SQLException{

        long totalTime = 0;
        for (int i = 0; i <  executeCount; i ++) {
            totalTime = totalTime + test(1000,false,false);
        }
        System.out.println(totalTime/3);
    }

}

情况	3次平均耗时（s）
客户端	44.922
客户端+缓存	36.154
服务器端	39.543
服务器端+缓存	35.162
statement语句

这个数据还是不准确，有太多影响因素，电脑环境等等，为降低影响每次执行程序后，在mysql客户端cmd命令行执行truncate user重新建表，然而在次运行它们的相对排序还是会变，但总体上来讲使用客户端时间最长，服务器端+缓存时间最少（忽略statement）