黄色网址大全免费-黄色网址你懂得-黄色网址你懂的-黄色网址有那些-免费超爽视频-免费大片黄国产在线观看

Java中連結(jié)MySQL啟用預(yù)編譯的先決條件


在Java編程中，應(yīng)用代碼絕大多數(shù)使用了使用了PreparedStatement,無論你是直接使用JDBC還是使用框架。

在Java編程中，絕大多數(shù)使用了使用了PreparedStatement的應(yīng)用代碼沒有啟用預(yù)編譯,無論你是直接使用JDBC還是使用框架。

在我所能見到的項(xiàng)目中，幾乎沒有見過啟用MySQL預(yù)編譯功能的。網(wǎng)上更有文章說MySQL不支持預(yù)編譯，實(shí)在是害人不淺。

這篇文章分以下幾個(gè)方面：

一、MySQL是支持預(yù)編譯的

打開MySQL日志功能，啟動(dòng)MySQL，然后 tail -f mysql.log.path(默認(rèn)：/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;


控制臺(tái)可以正確地輸出：

mysql> execute myPreparedStmt using @name;

　　+----+-------+------+

　　| ID | name | age |

　　+----+-------+------+

　　| 1 | axman | 1000 |

　　+----+-------+------+

　　1 row in set (0.00 sec)

而log文件中也忠實(shí)地記錄如下：

　　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本身是可以預(yù)編譯的，這個(gè)不用多說。相當(dāng)于我們把控制臺(tái)輸入的命令直接通過JDBC語句來執(zhí)行：

　　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

三、默認(rèn)的PrearedStatement不能開啟MySQL預(yù)編譯功能：

　　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

兩條語句都是直接執(zhí)行，而沒有預(yù)編譯。注意我的第一條語句select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'，下面還會(huì)說到它。

接著我們改變一下jdbc.url的選項(xiàng)：

　　String url = "jdbc:mysql://localhost:3306/mysql?cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

執(zhí)行上面的代碼還是沒有開啟Mysql的預(yù)編譯。

四、只有使用了useServerPrepStmts=true才能開啟Mysql的預(yù)編譯。

上面的代碼其它不變，只修改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時(shí)會(huì)產(chǎn)生一個(gè)ServerPreparedStatement.在這個(gè)ServerPreparedStatement對(duì)象構(gòu)造時(shí)首先會(huì)把當(dāng)前SQL語句發(fā)送給MySQL進(jìn)行預(yù)編譯，然后將返回的結(jié)果緩存起來，其中包含預(yù)編譯的名稱(我們可以看成是當(dāng)前SQL語句編譯后的函數(shù)名)，簽名(參數(shù)列表)，然后執(zhí)行的時(shí)候就會(huì)直接把參數(shù)傳給這個(gè)函數(shù)請(qǐng)求MySQL執(zhí)行這個(gè)函數(shù)。否則返回的是客戶端預(yù)編譯語句，它僅做參數(shù)化工作，見第五節(jié)。

注意上面的代碼中，兩次執(zhí)行使用的是同一個(gè)PreparedStatement句柄.如果使用個(gè)不同的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

同一個(gè)SQL語句發(fā)生了兩次預(yù)編譯。這不是我們想要的效果，要想對(duì)同一SQL語句多次執(zhí)行不是每次都預(yù)編譯，就要使用cachePrepStmts=true，這個(gè)選項(xiàng)可以讓JVM端緩存每個(gè)SQL語句的預(yù)編譯結(jié)果，說白了就是以SQL語句為key, 將預(yù)編譯結(jié)果緩存起來，下次遇到相同的SQL語句時(shí)作為key去get一下看看有沒有這個(gè)SQL語句的預(yù)編譯結(jié)果，有就直接合出來用。我們還是以事實(shí)來說明：

上面的代碼只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

這行代碼中有其它參數(shù)自己去讀文檔，我不多啰嗦，執(zhí)行的結(jié)果：

　　111028 10:27:23 58 Connect root@localhost on mysql

　　58 Query /* mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */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: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) */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

注意僅發(fā)生一次預(yù)編譯，盡管代碼本身在第一次執(zhí)行后關(guān)閉了ps.close();但因?yàn)槭褂昧薱achePrepStmts=true,底層并沒有真實(shí)關(guān)閉。

五、即使沒有開啟MySQL的預(yù)編譯，堅(jiān)持使用PreparedStatement仍然非常必要。

在第三節(jié)的最后我說到"注意我的第一條語句select * from axman_test where name = 'axman' _cke_saved_name = 'axman' or 1==1'，下面還會(huì)說到它。"，現(xiàn)在我們回過頭來看，即使沒有開啟MySQL端的預(yù)編譯，我們?nèi)匀灰獔?jiān)持使用PreparedStatement，因?yàn)镴VM端對(duì)PreparedStatement的SQL語句進(jìn)行了參數(shù)化，即用占位符替換參數(shù)，以后任何內(nèi)容輸入都是字符串或其它類型的值，而不會(huì)和原始的SQL語句拚接產(chǎn)生SQL注入，對(duì)字符串中的任何字符都會(huì)做檢查，如果可能是SQL語句使用的標(biāo)識(shí)符，會(huì)進(jìn)行轉(zhuǎn)義。然后發(fā)送一個(gè)合法的安全的SQL語句給數(shù)據(jù)庫執(zhí)行。

上一篇：12306奇葩驗(yàn)證碼的開發(fā)者會(huì)是那家的？
下一篇：Java視頻意想不到的結(jié)果

開班信息

主站蜘蛛池模板： 女性成人毛片a级 | 精品国产成人a区在线观看 精品国产v无码大片在线观看 | 国产精品视频福利一区二区 | 天天操婷婷 | 99ri精品国产亚洲 | 人成在线视频 | 亚洲综合男人的天堂色婷婷 | 一级毛片在线免费看 | 国产不卡精品一区二区三区 | 色综合网站国产麻豆 | 欧美一级高清在线观看 | 亚洲人精品 | 中文日韩亚洲欧美制服 | 国内精品久久久久久西瓜色吧 | 一级片aaa | 国产黄色小视频 | 日日干天天爽 | 黄色a视频 | 日本国产成人精品视频 | 国产男女自拍视频 | a级男女性高爱潮高清试 | 一级毛片在线视频 | 国产乡下三片在线观看64 | 色屁屁一区二区三区视频国产 | 在线精品欧美 | 欧美变态口味重另类牲 | 日韩 欧美 综合 | 一级片麻豆 | 伊人久久大杳蕉综合大象 | 亚洲日本va中文字幕婷婷 | 欧美一级特黄视频 | 亚洲欧洲视频 | 香港国产特级一级毛片 | 天天拍天天射 | 亚洲国产精品v在线播放 | 青青免费在线视频 | 成人h免费观看视频 | 岛国毛片在线观看 | 男人女人真曰批视频大全免费观看 | 国产伦一区二区三区高清 | 欧美黑人性猛交╳xx╳动态图 |