JDBC當中的批處理

JDBC當中的批處理

在對資料庫進行批量操作時，應分析操作的前後相關性，如果屬於大批量的操作，而且前續操作的結果不依賴與後繼操作，則完全可以使用批處理來操作DB。

 

使用批處理的優點：

 

1.  多個SQL語句的執行，共用一個Connection資源。在對資料庫操作時，connection資源是很寶貴的，資料庫的維護從某種角度來說，就是減少資料庫的連線數，減輕對DB的壓力。建立一個資料連線要遠遠比使用資料庫連線消耗資源。這也正是資料庫連線池存在的意義。

 

2. 批處理在效率上總是比逐條處理有優勢，要處理的資料的記錄條數越大，批處理的優勢越明顯。在處理大批量相同業務邏輯的DB操作可以使用批處理達到簡化、高效處理。

3. 在單一時間段，提高應用與DB間的吞吐量，縮短DB響應時間。大部分應用都有DB操作，如果SQL語句操作不當會導致DB長時間處於不可用狀態，或是使DB資源佔用率升高，從而影響了應用，最終被DB拖垮。縮短DB的響應時間，一來可以提供應用效能，二來減輕DB壓力，對維持高系能的應用有極大的幫助。

 

使用JDBC本身提供的批處理功能很簡單，如下例子是根據主鍵批量更新test_table表。

+ expand sourceview plaincopy to clipboardprint?
public void updateStateBactch(List elms) {   
Connection conn = null;   
PreparedStatement ps = null;   
String sql = "update test_table set state=? where keyid = ?";   
 
conn = DBTools.getConnection();   
 
if(conn == null)   
{   
log.error("[update][state][error][conn is null]");   
return;   
}   
 
try {   
ps = conn.prepareStatement(sql);   
for(int i = 0; i < elms.size(); i++) {   
Element elm = (Element) elms.get(i);   
if(null == elm || null == elm.getUserId()   
|| null == elm.getState()) {   
continue;   
}   
ps.setInt(1, elm.getStatus());   
ps.setString(2, elm.getProcID());   
ps.addBatch();   
}   
ps.executeBatch();   
ps.clearBatch();   
} catch (SQLException sqlEx) {   
log.warn("[update][state][error][SQLException]");   
} catch (Exception e) {   
log.warn("[update][state][error][SQLException]");   
} finally {   
DBTools.close(conn, ps, null);   
}   
}  
public void updateStateBactch(List elms) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "update test_table set state=? where keyid = ?";

conn = DBTools.getConnection();

if(conn == null)
{
log.error("[update][state][error][conn is null]");
return;
}

try {
ps = conn.prepareStatement(sql);
for(int i = 0; i < elms.size(); i++) {
Element elm = (Element) elms.get(i);
if(null == elm || null == elm.getUserId()
|| null == elm.getState()) {
continue;
}
ps.setInt(1, elm.getStatus());
ps.setString(2, elm.getProcID());
ps.addBatch();
}
ps.executeBatch();
ps.clearBatch();
} catch (SQLException sqlEx) {
log.warn("[update][state][error][SQLException]");
} catch (Exception e) {
log.warn("[update][state][error][SQLException]");
} finally {
DBTools.close(conn, ps, null);
}
}
 

 

使用批處理是有絕對的好處，可是凡事都利必有弊。在使用批處理應用注意以下幾點，這些小的細節常常被我們忽略，但是這些細節都對你應用的效能有著至關重要的影響。

1. 使用批出量沒有進行分批分量處理。在使用批處理首先應該注意一點，批處理也不是萬能的，批處理都存在一次執行的最大吞吐量限制。正如上面所提到的，批處理在單一時間段提高了與DB間的吞吐量，但是任何DB都是有吞吐量最大限制。當達到或是超過，最大吞吐量的峰值時，容易導致DB過載，更嚴重會導致DB當機。例如上面的示例程式碼，如果入參list長度很大，幾萬甚至幾十萬，想想會導致上面結果呢。當然是背道而馳使應用的系能急劇下降，而且給DB帶來風險。正確的做法應該是分批分量進行提交。處理執行SQL的時候，批處理的分批的大小與資料庫的吞吐量以及硬體配置有很大關係，需要通過測試找到最佳的分批大小，一般在200-2000之間。

 

