mybatis批量更新,和修改出现的小问题

在连接数据库的配置url中要加入?allowMultiQueries=true这段

 

  然后mapper层就如下写

 

 

 

最后mapper.xml就是正常的写法，解释一下，我的collection="list"，为什么写list，因为传入的是一个list集合，这里必须写list，

如果传入一个数组比如Integer[]，那么就要写collection="array"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<update id="updateStudentAnswer" parameterType="java.util.List">         <if test="list!=null">             <foreach collection="list" item="studentAnswer" index= "index" open="" close="" separator =";">                 update studentanswerinfo                 <set>                     SAnswer=#{studentAnswer.SAnswer},                     Getpoint=#{studentAnswer.Getpoint},                     other=#{studentAnswer.other}                 </set>                 <where>                     questionID=#{studentAnswer.questionID}                 </where>             </foreach>         </if>     </update>

 

    <!--查询状态后同步 -->
    <update id="updateTaskInfo" parameterType="java.util.List">

            <foreach collection="list" item="TmpTableTaskInfoDTO" index="index" open="" close="" separator=";">
                update mapping_integration.task_info
                <set>
                    project_name = #{TmpTableTaskInfoDTO.projectId},
                    task_status_desc = #{TmpTableTaskInfoDTO.status},
                    create_person = #{TmpTableTaskInfoDTO.submitUser},
                    flow = #{TmpTableTaskInfoDTO.flowId},
                    finished_time = #{TmpTableTaskInfoDTO.endTime}
                </set>
                <where>
                    exec_id = #{TmpTableTaskInfoDTO.execId}
                </where>
            </foreach>
    </update>

 

 

连接数据库的开头加入?allowMultiQueries=true 时，还是一直报这个错误

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 ';

 

 

今天在Stack Overflow上找到了另外一种解决办法，也不需要在连接数据库的url上加入?allowMultiQueries=true。原来出现这种原因，主要是批量插入list时，mysql是拼接sql语句，之前这样写，拼接会出现values后面会有空格等mysql不识别的sql语句。需要如下拼接

 

1 2 3 4 5 6 7 8

<insert id="insertRecords" parameterType="java.util.List">             replace into bi_staff_skill_information             (staff_id, skill_id, skill_level)values             <foreach item="staffSkillInfo" index="index" collection="list"                  open="(" separator="),(" close=")">                 #{staffSkillInfo.staffId},#{staffSkillInfo.skillId},#{staffSkillInfo.skillLevel}             </foreach>     </insert>