Java批量处理的用法

最精公司里头碰到某个项目，对数据库增删改效率要求比较高。

因此讨论讨论： 对于普通sql都用SP（数据库存储过程）代替。

在某个逻辑上，业务数据量用的时候会比较大，因此小组讨论用批量处理。

  1 public String saveFundMapping(ArrayList<FundMappingDTO> list)
  2     {
  3     Connection connection = null;
  4     Statement batchStatement = null;
  5     ResultSet resultSet = null;
  6     String error = "";
  7     DateFormat sdf = new SimpleDateFormat(MoiContants.Date_Pattern_DD_MMM_YY);
  8     try
  9     {
 10         connection = getConnection();
 11         connection.setAutoCommit(false);
 12         batchStatement = connection.createStatement();
 13         boolean rollback = false;
 14 
 15         
 16         String query = "";
 17 
 18         Iterator<FundMappingDTO> iter = list.iterator();
 19 
 20         while (iter != null && iter.hasNext())
 21         {
 22         FundMappingDTO fundMappingDTO = (FundMappingDTO) iter.next();
 23 
 24         CallableStatement cstmt1 = connection.prepareCall("{ call KIAUTILITIES_M.uploadFundMapping_M1(?) }");
 25         cstmt1.setString(1, fundMappingDTO.getDescription());
 26         cstmt1.execute();
 27         DBHelper.closeAll(cstmt1);
 28 
 29         CallableStatement cstmt2 = connection.prepareCall("{ call KIAUTILITIES_M.uploadFundMapping_M2(?,?,?,?) }");
 30         cstmt2.setString(1, fundMappingDTO.getMchFund());
 31         cstmt2.setString(2, fundMappingDTO.getDescription());
 32         cstmt2.setString(3, fundMappingDTO.getPrimary());
 33         cstmt2.registerOutParameter(4, OracleTypes.INTEGER);
 34         cstmt2.execute();
 35         
 36         int jvrCount = cstmt2.getInt(4);
 37 
 38         StringBuffer batch = new StringBuffer(query);
 39         if (jvrCount == 0)
 40         {
 41             batch.append(" call KIAUTILITIES_M.uploadFundMapping_M3( ");
 42         }
 43         else
 44         {
 45             batch.append(" call KIAUTILITIES_M.uploadFundMapping_M4( ");
 46         }
 47         DBHelper.closeAll(cstmt2);
 48         
 49          batch.append('\'').append(fundMappingDTO.getMchFund()).append('\'').append(',');
 50          batch.append(Long.parseLong(fundMappingDTO.getNominalNumber())).append(',');
 51          batch.append(Long.parseLong(fundMappingDTO.getAccNumber())).append(',');
 52          batch.append('\'').append(fundMappingDTO.getDescription()).append('\'').append(',');
 53          batch.append('\'').append(fundMappingDTO.getPrimary()).append('\'').append(',');
 54          batch.append('\'').append(fundMappingDTO.getModifiedBy()).append('\'').append(',');
 55          batch.append('\'').append(sdf.format(new java.sql.Date((new
 56          Date().getTime())))).append('\'').append(')');
 57          batchStatement.addBatch(batch.toString());
 58          logger.debug("query" + batch.toString()); 
 59         }
 60         int err[];
 61         try
 62         {
 63         //connection.setAutoCommit(false);
 64         err = batchStatement.executeBatch();
 65         }
 66         catch (BatchUpdateException e)
 67         {
 68         logger.error("KIADAO.saveFundMapping:BatchUpdateException  :" + e);
 69         rollback = true;
 70         error = e.getMessage();
 71         }
 72         // Commit the transaction
 73         if (rollback)
 74         {
 75         connection.rollback();
 76         }
 77         else
 78         {
 79         connection.commit();
 80         }
 81 
 82     }
 83     catch (SQLException sqlException)
 84     {
 85         logger.error("KIADAO.saveFundMapping:SQLException  :" + sqlException);
 86         error = sqlException.getMessage();
 87 
 88     }
 89     catch (Exception exception)
 90     {
 91         logger.error("KIADAO.saveFundMapping: " + exception);
 92         error = exception.getMessage();
 93     }
 94     finally
 95     {
 96         closeAll(resultSet, batchStatement, connection);
 97     }
 98 
 99     return error;
100     }



posted @ 2011-12-28 16:27  AppleFramework  阅读(403)  评论(0)    收藏  举报