最近在做公司项目中,发现有些表中的数据属于结果类型的数据,这种数据我们只需要插入最新的即可,比如人员信息,工作分配信息等等。还有一种过程数据,比如家庭信息,一个人会有多个家庭成员。
对于结果类型的数据插入表中时,我们可以根据员工编号作为主键,进行覆盖操作。比如下面的SQL。
INSERT INTO family (ygid,name,age) VALUES (?,?,?) ON DUPLICATE KEY UPDATE name=?,age=?;
对于过程类型的数据而言,就不能使用员工编号作为主键了,一个员工编号对应多条记录,一般都是使用自增ID作为主键。当然如果有唯一主键的话也可以采用上面的方法。下面讨论不属于上面的情况的解决方案。
一种思路就是根据这条记录中某个字段来选择更新,比如家庭信息,如果有成员类型的字段就可以使用update语句进行更新记录。但是这种情况对应不增加记录的情况是可以的,但是很多情况是就会有新增的情况,又有更新的情况。只用update语句就不行了。比如家庭成员中,新出生了一个孩子,就是新增情况;你发现你的家庭成员信息中出生日期错了,就涉及到修改的问题。
对于这种情况就需要采用另一种思路,就是先根据员工编号进行删除,然后在进行新增就可以解决上面的既有新增又有更新的问题了。具体的代码可以参考下面思路。
public static void batchInsert(List<familyInfo> infos) throws SQLException {
long start = System.currentTimeMillis();
Connection conn = getConnection();
conn.setAutoCommit(false);
int count = 1;
PreparedStatement ps = null;
PreparedStatement clean = null;
String cleansql = "delete from family where ygid = ?";
String sql = "insert into family values (?,?,?)";
// 批量插入时ps对象必须放到for循环外面
clean = conn.prepareStatement(cleansql);
ps = conn.prepareStatement(sql);
for (familyInfo info : infos){
clean.setString(1, info.getYgid());
ps.setString(1, info.getYgid());
ps.setString(2, info.getName());
ps.setString(3, info.getAge());
clean.addBatch();
ps.addBatch();
// 每1000条记录插入一次
if (count % 1000 == 0){
clean.executeBatch();
ps.executeBatch();
conn.commit();
clean.clearBatch();
ps.clearBatch();
}
}
// 剩余数量不足1000
clean.executeBatch();
ps.executeBatch();
conn.commit();
clean.clearBatch();
ps.clearBatch();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
我自己测试过程按照上面的思路执行,我的项目2万多条记录先删除再插入25s,只是插入时间是19s。最后再提醒一点,执行delete 操作的SQL语句where条件后面的字段ygid,一定要加一个索引,我这边是加了一个Normal的索引。不加索引,连删除再加插入2万多条记录的时间是240s,差了一个数量级了。后来通过对比发现慢就慢在删除的SQL语句执行上面了。
参考资料
https://www.cnblogs.com/rocky-AGE-24/p/7392641.html
https://blog.csdn.net/qq_36092584/article/details/80721904