@stefanlu 2016-03-04T03:21:17.000000Z 字数 10683 阅读 5059

JDBC PreparedStatement

java

参考文档：
java doc
mysql doc
维基百科
 博客
 jdbc url参数介绍
 mysql版本对预编译的支持

环境：
mysql-server: 5.6.25
mysql-client: mysql-connector-java.jar 5.1.31

PreparedStatement preparedStatement = connection.prepareStatement(sql);
- Creates a PreparedStatement object for sending parameterized SQL statements to the database(!JAVA DOC).
- A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
- If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation
preparedStatement.setString(int parameterIndex, String param);
- Sets the designated parameter to the given Java String value
- Throw SQLException: if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement
ResultSet resultSet = preparedStatement.executeQuery();
- Executes the SQL statement in this PreparedStatement object

上述例子是JAVA doc里边对PreparedStatement和其提供的操作数据库方法的介绍，其中：

PreparedStatement prepareStatement = connection.prepareStatement(sql);会将SQl语句保存到preparedStatement对象中，如果db支持预编译，则会将SQL语句发送给db进行预编译，若不支持，则此处不与数据库交互。并且PreparedStatement支持一次编译多次执行
- 此处的db是否支持预编译有两层意思：1. db是否支持预编译 2. 连接数据库的url是否指定了需要预编译
preparedStatement.setString(int parameterIndex, String param);会将传进来的参数设置到对应占位符的位置
- 此处JDBC中会对参数占位符的个数和传递的参数做参数匹配，若超出(即只需要1个参数，却设置了两个参数，在设置第二个参数的时候就会抛异常)则会抛出SQLException
ResultSet resultSet = preparedStatement.executeQuery();首先会进行参数判断，若参数对应不上(即需要1个参数，但没有传递参数，则会抛SQLException异常)。然后会将SQL语句交给db去编译(若没有开启预编译)，执行

JDBC连接mysql实例

一. 不使用预编译

代码：

