腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

Mybatis foreach用法解析--對(duì)于list和array

更新時(shí)間：2022年03月23日 09:38:50 作者：二十六畫(huà)生的博客

這篇文章主要介紹了Mybatis foreach用法解析--對(duì)于list和array，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

foreach用法--對(duì)于list和array

foreach的主要用在構(gòu)建in條件中，它可以在SQL語(yǔ)句中進(jìn)行迭代一個(gè)集合。

foreach元素的屬性主要有 item，index，collection，open，separator，close。

item表示集合中每一個(gè)元素進(jìn)行迭代時(shí)的別名，
index指定一個(gè)名字，用于表示在迭代過(guò)程中，每次迭代到的位置，
open表示該語(yǔ)句以什么開(kāi)始，
separator表示在每次進(jìn)行迭代之間以什么符號(hào)作為分隔符，
close表示以什么結(jié)束。

在使用foreach的時(shí)候最關(guān)鍵的也是最容易出錯(cuò)的就是collection屬性，該屬性是必須指定的，但是在不同情況下，該屬性的值是不一樣的，主要有一下3種情況：

1.如果傳入的是單參數(shù)且參數(shù)類(lèi)型是一個(gè)List的時(shí)候，collection屬性值為list

2.如果傳入的是單參數(shù)且參數(shù)類(lèi)型是一個(gè)array數(shù)組的時(shí)候，collection的屬性值為array

3.如果使用Map封裝了，collection的屬性值為對(duì)應(yīng)的Key

4.parameterType為list，java.util.List，java.util.ArrayList均可以，切記不可以為Array.

建表語(yǔ)句和表的數(shù)據(jù)，以及范例如下

第1種

DAO:

public List<User> findByIdList(List<String> idList);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="list"> ?<!-- parameterType="java.util.List" 也正確 -->
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="list" item="id" open="(" separator="," close=")">
?? ??? ?#{id}
?? ?</foreach>
</select>

2018-02-04 14:44:18,015 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (66277ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE id in ( ? , ? , ? )
2018-02-04 14:44:18,015 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (66277ms)] - ==>
Parameters: 1(String), 2(String), 3(String)
2018-02-04 14:44:18,062 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (66324ms)] - <==
Total: 3

第2種

DAO:

public List<User> findByIdList(List<String> idList);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="list"> ?<!-- parameterType="java.util.List" 也正確 -->
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="list" item="item123" open="(" separator="," close=")">
?? ??? ?#{item123}
?? ?</foreach>
</select>

2018-02-04 14:48:25,654 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (16876ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE id in ( ? , ? , ? )
2018-02-04 14:48:25,654 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (16876ms)] - ==>
Parameters: 1(String), 2(String), 3(String)
2018-02-04 14:48:25,654 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (16876ms)] - <==
Total: 3

第3種

DAO:

public List<User> findByIdList(List<String> idList);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="java.util.List">
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="List" item="item123" open="(" separator="," close=")"> ? <!-- 這里collection="List"報(bào)錯(cuò) -->
?? ??? ?#{item123}
?? ?</foreach>
</select>

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException:
Parameter 'List' not found. Available parameters are [list]

第4種

SERVICE:

public List<User> findByIdList(List<String> idList) {
?? ?Map<String, Object> map = new HashMap<String, Object>();
?? ?map.put("List123", idList);
?? ?return userDao.findByIdList(map);
}

DAO:

public List<User> findByIdList(Map<String, Object> map);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="java.util.List">
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="List123" item="item123" open="(" separator="," close=")"> ? <!-- 使用Map封裝，key是List123 -->
?? ??? ?#{item123}
?? ?</foreach>
</select>

2018-02-04 15:10:46,794 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (14878ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE id in ( ? , ? , ? )
2018-02-04 15:10:46,794 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (14878ms)] - ==>
Parameters: 1(String), 2(String), 3(String)
2018-02-04 15:10:46,809 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (14893ms)] - <==
Total: 3

第5種

SERVICE:

public List<User> findByIdList(String[] idArray) {
?? ?Map<String, Object> map = new HashMap<String, Object>();
?? ?map.put("Array123", idArray);
?? ?return userDao.findByIdList(map);
}

DAO:

public List<User> findByIdList(Map<String, Object> map);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="array"> ?<!-- 這里使用array會(huì)報(bào)錯(cuò) -->
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="Array123" item="item123" open="(" separator="," close=")">
?? ??? ?#{item123}
?? ?</foreach>
</select>

Caused by: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML.
Cause: org.apache.ibatis.builder.BuilderException: Error resolving class.
Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'array'.
Cause: java.lang.ClassNotFoundException: Cannot find class: array

第6種

SERVICE:

public List<User> findByIdList(String[] idArray) {
?? ?Map<String, Object> map = new HashMap<String, Object>();
?? ?map.put("Array123", idArray);
?? ?return userDao.findByIdList(map);
}

DAO:

public List<User> findByIdList(Map<String, Object> map);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="list"> ? <!-- parameterType="java.util.List" 或者 parameterType="java.util.ArrayList" 均正確 -->?
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="Array123" item="item123" open="(" separator="," close=")">
?? ??? ?#{item123}
?? ?</foreach>
</select>

2018-02-04 15:23:07,928 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (43730ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE id in ( ? , ? , ? )
2018-02-04 15:23:07,928 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (43730ms)] - ==>
Parameters: 1(String), 2(String), 3(String)
2018-02-04 15:23:07,928 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (43730ms)] - <==
Total: 3

第7種

DAO:

public List<User> findByIdList(String[] idArray);

SQL:

<select id="findByIdList" resultMap="resultMap" parameterType="java.util.ArrayList">
?? ?SELECT
?? ?<include refid="columnSql"></include>
?? ?FROM t_user
?? ?WHERE id in
?? ?<foreach collection="array" item="item123" open="(" separator="," close=")">
?? ??? ?#{item123}
?? ?</foreach>
</select>

2018-02-04 15:32:44,682 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (21575ms)] - ==>
Preparing: SELECT id, username, password, sex FROM t_user WHERE id in ( ? , ? , ? )
2018-02-04 15:32:44,682 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (21575ms)] - ==>
Parameters: 1(String), 2(String), 3(String)
2018-02-04 15:32:44,682 -[DEBUG] method:[com.springmvc.dao.UserDao.findByIdList (21575ms)] - <==
Total: 3

    <sql id="columnSql">
        <trim suffixOverrides=","> <!-- suffixOverrides此時(shí)的作用是去除最后一個(gè)逗號(hào) -->
            id,
            username,
            password,
            sex,
        </trim>
    </sql>

以上基于JDK1.8。日志為親測(cè)。僅為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: