mybatis批量insert&update

最新推荐文章于 2024-05-21 18:07:54 发布

wan_zaiyunduan

最新推荐文章于 2024-05-21 18:07:54 发布

阅读量240

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/wan_zaiyunduan/article/details/103033545

版权

数据库专栏收录该内容

18 篇文章 0 订阅

订阅专栏

int batchInsert(@Param("records") List<TypeDO> records);

批量插入separator=","

  <insert id="batchInsert">
    insert into case_type (id, type, failed_number,
    create_time, remark)
    values
    <foreach collection="records" item="record" separator="," close="">
      (#{record.id,jdbcType=INTEGER}, #{record.type,jdbcType=VARCHAR}, #{record.failedNumber,jdbcType=DOUBLE},
      #{record.createTime,jdbcType=TIMESTAMP}, #{record.remark,jdbcType=VARCHAR})
    </foreach>
  </insert>

批量更新 separator=";"

    <update id="batchUpdateByMonth" parameterType="java.util.List">
        <foreach collection="records" item="record" index="index" open="" close="" separator=";">
            update interface_statistics_month
            <set>
                add_count=#{record.addCount,jdbcType=INTEGER},
                `name`=#{record.name,jdbcType=VARCHAR},
                create_time=#{record.createTime,jdbcType=TIMESTAMP},
                comment=#{record.comment,jdbcType=VARCHAR}
            </set>
            where name = #{record.name,jdbcType=VARCHAR}
            AND comment=#{record.comment,jdbcType=VARCHAR}
        </foreach>
    </update>

今天写了两个批量操作语句，先写了一个批量插入，直接复制改成批量更新，由于separator的不同，一直报一个语法错误，如下

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 
            update interface_statistics_month
             SET add_count=0,
  ' at line 8
### The error may involve com.sankuai.sjst.qa.rms.cloud.api.lib.dao.interfacestatistics.mapper.InterfaceStatisticsMapper.batchUpdateByMonth-Inline
### The error occurred while setting parameters

后来把mybatis语句打印出来，才发现插入跟更新分隔符不一样

mybatis sql输出到工作台配置

修改mybatis-config.xml配置文件，增加 <setting name="logImpl" value="STDOUT_LOGGING" />

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="false"/>
        <setting name="lazyLoadingEnabled" value="false"/>
        <setting name="aggressiveLazyLoading" value="false"/>
        <setting name="multipleResultSetsEnabled" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <setting name="defaultStatementTimeout" value="25000"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 是否使用插入数据后自增主键的值，需要配合keyProperty使用 -->
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
</configuration>