mybatis批量查询


注意这里的 in 和   <trim prefix="(" suffix=")"> 以及 in ( )的三种方式的(例1​(推荐)​,例2,例3(推荐))等价使用

 ​例1:

List<UBaseMenu> findMenuName(List<String> valueList);

Mybatis批处理(批量查询,更新,插入)_xml

<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>

Mybatis批处理(批量查询,更新,插入)_xml

例2:

List<ReturnCodeEntity> selectByIds(@Param("ids") List<Long> ids);

Mybatis批处理(批量查询,更新,插入)_xml

<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>

Mybatis批处理(批量查询,更新,插入)_xml

 例3:

mapper接口代码:

public List<User> findUserListByIdList(List<Long> idList);


xml代码:

Mybatis批处理(批量查询,更新,插入)_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>

Mybatis批处理(批量查询,更新,插入)_xml


批量插入:

mapper.java

int addResource(List<Resource> ResourceList);


mapper.xml

Mybatis批处理(批量查询,更新,插入)_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>

Mybatis批处理(批量查询,更新,插入)_xml


批量更新:

mapper.java

int updateRoles(List<String> roleList);


mapper.xml

Mybatis批处理(批量查询,更新,插入)_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>

Mybatis批处理(批量查询,更新,插入)_xml

梅花香自古寒来