鍍金池/ 問答/Java/ mybatis批量insert出錯(cuò)

mybatis批量insert出錯(cuò)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qtay.gls.dao.TeacherLabelDao">
    <sql id="key">
        <trim suffixOverrides=",">
            <if test="item.id!=null">
                id,
            </if>
            <if test="item.teacherId != null">
                teacher_id,
            </if>
            <if test="item.name!=null">
                name,
            </if>
        </trim>
    </sql>
    <sql id="value">
        <trim suffixOverrides=",">
            <if test="item.id!=null">
                #{item.id},
            </if>
            <if test="item.teacherId!=null">
                #{item.teacherId},
            </if>
            <if test="item.name!=null">
                #{item.name},
            </if>
        </trim>
    </sql>
    <insert id="saveTeacherLabels" keyProperty="id" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into teacher_labels(<include refid="key"/>) values(<include refid="value"/>)
        </foreach>
    </insert>
</mapper>
### 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 'insert into teacher_labels( 
         id,
            
            
            ' at line 19
### The error may involve com.qtay.gls.dao.TeacherLabelDao.saveTeacherLabels-Inline
### The error occurred while setting parameters
### SQL: insert into teacher_labels(           id,                                           teacher_id,                                           name       ) values(           ?,                                           ?,                                           ?       )          ;              insert into teacher_labels(           id,                                           teacher_id,                                           name       ) values(           ?,                                           ?,                                           ?       )
### 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 'insert into teacher_labels( 
         id,
            
            
            ' at line 19
; bad SQL grammar []; nested exception is 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 'insert into teacher_labels( 
         id,
            
            
            ' at line 19, path=/teacher/save}] as "application/json;charset=utf-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@39023dbf]
2017-11-20 14:22:55.547 DEBUG [http-nio-9090-exec-4] o.s.web.servlet.DispatcherServlet - Null ModelAndView returned to DispatcherServlet with name 'dispatcherServlet': assuming HandlerAdapter completed request handling
2017-11-20 14:22:55.547 DEBUG [http-nio-9090-exec-4] o.s.web.servlet.DispatcherServlet - Successfully completed request

只單個(gè)添加一條數(shù)據(jù)是正確的,當(dāng)添加2個(gè)及以上就報(bào)上面的錯(cuò),請(qǐng)問如何解決?

回答
編輯回答
脾氣硬

Mybatis 每次只能執(zhí)行一條語句。
批量插入

<insert id="saveTeacherLabels" keyProperty="id" parameterType="java.util.List">
    insert into teacher_labels(id, teacher_id, name) values
    <foreach collection="list" item="item" index="index" separator=",">
        (<include refid="value"/>)
    </foreach>
</insert>
2017年11月12日 19:07
編輯回答
放開她

根據(jù)樓上的提示,發(fā)現(xiàn)確實(shí)如此,然后百度了一下,加上allowMultiQueries=true就OK了

spring.datasource.url=jdbc:mysql://yoururl?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
2018年7月31日 19:05