首先推荐使用PreparedStatement的批量处理操作。
Connection conn = null;
PreparedStatement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS); String SQL = "INSERT INTO Employees(id,first,last,age) " +
"VALUES(?, ?, ?, ?)"; stmt = conn.prepareStatement(SQL); conn.setAutoCommit(false);
//数据量多的 可以使用for循环批量
stmt.setInt( 1, 400 );
stmt.setString( 2, "Python" );
stmt.setString( 3, "Zhang" );
stmt.setInt( 4, 33 );
stmt.addBatch(); stmt.setInt( 1, 401 );
stmt.setString( 2, "C++" );
stmt.setString( 3, "Huang" );
stmt.setInt( 4, 31 );
stmt.addBatch();
int[] count = stmt.executeBatch();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
或类似于
- PreparedStatement ps = conn.prepareStatement(
- "INSERT into employees values (?, ?, ?)");
- for (n = 0; n < 100; n++) {
- ps.setString(name[n]);
- ps.setLong(id[n]);
- ps.setInt(salary[n]);
- ps.addBatch();
- }
删除的，类似。
其次，使用下面的方式：
1.批量新增
int count = list.size() / 500; //自己定义一次批量处理条数（不要太高，我这边遇到批量超过20000条，速度明显变慢，批量15w等的急死，如果太大的话，还有可能超过mysql默认的最大长度1M的限制）
int remainder = list.size() % 500;
List<Map<String, String>> oneSubmit = null;
for(int i = 0; i<= count; i++) {
if(i == count) {
if(remainder > 0) {
oneSubmit = list.subList(500*i, 500*i+remainder);
ceShiDao.batchInsertZztContactTmp(oneSubmit);
}
}else {
oneSubmit = list.subList(500*i, 500*(i+1));
ceShiDao.batchInsert(oneSubmit);
}
} @Insert({"<script>",
"insert into xxx (file_name, file_id, user_id, phone,create_user_id,update_user_id,status,params) values ",
"<foreach collection='list' item='item' separator=','>",
" (#{item.fileName},",
" #{item.fileId},",
" #{item.userId},",
" #{item.phone},",
" #{item.createUserId},",
" #{item.updateUserId},",
" #{item.status},",
" #{item.params})",
"</foreach>",
"</script>"})
int batchInsert(@Param("list") List<Map<String, String>> list);
2.批量删除
int count = contactIdList.size() / 500;
int remainder = contactIdList.size() % 500;
List<Integer> oneSubmit = null;
for(int i = 0; i<= count; i++) {
if(i == count) {
if(remainder > 0) {
oneSubmit = contactIdList.subList(500*i, 500*i+remainder);
ceShiDao.batchDelete(oneSubmit);
}
}else {
oneSubmit = contactIdList.subList(500*i, 500*(i+1));
ceShiDao.batchDelete(oneSubmit);
}
} @Delete({"<script>",
"delete from xxx",
"where tmp_contact_id in ",
" <foreach collection='list' item='item' open='(' separator=',' close=')'>",
" #{item}",
" </foreach>",
"</script>"
})
int batchDelete(@Param("list") List<Integer>list);