AI专业者开发社区 1 号成员
  •  0 回帖  •  4 浏览  •  5 个月前
 

mybatis中foreach使用方法

foreach 一共有三种类型，分别为 List,[](array),Map 三种。

foreach 属性

属性描述
item 循环体中的具体对象。支持属性的点路径访问，如 item.age,item.info.details。 具体说明：在 list 和数组中是其中的对象，在 map 中是 value。 该参数为必选。
collection 要做 foreach 的对象，作为入参时，List<?> 对象默认用 list 代替作为键，数组对象有 array 代替作为键，Map 对象用 map 代替作为键。 当然在作为入参时可以使用 @Param("keyName") 来设置键，设置 keyName 后，list,array,map 将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子： 如果 User 有属性 List ids。入参是 User 对象，那么这个 collection = "ids" 如果 User 有属性 Ids ids; 其中 Ids 是个对象，Ids 有个属性 List id; 入参是 User 对象，那么 collection = "ids.id" 上面只是举例，具体 collection 等于什么，就看你想对那个元素做循环。 该参数为必选。
separator 元素之间的分隔符，例如在 in()的时候，separator="," 会自动在元素中间用“,“隔开，避免手动输入逗号导致 sql 错误，如 in(1,2,) 这样。该参数可选。
open foreach 代码的开始符号，一般是 (和 close=")" 合用。常用在 in(),values() 时。该参数可选。
close foreach 代码的关闭符号，一般是 )和 open="(" 合用。常用在 in(),values() 时。该参数可选。
index 在 list 和数组中,index 是元素的序号，在 map 中，index 是元素的 key，该参数可选。

 

示例一：

<select id="countByUserList" resultType="_int" parameterType="list">
select count(*) from users
  <where>
    id in
    <foreach item="item" collection="list" separator="," open="(" close=")" index="">
      #{item.id, jdbcType=NUMERIC}
    </foreach>
  </where>
</select>

注：select count(*) from users WHERE id in (? , ?) 

 

