Implementation of Mybatis Writing in Query with Annotations
- 2021-10-25 06:47:28
- OfStack
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.