Usage of @Select and foreach in Mybatis

Mybatis @Select、foreach

foreach attribute

property	describe
item	The specific object in the loop body. Supports point path access to properties, such as,. Specific description: In list and array are the objects in it, and in map is the value. This parameter is required.
collection	To be a foreach object, when used as an entry parameter, the List<?> object is replaced by list as a key by default, the array object is replaced by array as a key, and the Map object is replaced by map as a key. Of course, when using the entry parameter, you can use @Param("keyName") to set the key. After setting the keyName, the list, array, map will be invalid. In addition to the case of parameter entry, there is also a time when a certain field is used as a parameter object. For example: If the User has the attribute List ids. The entry parameter is a User object, then this collection = "ids". If the User has attribute Ids ids; where Ids is an object, and Ids has an attribute List id; the entry parameter is a User object, then the collection = "" is just an example. What the specific collection equals depends on which element you want to loop. This parameter is required.
separator	Separator between elements, for example, when in(), separator="," will be automatically separated by "," in the middle of the element to avoid manually typing commas to cause SQL errors, such as in(1,2,). This parameter is optional.
open	The start symbol of the foreach code is generally used together (and close=")". Commonly used in in() and values(). This parameter is optional.
close	The close symbol of the foreach code is usually used together with open="(". It is commonly used when in() and values(). This parameter is optional.
index	In list and array, index is the element's order number, and in map, index is the element's key, and this parameter is optional.

Mapper layer

List<UserRole> selectIdList(List<Long> userList);

XML statements

<select  parameterType="" resultType="">
        SELECT id,user_id,role_id FROM sys_user_role
        WHERE user_id IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

@Select annotation

Common queries

@Select("select * from web_admins where id = #{id}")
Admin selectAdminById(@Param("id") Integer id);

Spelling dynamic SQL statements in @Select

For situations where conditions are more complicated, this method is not recommended. There will inevitably be many errors in the string and the readability is very poor.

@Select("<script>" +
        "SELECT * FROM users WHERE id in \n" +
        "<foreach collection='usedUserIdList' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach> \n" +
        "AND open_card_order_state = 1" +
        "</script>")
List<Users> selectUsers(@Param("usedUserIdList") List<Integer> usedUserIdList,Page<Users> page);

Mybatis foreach 3 usages

Use mybatis to implement the select field from table where id in(1,2,3,4,5):

Foreach provided by mybatis can be implemented. Foreach has three ways to pass values: array, list, map.

Among them, Array mapper writing

<select  resultMap="BaseResultMap">
    SELECT
    <include ref />
    from user
    where id in
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Note: The value in collection in the writing method of array must be array. Array cannot be written, and an error will be reported. But the name of the parameter may not be an array

List<User>selectUserArr(int []arr);
List<User>userArray=(arr);

How to write list

<select  resultMap="BaseResultMap">
    SELECT
    <include ref />
    from user
    where id in
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Note: The value in the collection in the list writing method must be a list, nor can it be a List, but the parameter naming sent may not be a list

List<User>selectUserList(List list1);
List<User>userList=(list1);

How to write map

<select  resultMap="BaseResultMap">
    SELECT
    <include ref />
    from user
    where id in
    <foreach collection="map1" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Note: The collection in the map writing method is filled in the map key value, and if it does not correspond, an error will be reported.

List<User>selectUserMap(Map map);
("map1",map1);
List<User> userMap=(map);

Testing in springboot's startup class:

@SpringBootApplication
@MapperScan("")
public class Application
{
    public static void main( String[] args )
    {
        ApplicationContext context  = (, args);
       
        UserMapper userMapper=();

        int []arr=new int[5];
        List list1=new ArrayList();
        Map<String,Object> map=new HashMap<String, Object>(5);
        int i;
        for(i=1;i<5;i++){
            arr[i-1]=i;
            (i);

        }
        int []map1={1,2,3,4,5};
        ("map1",map1);
        List<User>userArray=(arr);
        List<User>userList=(list1);
        List<User> userMap=(map);

        ("Array=============");
        sys(userArray);
        ("list=============");
        sys(userList);
        ("map:================");
        sys(userMap);

    public static void sys(List<User> list){
        for (User user : list){
            ("user:"+());
        }
    }
}

The effects are as follows:

Array=============
user:User{id=1, uname='Xiaohei', upwd='123', unum='123', sex=0, age=18, addr='Guangdong', tid=1}
user:User{id=2, uname='Xiaobai', upwd='234', unum='234', sex=1, age=21, addr='Guangzhou', tid=1}
user:User{id=3, uname='Xiaohong', upwd='345', unum='345', sex=0, age=15, addr='Tianhe District', tid=2}
user:User{id=4, uname='Xiaoxiao', upwd='123', unum='567', sex=1, age=21, addr='null', tid=3}
list=============
user:User{id=1, uname='Xiaohei', upwd='123', unum='123', sex=0, age=18, addr='Guangdong', tid=1}
user:User{id=2, uname='Xiaobai', upwd='234', unum='234', sex=1, age=21, addr='Guangzhou', tid=1}
user:User{id=3, uname='Xiaohong', upwd='345', unum='345', sex=0, age=15, addr='Tianhe District', tid=2}
user:User{id=4, uname='Xiaoxiao', upwd='123', unum='567', sex=1, age=21, addr='null', tid=3}
map:================
user:User{id=1, uname='Xiaohei', upwd='123', unum='123', sex=0, age=18, addr='Guangdong', tid=1}
user:User{id=2, uname='Xiaobai', upwd='234', unum='234', sex=1, age=21, addr='Guangzhou', tid=1}
user:User{id=3, uname='Xiaohong', upwd='345', unum='345', sex=0, age=15, addr='Tianhe District', tid=2}
user:User{id=4, uname='Xiaoxiao', upwd='123', unum='567', sex=1, age=21, addr='null', tid=3}
user:User{id=5, uname='Zhecheng', upwd='123', unum='423', sex=0, age=23, addr='null', tid=2}

The above is personal experience. I hope you can give you a reference and I hope you can support me more.