由于SQL插入语句是通过判断拼接,导致语句中包含'',原SQL语句如下:
import org.springframework.jdbc.core.JdbcTemplate;
@Autowired
JdbcTemplate jdbcTemplate;
/**
* 批量插入对账单比对解析表数据
* @param reportId 报告ID
* @param list 对账单比对解析表数据
* @param objConnector
* @param type
* @return
*/
public boolean batchInsertFileCompareParses(int reportId, List<DtFileCompareParse> list, String objConnector, String type) {
// 判断是否为空
if (list == null || list.size() == 0) {
return true;
}
try {
objConnector = objConnector + objConnector + objConnector;
List<String> tempSqlList = new ArrayList<>();
for (DtFileCompareParse dtFileCompareParse : list) {
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO dt_file_compare_parse_" + reportId +" (");
if (DataUtils.isNotEmpty(dtFileCompareParse.getUniqueKey()))
sql.append("unique_key,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldFilename()))
sql.append("old_filename,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldLineNumber()))
sql.append("old_line_number,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldBillItem()))
sql.append("old_bill_item,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewFilename()))
sql.append("new_filename,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewLineNumber()))
sql.append("new_line_number,");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewBillItem()))
sql.append("new_bill_item,");
if (dtFileCompareParse.getCompared() != null)
sql.append("compared,");
sql.append(") VALUES ( ");
if (DataUtils.isNotEmpty(dtFileCompareParse.getUniqueKey()))
sql.append("'").append(dtFileCompareParse.getUniqueKey()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldFilename()))
sql.append("'").append(dtFileCompareParse.getOldFilename()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldLineNumber()))
sql.append("'").append(dtFileCompareParse.getOldLineNumber()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldBillItem()))
sql.append("'").append(dtFileCompareParse.getOldBillItem()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewFilename()))
sql.append("'").append(dtFileCompareParse.getNewFilename()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewLineNumber()))
sql.append("'").append(dtFileCompareParse.getNewLineNumber()).append("',");
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewBillItem()))
sql.append("'").append(dtFileCompareParse.getNewBillItem()).append("',");
if (dtFileCompareParse.getCompared() != null)
sql.append(dtFileCompareParse.getCompared()).append(",");
sql.append(")");
sql.append(" ON DUPLICATE KEY UPDATE ");
sql.append("unique_key = '").append(dtFileCompareParse.getUniqueKey() + "',");
if(type.equals(Constants.IS_OLD_FILE)) {
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldFilename())) {
sql.append("old_filename = CONCAT(IFNULL(old_filename,''), '"+ objConnector + "','" + dtFileCompareParse.getOldFilename() + "'),");
}
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldLineNumber())) {
sql.append("old_line_number = CONCAT(IFNULL(old_line_number,''), '"+ objConnector + "','" + dtFileCompareParse.getOldLineNumber() + "'),");
}
if (DataUtils.isNotEmpty(dtFileCompareParse.getOldBillItem())) {
sql.append("old_bill_item = CONCAT(IFNULL(old_bill_item,''), '"+ objConnector + "','" + dtFileCompareParse.getOldBillItem() + "'),");
}
}else {
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewFilename())) {
sql.append("new_filename = CONCAT(IFNULL(new_filename,''), '"+ objConnector + "','" + dtFileCompareParse.getNewFilename() + "'),");
}
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewLineNumber())) {
sql.append("new_line_number = CONCAT(IFNULL(new_line_number,''), '"+ objConnector + "','" + dtFileCompareParse.getNewLineNumber() + "'),");
}
if (DataUtils.isNotEmpty(dtFileCompareParse.getNewBillItem())) {
sql.append("new_bill_item = CONCAT(IFNULL(new_bill_item,''), '"+ objConnector + "','" + dtFileCompareParse.getNewBillItem() + "'),");
}
}
sql.append("compared = "+ dtFileCompareParse.getCompared());
String str = sql.toString().replace(",)", ")");
logger.info("批量插入对账单比对解析表数据,执行SQL语句:" + str);
tempSqlList.add(str);
}
String[] tempSqlArray = tempSqlList.toArray(new String[tempSqlList.size()]);
jdbcTemplate.batchUpdate(tempSqlArray);
}catch (Exception e) {
logger.error("批量插入对账单比对解析表数据发生异常", e);
return false;
}
return true;
}
使得执行时报错,如下图:
由于内容中含有·导致SQL拼接错误
SQL修改为传参方式实现批量插入,如下图:
public boolean batchInsertFileCompareParses(int reportId, List<DtFileCompareParse> list, String objConnector, String type) {
// 判断是否为空
if (list == null || list.size() == 0) {
return true;
}
objConnector = objConnector + objConnector + objConnector;
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO dt_file_compare_parse_" + reportId + " ( unique_key, old_filename, old_line_number, "
+ " old_bill_item, new_filename, new_line_number, new_bill_item,compared ) VALUES (?,?,?,?,?,?,?,?) "
+ " ON DUPLICATE KEY UPDATE unique_key = ? ,");
if(type.equals(Constants.IS_OLD_FILE)) {
sql.append("old_filename = CONCAT(IFNULL(old_filename,''), ',',?),");
sql.append("old_line_number = CONCAT(IFNULL(old_line_number,''), ',',?),");
sql.append("old_bill_item = CONCAT(IFNULL(old_bill_item,''), '"+ objConnector + "',?),");
}else {
sql.append("new_filename = CONCAT(IFNULL(new_filename,''), ',',?),");
sql.append("new_line_number = CONCAT(IFNULL(new_line_number,''), ',',?),");
sql.append("new_bill_item = CONCAT(IFNULL(new_bill_item,''), '"+ objConnector + "',?),");
}
sql.append("compared = ?");
try {
List batchArgs = new ArrayList<>();
List<String> tempSqlList = new ArrayList<>();
for (DtFileCompareParse dtFileCompareParse : list) {
if(type.equals(Constants.IS_OLD_FILE)) {
Object[] objects = {
dtFileCompareParse.getUniqueKey(),dtFileCompareParse.getOldFilename(),dtFileCompareParse.getOldLineNumber(),
dtFileCompareParse.getOldBillItem(),dtFileCompareParse.getNewFilename(),dtFileCompareParse.getNewLineNumber(),
dtFileCompareParse.getNewBillItem(),dtFileCompareParse.getCompared(),
dtFileCompareParse.getUniqueKey(),
dtFileCompareParse.getOldFilename(),dtFileCompareParse.getOldLineNumber(),dtFileCompareParse.getOldBillItem(),
dtFileCompareParse.getCompared()
};
batchArgs.add(objects);
}else {
Object[] objects = {
dtFileCompareParse.getUniqueKey(),dtFileCompareParse.getOldFilename(),dtFileCompareParse.getOldLineNumber(),
dtFileCompareParse.getOldBillItem(),dtFileCompareParse.getNewFilename(),dtFileCompareParse.getNewLineNumber(),
dtFileCompareParse.getNewBillItem(),dtFileCompareParse.getCompared(),
dtFileCompareParse.getUniqueKey(),
dtFileCompareParse.getNewFilename(),dtFileCompareParse.getNewLineNumber(),dtFileCompareParse.getNewBillItem(),
dtFileCompareParse.getCompared()
};
batchArgs.add(objects);
}
}
int[] updateCountArray = jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
}catch (Exception e) {
logger.error("批量插入对账单比对解析表数据发生异常", e);
return false;
}
return true;
}