轉自：http://blog.csdn.net/QH_JAVA/article/details/48245945
一、prepareStatement 的用法和解釋
1.PreparedStatement是預編譯的,對於批量處理可以大大提高效率. 也叫JDBC儲存過程
2.使用 Statement 物件。在對資料庫只執行一次性存取的時侯，用 Statement 物件進行處理。PreparedStatement 物件的開銷比Statement大，對於一次性操作並不會帶來額外的好處。
3.statement每次執行sql語句，相關資料庫都要執行sql語句的編譯，preparedstatement是預編譯得, preparedstatement支援批處理
4、
Code Fragment 1:
String updateString = "UPDATE COFFEES SET SALES = 75 " + "WHERE COF_NAME LIKE ′Colombian′";
stmt.executeUpdate(updateString);
Code Fragment 2:
PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
片斷2和片斷1的區別在於，後者使用了PreparedStatement物件，而前者是普通的Statement物件。PreparedStatement物件不僅包含了SQL語句，而且大多數情況下這個語句已經被預編譯過，因而當其執行時，只需DBMS執行SQL語句，而不必先編譯。當你需要執行Statement物件多次的時候，PreparedStatement物件將會大大降低執行時間，當然也加快了訪問資料庫的速度。
這種轉換也給你帶來很大的便利，不必重複SQL語句的句法，而只需更改其中變數的值，便可重新執行SQL語句。選擇PreparedStatement物件與否，在於相同句法的SQL語句是否執行了多次，而且兩次之間的差別僅僅是變數的不同。如果僅僅執行了一次的話，它應該和普通的物件毫無差異，體現不出它預編譯的優越性。
5.執行許多SQL語句的JDBC程式產生大量的Statement和PreparedStatement物件。通常認為PreparedStatement物件比Statement物件更有效,特別是如果帶有不同引數的同一SQL語句被多次執行的時候。PreparedStatement物件允許資料庫預編譯SQL語句，這樣在隨後的執行中可以節省時間並增加程式碼的可讀性。
然而，在Oracle環境中，開發人員實際上有更大的靈活性。當使用Statement或PreparedStatement物件時，Oracle資料庫會快取SQL語句以便以後使用。在一些情況下,由於驅動器自身需要額外的處理和在Java應用程式和Oracle伺服器間增加的網路活動，執行PreparedStatement物件實際上會花更長的時間。
然而，除了緩衝的問題之外，至少還有一個更好的原因使我們在企業應用程式中更喜歡使用PreparedStatement物件,那就是安全性。傳遞給PreparedStatement物件的引數可以被強制進行型別轉換，使開發人員可以確保在插入或查詢資料時與底層的資料庫格式匹配。
當處理公共Web站點上的使用者傳來的資料的時候，安全性的問題就變得極為重要。傳遞給PreparedStatement的字串引數會自動被驅動器忽略。最簡單的情況下，這就意味著當你的程式試著將字串“D'Angelo”插入到VARCHAR2中時，該語句將不會識別第一個“，”，從而導致悲慘的失敗。幾乎很少有必要建立你自己的字串忽略程式碼。
在Web環境中，有惡意的使用者會利用那些設計不完善的、不能正確處理字串的應用程式。特別是在公共Web站點上,在沒有首先通過PreparedStatement物件處理的情況下，所有的使用者輸入都不應該傳遞給SQL語句。此外，在使用者有機會修改SQL語句的地方，如HTML的隱藏區域或一個查詢字串上，SQL語句都不應該被顯示出來。
在執行SQL命令時，我們有二種選擇：可以使用PreparedStatement物件，也可以使用Statement物件。無論多少次地使用同一個SQL命令，PreparedStatement都只對它解析和編譯一次。當使用Statement物件時，每次執行一個SQL命令時，都會對它進行解析和編譯。
第一：
prepareStatement會先初始化SQL，先把這個SQL提交到資料庫中進行預處理，多次使用可提高效率。
Statement不會初始化，沒有預處理，沒次都是從0開始執行SQL
第二：
prepareStatement可以替換變數
在SQL語句中可以包含?，可以用ps=conn.prepareStatement("select * from Cust where ID=?");
int sid=1001;
ps.setInt(1, sid);
rs = ps.executeQuery();
可以把?替換成變數。
而Statement只能用
int sid=1001;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from Cust where ID="+sid);
來實現。
二、深入理解statement 和prepareStatement
1、使用Statement而不是PreparedStatement物件
JDBC驅動的最佳化是基於使用的是什麼功能. 選擇PreparedStatement還是Statement取決於你要怎麼使用它們. 對於只執行一次的SQL語句選擇Statement是最好的. 相反, 如果SQL語句被多次執行選用PreparedStatement是最好的.
PreparedStatement的第一次執行消耗是很高的. 它的效能體現在後面的重複執行. 例如, 假設我使用Employee ID, 使用prepared的方式來執行一個針對Employee表的查詢. JDBC驅動會傳送一個網路請求到資料解析和優化這個查詢. 而執行時會產生另一個網路請求.在JDBC驅動中，減少網路通訊是最終的目的. 如果我的程式在執行期間只需要一次請求, 那麼就使用Statement. 對於Statement, 同一個查詢只會產生一次網路到資料庫的通訊.
對於使用PreparedStatement池的情況下, 本指導原則有點複雜. 當使用PreparedStatement池時, 如果一個查詢很特殊, 並且不太會再次執行到, 那麼可以使用Statement. 如果一個查詢很少會被執行,但連線池中的Statement池可能被再次執行, 那麼請使用PreparedStatement. 在不是Statement池的同樣情況下, 請使用Statement.
2、使用PreparedStatement的Batch功能
Update大量的資料時, 先Prepare一個INSERT語句再多次的執行, 會導致很多次的網路連線. 要減少JDBC的呼叫次數改善效能, 你可以使用PreparedStatement的AddBatch()方法一次性傳送多個查詢給資料庫. 例如, 讓我們來比較一下下面的例子.
- PreparedStatement ps = conn.prepareStatement(
- "INSERT into employees values (?, ?, ?)");
- for (n = 0; n < 100; n++) {
- ps.setString(name[n]);
- ps.setLong(id[n]);
- ps.setInt(salary[n]);
- ps.executeUpdate();
- }
- PreparedStatement ps = conn.prepareStatement(
- "INSERT into employees values (?, ?, ?)");
- for (n = 0; n < 100; n++) {
- ps.setString(name[n]);
- ps.setLong(id[n]);
- ps.setInt(salary[n]);
- ps.addBatch();
- }
- ps.executeBatch();
在例 1中, PreparedStatement被用來多次執行INSERT語句. 在這裡, 執行了100次INSERT操作, 共有101次網路往返.
其中,1次往返是預儲PreparedStatement, 另外100次往返執行每個迭代.
在例2中, 當在100次INSERT操作中使用addBatch()方法時, 只有兩次網路往返.
1次往返是預儲PreparedStatement, 另一次是執行batch命令. 雖然Batch命令會用到更多的資料庫的CPU週期, 但是通過減少網路往返，效能得到提高.記住, JDBC的效能最大的增進是減少JDBC驅動與資料庫之間的網路通訊.次數
注：Oracel 10G的JDBC Driver限制最大Batch size是16383條，如果addBatch超過這個限制，那麼executeBatch時就會出現“無效的批值”（Invalid Batch Value） 異常。因此在如果使用的是Oracle10G，在此bug減少前，Batch size需要控制在一定的限度。
同樣mysql 5.5.28 批量執行的資料最大限度是多少不清楚，但自己試了1w,2w,3w 都沒問題，記得在url 後面新增：rewriteBatchedStatements=true 表示批量插入，如果不新增的話即使使用addbatch() ,executeBatch() 在後臺入庫的地方還是不會一次請求入庫而是多次請求入庫。
3、選擇合適的游標型別
的游標型別以最大限度的適用你的應用程式. 本節主要討論三種游標型別的效能問題.
對於從一個表中順序讀取所有記錄的情況來說, Forward-Only型的游標提供了最好的效能. 獲取表中的資料時, 沒有哪種方法比使用Forward-Only型的游標更快. 但不管怎樣, 當程式中必須按無次序的方式處理資料行時, 這種游標就無法使用了.
對於程式中要求與資料庫的資料同步以及要能夠在結果集中前後移動游標, 使用JDBC的Scroll-Insensitive型游標是較理想的選擇. 此型別的游標在第一次請求時就獲取了所有的資料(當JDBC驅動採用'lazy'方式獲取資料時或許是很多的而不是全部的資料)並且儲存在客戶端. 因此, 第一次請求會非常慢, 特別是請求長資料時會理嚴重. 而接下來的請求並不會造成任何網路往返(當使用'lazy'方法時或許只是有限的網路交通) 並且處理起來很快. 因為第一次請求速度很慢, Scroll-Insensitive型游標不應該被使用在單行資料的獲取上. 當有要返回長資料時, 開發者也應避免使用Scroll-Insensitive型游標, 因為這樣可能會造成記憶體耗盡. 有些Scroll-Insensitive型游標的實現方式是在資料庫的臨時表中快取資料來避免效能問題, 但多數還是將資料快取在應用程式中.
Scroll-Sensitive型游標, 有時也稱為Keyset-Driven游標, 使用識別符號, 像資料庫的ROWID之類. 當每次在結果集移動游標時, 會重新該識別符號的資料. 因為每次請求都會有網路往返, 效能可能會很慢. 無論怎樣, 用無序方式的返回結果行對效能的改善是沒有幫助的.
現在來解釋一下這個, 來看這種情況. 一個程式要正常的返回1000行資料到程式中. 在執行時或者第一行被請求時, JDBC驅動不會執行程式提供的SELECT語句. 相反, 它會用鍵識別符號來替換SELECT查詢, 例如, ROWID. 然後修改過的查詢都會被驅動程式執行，跟著會從資料庫獲取所有1000個鍵值. 每一次對一行結果的請求都會使JDBC驅動直接從本地快取中找到相應的鍵值, 然後構造一個包含了'WHERE ROWID=？'子句的最佳化查詢, 再接著執行這個修改過的查詢, 最後從伺服器取得該資料行.
當程式無法像Scroll-Insensitive型游標一樣提供足夠快取時, Scroll-Sensitive型游標可以被替代用來作為動態的可滾動的游標.
4、使用有效的getter方法
JDBC提供多種方法從ResultSet中取得資料, 像getInt(), getString(), 和getObject()等等. 而getObject()方法是最泛化了的, 提供了最差的效能。 這是因為JDBC驅動必須對要取得的值的型別作額外的處理以對映為特定的物件. 所以就對特定的資料型別使用相應的方法.
要更進一步的改善效能, 應在取得資料時提供欄位的索引號, 例如, getString(1), getLong(2), 和getInt(3)等來替代欄位名. 如果沒有指定欄位索引號, 網路交通不會受影響, 但會使轉換和查詢的成本增加. 例如, 假設你使用getString("foo") ... JDBC驅動可能會將欄位名轉為大寫(如果需要), 並且在到欄位名列表中逐個比較來找到"foo"欄位. 如果可以, 直接使用欄位索引, 將為你節省大量的處理時間.
例如, 假設你有一個100行15列的ResultSet, 欄位名不包含在其中. 你感興趣的是三個欄位 EMPLOYEENAME (字串型), EMPLOYEENUMBER (長整型), 和SALARY (整型). 如果你指定getString(“EmployeeName”), getLong(“EmployeeNumber”), 和getInt(“Salary”), 查詢旱每個欄位名必須被轉換為metadata中相對應的大小寫, 然後才進行查詢. 如果你使用getString(1), getLong(2), 和getInt(15). 效能就會有顯著改善.
5、獲取自動生成的鍵值
有許多資料庫提供了隱藏列為表中的每行記錄分配一個唯一鍵值. 很典型, 在查詢中使用這些欄位型別是取得記錄值的最快的方式, 因為這些隱含列通常反應了資料在磁碟上的物理位置. 在JDBC3.0之前, 應用程式只可在插入資料後通過立即執行一個SELECT語句來取得隱含列的值.
- //插入行
- int rowcount = stmt.executeUpdate (
- "insert into LocalGeniusList (name) values ('Karen')");
- // 現在為新插入的行取得磁碟位置 - rowid
- ResultSet rs = stmt.executeQuery (
- "select rowid from LocalGeniusList where name = 'Karen'");
(譯者：由於不同的資料庫支援的程度不同，返回rowid的方式各有差異。在SQL Server中，返回最後插入的記錄的id可以用這樣的查詢語句：SELECT @IDENTITY )
JDBC3.0規範中的一個可選特性提供了一種能力, 可以取得剛剛插入到表中的記錄的自動生成的鍵值.
- int rowcount = stmt.executeUpdate (
- "insert into LocalGeniusList (name) values ('Karen')",
- // 插入行並返回鍵值
- Statement.RETURN_GENERATED_KEYS);
- ResultSet rs = stmt.getGeneratedKeys ();
- // 得到生成的鍵值
這種取得自動生成的鍵值的方式給JDBC的開發者提供了靈活性, 並且使存取資料的效能得到提升.
6、選擇合適的資料型別
接收和傳送某些資料可能代價昂貴. 當你設計一個schema時, 應選擇能被最有效地處理的資料型別. 例如, 整型數就比浮點數或實數處理起來要快一些. 浮點數的定義是按照資料庫的內部規定的格式, 通常是一種壓縮格式. 資料必須被解壓和轉換到另外種格式, 這樣它才能被資料的協議處理.
7、獲取ResultSet
由於資料庫系統對可滾動游標的支援有限, 許多JDBC驅動程式並沒有實現可滾動游標. 除非你確信資料庫支援可滾動游標的結果集, 否則不要呼叫rs.last()和rs.getRow()方法去找出資料集的最大行數. 因為JDBC驅動程式模擬了可滾動游標, 呼叫rs.last()導致了驅動程式透過網路移到了資料集的最後一行. 取而代之, 你可以用ResultSet遍歷一次計數或者用SELECT查詢的COUNT函式來得到資料行數.
通常情況下，請不要寫那種依賴於結果集行數的程式碼, 因為驅動程式必須獲取所有的資料集以便知道查詢會返回多少行資料.
三、preparestatement 防止sql注入
在JDBC應用中,如果你已經是稍有水平開發者,你就應該始終以PreparedStatement代替Statement.也就是說,在任何時候都不要使用Statement.基於以下的原因:
1、程式碼的可讀性和可維護性.雖然用PreparedStatement來代替Statement會使程式碼多出幾行,但這樣的程式碼無論從可讀性還是可維護性上來說.都比直接用Statement的程式碼高很多檔次:
stmt.executeUpdate("insert into tb_name (col1,col2,col2,col4) values ('"+var1+"','"+var2+"',"+var3+",'"+var4+"')");
perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)");
perstmt.setString(1,var1);perstmt.setString(2,var2);perstmt.setString(3,var3);perstmt.setString(4,var4);
perstmt.executeUpdate();
不用我多說,對於第一種方法.別說其他人去讀你的程式碼,就是你自己過一段時間再去讀,都會覺得傷心.
2、PreparedStatement盡最大可能提高效能.每一種資料庫都會盡最大努力對預編譯語句提供最大的效能優化.因為預編譯語句有可能被重複呼叫.所以語句在被DB的編譯器編譯後的執行程式碼被快取下來,那麼下次呼叫時只要是相同的預編譯語句就不需要編譯,只要將引數直接傳入編譯過的語句執行程式碼中(相當於一個涵數)就會得到執行.這並不是說只有一個 Connection中多次執行的預編譯語句被快取,而是對於整個DB中,只要預編譯的語句語法和快取中匹配.那麼在任何時候就可以不需要再次編譯而可以直接執行.而statement的語句中,即使是相同一操作,而由於每次操作的資料不同所以使整個語句相匹配的機會極小,幾乎不太可能匹配.比如:insert into tb_name (col1,col2) values ('11','22');insert into tb_name (col1,col2) values ('11','23');即使是相同操作但因為資料內容不一樣,所以整個個語句本身不能匹配,沒有快取語句的意義.事實是沒有資料庫會對普通語句編譯後的執行程式碼快取.這樣每執行一次都要對傳入的語句編譯一次.
當然並不是所以預編譯語句都一定會被快取,資料庫本身會用一種策略,比如使用頻度等因素來決定什麼時候不再快取已有的預編譯結果.以儲存有更多的空間儲存新的預編譯語句.
3、最重要的一點是極大地提高了安全性.
即使到目前為止,仍有一些人連基本的惡義SQL語法都不知道.String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";如果我們把[' or '1' = '1]作為varpasswd傳入進來.使用者名稱隨意,看看會成為什麼?
select * from tb_name = '隨意' and passwd = '' or '1' = '1';因為'1'='1'肯定成立,所以可以任何通過驗證.更有甚者:把[';drop table tb_name;]作為varpasswd傳入進來,則:select * from tb_name = '隨意' and passwd = '';drop table tb_name;有些資料庫是不會讓你成功的,但也有很多資料庫就可以使這些語句得到執行.
而如果你使用預編譯語句.你傳入的任何內容就不會和原來的語句發生任何匹配的關係.(前提是資料庫本身支援預編譯,但上前可能沒有什麼服務端資料庫不支援編譯了,只有少數的桌面資料庫,就是直接檔案訪問的那些)只要全使用預編譯語句,你就用不著對傳入的資料做任何過慮.而如果使用普通的statement, 有可能要對drop,;等做費盡心機的判斷和過慮.
上面的幾個原因,還不足讓你在任何時候都使用PreparedStatement嗎?
總結： 上面是三篇文章，三篇文章詳細介紹了statement 和preparestatement 兩個物件的使用以及效率、安全問題。在實際專案中如果能夠使用preparestatement 還是建議使用preparestatement 原因有3：
1）、上面說了 如果sql中只有數值在變則效率高
2）、preparestatement 具有防sql注入
3）、程式碼可讀性比較好
例項：下面這個比喻很好，很明確的說明了批量新增，並且從中也可以看出在批量新增的時候PreparedStatement為什麼比Statement快的原因~
Statement和PreparedStatement的區別就不多廢話了,直接說PreparedStatement最重要的addbatch()結構的使用.
PreparedStatement 的addBatch和executeBatch實現批量新增
1.建立連結
Connection connection =getConnection();
2.不自動 Commit (瓜子不是一個一個吃,全部剝開放桌子上,然後一口舔了)
connection.setAutoCommit(false);
3.預編譯SQL語句,只編譯一回哦,效率高啊.(發明一個剝瓜子的方法,以後不要總想怎麼剝瓜子好.就這樣剝.)
PreparedStatement statement = connection.prepareStatement("INSERT INTO TABLEX VALUES(?, ?)");
4.來一個剝一個,然後放桌子上
//記錄1
statement.setInt(1, 1);
statement.setString(2, "Cujo");
statement.addBatch();
//記錄2
statement.setInt(1, 2);
statement.setString(2, "Fred");
statement.addBatch();
//記錄3
statement.setInt(1, 3);
statement.setString(2, "Mark");
statement.addBatch();
//批量執行上面3條語句. 一口吞了,很爽
int [] counts = statement.executeBatch();
//Commit it 嚥下去,到肚子(DB)裡面
connection.commit();
statement 物件的addBatch 和 executeBatch 來實現批量新增
stmt.addBatch("update TABLE1 set 題目="盛夏話足部保健1" where id="3407"");
stmt.addBatch("update TABLE1 set 題目="夏季預防中暑膳食1" where id="3408"");
stmt.addBatch("INSERT INTO TABLE1 VALUES("11","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("12","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("13","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("14","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("15","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("16","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("17","12","13","","")");
stmt.addBatch("INSERT INTO TABLE1 VALUES("18","12","13","","")");
int [] updateCounts=stmt.executeBatch();
cn.commit();
例項：批量新增
- public static void insertData(List<Map<String,String>> list,Logger log){
- //獲取的資料
- List <Map<String,String>> nlist= list;
- String upsql="update hrd_staff set position =? where id=?";
- Iterator<Map<String,String>> iter= nlist.iterator();
- Connection con= Utils.getCon();
- int count=0;
- try {
- //在皮臉新增的時候注意事務提交方式
- con.setAutoCommit(false);
- //PreparedStatement方法的使用
- PreparedStatement pstm = con.prepareStatement(upsql);
- while(iter.hasNext()){
- count++;
- Map<String,String> map= iter.next();
- String jon_name= map.get("job_name");
- String uid= map.get("uid");
- pstm.setString(1,jon_name);
- pstm.setString(2,uid);
- //新增到快取中
- pstm.addBatch();
- // 如果資料量很大，不能一次性批量新增所以我們要分批次新增，這裡就是300條一次
- if(count%300==0){
- //持久化
- int []res=pstm.executeBatch();
- //提交事務，持久化資料
- con.commit();
- pstm.clearBatch();
- log.info("300整除插入結果: "+res.length);
- }
- }
- //小於300條的在這裡持久化
- int []ress= pstm.executeBatch();
- //事務提交持久化
- con.commit();
- pstm.clearBatch();
- log.info("插入資料結果："+ress.length);
- } catch (SQLException e) {
- try {
- con.rollback();
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- e.printStackTrace();
- }finally{
- try {
- if(null!=con){
- con.close();
- con.setAutoCommit(true);
- }
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
這裡除了下面說的url中的批量設定外，我們也要注意事務的設定，不能設定為自動提交，要批量新增後在提交事務
總結：
addBatch() 就是把你的處理內容新增到批處理單元中。即新增到了batch中。你可以迴圈加入很多，資料庫都不會處理，直到呼叫如下程式碼executeBatch() 此時，資料庫把剛才加到batch中的命令批量處理。
使用批量插入的好處： , 當在100次INSERT操作中使用addBatch()方法時, 只有兩次網路往返. 1次往返是預儲statement, 另一次是執行batch命令. 雖然Batch命令會用到更多的資料庫的CPU週期, 但是通過減少網路往返，效能得到提高. 記住, JDBC的效能最大的增進是減少JDBC驅動與資料庫之間的網路通訊. 如果沒有使用批處理則網路往返101次這樣會耗很多時間，自然效率也就一般
這裡要注意：在mysql 下使用批量執行的時候要在，url 後面新增手動設定支援批量新增 例項如下：
String url="jdbc:mysql://localhost:3306/music?rewriteBatchedStatements=true";
// 預設情況下rewriteBatchedStatements 的值為false 也就是批量新增功能是關閉的，如果使用則要手動開啟！
還有就是事務的設定，不能使自動提交，要批量新增後才提交！！！