PreparedStatement与Statement

sql优化的一个境界是不仅要PreparedStatement,还要尽量减少sql的执行次数,大数据一般是在应用程序中通过循环嵌套形成,另一种通过表表的笛卡尔集形成.减少sql的执行次数可以通过巧妙的利用数据库的笛卡尔集实现.


PreparedStatement:

1.PreparedStatement的节省的时间是网络开销的时间,真正sql执行的时间大概占10%,建立网络连接的时间大概占90%.

对于一些批量插入的操作,DB的服务器端仍然是逐条插入的(以前一直以为DB服务器端对于PrestatedStatement的sql只执行一次,这是错误的理解, 仍然是多次,只是这个多次是在一次连接的基础上实现,而Statement的sql是每次一个连接.多次执行)

2.另外一个省掉的时间是预编译的sql被缓存,并且只生成一次执行计划,而Statement的每一条sql都会生成一次执行划.

3.PreparedStatement一次把参数和带占位符的sql通过连接全部发送到数据库.注意,有可能是多次发送报文,如果报文过大的话,在应用层协议下面的协议会将报文分批发送.

4.PrestateStatement的主要问题是sql调试不爽,只能看到带?的sql, 不过这个通过jdbclogger的工具可以解决,通过jdbclogger其实可以看到预编译sql执行是多条,如果不配置jdbclogger这样的工具,在日志文件里只能看到一条带?点位符的sql, 会误以为只执行一次.

使用PrepareStatement的一个极致的例子:

/* Formatted on 2011-8-2 18:54:00 (QP5 v5.114.809.3010) */
MERGE
INTO CPC.CPCPARTNERAUDIT cpa
USING (
SELECT5011AS OPID,1011AS ACCOUNTID,2011AS GROUPID,3013AS IDEAID,4011AS KEYID,0AS CHECKSTATUS,TO_DATE('07/21/2011 14:22:50', 'MM/DD/YYYY HH24:MI:SS') AS CREATEDATE,'包含了正在审核中的元素'AS REFUSEREASON, 0AS ADMINUSERID,'自动审核'AS ADMINUSERNAME,'包含了正在审核中的元素'AS AUDITREASON,NULLAS BACKUPIDEAID FROM DUAL) cpai
ON (cpa.keyid=cpai.keyid AND cpa.ideaid=cpai.ideaid)
WHENNOT MATCHED THEN
INSERT
VALUES (cpai.OPID, cpai.ACCOUNTID, cpai.GROUPID, cpai.IDEAID, cpai.KEYID,
cpai.CHECKSTATUS, cpai.CREATEDATE, cpai.REFUSEREASON, cpai.ADMINUSERID, cpai.ADMINUSERNAME,
cpai.AUDITREASON,cpai.BACKUPIDEAID)

 

对于应用程序,不再需要二条sql,一条判重,一条插入.只需要面对一条PreparedStatement sql.

/* Formatted on 2011-8-2 18:54:00 (QP5 v5.114.809.3010) */
MERGE
INTO CPC.CPCPARTNERAUDIT cpa
USING (
SELECT ? AS OPID,? AS ACCOUNTID,? AS GROUPID,? AS IDEAID,? AS KEYID,? AS CHECKSTATUS,? AS CREATEDATE,? AS REFUSEREASON, ? AS ADMINUSERID,? AS ADMINUSERNAME,? AS AUDITREASON,? AS BACKUPIDEAID  FROM DUAL) cpai
ON (cpa.keyid=cpai.keyid AND cpa.ideaid=cpai.ideaid)
WHENNOT MATCHED THEN
INSERT
VALUES (cpai.OPID, cpai.ACCOUNTID, cpai.GROUPID, cpai.IDEAID, cpai.KEYID,
cpai.CHECKSTATUS, cpai.CREATEDATE, cpai.REFUSEREASON, cpai.ADMINUSERID, cpai.ADMINUSERNAME,
cpai.AUDITREASON,cpai.BACKUPIDEAID)

 

 

 

Statement:

1.Statement的这网络开销与执行计划开销都存在

db_conn = DriverManager.getConnection("jdbc:oracle:thin:" + s1);

db_stmt = db_conn.createStatement();

db_rset = db_stmt.executeQuery(s1);

db_stmt.close();

db_rset.close();

db_conn.close();

 

2.Statement可能sql注入.

        Example: you have a query 
String query = "select * from t_location where id = '" + user_wanna_see_this_location + "'"; 
where user_wanna_see_this_location is submitted by user. http://daodao.com/example?user_wanna_see_this_location=2011
        Someone submitted 
' or true; drop table t_location; select * from t_location where '' = '
        Always use parameterized sql statement
String query = "select * from t_location where id = ?";

 

3.PrestatedStatement单次执行较Statement慢,如果只执行一次,Statement的开销较PreparedStatement的开销小.

4.Statement每次sql都会打印,好调试.

 

-------------------------------------------

附:sql注入:
当处理公共Web站点上的用户传来的数据的时候,安全性的问题就 变得极为重要。传递给PreparedStatement的字符串参数会自动被驱动器忽略。最简单的情况下,这就意味着当你的程序试着将字符串 “D'Angelo”插入到VARCHAR2中时,该语句将不会识别第一个“,”,从而导致悲惨的失败。几乎很少有必要创建你自己的字符串忽略代码。

在 Web环境中,有恶意的用户会利用那些设计不完善的、不能正确处理字符串的应用程序。特别是在公共Web站点上,在没有首先通过 PreparedStatement对象处理的情况下,所有的用户输入都不应该传递给SQL语句。此外,在用户有机会修改SQL语句的地方,如HTML的 隐藏区域或一个查询字符串上,SQL语句都不应该被显示出来。
在执行SQL命令时,我们有二种选择:可以使用PreparedStatement 对象,也可以使用Statement对象。无论多少次地使用同一个SQL命令,PreparedStatement都只对它解析和编译一次。当使用 Statement对象时,每次执行一个SQL命令时,都会对它进行解析和编译。  

posted @ 2011-08-02 16:15  highriver  阅读(1735)  评论(0编辑  收藏  举报