public class Main {
    public static void main(String[] args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "xxx");
            String sql = "SELECT ROUND(member_price,10) as member_price  from member_price WHERE 'month' = '2015-11' and id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1 or '1'='1'");
            resultSet = preparedStatement.executeQuery();
            preparedStatement.setString(1, "1");
            resultSet = preparedStatement.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(resultSet != null){
                    resultSet.close();
                }
                if(preparedStatement != null){
                    preparedStatement.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

mysql日志信息：

抓包信息：

总结：

通过mysql日志和抓包可以发现：
1. 虽然使用了PreparedStatement，但对性能没有多少提高，每次都是将完整的SQL语句已经替换掉占位符发给mysql，所以每次mysql都会重新编译，执行(mysql日志中的指令为Query)
2. 使用PreparedStatement可以防止SQL注入，将查询的内容当做参数而不是SQL指令通过添加单引号实现

二. 使用预编译，不使用缓存

代码：

public class Main {
    public static void main(String[] args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true", "root", "xxx");
            String sql = "SELECT ROUND(member_price,10) as member_price  from member_price WHERE `month` = '2015-11' and id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1 or '1'='1'");
            resultSet = preparedStatement.executeQuery();
            preparedStatement.setString(1, "1");
            resultSet = preparedStatement.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(resultSet != null){
                    resultSet.close();
                }
                if(preparedStatement != null){
                    preparedStatement.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

mysql日志信息：

抓包信息：

总结：

代码区别：链接url中添加?useServerPrepStmts=true
从mysql日志和抓包信息中可以看到，这次执行向mysql发了三次sql的执行信息
- 第一次发送需要预编译的代码给mysql(mysql指令Prepare)
- 第二次发送需要执行的SQL语句(Statement ID: 1)和参数(Value: 1 or '1'='1')
- 第三次同第二次
此次，由于手动指定了需要预编译SQL语句(url参数 ?useServerPrepStmts=true)，则在创建PreparedStatement是就会将SQL语句发送给Mysql进行预编译，此时编译通过的SQL语句就会被Mysql给缓存起来,并生成一个唯一的Statement ID，并返回一个Statement ID(Statement ID：1)，下次需要执行这个SQL语句时，程序将需要执行的SQL语句的Statement ID和参数传给MySql，MySQL就会执行对应的SQL语句
若预编译的SQL语句有语法错误，则MYSQL预编译期就会返回错误但此时的错误JDBC是感知不到的，即它还会继续执行后续代码，当执行到(preparedStatement.executeQuery();)时，由于没有Statement ID信息，则JDBC会将完整的SQL语句(替换掉占位符的语句)发给mysql，此时就会抛出语法错误异常

三. 使用预编译，使用缓存

代码：

public class Main {
    public static void main(String[] args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=128&prepStmtCacheSqlLimit=256", "root", "xxx");
            String sql = "SELECT ROUND(member_price,10) as member_price  from member_price WHERE `month` = '2015-11' and id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "2");
            resultSet = preparedStatement.executeQuery();
            preparedStatement.close(); 
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1 or '1'='1'");
            resultSet = preparedStatement.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(resultSet != null){
                    resultSet.close();
                }
                if(preparedStatement != null){
                    preparedStatement.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

mysql日志信息：

抓包信息：

总结：

通过抓包信息可以发现，preparedStatement.close();和preparedStatement = connection.prepareStatement(sql);这两句执行的时候没有向mysql发送请求。
mysql sql语句的缓存是针对连接的，即每个连接的缓存都是独立的
结合useServerPrepStmts=true&cachePrepStmts=true才能真正的提高性能，因为如果只设置了useServerPrepStmts=true，虽然可以一次编译，多次执行。但这次关闭后，下次又需重新预编译，加上缓存后，下次就不用重新预编译。具体缓存是在java中做的，主要是mysql-connector-java.jar中实现的缓存。
对于connection.prepareStatement(sql);，只要向mysql中发预编译的请求，mysql就会预编译此sql语句不管之前是否已经编译过，并对此链接生成唯一的Statement ID，只要此链接中的请求拿着Statement ID和参数去执行SQL语句，mysql就不会重新编译SQL语句，而是直接执行SQL语句

若使用了cachePrepStmts=true参数，当手动调用prepareStatement.close()时PrepareStatement对象只会将关闭状态置为关闭，并不会向mysql发送关闭请求，prepareStatement对象会被缓存起来，等下次使用的时候直接从缓存中取出来使用。原理：

public void close() throws SQLException {
    MySQLConnection locallyScopedConn = this.connection;
    if (locallyScopedConn == null) return; // already closed
    synchronized (locallyScopedConn.getConnectionMutex()) {
        if (this.isCached && !this.isClosed) { //注意此处
            clearParameters();
            this.isClosed = true; //若开启缓存，则只会将状态位设为已关闭，并且刷新缓存
            this.connection.recachePreparedStatement(this);
            return;
        }

        //没有开启缓存，则会向mysql发送closeStmt的请求
        realClose(true, true);
    }
}

//刷新缓存代码
public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
    synchronized (getConnectionMutex()) {
        if (pstmt.isPoolable()) {
            synchronized (this.serverSideStatementCache) {
                this.serverSideStatementCache.put(pstmt.originalSql, pstmt); //将sql语句作为key，PreparedStatement对象作为value存放到缓存中
            }
        }
    }
}

preparedStatement = connection.prepareStatement(sql);原理：

public java.sql.PreparedStatement prepareStatement(String sql,
        int resultSetType, int resultSetConcurrency) throws SQLException {
    synchronized (getConnectionMutex()) {
        checkClosed();

        //
        // FIXME: Create warnings if can't create results of the given
        // type or concurrency
        //
        PreparedStatement pStmt = null;

        boolean canServerPrepare = true;

        String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;

        if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
            canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
        }

        if (this.useServerPreparedStmts && canServerPrepare) { //使用预编译
            if (this.getCachePreparedStatements()) {
                synchronized (this.serverSideStatementCache) {
                    pStmt = (com.mysql.jdbc.ServerPreparedStatement)this.serverSideStatementCache.remove(sql); //从缓存中取，缓存的key值是完整的sql语句

                    if (pStmt != null) { //缓存中有，则清空设置的参数，并关闭状态设置为开启
                        ((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false);
                        pStmt.clearParameters();
                    }

                    if (pStmt == null) { //缓存中没有，则向mysql发送请求创建新的
                        try {
                            pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
                                    this.database, resultSetType, resultSetConcurrency);
                            if (sql.length() < getPreparedStatementCacheSqlLimit()) {
                                ((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
                            }

                            pStmt.setResultSetType(resultSetType);
                            pStmt.setResultSetConcurrency(resultSetConcurrency);

//注意，当创建新的PreparedStatement对象时，不会马上把其存到缓存中，只有当PreparedStatement对象关闭时才会放到缓存中

                        } catch (SQLException sqlEx) {
                            // Punt, if necessary
                            if (getEmulateUnsupportedPstmts()) {
                                pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);

                                if (sql.length() < getPreparedStatementCacheSqlLimit()) {
                                    this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
                                }
                            } else {
                                throw sqlEx;
                            }
                        }
                    }
                }
            } else {
                try {
                    pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
                            this.database, resultSetType, resultSetConcurrency);

                    pStmt.setResultSetType(resultSetType);
                    pStmt.setResultSetConcurrency(resultSetConcurrency);
                } catch (SQLException sqlEx) {
                    // Punt, if necessary
                    if (getEmulateUnsupportedPstmts()) {
                        pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                    } else {
                        throw sqlEx;
                    }
                }
            }
        } else {
            pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
        }

        return pStmt;
    }
}

本文总结

PreparedStatement的预编译，需要在url中指定开启预编译useServerPrepStmts=true，才会在创建PreparedStatement对象时向mysql发送预编译的请求
每次向mysql发送预编译请求，不管之前当前链接有没有编译过此SQL语句，mysql都会预编译一次其实这和Statement每次都需要mysql重新编译一次SQL语句的道理是一样的，不管之前有没有执行过此SQL语句，只要请求的命令是Prepare或Query，mysql就会重新编译一次SQL语句，并返回此链接当前唯一的Statement ID，后续执行SQL语句的时候，程序只需拿着Statement ID和参数就可以了。
若预编译的SQL语句有语法错误，则mysql的响应会携带错误信息，但此错误信息JDBC感知不到(或者说mysql-connetor-java.jar包里的实现将其忽略掉了)，此时还会继续往下执行代码，当执行到executeXxx()方法时，由于没有Statement ID，所以就会将拼接完整的SQL语句值已经将占位符(?)替换掉发给mysql请求执行，此时mysql响应有语法错误，然后JDBC就会抛出语法错误异常，所以检查语法那一步实在mysql-server中做的(通过抓包可以看到)
PreparedStatement对性能的提高是利用缓存实现的，此缓存是mysql-connetor-java.jar包里实现的，而非mysql-server中的缓存，缓存需要在url中指定开启cachePrepStmts=true才会有效，缓存的key是完整的sql语句，value是PreparedStatement对象。
mysql-connetor-java.jar包里缓存的实现：只有在PreparedStatement对象关闭时close()才会将PreparedStatement对象放到缓存中，所以只要缓存PreparedStatement对象没有关闭，你不管调用多少次connection.prapareStatement(sql)对相同的sql语句进行预编译，都会将预编译的请求发给mysql，mysql也会对每一个sql语句不管是否相同进行预编译，并生成一个唯一的Statement ID并返回

mysql-connetor-java.jar实现的缓存是针对链接的，每个链接都是独立的，不共享缓存
部分代码：

try {
    Class.forName("com.mysql.jdbc.Driver");
    //第一个连接
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=128&prepStmtCacheSqlLimit=256", "root", "xxx");
    String sql = "SELECT ROUND(member_price,10) as member_price  from member_price WHERE `month` = '2015-11' and id = ?";
    PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
    preparedStatement1.setString(1, "2");
    ResultSet resultSet = preparedStatement1.executeQuery();
    preparedStatement1.close();

    //第二个连接
    Connection connection2 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=128&prepStmtCacheSqlLimit=256", "root", "xxx");
    PreparedStatement preparedStatement2 = connection2.prepareStatement(sql);
    preparedStatement2.setString(1, "1");
    ResultSet resultSet = preparedStatement2.executeQuery();
}catch (Exception e){
    e.printStackTrace();
}

mysql日志：

注意： mysql预编译功能有版本要求，包括server版本和mysql.jar包版本。以前的版本默认useServerPrepStmts=true，5.0.5以后的版本默认useServerPrepStmts=false

JDBC PreparedStatement

JDBC连接mysql实例

一. 不使用预编译

代码：

mysql日志信息：

抓包信息：

总结：

二. 使用预编译，不使用缓存

代码：

mysql日志信息：

抓包信息：

总结：

三. 使用预编译，使用缓存

代码：

mysql日志信息：

抓包信息：

总结：

本文总结

内容目录