首页 > 行业资讯 > 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资讯

常见问题

学院新闻

  • Java培训班
  • 热点话题
  • 城市分站
  • 资源广场
  • 热门专题
Java培训实验班 Java培训就业班 Java培训夜校班 Java培训在职加薪班 Java培训架构师班 Java培训课程
Java培训动态 零基础学Java Java职场就业
北京Java培训 广州Java培训 深圳Java培训 上海Java培训 武汉Java培训 成都Java培训 西安Java培训 郑州Java培训 南京Java培训 长沙Java培训
Java书籍 Java视频教程 Java毕业设计 Java面试题 Java API文档 Java开发工具 Java星辉在线学习
上海java培训 北京java培训 深圳java培训 广州java培训 武汉java培训 成都java培训 西安java培训 郑州java培训 南京java培训 长沙java培训 java线上培训班
更新时间：2024-05-21 06:00:43 免费电话：444-1124-454 备案号：京ICP备09027468号