Java爬坑日記之批量更新

胖子k發表於2019-04-11
Java

筆者曾在專案中碰到這樣一個問題：系統每隔15min將本地oracle資料庫中的交易打包形成檔案上傳到FTP，上傳完成後將交易的標記位更改為已上傳，大致sql操作順序如下

1. select * from a where balance_status='00' //00為初始態（未上傳），01為已上傳
2. ......//打包形成檔案上傳
3. update a set balance_status='01' where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id}
        </foreach>  //第3步的list就是來自第一步中取的集合複製程式碼

專案上線跑了一段時間沒有任何問題，直到有一天突然出現了問題，把日誌down下來發現在執行第3步的時候報了錯 

java.sql.SQLException: ORA-01795: 列表中的最大表示式數為 1000

一看list的大小超過了1000條，百度這個錯誤程式碼，發現是oracle對in()中的數量有限制，最高不能超過1000，發現了問題怎麼改呢？最終經過討論決定使用jdbc的批量更新，廢話少說，直接上程式碼

int m=list.size(); //select獲取的集合
String[] sqls=new String[m];
for(int t=0;t<m;t++){
   sqls[t]="update a set balance_status='01' where id ='"+list.get(t).id+"'";
}
//呼叫工具類的批量更新方法，本質上使用了Statement介面定義的executeBatch()方法
DbUtils.executeBatchUpdate(sqls);  複製程式碼
public class DbUtils {

   private static String driverClass = PropertiesUtils.getPropertiesValue("DB_DRIVER_CLASS");
   private static String url = PropertiesUtils.getPropertiesValue("DB_URL");
   private static String user = PropertiesUtils.getPropertiesValue("DB_USER");
   private static String pwd = PropertiesUtils.getPropertiesValue("DB_PWD");
   private static Statement stm = null;
   private static PreparedStatement sta = null;
   private static ResultSet rs = null;
   private static Connection conn;
   private static CallableStatement cs = null;

   /**
    * 載入驅動程式
    */
   static {
      try {
         Class.forName(driverClass);
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }
   }

   /**
    * @return 連線物件
    */
   public static Connection getConn() throws ClassNotFoundException, SQLException {
      try {
         conn = DriverManager.getConnection(url, user, pwd);
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return conn;
   }

   /**
    * 批量更新資料
    * @param sqls
    * @throws Exception
    */
   public static void executeBatchUpdate(String[] sqls) throws Exception {
      if (sqls!=null&&sqls.length>0) {
         try {
            getConn();
            //關閉自動提交
            conn.setAutoCommit(false);
            stm=conn.createStatement();
            for(String sql: sqls){
               // 用PreparedStatement的批量處理
               stm.addBatch(sql);
            }
            stm.executeBatch();// 執行批處理
            conn.commit();
         } catch (Exception e) {
            e.printStackTrace();
         } finally {
            close();
         }
      }
   }

   /**
    * 關閉資源
    */
   public static void close() {
      try {
         if (rs != null) {
            rs.close();
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         try {
            if (sta != null) {
               sta.close();
            }
            if (cs != null) {
               cs.close();
            }
         } catch (SQLException e2) {
            e2.printStackTrace();
         } finally {
            if (conn != null) {
               try {
                  conn.close();
               } catch (SQLException e) {
                  e.printStackTrace();
               }
            }
         }
      }
   }
}複製程式碼

總結

    產生這個問題的根源在於筆者存在知識盲點，就是in後面的列舉數不能超過1000，測試的時候又沒有達到這個資料量所以沒有發現這個問題。為了儘量避免這類問題，一方面是開發人員提高自身，另一方面是測試人員有條件的情況下要儘可能測試大資料量的情況，再者萬一出現問題，要細心分析解決。有個通用的原則就是資料量較大時儘量少用框架，可改為jdbc，儲存過程或者其它方式來解決


相關文章