Mybatis查詢條件包含List的情況說明
查詢條件包含List的情況
在mybatis中進行搜索時,有時候參數中包含瞭List,比如傳入參數:
public class FileRequest{ //文件類型 private Integer fileType; //狀態 private List<Status> statusList; } public class Status{ //註冊狀態 private Integer registerStatus; //會議狀態 private Integer meetingStatus }
在mybatis中查詢的語句
<select id="findList" parameterType="FileRequest" resultMap="..."> select * from tableName where 1=1 <if test="fileType != null "> and file_type = #{fileType} </if> <if test="statusList != null "> and <foreach collection="statusList" index="index" item="item" open"(" separator="or" close=")"> <if test="item.registerStatus != null "> and register_status= #{item.registerStatus} </if> <if test="item.meetingStatus != null "> and meeting_status= #{item.meetingStatus } </if> </foreach> </if> </select>
查詢條件帶List和其他類型字段
需求
Mybatis查詢條件帶List和其他類型字段(Integer,String,…).
select * from table where type=? and code in (?,?,?,?)
Mapper.java文件
List<BaseDictionary> selectByTypeAndCodes( @Param("codes") List<Integer> codes, @Param("type") Integer type); Mapper.xml.
註意其中<foreach collection=”codes”中的collection的值要和你定義的List別名@Param(“codes”)一致,
而不是隻有一個list參數時的<foreach collection=”list”
<select id="selectByTypeAndCodes" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from base_dictionary where type = #{type} AND code in <foreach collection="codes" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> AND show_enable=1 AND obj_status=1 ORDER BY sort </select>
執行結果:
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)==> Preparing: select id, type, name, code, sort, show_enable, obj_remark, obj_status, obj_createdate, obj_createuser, obj_modifydate, obj_modifyuser from base_dictionary where type = ? AND code in ( ? , ? , ? ) AND show_enable=1 AND obj_status=1 ORDER BY sort
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)==> Parameters: 34(Integer), 1(Integer), 2(Integer), 3(Integer)
BaseJdbcLogger.debug(BaseJdbcLogger.java:145)<== Total: 2
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- MyBatis傳入參數為List對象的實現
- MyBatis批量插入/修改/刪除MySql數據
- MyBatis中foreach標簽的collection屬性的取值方式
- mybatis update更新字段的使用操作
- 基於mybatis 動態SQL查詢總結