mybatis+MySQL批量插入更新数据

声明：所有数据来源于网络

service层方法

public int saveOrUpdate(List<Map<String,Object>> insertItems, String tableName){
        int eachSize = 1000;//每次提交数量
        if(!insertItems.isEmpty()){
            Map<String,Object> params = new HashMap<>();
            int count = insertItems.size()/eachSize;
            int yu = insertItems.size()%eachSize;
            for(int i = 0; i <= count; ++i){
                List<Map<String,Object>> subList = new ArrayList<>();
                if( i == count){
                    if(yu != 0){
                        subList = insertItems.subList(i * eachSize, eachSize * i + yu);
                    }else {
                        continue;
                    }
                } else {
                    subList = insertItems.subList(i * eachSize, eachSize * (i + 1));
                }
                params.put("table_name",tableName);
                params.put("fields",subList.get(0));
                params.put("list",subList);
                //调用mapper插入数据库
                mapper.saveOrUpdate(params);
                return 1;
            }
        }
        return 0;
    }

mapper层sql注解

@Insert({
            "<script>",
            "INSERT INTO ${table_name}",
            "<foreach collection=\"fields\" index=\"field\"  item=\"fieldVal\" separator=\",\" open=\"(\" close=\")\">",
            "${field}",
            "</foreach>",
            "VALUES  ",
            "<foreach collection=\"list\" index=\"index\" item=\"record\" separator=\",\" >",
            "<foreach collection=\"record\" index=\"key\"  item=\"item\" separator=\",\" open=\"(\" close=\")\">",
            "#{item}",
            "</foreach>",
            "</foreach> ",
            "ON DUPLICATE KEY UPDATE ",
            "<foreach collection=\"fields\" index=\"field\"  item=\"fieldVal\" separator=\",\">",
            "${field}=VALUES(${field})",
            "</foreach>",
            "</script>"
    })
    int saveOrUpdate(Map<String,Object> map);

SQL示例

INSERT INTO `test` (`a`,`b`,`c`) VALUES
('3','1','1'),
('3','2','1'),
('3','3','1'),
ON DUPLICATE KEY UPDATE
a = VALUES(a),
b = VALUES(b),
c = VALUES(c)