mybatis批量添加修改操作

客官°小女子只卖身不卖艺 2024-04-07 14:01 91阅读 0赞

mybatis批量查询  
注意这里的 in 和 以及 in ( ）的三种方式的（例1（推荐），例2，例3（推荐））等价使用

例1；  
List findMenuName(List valueList);

<select id="findMenuName" resultType="java.lang.String" parameterType="java.util.List">
    select menu_name
    from menu
    where menu_id in
    <foreach collection="list" item="valueList" open="(" close=")" separator=",">
    #{
        valueList}
    </foreach>
    </select>

例2；  
List selectByIds(@Param(“ids”) List ids);

<select id="selectByIds" parameterType="java.util.List"
                resultType="com.paic.ocss.gateway.model.entity.ReturnCodeEntity">
            SELECT
            id,
            code,
            message,
            recommendation,
            status
            FROM openapi_return_code
            WHERE id in
            <trim prefix="(" suffix=")">
                <foreach collection="ids" index="index" item="id" separator=",">
                    #{
        id}
                </foreach>
            </trim>
        </select>

例3：  
public List findUserListByIdList(List idList);

<select id="findUserListByIdList" parameterType="java.util.ArrayList" resultType="User">    
        select * from user user    
        <where>    
            user.ID in (    
              <foreach collection="list"  item="id" index="index" separator=",">   
                 #{
        id}   
              </foreach>    
            )    
        </where>    
    </select>

例4：批量插入  
int addResource(List ResourceList);

<insert id="addResource" parameterType="java.util.List">
    insert into resource (object_id, res_id, res_detail_value, 
    res_detail_name)
    values
    <foreach collection="list" item=" ResourceList " index="index" separator=",">
    (#{
        ResourceList.objectId,jdbcType=VARCHAR},
    #{
        ResourceList.resId,jdbcType=VARCHAR},
    #{
        ResourceList.resDetailValue,jdbcType=VARCHAR},
    #{
        ResourceList.resDetailName,jdbcType=VARCHAR}
    )
    </foreach>
    </insert>

批量更新：  
int updateRoles(List roleList);

<update id="updateRoles" parameterType="java.util.List">
    update role
    set enabled = '0'
    where role_id in <foreach collection="list" item="roleIds" index="index" open="(" separator="," close=")"> 
    #{
        roleIds} 
    </foreach>
    </update>

批量删除  
int bathDelete(@Param(“ids”) Long\[\] ids);

<delete id="bathDelete" parameterType="Long">
            delete from sys_type where id in
            <foreach item="id" collection="ids" open="(" separator="," close=")">
            	#{
        id}
            </foreach>
        </delete>