1 批量更新SQL

当我们需要对MySQL数据库中的数据进行批量更新时，可以采用以下方式进行更新，以下为mybatis配置的SQL语句

<!-- 批量更新，通过 case when语句变相的进行批量更新 --><update id="batchUpdateRecords" parameterType="java.util.List">update table_name<trim prefix="set" suffixOverrides=","><trim prefix="detail_status = case" suffix="end,"><foreach collection="list" item="cus"><if test="cus.detailStatus!=null">when id=#{cus.id} then #{cus.detailStatus}</if></foreach></trim><trim prefix="error_json = case" suffix="end,"><foreach collection="list" item="cus"><if test="cus.errorJson!=null">when id=#{cus.id} then #{cus.errorJson}</if></foreach></trim><trim prefix="change_content = case" suffix="end,"><foreach collection="list" item="cus"><if test="cus.changeContent!=null">when id=#{cus.id} then #{cus.changeContent}</if></foreach></trim><trim prefix="update_time = case" suffix="end,"><foreach collection="list" item="cus"><if test="cus.updateTime!=null">when id=#{cus.id} then #{cus.updateTime}</if></foreach></trim><trim prefix="yn = case" suffix="end,"><foreach collection="list" item="cus"><if test="cus.yn!=null">when id=#{cus.id} then #{cus.yn}</if></foreach></trim></trim><where><foreach collection="list" separator="or" item="cus">id = #{cus.id}</foreach></where></update>

2 批量插入SQL

以下是批量插入的SQL语句

<!-- 插入 --><insert id="batchInsert" parameterType="com.dd.toolbox.manager.po.TransbillPo">INSERT INTO print_transbill (dept_no, dept_name, create_user, create_time, update_user, update_time,transbill_code, box_num_start, box_num_end, box_print_status, print_type, ts, yn)VALUES<foreach collection="list" item="printPo" separator="," close=";">(#{printPo.deptNo,jdbcType=VARCHAR}, #{printPo.deptName,jdbcType=VARCHAR}, #{printPo.createUser,jdbcType=VARCHAR},#{printPo.createTime,jdbcType=TIMESTAMP}, #{printPo.updateUser,jdbcType=VARCHAR}, #{printPo.updateTime,jdbcType=TIMESTAMP},#{printPo.transbillCode,jdbcType=VARCHAR}, #{printPo.boxNumStart,jdbcType=VARCHAR}, #{printPo.boxNumEnd,jdbcType=VARCHAR},#{printPo.boxPrintStatus,jdbcType=VARCHAR},  #{printPo.printType,jdbcType=VARCHAR}, now(), 1)</foreach></insert>