mybatis批量更新、修改，以及异常，新增。较优

小灰灰 2021-10-14 19:45 329阅读 0赞

springboot2.1.1

**批量修改**

<update id="updateBatch" parameterType="com.yst.user.entity.User">
    		<foreach collection="list" item="item" index="index"
    			separator=";" close=";">
    			UPDATE T_USER
    			<set>
    				<if test="item.membershipId != null">MEMBERSHIP_ID = #{item.membershipId},</if>
    				<if test="item.membershipType != null">MEMBERSHIP_TYPE = #{item.membershipType},</if>
    				<if test="item.updateDate != null">UPDATE_DATE = #{item.updateDate}</if>
    			</set>
    			WHERE USER_ID = #{item.userId}
    		</foreach>
    	</update>

**如果出现异常：BadSqlGrammarException**

是没有设置数据库的配置&allowMultiQueries=true

spring.datasource.url=jdbc:mysql://localhost:3306/user?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&allowMultiQueries=true

另外还有一种方式，是不能修改配置的情况下才使用，附上原文链接：[https://blog.csdn.net/soda\_lw/article/details/88849259][https_blog.csdn.net_soda_lw_article_details_88849259]

批量添加：

<insert id="largeSave">
    		INSERT INTO bus_project_maintenance_detail(
    			id,
    			create_by,
    			create_date,
    			update_by,
    			update_date,
    			remarks,
    			del_flag,
    			projectname,
    			year,
    			username,
    			userid,
    			coercion_type,
    			compulsory_own_funds,
    			compulsory_company_borrowing,
    			compulsory_mammon_loan,
    			isforceorright,
    			people_type,
    			mainid
    		) VALUES
    		 	<foreach collection="list" item="busProjectMaintenanceDetail" separator=",">
    		 (
    				#{busProjectMaintenanceDetail.id},
    				#{busProjectMaintenanceDetail.createBy.id},
    				#{busProjectMaintenanceDetail.createDate},
    				#{busProjectMaintenanceDetail.updateBy.id},
    				#{busProjectMaintenanceDetail.updateDate},
    				#{busProjectMaintenanceDetail.remarks},
    				#{busProjectMaintenanceDetail.delFlag},
    				#{busProjectMaintenanceDetail.projectname},
    				#{busProjectMaintenanceDetail.year},
    				#{busProjectMaintenanceDetail.username},
    				#{busProjectMaintenanceDetail.userid},
    				#{busProjectMaintenanceDetail.coercionType},
    				#{busProjectMaintenanceDetail.compulsoryOwnFunds},
    				#{busProjectMaintenanceDetail.compulsoryCompanyBorrowing},
    				#{busProjectMaintenanceDetail.compulsoryMammonLoan},
    				#{busProjectMaintenanceDetail.isforceorright},
    				#{busProjectMaintenanceDetail.peopleType},
    				#{busProjectMaintenanceDetail.mainid}
    		)
    			</foreach>
    	</insert>

[https_blog.csdn.net_soda_lw_article_details_88849259]: https://blog.csdn.net/soda_lw/article/details/88849259