mybatis 多条件批量增删改查

绝地灬酷狼 2022-12-28 15:30 200阅读 0赞

批量删除用or拼接where，批量更新用;拼接每局，批量插入用，拼接值，批量查询用（）in整体打包

用；批量更新jdbc连接需设置allowMultiQueries=true---也就是一句一句完整句子用；批量插入也可用此方式

jdbc:mysql://1.4.5.51:51/test?useSSL=false&useUnicode=true&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&autoReconnect=true

批量查询

/**
         * 根据pos版本,数据版本，餐厅组查询所用菜单版本信息
         *
         * @param assignAddStoreParamVos 菜单版本
         * @return 菜单版本
         */
        List<TemplateVersionVO> getTemplateVersionInfo(@Param("assignAddStoreParamVos") List<AssignAddStoreParamVO> assignAddStoreParamVos);
    
             
        <select id="getTemplateVersionInfo" resultMap="BaseResultMap">
            SELECT
            DISTINCT tv.id,tv.pos_menu_version,tv.sequence_no,tv.data_version
            FROM
                t_template_version tv
                LEFT JOIN t_template_used tu ON tv.pos_menu_version = tu.pos_menu_version
                AND tv.sequence_no = tu.sequence_no
                AND tv.data_version = tu.data_version
                LEFT JOIN t_template_store_group tsg ON tsg.data_version = tu.data_version
                AND tsg.channel_group_code = tu.channel_group_code
                AND tsg.template_id = tu.template_id
                LEFT JOIN t_store_group_store tsts ON tsts.data_version = tsg.data_version
                AND tsts.store_group_code = tsg.store_group_code
            WHERE
            <if test="assignAddStoreParamVos !=null and assignAddStoreParamVos.size()>0">
                (tsts.store_group_code, tu.data_version,tv.pos_menu_version,tv.sequence_no) in
                <foreach collection="assignAddStoreParamVos" item="assignAddStore" index="index" open="(" separator="," close=")">
                    (#{assignAddStore.storeGroupCode}, #{assignAddStore.dataVersion},#{assignAddStore.posMenuVersion},#{assignAddStore.sequenceNo})
                </foreach>
            </if>
                AND tv.`status` IN ( 11, 12, 13, 14, 15 )
        </select>

批量更新

<update id="batchUpdateTemplateStatus" parameterType="com.mcd.cn.rdc.pcm.product.business.vo.template.TemplateParam">
            update t_template
            set status = #{status},
            version = version + 1,
            updated_user = #{updatedUser},
            updated_date = NOW()
            where data_version = #{dataVersion}
              and template_id in (
                <foreach collection="templateIdList" item="obj" index="index" separator="," >
                    #{obj}
                </foreach>
                )
        </update>
        
         
        <update id="updateTemplateCategoryProductSort" parameterType="list">
            <foreach collection="list" item="obj" index="index" separator=";">
                update t_template_category_product
                set sort = #{obj.sort},
                version = version + 1,
                updated_user = #{obj.updatedUser},
                updated_date = NOW()
                where data_version = #{obj.dataVersion}
                and template_id = #{obj.templateId}
                and dimension_id = #{obj.dimensionId}
                and category_id = #{obj.categoryId}
                and product_code = #{obj.productCode}
            </foreach>
        </update>

批量增加

<insert id="batchSaveUser">
            insert into t_user (user_name,sex) values
            
            
            <foreach collection="users" item="user" separator=",">
                (#{user.userName},#{user.sex})
            </foreach>
        </insert>

批量增加方式二：

<insert id="syncProductLanguageData">
            <foreach collection="list" item="obj" index="index" separator=";" >
            INSERT INTO t_language (
                data_version,
                LANGUAGE,
                table_name,
                field_name,
                key_code,
                content,
                created_user,
                created_date,
                updated_user,
                updated_date,
                version
            ) SELECT
                #{obj.targetDataVersion} data_version,
                  LANGUAGE,
                  table_name,
                  field_name,
                  key_code,
                  content,
                #{obj.createdUser} created_user,
                  now() created_date,
                #{obj.createdUser} updated_user,
                  now() updated_date,
                  1 version
            FROM
                t_language
            WHERE
                data_version =  #{obj.sourceDataVersion}
                and table_name in ('t_product','t_product_choice','t_product_combo_composition','t_product_grill','t_product_grill_item')
                and (
                <foreach collection="obj.productCodeList" item="productCode" index="index" separator="or">
                    (key_code = #{productCode} or key_code like concat(#{productCode},'-%'))
                </foreach>
                )
            </foreach>
        </insert>

批量删除

<delete id="batchDeleteUser">
            delete from t_user where id in (
            <foreach collection="ids" item="id" separator=",">
                #{id}
            </foreach>
            )  
    </delete>

第二种批量删除的写法

open表示该语句以什么开始，close表示以什么结束

<delete id="batchDeleteUser">
            delete from t_user where id in 
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
    </delete>

复杂条件批量删除

<delete id="deleteByLogic"  parameterType = "java.util.List">
         delete from user where id > 2000
             or id in
          <foreach collection="list"  item="item" open="(" separator="," close=")"  >
               #{item}
          </foreach>
    </delete>

复杂条件批量删除 建议增加联合索引

<delete id="batchDeleteByCondition">
            delete from test where
            <foreach collection="list" item="item" separator=" or " index="index">
                (name = #{item.name} and area_code = #{item.areaCode} and type = #{item.type})
            </foreach>
        </delete>

复杂条件批量删除 IN操作

<delete id="deleteBatch" parameterType="java.util.List">
        delete from test
        where (PRSNLID, ENTID) in
        <foreach item="item" index="index" collection="list" separator="," open="(" close=")">
    (#{item.prsnlid},#{item.entid})
    	</foreach>
    </delete>
    
    
    
     <delete id="deleteByStore" parameterType="com.mcd.cn.rdc.pcm.product.business.model.store.StoreMenuRecord">
        delete from t_template_menu_record
        <where>
            <foreach collection="menuStores" item="menuStore" index="index" separator=" or ">
            ( pos_menu_version = #{menuStore.posMenuVersion,jdbcType=VARCHAR}
            AND sequence_no=#{menuStore.sequenceNo,jdbcType=INTEGER}
            AND store_group_code=#{menuStore.storeGroupCode,jdbcType=VARCHAR}
            AND store_code=#{menuStore.storeCode,jdbcType=INTEGER}
            )
            </foreach>
        </where>
      </delete>