myBaits 拼接sql语句进行批量执行sql语句插入

爱学习的呆鸟

于 2024-02-07 14:12:20 发布

阅读量364

点赞数 4

文章标签： sql 数据库

本文链接：https://blog.csdn.net/iu921/article/details/136068841

版权

##最重要的一点！！！！

在配置文件中必须添加“&allowMultiQueries=true”

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://********:3306/tollstationweightdetection?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true
    username: root
    password: ********

service层：

@Autowired
ParameterVersionMapper parameterVersionMapper;

@Override
public int insertPermissionVersionInfo(String permissionDownloadRes) {
    //批量插入，sql语句进行拼接
    StringBuffer sqlStrBuffer = new StringBuffer();
    StringBuffer valueStrBuffer = new StringBuffer();
    ArrayList<String> sqlList = new ArrayList<>();
    String insert = "INSERT INTO tb_permissionall (";
    String value = "values (";
    for (String res : permissionDownloadRes.split("\n")) {
        sqlStrBuffer.append(insert);
        valueStrBuffer.append(value);
        if (!"".equals(res.split("#")[0]) && res.split("#")[0] != null) {
            sqlStrBuffer.append("cert_no,");
            valueStrBuffer.append("'").append(res.split("#")[0]).append("',");
        }
        if (!"".equals(res.split("#")[1]) && res.split("#")[1] != null) {
            sqlStrBuffer.append("provinces,");
            valueStrBuffer.append("'").append(res.split("#")[1]).append("',");
        }
        sqlStrBuffer.deleteCharAt(sqlStrBuffer.length() - 1);
        valueStrBuffer.deleteCharAt(valueStrBuffer.length() - 1);
        sqlStrBuffer.append(") ").append(valueStrBuffer).append(")");
        valueStrBuffer.setLength(0);
        sqlList.add(sqlStrBuffer.toString());
        sqlStrBuffer.setLength(0);
    }
   return parameterVersionMapper.insertPermissionVersionInfo(sqlList);
}

**mapper.xml

<update id="insertPermissionVersionInfo" parameterType="java.util.List">
    <foreach collection="sqlList" item="item" separator=";">
        ${item}
    </foreach>
</update>