数据量大时处理。
// 生成自测成绩 lizhuang 2012年8月6日
@SuppressWarnings("unchecked")
public String makeTestScore() {
dbpool pool = new dbpool();
Connection conn = pool.getConn();
Statement ps = null;
try {
ps = conn.createStatement();
for (int j = 0; j < stuList.size(); j++) {
String sql = "UPDATE pe_bzz_examscore t SET t.test_score=IFNULL((select round(s.AVG_test_score,1) from stat_study_summary s" + " where s.student_id=0;";
ps.addBatch(sql);
if (StringUtils.isNotBlank(stuList.get(j).getTotal_score())) {
Double total_score = (Double.parseDouble(exam_score) * exam_scale + Double.parseDouble(test_score) * test_scale) / 100;
String totalScore = df.format(total_score);
sql = "update pe_bzz_examscore t set t.total_score=" + totalScore + " where t.id='" + stuList.get(j).getId() + "';";
ps.addBatch(sql);
}
// 如果考核等级不为空,同步更新
if (StringUtils.isNotBlank(stuList.get(j).getTotal_grade())) {
String grade = null;
Double total_score = Double.parseDouble(stuList.get(j).getTotal_score());
if (total_score < 60) {
grade = "不及格";
} else if (total_score >= 60 && total_score < 75) {
grade = "合格";
} else if (total_score >= 75 && total_score < 90) {
grade = "良好";
} else if (total_score >= 90) {
grade = "优秀";
}
sql = "update pe_bzz_examscore t set t.total_grade='" + grade + "'where t.id='" + stuList.get(j).getId() + "';";
ps.addBatch(sql);
}
if (j == stuList.size() - 1) {// 如果到达末尾,全部提交
ps.executeBatch();
break;
}
if (j % 200 == 0 && j != 0) {// 每200条批量提交一次
ps.executeBatch();
}
}
ps.close();
conn.close();
} catch (Exception e1) {
e1.printStackTrace();
request().setAttribute("msg", "生成失败。");
return "extmsg";
}
request().setAttribute("msg", "全部生成成功!");
// request().setAttribute("url",
// "/entity/exam/peBzzExamScore.action?tag=search");
return "extmsg";
}
就是一个addBatch和executeBatch何时提交的问题。记录一下。
addBatch