[关闭]
@stefanlu 2016-03-04T03:21:17.000000Z 字数 10683 阅读 2750

JDBC PreparedStatement

java


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


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


  1. 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
  2. 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
  3. ResultSet resultSet = preparedStatement.executeQuery();
    • Executes the SQL statement in this PreparedStatement object

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


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日志信息:

抓包信息:

1
2

总结:

通过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日志信息:

抓包信息:

1
2
3

总结:

  1. 代码区别: 链接url中添加?useServerPrepStmts=true
  2. 从mysql日志和抓包信息中可以看到,这次执行向mysql发了三次sql的执行信息

    • 第一次发送需要预编译的代码给mysql(mysql指令Prepare)
    • 第二次发送需要执行的SQL语句(Statement ID: 1)和参数(Value: 1 or '1'='1')
    • 第三次同第二次
  3. 此次,由于手动指定了需要预编译SQL语句(url参数 ?useServerPrepStmts=true),则在创建PreparedStatement是就会将SQL语句发送给Mysql进行预编译,此时编译通过的SQL语句就会被Mysql给缓存起来,并生成一个唯一的Statement ID,并返回一个Statement ID(Statement ID:1),下次需要执行这个SQL语句时,程序将需要执行的SQL语句的Statement ID和参数传给MySql,MySQL就会执行对应的SQL语句

  4. 若预编译的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日志信息:

1

抓包信息:

2

总结:

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

本文总结

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


添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注