dongxurr 2019-12-09
mybatis批量查询
注意这里的 in 和 <trim prefix="(" suffix=")"> 以及 in ( )的三种方式的(例1(推荐),例2,例3(推荐))等价使用
例1:
1 | List< UBaseMenu > findMenuName(List< String > 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<ReturnCodeEntity> selectByIds(@Param("ids") List<Long> 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:
mapper接口代码:
public List<User> findUserListByIdList(List<Long> idList);
xml代码:
<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>
批量插入:
mapper.java
int addResource(List<Resource> ResourceList);
mapper.xml
<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>
批量更新:
mapper.java
int updateRoles(List<String> roleList);
mapper.xml
<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>