普通方法的查询：
@Override
public List<Map<String, Object>> selectSpentAmount(Integer MAT_TYPE_, String DEPT_CODE_, Integer YEAR_, Map<String, Object> operator) {
String sql = "select MAT_NO_, sum(AMOUNT_) as SPENT_AMOUNT_ from DM_MAT_MONTH_PLAN where MAT_TYPE_ = ? and DEPT_CODE_ = ? and YEAR_ = ? and PROCESS_STATUS_ in (1, 2) group by MAT_NO_";
return dmJdbcTemplate.queryForList(sql, MAT_TYPE_, DEPT_CODE_, YEAR_);
}
可为空方法的查询：
@Override
public List<Map<String, Object>> selectSpentBudget(Integer MAT_TYPE_, String DEPT_CODE_, Integer YEAR_, Integer MONTH_, String MAT_BUDGET_CAT_ID_, List<Integer> PROCESS_STATUS_LIST, Map<String, Object> operator) {
String sql = "select MAT_NO_, sum(PLANNED_PRICE_ * AMOUNT_) as SPENT_BUDGET_ from DM_MAT_MONTH_PLAN where 1 = 1";
Map<String, Object> paramMap = new HashMap<String, Object>(); if (MAT_TYPE_ != null) {
sql += " and MAT_TYPE_ = :MAT_TYPE_";
paramMap.put("MAT_TYPE_", MAT_TYPE_);
} if (StringUtils.isNotEmpty(DEPT_CODE_)) {
sql += " and DEPT_CODE_ = :DEPT_CODE_";
paramMap.put("DEPT_CODE_", DEPT_CODE_);
} if (YEAR_ != null) {
sql += " and YEAR_ = :YEAR_";
paramMap.put("YEAR_", YEAR_);
} if (MONTH_ != null) {
sql += " and MONTH_ = :MONTH_";
paramMap.put("MONTH_", MONTH_);
} if (StringUtils.isNotEmpty(MAT_BUDGET_CAT_ID_)) {
sql += " and MAT_BUDGET_CAT_ID_ = :MAT_BUDGET_CAT_ID_";
paramMap.put("MAT_BUDGET_CAT_ID_", MAT_BUDGET_CAT_ID_);
} if (PROCESS_STATUS_LIST != null && PROCESS_STATUS_LIST.size() > 0) {
sql += " and PROCESS_STATUS_ in (:PROCESS_STATUS_LIST)";
paramMap.put("PROCESS_STATUS_LIST", PROCESS_STATUS_LIST);
}
sql += " group by MAT_NO_";
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dmJdbcTemplate);
return namedParameterJdbcTemplate.queryForList(sql, paramMap);
}
批量处理SQL语句：
@Override
public int updateStatus(final List<String> MAT_AUX_PLAN_REC_ID_LIST, final Map<String, Object> operator) {
if (MAT_AUX_PLAN_REC_ID_LIST == null || MAT_AUX_PLAN_REC_ID_LIST.size() == 0) {
return 0;
} String sql = "update DM_MAT_AUX_PLAN_REC set STATUS_ = 1 where MAT_AUX_PLAN_REC_ID_ = ? and STATUS_ = 0"; BatchPreparedStatementSetter batch = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, MAT_AUX_PLAN_REC_ID_LIST.get(i));
} public int getBatchSize() {
return MAT_AUX_PLAN_REC_ID_LIST.size();
}
};
return dmJdbcTemplate.batchUpdate(sql, batch).length;
}