示例二：

    <select id="selectStorageProductInventroy" parameterType="java.util.Map" resultType="StorageProductInventroyModel">
        select op.inventroy_operation_id  ,
        op.inventroy_id  ,
        ty.name as operation_type,
        DATE_FORMAT(op.insert_time,'%Y/%m/%d') as insert_time,
        em.name as employee,
        sm1.name as from_store,
        st1.name as to_store,
        sm.name as from_storage,
        st.name as to_storage,
        hi.number,
        pr.name as product_name,
        pr.category_id as categoryString,
        pr.product_code,
        hi.amount,
        hi.back_amount,
        hi.unit,
        re.description as operation_reason,
        hi.description

    </span><span style="color: rgba(0, 0, 255, 1)">from</span> inventroy_history hi  <span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span><span style="color: rgba(0, 0, 0, 1)"> inventroy_operation  op 
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> hi.inventroy_operation_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> op.inventroy_operation_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span><span style="color: rgba(0, 0, 0, 1)"> product pr 
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> hi.product_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)">pr.product_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span><span style="color: rgba(0, 0, 0, 1)"> operation_type ty 
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.operation_type_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> ty.operation_type_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span><span style="color: rgba(0, 0, 0, 1)"> employee em 
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.employee_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> em.employee_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span>   (<span style="color: rgba(0, 0, 255, 1)">select</span>  inventroy_operation.to_storage_id <span style="color: rgba(0, 0, 255, 1)">as</span> to_storage_id,storage.name <span style="color: rgba(0, 0, 255, 1)">as</span><span style="color: rgba(0, 0, 0, 1)"> name
    </span><span style="color: rgba(0, 0, 255, 1)">from</span><span style="color: rgba(0, 0, 0, 1)">  inventroy_operation , storage
    </span><span style="color: rgba(0, 0, 255, 1)">where</span> inventroy_operation.to_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> storage.storage_id) st
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.to_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> st.to_storage_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span>   (<span style="color: rgba(0, 0, 255, 1)">select</span>  inventroy_operation.from_storage_id <span style="color: rgba(0, 0, 255, 1)">as</span> from_storage_id,storage.name <span style="color: rgba(0, 0, 255, 1)">as</span><span style="color: rgba(0, 0, 0, 1)"> name
    </span><span style="color: rgba(0, 0, 255, 1)">from</span><span style="color: rgba(0, 0, 0, 1)">  inventroy_operation , storage
    </span><span style="color: rgba(0, 0, 255, 1)">where</span> inventroy_operation.from_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> storage.storage_id) sm
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.from_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> sm.from_storage_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span>   (<span style="color: rgba(0, 0, 255, 1)">select</span>  inventroy_operation.to_store_id <span style="color: rgba(0, 0, 255, 1)">as</span> to_store_id,store.name <span style="color: rgba(0, 0, 255, 1)">as</span><span style="color: rgba(0, 0, 0, 1)"> name
    </span><span style="color: rgba(0, 0, 255, 1)">from</span><span style="color: rgba(0, 0, 0, 1)">  inventroy_operation , store
    </span><span style="color: rgba(0, 0, 255, 1)">where</span> inventroy_operation.to_store_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> store.store_id) st1
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.to_store_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> st1.to_store_id
           
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span>   (<span style="color: rgba(0, 0, 255, 1)">select</span>  inventroy_operation.from_store_id <span style="color: rgba(0, 0, 255, 1)">as</span> from_store_id,store.name <span style="color: rgba(0, 0, 255, 1)">as</span><span style="color: rgba(0, 0, 0, 1)"> name
    </span><span style="color: rgba(0, 0, 255, 1)">from</span><span style="color: rgba(0, 0, 0, 1)">  inventroy_operation , store
    </span><span style="color: rgba(0, 0, 255, 1)">where</span> inventroy_operation.from_store_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> store.store_id) sm1
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> op.from_store_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> sm1.from_store_id
               
    </span><span style="color: rgba(128, 128, 128, 1)">left</span> <span style="color: rgba(128, 128, 128, 1)">join</span><span style="color: rgba(0, 0, 0, 1)"> operation_reason re 
    </span><span style="color: rgba(0, 0, 255, 1)">on</span> hi.operation_reason_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> re.operation_reason_id 
    </span><span style="color: rgba(0, 0, 255, 1)">WHERE</span> <span style="color: rgba(128, 0, 0, 1); font-weight: bold">1</span><span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(128, 0, 0, 1); font-weight: bold">1</span>
    <span style="color: rgba(128, 128, 128, 1)">&lt;</span><span style="color: rgba(0, 0, 255, 1)">if</span> test<span style="color: rgba(128, 128, 128, 1)">=</span>"operation_type_id <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(0, 0, 255, 1)">null</span> <span style="color: rgba(128, 128, 128, 1)">and</span> operation_type_id.length <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(128, 0, 0, 1); font-weight: bold">0</span>"<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
        <span style="color: rgba(128, 128, 128, 1)">and</span>
        <span style="color: rgba(128, 128, 128, 1)">&lt;</span>foreach collection<span style="color: rgba(128, 128, 128, 1)">=</span>"operation_type_id" item<span style="color: rgba(128, 128, 128, 1)">=</span>"operationTypeId" <span style="color: rgba(0, 0, 255, 1)">index</span><span style="color: rgba(128, 128, 128, 1)">=</span>"<span style="color: rgba(0, 0, 255, 1)">index</span>"  <span style="color: rgba(0, 0, 255, 1)">open</span><span style="color: rgba(128, 128, 128, 1)">=</span>"(" separator<span style="color: rgba(128, 128, 128, 1)">=</span>"<span style="color: rgba(128, 128, 128, 1)">or</span>" <span style="color: rgba(0, 0, 255, 1)">close</span><span style="color: rgba(128, 128, 128, 1)">=</span>")"<span style="color: rgba(128, 128, 128, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
            op.operation_type_id </span><span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> #{operationTypeId} 
        </span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span>foreach<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">&lt;</span><span style="color: rgba(0, 0, 255, 1)">if</span> test<span style="color: rgba(128, 128, 128, 1)">=</span>"storage_id <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(128, 0, 0, 1); font-weight: bold">0</span> <span style="color: rgba(128, 128, 128, 1)">and</span> storage_id <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(0, 0, 255, 1)">null</span>"<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
        <span style="color: rgba(128, 128, 128, 1)">and</span> (op.from_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span> #{storage_id} <span style="color: rgba(128, 128, 128, 1)">or</span> op.to_storage_id <span style="color: rgba(128, 128, 128, 1)">=</span><span style="color: rgba(0, 0, 0, 1)"> #{storage_id})
    </span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span>        
    <span style="color: rgba(128, 128, 128, 1)">&lt;</span><span style="color: rgba(0, 0, 255, 1)">if</span> test<span style="color: rgba(128, 128, 128, 1)">=</span>"category_id <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(128, 0, 0, 1); font-weight: bold">0</span> <span style="color: rgba(128, 128, 128, 1)">and</span> category_id <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(0, 0, 255, 1)">null</span>"<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
        <span style="color: rgba(128, 128, 128, 1)">and</span> pr.category_id <span style="color: rgba(128, 128, 128, 1)">like</span> concat(<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span>,<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">(</span><span style="color: rgba(255, 0, 0, 1)">'</span>,#{category_id},<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">)</span><span style="color: rgba(255, 0, 0, 1)">'</span>,<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    </span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">&lt;</span><span style="color: rgba(0, 0, 255, 1)">if</span> test<span style="color: rgba(128, 128, 128, 1)">=</span>"product_code <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(0, 0, 255, 1)">null</span>"<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
        <span style="color: rgba(128, 128, 128, 1)">and</span> pr.product_code <span style="color: rgba(128, 128, 128, 1)">like</span> concat(<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span>,#{product_code},<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    </span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">&lt;</span><span style="color: rgba(0, 0, 255, 1)">if</span> test<span style="color: rgba(128, 128, 128, 1)">=</span>"product_name <span style="color: rgba(128, 128, 128, 1)">!=</span> <span style="color: rgba(0, 0, 255, 1)">null</span>"<span style="color: rgba(128, 128, 128, 1)">&gt;</span>
        <span style="color: rgba(128, 128, 128, 1)">and</span> pr.name  <span style="color: rgba(128, 128, 128, 1)">like</span> concat(<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span>,#{product_name},<span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(255, 0, 0, 1)">%</span><span style="color: rgba(255, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    </span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span>
    <span style="color: rgba(128, 128, 128, 1)">and</span> date(op.insert_time) <span style="color: rgba(128, 128, 128, 1)">between</span> #{start_time} <span style="color: rgba(128, 128, 128, 1)">and</span><span style="color: rgba(0, 0, 0, 1)"> #{end_time}
    </span><span style="color: rgba(0, 0, 255, 1)">group</span> <span style="color: rgba(0, 0, 255, 1)">by</span><span style="color: rgba(0, 0, 0, 1)"> hi.inventroy_history_id        
</span><span style="color: rgba(128, 128, 128, 1)">&lt;/</span><span style="color: rgba(0, 0, 255, 1)">select</span><span style="color: rgba(128, 128, 128, 1)">&gt;</span></pre>


注：and （op.operation_type_id = ？ or op.operation_type_id = ？）

 

示例三：数组

 public void testQuery() {
        ColInfoDao dao=(ColInfoDao)ctx.getBean("colInfoDao");
        Map map = new HashMap();
        map.put("userId", "tom");
        map.put("password", "123");
        String[] a = { "20000001", "20000002" };
        map.put("classIds", Arrays.asList(a));
        Object password = dao.query(map);
        System.out.println("password:" + password);
        Assert.assertEquals("123", password);}


XML：（感觉不适合 mybatis，可以使用在 ibatis 中，iBatis 2.x 和 MyBatis 3.0.x）

<select id="queryPasswordByUserId" parameterClass="java.util.Map"      resultClass="java.lang.String">
        <![CDATA[
        select PASSWORD as password from T_S_P_USER
        ]]>
        <dynamic prepend="where">
            <isNotEmpty prepend="AND" property="userId">
                USER_ID=#userId#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="password">
                PASSWORD=#password#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="classIds">
                <iterate property="classIds" open="(" conjunction="OR" close=")">
                    CLASS_ID = #classIds[]#
                </iterate>
            </isNotEmpty>
        </dynamic>
</select>


示例四：Map

map 和 List,array 相比，map 是用 K,V 存储的，在 foreach 中，使用 map 时，index 属性值为 map 中的 Key 的值。

因为 map 中的 Key 不同于 list,array 中的索引，所以会有更丰富的用法。

<insert id="ins_string_string">
        insert into string_string (key, value) values
        <foreach item="item" index="key" collection="map"
            open="" separator="," close="">(#{key}, #{item})</foreach>
</insert>

可以看到这个例子相当简单，表中需要两个值，正好和 K,V 对应，因而 map 中的一个 K,V 就对应一条数据，如果 map 中有多个 K,V，就会保存多个结果。

如果 map 中有两对 K,V，那么执行 SQL 如下：

DEBUG [main] - ==>  Preparing: insert into string_string (key, value) values (?, ?) , (?, ?) 
DEBUG [main] - ==> Parameters: key 1(String), value 1(String), key 2(String), value 2(String)
DEBUG [main] - <==    Updates: 2

 

下面再看一个 select 的例子：

<select id="sel_key_cols" resultType="int">
        select count(*) from key_cols where
        <foreach item="item" index="key" collection="map"
            open="" separator="AND" close="">${key} = #{item}</foreach>
</select>

可以看到这里用 key=value 来作为查询条件，对于动态的查询，这种处理方式可以借鉴。一定要注意到 $ 和 #的区别，$ 的参数直接输出，# 的参数会被替换为?，然后传入参数值执行。

DEBUG [main] - ==>  Preparing: select count(*) from key_cols where col_a = ? AND col_b = ? 
DEBUG [main] - ==> Parameters: 22(Integer), 222(Integer)
DEBUG [main] - <==      Total: 1

最后，如果不考虑元素的顺序和 map 中 Key，map 和 list,array 可以拥有一样的效果，都是存储了多个值，然后循环读取出来。