jdbc批量更新数据

来源：互联网发布：微博数据分析编辑：程序博客网时间：2024/06/10 12:49

有些时候，适合用jdbc。比如读一个大的excel文件数据到数据库

public static List<String> welfareImport(MultipartFile file,String id,String businessType,IWelfareDao welfareDao) throws IOException, SQLException,ExcelParseException {

XSSFWorkbook xssfWorkbook;
HSSFWorkbook hssfWorkbook;
Sheet sheet = null; ;
List<String> sourceData = new ArrayList<String>();
List<Map<String,Object>> listmap = new ArrayList<Map<String,Object>>();
String notExistIds = null;

String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().indexOf(".")+1, file.getOriginalFilename().length());

   if (fileType.equals("xls")) {
   hssfWorkbook = new HSSFWorkbook(file.getInputStream());
   sheet = hssfWorkbook.getSheetAt(0);
   } else if (fileType.equals("xlsx")) {
   xssfWorkbook = new XSSFWorkbook(file.getInputStream());
   sheet = xssfWorkbook.getSheetAt(0);
   } else {
   System.out.println("您导入的excel格式不正确");
   }

int startRow = sheet.getFirstRowNum();
int endRow = sheet.getLastRowNum();
int rows = endRow - startRow + 1;
long start = System.currentTimeMillis();
connection = DBUtil.createConnection();
connection.setAutoCommit(false);
Statement statement = (Statement) connection.createStatement();
try{
for (int i =startRow;i<=endRow;i++ ) {
Row row =sheet.getRow(i);
SimpleDateFormat sdfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sql = "update welfare_achive_info set status='1',taken_time='"+SqlHelper.sqlInjectionHelper(sdfd.format(row.getCell(1).getDateCellValue()).trim()) +"'where welfare_id='"+id+"' and foxconn_no='"+SqlHelper.sqlInjectionHelper(row.getCell(0).toString().trim())+"'";
Map<String,Object> paramMap = new HashMap<String, Object>();

if ("02".equals(businessType)) {//
//格式校验
if (!"".equals(row.getCell(1)) && !CommonUtils.checkDateFormat(row.getCell(1))){
throw new ExcelParseException("第"+(i+1)+"行第2列，福利领取日期格式不正确，参照格式2000/1/1或2000-1-1");
}
/* paramMap.put("id",id);
paramMap.put("welfareId", id);*/
/*if (String.valueOf(row.getCell(0)).indexOf(".") > 0) {
paramMap.put("foxconnNo", String.valueOf(row.getCell(0)).substring(0, String.valueOf(row.getCell(0)).indexOf(".")));
} else {
paramMap.put("foxconnNo", String.valueOf(row.getCell(0)));
} */

if (row.getCell(1) == null || "".equals(row.getCell(1).toString())) {
throw new ExcelParseException("请在第二列填上福利领取日期，参照格式2000/1/1或2000-1-1");
}

SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
Date date = new Date();
try {
date = row.getCell(1).getDateCellValue();
paramMap.put("takenTime",date);
} catch (Exception e) {
try {
paramMap.put("takenTime",sdf.parse(row.getCell(1).toString().trim()));
} catch (ParseException e1) {
throw new ExcelParseException("第"+(i+1)+"行，福利领取日期格式不正确，参照格式2000/1/1或2000-1-1");
}
}

statement.addBatch(sql);

}

}
}
statement.executeBatch();
connection.commit();
}catch(Exception e){
connection.rollback();
}
connection.setAutoCommit(true);

System.out.println("时间 -----------"+(System.currentTimeMillis() - start));
System.out.println("----执行完毕----");
return sourceData;

}

在执行过程中，发现数据量很大的时候，更新很慢，所以，在表上加了一个索引，把where条件中的字段，作为索引，要不然查找很慢。

0 0