目录
1、使用背景
有部分查询,需要使用如Id字段已经选择不显示,此时可能使用到Not In操作SQL,如果直接使用 SQL in 字符串 2,3如下所示:
SELECT
t.id,
t.belong_type as belongType,
t.enclosure_name as enclosureName,
t.course_first_category as courseFirstCategory,
t.enclosure_size as enclosureSize,
t.enclosure_format as enclosureFormat,
t.sort,
t.baidu_cloud as baiduCloud,
t.baidu_cloud_thumbnail as baiduCloudThumbnail,
t.enclosure_state as enclosureState,
t.enclosure_length as enclosureLength,
t.multimedia_millisecond as multimediaMillisecond
FROM
course_enclosure AS t
WHERE t.enclosure_format = '05'
AND t.enclosure_state='1' AND t.course_first_category is null
<if test="selectedVideoIds != null">
AND t.id NOT IN #{selectedVideoIds}
</if>
但是此种方式执行无法执行过滤的。样的写法,看似很简单,但是MyBatis不支持。。但是MyBatis中提供了foreach语句实现IN查询,foreach语法如下:
foreach语句中, collection属性的参数类型可以使:List、数组、map集合
collection: 必须跟mapper.java中@Param标签指定的元素名一样
item: 表示在迭代过程中每一个元素的别名,可以随便起名,但是必须跟元素中的#{}里面的名称一样。
index:表示在迭代过程中每次迭代到的位置(下标)
open:前缀, sql语句中集合都必须用小括号()括起来
close:后缀
separator:分隔符,表示迭代时每个元素之间以什么分隔
2、Mybatis后端Xml实现方式和接口函数注意事项
正确的写法有以下两类写法:
2.1、接口仅仅只有一个参数情况
如果参数的类型是List, 则在使用时,collection属性要必须指定为 list
List<Map<String, Object>> searchTrainingProjectNeedVideosByName(List idList);
<select id="searchTrainingProjectNeedVideosByName" resultType="hashmap">
SELECT
t.id,
t.belong_type as belongType,
t.enclosure_name as enclosureName,
t.course_first_category as courseFirstCategory,
t.enclosure_size as enclosureSize,
t.enclosure_format as enclosureFormat,
t.sort,
t.baidu_cloud as baiduCloud,
t.baidu_cloud_thumbnail as baiduCloudThumbnail,
t.enclosure_state as enclosureState,
t.enclosure_length as enclosureLength,
t.multimedia_millisecond as multimediaMillisecond
FROM
course_enclosure AS t
WHERE t.enclosure_format = '05'
AND t.enclosure_state='1' AND t.course_first_category is null
<if test="selectedVideoIds != null">
AND t.id NOT IN
<foreach collection="list" item="id" index="index" open="(" close=")"
separator=",">
#{id}
</foreach>
</if>
</select>
如果参数的类型是Array,则在使用时,collection属性要必须指定为 array
List<Map<String, Object>> searchTrainingProjectNeedVideosByName(String[] selectedVideoIds );
<select id="searchTrainingProjectNeedVideosByName" resultType="hashmap">
SELECT
t.id,
t.belong_type as belongType,
t.enclosure_name as enclosureName,
t.course_first_category as courseFirstCategory,
t.enclosure_size as enclosureSize,
t.enclosure_format as enclosureFormat,
t.sort,
t.baidu_cloud as baiduCloud,
t.baidu_cloud_thumbnail as baiduCloudThumbnail,
t.enclosure_state as enclosureState,
t.enclosure_length as enclosureLength,
t.multimedia_millisecond as multimediaMillisecond
FROM
course_enclosure AS t
WHERE t.enclosure_format = '05'
AND t.enclosure_state='1' AND t.course_first_category is null
<if test="selectedVideoIds != null">
AND t.id NOT IN
<foreach collection="array" item="id" index="index" open="(" close=")"
separator=",">
#{id}
</foreach>
</if>
</select>
2.2、参数有多个时
当查询的参数有多个时,有两种方式可以实现,一种是使用@Param("xxx")进行参数绑定,另一种可以通过Map来传参数。
@Param("xxx")方式
List<Map<String, Object>> searchTrainingProjectNeedVideosByName(@Param("videoName")String videoName, @Param("selectedVideoIds")String[] selectedVideoIds);
<select id="searchTrainingProjectNeedVideosByName" resultType="hashmap">
SELECT
t.id,
t.belong_type as belongType,
t.enclosure_name as enclosureName,
t.course_first_category as courseFirstCategory,
t.enclosure_size as enclosureSize,
t.enclosure_format as enclosureFormat,
t.sort,
t.baidu_cloud as baiduCloud,
t.baidu_cloud_thumbnail as baiduCloudThumbnail,
t.enclosure_state as enclosureState,
t.enclosure_length as enclosureLength,
t.multimedia_millisecond as multimediaMillisecond
FROM
course_enclosure AS t
WHERE t.enclosure_format = '05'
AND t.enclosure_state='1' AND t.course_first_category is null
<if test="videoName != null and videoName != ''">
AND t.enclosure_name LIKE CONCAT('%', #{videoName,jdbcType=VARCHAR},'%')
</if>
<if test="selectedVideoIds != null">
AND t.id NOT IN
<foreach collection="selectedVideoIds" item="id" index="index" open="(" close=")"
separator=",">
#{id}
</foreach>
</if>
</select>
Map方式(推荐及常用方式)
public List<Map<String, Object>> searchTrainingProjectNeedVideosByName(Map<String, Object> paramMap);
<select id="searchTrainingProjectNeedVideosByName" resultType="hashmap" parameterType="java.util.Map">
SELECT
t.id,
t.belong_type as belongType,
t.enclosure_name as enclosureName,
t.course_first_category as courseFirstCategory,
t.enclosure_size as enclosureSize,
t.enclosure_format as enclosureFormat,
t.sort,
t.baidu_cloud as baiduCloud,
t.baidu_cloud_thumbnail as baiduCloudThumbnail,
t.enclosure_state as enclosureState,
t.enclosure_length as enclosureLength,
t.multimedia_millisecond as multimediaMillisecond
FROM
course_enclosure AS t
WHERE t.enclosure_format = '05' AND t.enclosure_state='1' AND t.course_first_category is null
<if test="selectedVideoIds != null">
AND t.id NOT IN
<foreach collection="selectedVideoIds" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<if test="videoName != null and videoName != ''">
AND t.enclosure_name LIKE CONCAT('%', #{videoName,jdbcType=VARCHAR},'%')
</if>
</select>