[framework] the parameters passed in by Mybatis are List objects

Posted by SpectralDesign.Net on Mon, 24 Jan 2022 22:48:21 +0100

SSM framework is a necessary framework for Java Web. Although it is a basic operation of adding, deleting, modifying and querying, it will still be at a loss when facing some special situations. This article is used to record the application of Mybatis framework in a special scenario

1. The passed in parameter is a List object

1. Scene reproduction

First, there is the following table

MySQL [test]> select * from t_entry_resource;
+----+-------------+------+----------+--------+--------+---------------------+
| id | resource_id | type | title    | banner | icon  | add_date            |
+----+-------------+------+----------+--------+--------+---------------------+
| 11 |          6  | 14   | classification     | 1.jpg  | 2.jpg  | 2017-11-17 11:22:30 |
| 12 |          3  | 1    | Test 12   | 3.jpg  | 4.jpg  | 2017-11-17 11:22:30 |
| 13 |        653  | 1    | Test 34   | 5.jpg  | 6.jpg  | 2017-11-20 02:32:26 |
| 14 |          1  | 1    | Test 5    | 7.jpg  | 8.jpg  | 2017-11-20 02:32:51 |
| 15 |        3942 | 3    | Test 6    | 9.jpg  | 10.jpg | 2017-11-20 02:34:27 |
+----+-------------+------+----------+--------+--------+---------------------+
5 rows in set (0.01 sec)

If you want to according to resource_id and type to batch query records, how to write Mybaits statements?

2. Solutions

The solutions for direct posting are as follows:

Dao Layer interface:

List<EntryResource> findByRidAndType(List<EntryResource> entryResources);

XML sentence:

<select id="findByRidAndType" resultMap="entryResource" parameterType="list">
        SELECT
        *
        FROM
        t_entry_resource a
        WHERE
<foreach collection="list" index="index" item="entryResources" open="(" close=")" separator="or">
            ( `type`=#{entryResources.type} and resource_id=#{entryResources.resourceId} )
</foreach>

</select>

This statement utilizes the foreach of mybatis to dynamically splice SQL.

3.foreach attribute

attribute	describe
item	Concrete objects in the loop body. Support point path access of attributes, such as item age,item. info. details. Specific description: in the list and array, it is the object, and in the map, it is value. This parameter is required.
collection	When the object to be foreach is used as an input parameter, list <? > By default, list is used as the key for the object, array is used as the key for the array object, and map is used as the key for the map object. Of course, you can use @ Param("keyName") as an input parameter to set the key. After setting the keyName, the list, array and map will become invalid. In addition to entering parameters, there is also a time when a field is used as a parameter object. For example: if the User has the attribute List ids. If the input parameter is a User object, then this collection = "ids" if the User has the attribute Ids ids; Where Ids is an object and Ids has an attribute List id; The input parameter is a User object, so collection = "ids.id" is just an example. 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 automatically separate elements with "," "to avoid sql errors caused by manually entering commas, such as in(1,2,). This parameter is optional.
open	The starting symbol of foreach code is generally used with (and close = ")". It is often used when in(),values(). This parameter is optional.
close	The closing symbol of foreach code is generally used with) and open = "(". It is often used when in(),values(). This parameter is optional.
index	In list and array, index is the sequence number of the element. In map, index is the key of the element. This parameter is optional.

4. Several usages of foreach

1)select count(*) from users id in (x1,x2,x3,...)

<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>

2)select count(*) from key_cols where col_a = ? AND col_b = ?

<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>

3)select * from t_news n where n.tags like ? or n.tags like ?

<select id="selectTestForEach" parameterType="News" resultMap="NewsResultMapper">
  select * from t_news n where 
  <foreach collection="listTag" index="index" item="tag" open="" separator="or" close="">
            n.tags like  '%'||#{tag}||'%'
  </foreach>
<select>

Reproduced in The MyBatis pass in parameter is a List object

Topics: Mybatis

Programmer Think