Mybatis ＜foreach＞遍历查询, 根据：数组、List、Map, 简洁例子

成功人chen某

已于 2024-08-10 12:53:12 修改

阅读量737

点赞数 3

CC 4.0 BY-SA版权

文章标签： mybatis list sql

于 2024-08-10 12:46:03 首次发布

本文链接：https://blog.youkuaiyun.com/qq_45258618/article/details/141071411

注：xxx为某某某的意思

数组

关键部分

        select * from xxxx where xxx in
        <foreach collection="array" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>

List

关键部分

        select * from xxxx where xxx in
        <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>

Map

关键部分

        select * from t_customer where jobs=#{jobs} and id in
        <foreach collection="id" index="index" item="roleMap" open="(" separator="," close=")">
            #{roleMap}
        </foreach>

对于<foreach collection="id" index="index" item="roleMap" open="(" separator="," close=")">中的内容：

open="(" 以前括号开头，

separator="," 逗号分隔，

close=")" 后括号结尾。

collection="” 当为List时写list，当为数组时写

array，当为Map时写Map里面对应的键名，如：

        List<Integer> ids=new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);

        Map<String,Object> conditionMap = new HashMap<String,Object>();
        conditionMap.put("id",ids);
        conditionMap.put("jobs","job");

;

        <foreach collection="id" index="index" item="roleMap" open="(" separator="," close=")">
            #{roleMap}
        </foreach>

id指的是Map里的键名“id” item指的是“id”指的叫ids的List中的元素

最终SQL语句可能是 select * from t_customer where jobs=#{jobs} and id in (1,2);

完整例子

//数组
        Integer[]  roleIds = {2,3};
        List<ccc> cccs= session.selectList("com.xxx.xxx.cccMapper.findByArray",roleIds);


//List
/*
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        List<ccc> cccs= session.selectList("com.xxx.xxx.cccMapper.findByList",ids);
*/


//Map
/*
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);

        Map<String,Object> conditionMap = new HashMap<String,Object>();
        conditionMap.put("id",ids);
        conditionMap.put("jobs","teacher");
        List<ccc> cccs= session.selectList("com.xxx.xxx.cccMapper.findByMap",conditionMap);
*/

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.xxx.cccMapper">
    <select id="findByArray" parameterType="java.util.Arrays"
            resultType="com.xxx.xxx.ccc">
<!--数组-->
        select * from xxxx where xxx in
        <foreach collection="array" index="index" item="id" open="(" separator="," close=")">


<!--List-->
<!--
        select * from xxxx where xxx in
        <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
           #{id}
        </foreach>
-->


<!--Map-->
<!--
        select * from t_customer where jobs=#{jobs} and id in
        <foreach collection="id" index="index" item="roleMap" open="(" separator="," close=")">
            #{roleMap}
        </foreach>
-->

    </select>
</mapper>