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>