JDBC預編譯語句
JDBC使用預編譯SQL的好處
1. 執行效率
PreparedStatement可以儘可能的提高訪問資料庫的效能，資料庫在處理SQL語句時都有一個預編譯的過程，而預編譯物件就是把一些格式固定的SQL編譯後，存放在記憶體池中即資料庫緩衝池，當我們再次執行相同的SQL語句時就不需要預編譯的過程了，只需DBMS執行SQL語句。所以當你需要執行Statement物件多次的時候，PreparedStatement物件將會大大降低執行時間，特別是的大型的資料庫中，它可以有效的也加快了訪問資料庫的速度。
String sqlString ="insert into user values('"+
user.id + "', '"+
user.password + "', '" +
user.name + "', '" +
user.email + "', '" +
user.address + "')";
使用PrearedStatement的SQL語句如下：
String sqlString ="insert into user(id, password, name, email, address) values(?, ?, ?, ?, ?)";
PreparedStatement pstmt = connection.PreparedStatement(sqlString);
pstmt.setString(1, user.id);
pstmt.setString(2, user.password);
pstmt.setString(3, user.name);
pstmt.setString(4, user.email);
pstmt.setString(5, user.address);使用佔位符？代替引數，將引數與SQL語句分離出來，這樣就可以方便對程式的更改和延續，同樣，也可以減少不必要的錯誤。
|
1
|
sqlString =
"select * from user where user.id = '"
+ userID + "' and user.password = "' + userPassword + "'"; |
|
1
|
sqlString = "select * from user where user.id ='1'or'1'='1'and
user.password ='1'or'1'='1' |
這個SQL語句中的where字句沒有起到資料篩選的作用，因為只要資料庫中有記錄，就會返回一個結果不為空的記錄集，查詢就會通過。上面的例子說明：在Web環境中，有惡意的使用者會利用那些設計不完善的、不能正確處理字串的應用程式。特別是在公共Web站點上,在沒有首先通過PreparedStatement物件處理的情況下，所有的使用者輸入都不應該傳遞給SQL語句。此外，在使用者有機會修改SQL語句的地方，如HTML的隱藏區域或一個查詢字串上，SQL語句都不應該被顯示出來。
3. 減少硬解析，節約大量的CPU資源
使用預編譯可以利用資料庫的軟解析降低資源消耗，所謂軟解析，就是因為相同文字的SQL語句存在於library cache中，所以本次SQL語句的解析就可以去掉硬解析中的一個或多個步驟（主要為選擇執行計劃步驟），從而節省大量的資源的耗費。
關於軟解析和硬解析，可參見：http://blog.csdn.net/cyzero/article/details/8621171
附：
Statement，PreparedStatement 與 PreparedStatement + 批處理 的用法與比較
java方面
1.使用Statement物件
2.預編譯PreparedStatement
3.使用PreparedStatement + 批處理
為了區分出這三者之間的效率，下面的事例執行過程都是在資料庫表t1中插入1萬條記錄，並記錄出所需的時間(此時間與電腦硬體有關)
1.使用Statement物件
使用範圍：當執行相似SQL(結構相同，具體值不同)語句的次數比較少
優點：語法簡單
缺點:採用硬編碼效率低，安全性較差。
原理：硬編碼，每次執行時相似SQL都會進行編譯
事例執行過程:
public void exec(Connection conn){
try {
Long beginTime = System.currentTimeMillis();
conn.setAutoCommit(false);//設定手動提交
Statement st = conn.createStatement();
for(int i=0;i<10000;i++){
String sql="insert into t1(id) values ("+i+")";
st.executeUpdate(sql);
}
Long endTime = System.currentTimeMillis();
System.out.println("Statement用時："+(endTime-beginTime)/1000+"秒");//計算時間
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
執行時間：Statement用時：31秒
2.預編譯PreparedStatement
使用範圍：當執行相似sql語句的次數比較多（例如使用者登陸，對錶頻繁操作..）語句一樣，只是具體的值不一樣，被稱為動態SQL
優點：語句只編譯一次，減少編譯次數。提高了安全性（阻止了SQL隱碼攻擊）
缺點: 執行非相似SQL語句時，速度較慢。
原理：相似SQL只編譯一次，減少編譯次數
名詞解釋：
SQL隱碼攻擊:select * from user where username="張三" and password="123" or 1=1;
前面這條語句紅色部分就是利用sql注入，使得這條詞句使終都會返回一條記錄，從而降低了安全性。
事例執行過程:
public void exec2(Connection conn){
try {
Long beginTime = System.currentTimeMillis();
conn.setAutoCommit(false);//手動提交
PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");
for(int i=0;i<10000;i++){
pst.setInt(1, i);
pst.execute();
}
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("Pst用時:"+(endTime-beginTime)+"秒");//計算時間
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
執行時間：Pst用時:14秒
3.使用PreparedStatement + 批處理
使用範圍：一次需要更新資料庫表多條記錄
優點：減少和SQL引擎互動的次數，再次提高效率,相似語句只編譯一次，減少編譯次數。提高了安全性（阻止了SQL隱碼攻擊）
缺點:
原理：
批處理: 減少和SQL引擎互動的次數，一次傳遞給SQL引擎多條SQL。
名詞解釋：
PL/SQL引擎：在oracle中執行pl/sql程式碼的引擎，在執行中發現標準的sql會交給sql引擎進行處理。
SQL引擎：執行標準sql的引擎。
事例執行過程:
public void exec3(Connection conn){
try {
conn.setAutoCommit(false);
Long beginTime = System.currentTimeMillis();
PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");
for(int i=1;i<=10000;i++){
pst.setInt(1, i);
pst.addBatch();//加入批處理，進行打包
if(i%1000==0){//可以設定不同的大小；如50，100，500，1000等等
pst.executeBatch();
conn.commit();
pst.clearBatch();
}
}
pst.executeBatch();
Long endTime = System.currentTimeMillis();
System.out.println("pst+batch用時："+(endTime-beginTime)+"毫秒");
pst.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
執行時間：pst+batch用時：485毫秒
資料庫方面
1 靜態SQL
使用範圍：
優點：每次執行相似sql都會進行編譯。
缺點:效率低,佔用破費cpu資源，耗費SGA 資源。
原理：(檢查SQL 語句在SGA 中的共享池中是否存在, 如果不存在，則編譯、解析後執行：硬解析,
如果已存在，則取出編譯結果執行：軟解析)
事例執行過程:
DECLARE
time1 NUMBER;
time2 NUMBER;
BEGIN
time1 := dbms_utility.get_time;
EXECUTE IMMEDIATE 'truncate table t_demo';
FOR i IN 1..10000
LOOP
EXECUTE IMMEDIATE
'insert into t_demo(u_id) values('||i||')';
END LOOP;
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)/100||'秒');
END
執行時間：pst+batch用時：20.93秒
2 動態SQL
使用範圍：
優點：語句只編譯一次，減少編譯次數，提高效率。
缺點:
原理：減少編譯次數(檢查SQL 語句在SGA 中的共享池中是否存在, 如果不存在，則編譯、解析後執行：硬解析,
如果已存在，則取出編譯結果執行：軟解析)
事例執行過程:
DECLARE
time1 NUMBER;
time2 NUMBER;
BEGIN
time1 := dbms_utility.get_time;
EXECUTE IMMEDIATE 'truncate table t_demo';
FOR i IN 1..10000
LOOP
EXECUTE IMMEDIATE
'insert into t1(u_id) values(:x)' USING i;
END LOOP;
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)/100||'秒');
END;
執行時間：pst+batch用時：10.55秒
3 利用forall進行批處理<相似java中的批處理>
使用範圍：當執行相似sql語句的次數比較多（例如使用者登陸，對錶頻繁操作..）FORALL只能執行一條sql語句。
優點：減少與SQL引擎轉替次數，提交效率。
缺點:迴圈時從開始到結束，一次性讀完，不能取其中的某些迴圈進行操作。
原理：使用ForAll語句可以讓PL/SQL引擎一次將多條SQL轉發給SQL引擎，從而減少轉發次數，提高系統效能。
事例執行過程:
CREATE OR REPLACE PROCEDURE p_test
AS
--定義新的型別(不是定義變數！)
TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
--用新的型別來定義變數
id_table id_table_type;
time1 NUMBER;
time2 NUMBER;
BEGIN
FOR i IN 1..10000
LOOP
--往兩個陣列中存放資料
id_table(i) := i;
END LOOP;
--★：一次性向SQL引擎傳遞了多條資料而不是一條
time1 := dbms_utility.get_time;
FORALL i IN 1..id_table.COUNT
INSERT INTO t_demo(u_id) VALUES(id_table(i));
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)*10||'毫秒');
end p_test;
call p_test();
執行時間：pst+batch用時：170毫秒
4 利用BULK COLLECT
使用範圍：處理資料量比較少時。
優點：一次讀取多行資料，提高效率。
缺點: 需要較大的記憶體開銷
原理：將多個行引入一個或多個集合中。
事例執行過程:
DECLARE
TYPE books_aat
IS TABLE OF book%ROWTYPE
INDEX BY PLS_INTEGER;
book books_aat;
BEGIN
SELECT *
BULK COLLECT INTO book
FROM books
WHERE title LIKE '%PL/SQL%';
...
END;
注：本文整理自網路
相關文章
- x86彙編反編譯到c語言之——(2)if語句編譯C語言
- gcc 從語言編譯全過程 預處理---->編譯---->彙編----->連結GC編譯
- #ifndef預編譯編譯
- CSS預編譯語言Less的用法總結CSS編譯
- 【譯】編寫更好JavaScript條件語句的5個技巧JavaScript
- MySQL JDBC 出現多個 SHOW VARIABLES 語句。MySqlJDBC
- js預編譯 --預編譯詳解四部曲JS編譯
- [譯] part 10: switch 語句
- CSS預編譯器CSS編譯
- 手把手教你做一個 C 語言編譯器（7）：語句編譯
- x86彙編反編譯到c語言之——(1)表示式求值及賦值語句編譯C語言賦值
- c++進階（一）C語言條件編譯及編譯預處理階段C++C語言編譯
- GCC編譯過程（預處理-＞編譯-＞彙編-＞連結）GC編譯
- 對預編譯的理解編譯
- stylus（css預編譯器）CSS編譯
- JDBC使用INSERT RETURN語句報錯ORA-439JDBC
- 大話css預編譯處理（三）：基礎語法篇CSS編譯
- [譯] part 9: golang 迴圈語句Golang
- 關於預編譯頭 (轉)編譯
- JDBC 增刪改查程式碼 過濾查詢語句JDBC
- Go 編譯器內部知識：向 Go 新增新語句-第 2 部分Go編譯
- C語言編譯工具C語言編譯
- jdbc編碼？JDBC
- java編譯、編碼、語言設定Java編譯
- MySQL 遊標（PREPARE預處理語句）MySql
- [譯] 語句，訊息和歸約器
- sqlserver2005預編譯SQLServer編譯
- JavaScript的預編譯過程分析JavaScript編譯
- dll預編譯提高webpack打包速度編譯Web
- 預編譯過程（AO+GO）編譯Go
- JavaScript預編譯原理, 引擎，作用域JavaScript編譯原理
- css預編譯--sass基礎篇CSS編譯
- MySQL中常用SQL語句的編寫MySql
- 源語言、目標語言、翻譯器、編譯器、直譯器編譯
- 【編譯原理】語法分析（三）編譯原理語法分析
- 【指令碼編輯】每一千行UPDATE語句後插入一條COMMIT語句的編輯方法指令碼MIT
- flask之控制語句 if 語句與for語句Flask
- JAVA語法糖和語法糖編譯Java編譯