Java中连结MySQL启用预编译的先决条件_极悦注册
首页 > 行业资讯 > Java中连结MySQL启用预编译的先决条件

Java中连结MySQL启用预编译的先决条件


在Java编程中，应用代码绝大多数使用了使用了PreparedStatement,无论你是直接使用JDBC还是使用框架。

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

在我所能见到的项目中，几乎没有见过启用MySQL预编译功能的。网上更有文章说MySQL不支持预编译，实在是害人不浅。

这篇文章分以下几个方面：

一、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 = ? _cke_saved_name = ?';
　　set @name='axman' _cke_saved_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 = ? _cke_saved_name = ?'

　　51 Prepare select * from axman_test where name = ?

　　51 Query set @name='axman' _cke_saved_name='axman'

　　111028 9:25:08 51 Query execute myPreparedStmt using @name

　　51 Execute select * from axman_test where name = 'axman' _cke_saved_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 = ? _cke_saved_name = ?'");

　　stmt.execute("set @name='axman' _cke_saved_name='axman'");

　　stmt.executeQuery("execute mystmt using @name");

　　stmt.close();

　　} finally {

　　if (conn != null) {

　　conn.close();

　　}

　　}

看日志输出：

　　111028 9:30:19 52 Connect root@localhost on mysql

　　52 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_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 = ? _cke_saved_name = ?'

　　52 Prepare select * from axman_test where name = ?

　　52 Query set @name='axman' _cke_saved_name='axman'

　　52 Query execute mystmt using @name

　　52 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　52 Quit

三、默认的PrearedStatement不能开启MySQL预编译功能：

　　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 = ? _cke_saved_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 root@localhost on mysql

　　53 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_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' _cke_saved_name = 'axman' or 1==1'

　　111028 9:54:04 53 Query select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　53 Quit

两条语句都是直接执行，而没有预编译。注意我的第一条语句select * from axman_test where name = 'axman' _cke_saved_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 root@localhost on mysql

　　54 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_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' _cke_saved_name = 'axman' or 1==1'

　　111028 10:04:53 54 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　54 Close stmt

　　54 Quit

如果useServerPrepStmts=true，ConneciontImpl在prepareStatement时会产生一个ServerPreparedStatement.在这个ServerPreparedStatement对象构造时首先会把当前SQL语句发送给MySQL进行预编译，然后将返回的结果缓存起来，其中包含预编译的名称(我们可以看成是当前SQL语句编译后的函数名)，签名(参数列表)，然后执行的时候就会直接把参数传给这个函数请求MySQL执行这个函数。否则返回的是客户端预编译语句，它仅做参数化工作，见第五节。

注意上面的代码中，两次执行使用的是同一个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 = ? _cke_saved_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 = ? _cke_saved_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 root@localhost on mysql

　　55 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_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' _cke_saved_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' _cke_saved_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 root@localhost on mysql

　　58 Query /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SHOW VARIABLES WHERE Variable_name ='language' _cke_saved_name ='language' OR Variable_name = 'net_write_timeout' _cke_saved_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' _cke_saved_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' _cke_saved_name = 'wait_timeout' OR Variable_name = 'character_set_client' _cke_saved_name = 'character_set_client' OR Variable_name = 'character_set_connection' _cke_saved_name = 'character_set_connection' OR Variable_name = 'character_set' _cke_saved_name = 'character_set' OR Variable_name = 'character_set_server' _cke_saved_name = 'character_set_server' OR Variable_name = 'tx_isolation' _cke_saved_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' _cke_saved_name = 'transaction_isolation' OR Variable_name = 'character_set_results' _cke_saved_name = 'character_set_results' OR Variable_name = 'timezone' _cke_saved_name = 'timezone' OR Variable_name = 'time_zone' _cke_saved_name = 'time_zone' OR Variable_name = 'system_time_zone' _cke_saved_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' _cke_saved_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' _cke_saved_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' _cke_saved_name = 'net_buffer_length' OR Variable_name = 'sql_mode' _cke_saved_name = 'sql_mode' OR Variable_name = 'query_cache_type' _cke_saved_name = 'query_cache_type' OR Variable_name = 'query_cache_size' _cke_saved_name = 'query_cache_size' OR Variable_name = 'init_connect' _cke_saved_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' _cke_saved_name = 'axman' or 1==1'

　　111028 10:27:24 58 Execute select * from axman_test where name = 'axman' _cke_saved_name = 'axman'

　　58 Quit

注意仅发生一次预编译，尽管代码本身在第一次执行后关闭了ps.close();但因为使用了cachePrepStmts=true,底层并没有真实关闭。

五、即使没有开启MySQL的预编译，坚持使用PreparedStatement仍然非常必要。

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

上一篇：12306奇葩验证码的开发者会是那家的？
下一篇：Java视频意想不到的结果
分享到0

开班信息

Java资讯

常见问题

学院新闻

  • 极悦注册班
  • 热点话题
  • 城市分站
  • 资源广场
  • 热门专题
北京极悦注册 广州极悦注册 深圳极悦注册 上海极悦注册 武汉极悦注册 成都极悦注册 西安极悦注册 郑州极悦注册 南京极悦注册 长沙极悦注册
更新时间：2024-08-03 19:59:03 免费电话：444-1124-454 备案号：京ICP备09027468号