+ expand sourceview plaincopy to clipboardprint?
try {   
ps = conn.prepareStatement(sql);   
for(int i = 0; i < elms.size(); i++) {   
Element elm = (Element) elms.get(i);   
if(null == elm || null == elm.getUserId()   
|| null == elm.getState()) {   
continue;   
}   
ps.setInt(1, elm.getStatus());   
ps.setString(2, elm.getProcID());   
ps.addBatch();   
if ((i != 0 && i % 2000 == 0) || i == elms.size() - 1) {   
ps.executeBatch();   
ps.clearBatch();   
ps.close();   
ps = conn.prepareStatement(sql);   
}   
}   
} catch (SQLException sqlEx) {   
log.warn("[update][state][error][SQLException]");   
log.warn(sqlEx);   
} catch (Exception e) {   
log.warn("[update][state][error][SQLException]");   
log.warn(e);   
} finally {   
DBTools.close(conn, ps, null);   
}  
try {
ps = conn.prepareStatement(sql);
for(int i = 0; i < elms.size(); i++) {
Element elm = (Element) elms.get(i);
if(null == elm || null == elm.getUserId()
|| null == elm.getState()) {
continue;
}
ps.setInt(1, elm.getStatus());
ps.setString(2, elm.getProcID());
ps.addBatch();
if ((i != 0 && i % 2000 == 0) || i == elms.size() - 1) {
ps.executeBatch();
ps.clearBatch();
ps.close();
ps = conn.prepareStatement(sql);
}
}
} catch (SQLException sqlEx) {
log.warn("[update][state][error][SQLException]");
log.warn(sqlEx);
} catch (Exception e) {
log.warn("[update][state][error][SQLException]");
log.warn(e);
} finally {
DBTools.close(conn, ps, null);
}
 

 

2. 使用批處理時，沒有關注DB測異常情況，導致批處理失敗。這裡涉及到一些異常處理最基本的點。上述例程還有個小小的問題需要注意，當ps.executeBatch()執行時，如果該批次的SQL語句中有一條SQL丟擲異常，那麼後續的批處理將不會有執行的機會，導致漏執行。所以經過優化後：

+ expand sourceview plaincopy to clipboardprint?
try {   
ps = conn.prepareStatement(sql);   
for(int i = 0; i < elms.size(); i++) {   
try {   
Element elm = (Element) elms.get(i);   
if(null == elm || null == elm.getUserId()   
|| null == elm.getState()) {   
continue;   
}   
ps.setInt(1, elm.getStatus());   
ps.setString(2, elm.getProcID());   
ps.addBatch();   
if ((i != 0 && i % 2000 == 0) || i == elms.size() - 1) {   
ps.executeBatch();   
ps.clearBatch();   
ps.close();   
ps = conn.prepareStatement(sql);   
}   
} catch (SQLException e) {   
log.warn("[update][state][error][SQLException]");   
log.warn(e);   
ps.clearBatch();   
ps.close();   
ps = conn.prepareStatement(sql);   
} catch (Exception e) {   
log.warn("[update][state][error][SQLException]");   
log.warn(e);   
ps.executeBatch();   
ps.clearBatch();   
ps.close();   
ps = conn.prepareStatement(sql);   
}   
 
}   
} catch (SQLException sqlEx) {   
log.warn("[update][state][error][SQLException]");   
log.warn(sqlEx);   
} catch (Exception e) {   
log.warn("[update][state][error][SQLException]");   
log.warn(e);   
} finally {   
DBTools.close(conn, ps, null);   
}  
try {
ps = conn.prepareStatement(sql);
for(int i = 0; i < elms.size(); i++) {
try {
Element elm = (Element) elms.get(i);
if(null == elm || null == elm.getUserId()
|| null == elm.getState()) {
continue;
}
ps.setInt(1, elm.getStatus());
ps.setString(2, elm.getProcID());
ps.addBatch();
if ((i != 0 && i % 2000 == 0) || i == elms.size() - 1) {
ps.executeBatch();
ps.clearBatch();
ps.close();
ps = conn.prepareStatement(sql);
}
} catch (SQLException e) {
log.warn("[update][state][error][SQLException]");
log.warn(e);
ps.clearBatch();
ps.close();
ps = conn.prepareStatement(sql);
} catch (Exception e) {
log.warn("[update][state][error][SQLException]");
log.warn(e);
ps.executeBatch();
ps.clearBatch();
ps.close();
ps = conn.prepareStatement(sql);
}

}
} catch (SQLException sqlEx) {
log.warn("[update][state][error][SQLException]");
log.warn(sqlEx);
} catch (Exception e) {
log.warn("[update][state][error][SQLException]");
log.warn(e);
} finally {
DBTools.close(conn, ps, null);
}

 

3. 使用批處理時，當批處理中有一條SQL語句優化SQL異常而導致整個批處理失敗。在列印日誌時應該注意，以上的列印方式對問題定位沒有任何幫助。如上如果其中的一條SQL語句執行失敗，那麼你不知道究竟是什麼異常，因為沒有列印異常列，而只列印了最頂層異常。例如：如上程式在DB2資料庫中執行失敗後，只返回瞭如下資訊com.ibm.db2.jcc.c.vd: Non-atomic batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.大概意思是批處理執行失敗，批處理已經提交，但是其中至少有一條或者多條執行失敗。使用getNextException來檢視執行白失敗的SQL語句異常資訊，便於定位！可是這樣還是有問題，你知道了SQL語句的異常了，但是你不知道究竟是那條SQL語句導致的異常，其實可以更具批處理執行的返回值來檢查執行結果。

Int[] results = ps.executeBatch();