//1首先mysql默认是不支持一次性插入这么多的value。需要更改其配置文件增加
max_allowed_packet = 20M
//2拼装sb(即value)略....
if (i % 20000 == 0) { //20000的整数倍时执行插入操作,之后sb清空重新拼装SQL的values
sb = sb.delete(sb.length() - 1, sb.length());
try {
jdbcTemplate.execute(sql+sb);
} catch (Exception e) {
jjerorNum += insertWipeutError(jdbcTemplate,sql,jjeror,sb.toString());
}
sb.setLength(0);
}
方法insertWipeutError
// -----------------------------------------------
//
/**2 算法 核心思想 回调函数和二分查找
* 执行插入操作出错时,一次20000条,拆分执行,
* 最终忽略地错误语句 其他的语句正常执行 ( 返回值为 执行出错的条数 )
* @param jdt
* @param sql
* @param jjerror
* @param str
* @return
*/
public int insertWipeutError(JdbcTemplate jdt,String sql ,int jjerror,String str){
String[] arr = str.split(" , "); //3888
int length = arr.length;
int times = 2;
if(length==1){
try {
jdt.execute(sql+arr[0]);
} catch (Exception e) {
//
String errStr = e.getMessage().toString();
if(errStr.contains("[")&&errStr.contains("]")){
errStr = errStr.substring(errStr.indexOf("]"), errStr.length());
}
System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"执行插入语句:"+arr[0]);
System.out.println("错误日志:"+errStr);
System.out.println("~~~~~~~~~~~~~~~~~error~~~~~~~~~~~~~~~~~error~~~~~~~~~~~~~~~~~error~~~~~~~~~~~~~~~~~");
//
jjerror = jjerror+1;
return jjerror;
}
}
int lnum = length%2==0?length/2:length/2+1;
int m ;
int n ;
for (int i = 0; i < 2; i++) {
if(i==0){
m =0;
n =lnum;
}else{
m =lnum;
n =length;
}
StringBuilder sb = new StringBuilder();
for (int j = m; j < n; j++) {
sb.append(arr[j]).append(" , ");
}
sb = sb.delete(sb.length()-2, sb.length());
try {
jdt.execute(sql+sb);
} catch (Exception e) {
jjerror= 0 + insertWipeutError(jdt, sql,jjerror, sb.toString());
}
}
return jjerror;
}
// -----------------------------------------------