Java爬坑日记之批量更新-CSDN博客

笔者曾在项目中碰到这样一个问题：系统每隔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，存储过程或者其它方式来解决