Implementation of Mybatis Writing in Query with Annotations

Mybatis annotation writes in query

@Select("<script>"
        + "SELECT * FROM table WHERE OrderNo IN "
        + "<foreach item='item' index='index' collection='list'      open='(' separator=',' close=')'>"
        + "#{item}"
        + "</foreach>"
        + "</script>")
List<Map<String,Object>> selectdemo(@Param("list") List<String> list);

Here, the collection value in foreach is written as @ Param value.

IN statement query in Mybatis, foreach usage in Mybatis

1 Requirements

Query the data of user ID 101, 102, 103, and the parameter is 1 set

2 In the SQL statement

select * from t_user where user_id in ( '101' , '102' ,'103')

3 In Mybatis

You just need to

<select id="selectUserByIdList" resultMap="usesInfo">
 SELECT
 *
 from t_user
 WHERE id IN
 <foreach collection="idList" item="id" index="index" open="(" close=")" separator=",">
   #{id}
 </foreach>
</select>

The interface in the corresponding Mapper is as follows

List<UserInfo> selectUserByIdList(@Param("idList")List idList)

4 chat 1 time

The attributes of foreach elements mainly include collection, item, index, open, separator and close.

4.1 collection

Used to mark the object that will do foreach as a parameter

The List object defaults to "list" instead of the key

The array object has "array" instead as the key,

The Map object does not have a default key.

You can use @ Param ("keyName") to set the key when entering the parameter. After setting keyName, the default list and array will be invalid.

The following query can also be written as follows

List<UserInfo> selectUserByIdList(List idList)

<select id="selectUserByIdList" resultMap="usesInfo">
 SELECT
 *
 from t_user
 WHERE id IN
 <foreach collection="list" item="id" index="index" open="(" close=")" separator=",">
   #{id}
 </foreach>
</select>

If it is an array type

List<UserInfo> selectUserByIdList(Long[] idList)

<select id="selectUserByIdList" resultMap="usesInfo">
 SELECT
 *
 from t_user
 WHERE id IN
 <foreach collection="array" item="id" index="index" open="(" close=")" separator=",">
   #{id}
 </foreach>
</select>

4.2

item The alias of the element in the collection when it iterates, which is required.

index In list and arrays, index is the ordinal number of the element, and in map, index is the key of the element, which is optional

open Beginning symbol of an foreach code, 1 is (and close = ")". Commonly used in in () and values (). This parameter is optional

separator : Separators between elements, such as separator= "," in in (), will automatically be separated by "," in the middle of elements to avoid sql errors caused by manually entering commas, such as in (1, 2,). This parameter is optional.

close : The closing symbol of foreach code, 1 is) and open = "(" are used together. Commonly used in in (), values (). This parameter is optional.