当前位置: 代码迷 >> MySQL >> 【转载】Java中连结MySQL起用预编译的先决条件是useServerPstmts=true
  详细解决方案

【转载】Java中连结MySQL起用预编译的先决条件是useServerPstmts=true

热度:354   发布时间:2016-05-05 16:24:39.0
【转载】Java中连结MySQL启用预编译的先决条件是useServerPstmts=true.
Java中连结MySQL启用预编译的先决条件是useServerPstmts=true.
标签: mysqljavacharacterquerycollationtimezone
2011-10-28 10:47 7140人阅读 评论(4) 收藏 举报
分类:  go deep into final(37)   JDBC(28) 
版权声明:本文为博主原创文章,未经博主允许不得转载。

    在Java编程中,应用代码绝大多数使用了PreparedStatement,无论你是直接使用JDBC还是使用框架。
    在Java编程中,绝大多数使用了使用了PreparedStatement连接MySQL的应用代码没有启用预编译,无论你是直接使用JDBC还是使用框架。

    在我所能见到的项目中,几乎没有见过启用MySQL预编译功能的。网上更有文章说MySQL不支持预编译,实在是害人不浅。
    要想知道你的应用是否真正的使用了预编译,请执行:show global status like '%prepare%';看看曾经编译过几条,当前Prepared_stmt_count 是多少。大多数是0吧?
    这篇文章分以下几个方面:
   
    一.MySQL是支持预编译的

    打开MySQL日志功能,启动MySQL,然后 tail -f mysql.log.path(默认:/var/log/mysql/mysql.log).
   
    create table axman_test (ID int(4) auto_increment primary key, name varchar(20),age int(4));
    insert into axman_test (name,age) values ('axman',1000);

    prepare myPreparedStmt from 'select * from axman_test where name = ?';   
    set @name='axman';   
    execute myPreparedStmt using @name;

    控制台可以正确地输出:

mysql> execute myPreparedStmt using @name;
+----+-------+------+
| ID | name  | age  |
+----+-------+------+
|  1 | axman | 1000 |
+----+-------+------+
1 row in set (0.00 sec)       

    而log文件中也忠实地记录如下:
   
111028  9:25:06       51 Query    prepare myPreparedStmt from 'select * from axman_test where name = ?'
           51 Prepare    select * from axman_test where name = ?
           51 Query    set @name='axman'
111028  9:25:08       51 Query    execute myPreparedStmt using @name
           51 Execute    select * from axman_test where name = 'axman'



    二.通过JDBC本身是可以预编译的,这个不用多说。相当于我们把控制台输入的命令直接通过JDBC语句来执行:

        Class.forName("org.gjt.mm.mysql.Driver");
        String url = "jdbc:mysql://localhost:3306/mysql";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, "root", "12345678");
            Statement stmt = conn.createStatement();
            /*以下忽略返回值处理*/
            stmt.executeUpdate("prepare mystmt from 'select * from axman_test where name = ?'");
            stmt.execute("set @name='axman'");
            stmt.executeQuery("execute mystmt using @name");
            stmt.close();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

    看日志输出:

111028  9:30:19       52 Connect    [email protected] on mysql
           52 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
           52 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment
           52 Query    SHOW COLLATION
           52 Query    SET NAMES latin1
           52 Query    SET character_set_results = NULL
           52 Query    SET autocommit=1
           52 Query    SET sql_mode='STRICT_TRANS_TABLES'
           52 Query    prepare mystmt from 'select * from axman_test where name = ?'
           52 Prepare    select * from axman_test where name = ?
           52 Query    set @name='axman'
           52 Query    execute mystmt using @name
           52 Execute    select * from axman_test where name = 'axman'
           52 Quit   


    三.默认的PrearedStatement不能开启MySQL预编译功能:
      
       虽然第二节中我们通过JDBC手工指定MySQL进行预编译,但是PrearedStatement却并不自动帮我们做这件事。
        Class.forName("org.gjt.mm.mysql.Driver");
        String url = "jdbc:mysql://localhost:3306/mysql";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, "root", "12345678");
            PreparedStatement ps = conn.prepareStatement("select * from axman_test where name = ?");
            ps.setString(1, "axman' or 1==1");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            Thread.sleep(1000);
            rs.close();
            ps.clearParameters();
            ps.setString(1, "axman");
            rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            ps.close();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }

    废话少说,直接看日志:
111028  9:54:03       53 Connect    [email protected] on mysql
           53 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
           53 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment
           53 Query    SHOW COLLATION
           53 Query    SET NAMES latin1
           53 Query    SET character_set_results = NULL
           53 Query    SET autocommit=1
           53 Query    SET sql_mode='STRICT_TRANS_TABLES'
           53 Query    select * from axman_test where name = 'axman\' or 1==1'
111028  9:54:04       53 Query    select * from axman_test where name = 'axman'
           53 Quit   

    两条语句都是直接执行,而没有预编译。注意我的第一条语句select * from axman_test where name = 'axman\' or 1==1',下面还会说到它。
    接着我们改变一下jdbc.url的选项:
    String url = "jdbc:mysql://localhost:3306/mysql?cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";
    执行上面的代码还是没有开启Mysql的预编译。


    四.只有使用了useServerPrepStmts=true才能开启Mysql的预编译。

    上面的代码其它不变,只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";
    查看日志:
   
111028 10:04:52       54 Connect    [email protected] on mysql
           54 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
           54 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment
           54 Query    SHOW COLLATION
           54 Query    SET NAMES latin1
           54 Query    SET character_set_results = NULL
           54 Query    SET autocommit=1
           54 Query    SET sql_mode='STRICT_TRANS_TABLES'
           54 Prepare    select * from axman_test where name = ?
           54 Execute    select * from axman_test where name = 'axman\' or 1==1'
111028 10:04:53       54 Execute    select * from axman_test where name = 'axman'
           54 Close stmt   
           54 Quit   

    如果useServerPrepStmts=true,ConneciontImpl在prepareStatement时会产生一个ServerPreparedStatement.在这个ServerPreparedStatement对象构造时首先会把当前SQL语句发送给MySQL进行预编译,然后将返回的结果缓存起来,其中包含预编译的名称(我们可以看成是当前SQL语句编译后的函数名),签名(参数列表),然后执行的时候就会直接把参数传给这个函数请求MySQL执行这个函数。否则返回的是客户端预编译语句,它仅做参数化工作,见第五节。
    ServerPreparedStatement在请求预编译和执行预编译后的SQL 函数时,虽然和我们上面手工预编译工作相同,但它与MySQL交互使用的是压缩格式,如prepare指令码是22,这样可以减少交互时传输的数据量。
   
    注意上面的代码中,两次执行使用的是同一个PreparedStatement句柄.如果使用个不同的PreparedStatement句柄,把代码改成:
        Class.forName("org.gjt.mm.mysql.Driver");
        String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, "root", "12345678");
            PreparedStatement ps = conn.prepareStatement("select * from axman_test where name = ?");
            ps.setString(1, "axman' or 1==1");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            Thread.sleep(1000);
            rs.close();
            ps.close();
            ps = conn.prepareStatement("select * from axman_test where name = ?");
            ps.setString(1, "axman");
            rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            ps.close();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }   

    再看日志输出:
     Connect    [email protected] on mysql
           55 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
           55 Query    /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SELECT @@session.auto_increment_increment
           55 Query    SHOW COLLATION
           55 Query    SET NAMES latin1
           55 Query    SET character_set_results = NULL
           55 Query    SET autocommit=1
           55 Query    SET sql_mode='STRICT_TRANS_TABLES'
           55 Prepare    select * from axman_test where name = ?
           55 Execute    select * from axman_test where name = 'axman\' or 1==1'
111028 10:10:24       55 Close stmt   
           55 Prepare    select * from axman_test where name = ?
           55 Execute    select * from axman_test where name = 'axman'
           55 Close stmt   
           55 Quit   
           55 Quit
    同一个SQL语句发生了两次预编译。这不是我们想要的效果,要想对同一SQL语句多次执行不是每次都预编译,就要使用cachePrepStmts=true,这个选项可以让JVM端缓存每个SQL语句的预编译结果,说白了就是以SQL语句为key, 将预编译结果缓存起来,下次遇到相同的SQL语句时作为key去get一下看看有没有这个SQL语句的预编译结果,有就直接合出来用。我们还是以事实来说明:
    上面的代码只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";
这行代码中有其它参数自己去读文档,我不多啰嗦,执行的结果:
111028 10:27:23       58 Connect    [email protected] on mysql
           58 Query    /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
           58 Query    /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SELECT @@session.auto_increment_increment
           58 Query    SHOW COLLATION
           58 Query    SET NAMES latin1
           58 Query    SET character_set_results = NULL
           58 Query    SET autocommit=1
           58 Query    SET sql_mode='STRICT_TRANS_TABLES'
           58 Prepare    select * from axman_test where name = ?
           58 Execute    select * from axman_test where name = 'axman\' or 1==1'
111028 10:27:24       58 Execute    select * from axman_test where name = 'axman'
           58 Quit   

    注意仅发生一次预编译,尽管代码本身在第一次执行后关闭了ps.close();但因为使用了cachePrepStmts=true,底层并没有真实关闭。
    千万注意,同一条SQL语句尽量在一个全局的地方定义,然后在不同地方引用,这样做一是为了DBA方便地对SQL做统一检查和优化,就象IBatis把SQL语句定义在XML文件中一样。二是同一语句不同写法,即使空格不同,大小写不同也会重新预编译,因为JVM端缓存是直接以SQL本身为key而不会对SQL格式化以后再做为key。
    我们来看下面的输出:
           35 Prepare    select * from axman_test where name = ?
           35 Execute    select * from axman_test where name = 'axman\' or 1==1'
111029  9:54:31       35 Prepare    select * FROM axman_test where name = ?
           35 Execute    select * FROM axman_test where name = 'axman'
    第一条语句和第二条语句的差别是FROM在第二条语句中被大写了,这样还是发生了两次预编译。
           37 Prepare    select * from axman_test where name = ?
           37 Execute    select * from axman_test where name = 'axman\' or 1==1'
111029  9:59:00       37 Prepare    select * from    axman_test where name = ?
           37 Execute    select * from    axman_test where name = 'axman'
     这里两条语句只是第二条的from后面多了个空格,因为你现在看到是HTML格式,如果不加转义符,两个空格也显示一个空格,所以你能可看不到区别,但你可以在自己的机器上试一下。

    五.即使没有开启MySQL的预编译,坚持使用PreparedStatement仍然非常必要。
    在第三节的最后我说到"注意我的第一条语句select * from axman_test where name = 'axman\' or 1==1',下面还会说到它。",现在我们回过头来看,即使没有开启MySQL端的预编译,我们仍然要坚持使用PreparedStatement,因为JVM端对PreparedStatement的SQL语句进行了参数化,即用占位符替换参数,以后任何内容输入都是字符串或其它类型的值,而不会和原始的SQL语句拚接产生SQL注入,对字符串中的任何字符都会做检查,如果可能是SQL语句使用的标识符,会进行转义。然后发送一个合法的安全的SQL语句给数据库执行。
  相关解决方案