阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

深入理解MyBatis中的动态SQL语句

261次阅读
没有评论

共计 6650 个字符,预计需要花费 17 分钟才能阅读完成。

有时候,静态的 SQL 语句并不能满足应用程序的需求。我们可以根据一些条件,来动态地构建 SQL 语句。

例如,在 Web 应用程序中,有可能有一些搜索界面,需要输入一个或多个选项,然后根据这些已选择的条件去执行检索操作。我们可能需要根据用户选择的条件来构建动态的 SQL 语句。如果用户提供了任何一个条件,我们需要将那个条件添加到 SQL 语句的 WHERE 子句中。

!以下内容基于自己建的表和类!

1.<if> 标签被用来通过条件嵌入 SQL 片段,如果条件为 true,则相应地 SQL 片段将会被添加到 SQL 语句中。
 例如:
 假定有一个课程搜索界面,设置了讲师(Tutor)下拉列表框,课程名称(CourseName)文本输入框,开始时间(StartDate)输入框,结束时间(EndDate)输入框,作为搜索条件。假定课讲师下拉列表是必须选的,其他的都是可选的。当用户点击搜索按钮时,需要显示符合条件的列表数据。

对应的 sql 映射文件,如下所示:

<!– 独立的 Course 封装映射 –>
<resultMap type=”Course” id=”CourseResult”>
<id column=”course_id” property=”courseId” />
<result column=”name” property=”name” />
<result column=”description” property=”description” />
<result column=”start_date” property=”startDate” />
<result column=”end_date” property=”endDate” />
</resultMap>

 

<!– 查询 Course 的 select 语句,里面加入了 if 条件判断 –>
<select id=”searchCourses” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
WHERE TUTOR_ID= #{tutorId}
<if test=”courseName != null”>
AND NAME LIKE #{courseName}
</if>
<if test=”startDate != null”>
AND START_DATE >= #{startDate}
</if>
<if test=”endDate != null”>
AND END_DATE <![CDATA[ <=]]> #{endDate}
</if>
</select>

映射接口:

public interface DynamicSqlMapper{
List<Course> searchCourses(Map<String, Object> map);
}

测试方法:

@Test
public void test_searchCourses1(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Map<String,Object> map = new HashMap<String,Object>();
map.put(“tutorId”, 1);
map.put(“courseName”, “%Java%”);

LocalDate date = LocalDate.of(2019, 1, 10);
map.put(“startDate”, date);

List<Course> courses = mapper.searchCourses(map);

courses.forEach(System.out::println);

} catch (Exception e) {
e.printStackTrace();
}
}

2.choose,when 和 otherwise 条件
 有时候,查询功能是以查询类别为基础的。首先,用户需要先选择是通过讲师查询,还是课程名称查询,还是开始时间查询。然后根据选择的查询类别,输入相应的参数,再进行查询。

例如,页面中有一个下拉列表,可以选择查询的类别,可以选择根据讲师查询、根据课程名查询、根据时间查询等等,选择了列表之后,再输入关键字进行查询。

MyBatis 提供了 <choose> 标签可以支持此类型的查询处理。假设如果用户都没有选择,那么默认可以根据当前时间进行查询。

注意:mysql 中 now()表示当前时间 Oracle 需要使用 sysdate

对应的 sql 映射文件,如下所示:

<select id=”searchCourses” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
<choose>
<when test=”searchBy == ‘Tutor'”>
WHERE TUTOR_ID = #{tutorId}
</when>
<when test=”searchBy == ‘CourseName'”>
WHERE name like #{courseName}
</when>
<otherwise>
WHERE start_date >= sysdate
</otherwise>
</choose>
</select>

测试方法:

@Test
public void test_searchCourses2(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Map<String,Object> map = new HashMap<String,Object>();
//    map.put(“searchBy”, “Tutor”);
//    map.put(“tutorId”, 1);
map.put(“searchBy”, “CourseName”);
map.put(“courseName”, “%MyBatis%”);

List<Course> courses = mapper.searchCourses(map);

courses.forEach(System.out::println);

} catch (Exception e) {
e.printStackTrace();
}
}

MyBatis 计算 <choose> 中条件的值,并使用第一个值为 TRUE 的子句。如果没有条件为 true,则使用 <otherwise> 内的子句。

3.Where 条件
 有时候,所有的查询条件应该是可选的。在需要使用至少一种查询条件的情况下,可以直接使用 WHERE 子句。
如果有多个条件,我们需要在条件中添加 AND 或 OR。MyBatis 提供了 <where> 元素支持这种类型的动态 SQL 语句。

例如,在查询课程界面,假设所有的查询条件是可选的。

注意,<where> 元素只有在其内部标签有返回内容时才会在动态语句上插入 WHERE 条件语句。
 并且,如果 WHERE 子句以 AND 或者 OR 打头,则打头的 AND 或 OR 将会被移除。

映射文件:

<select id=”searchCourses” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
<where>
<if test=”tutorId != null “>
TUTOR_ID= #{tutorId}
</if>
<if test=”courseName != null”>
AND name like #{courseName}
</if>
<if test=”startDate != null”>
AND start_date >= #{startDate}
</if>
</where>
</select>

测试方法:

@Test
public void test_searchCourses3(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Map<String,Object> map = new HashMap<String,Object>();
//map.put(“tutorId”, 1);
//map.put(“courseName”, “JavaSE”);
//map.put(“startDate”, LocalDate.of(2019, 1, 10));

List<Course> courses = mapper.searchCourses(map);

courses.forEach(System.out::println);

} catch (Exception e) {
e.printStackTrace();
}
}

4.<trim> 条件
<trim> 元素和 <where> 元素类似,但是 <trim> 提供了添加 前缀 / 后缀 或者 移除 前缀 / 后缀 的功能。

映射文件:

<select id=”searchCourses” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
<trim prefix=”WHERE” suffixOverrides=”and”>
<if test=” tutorId != null “>
TUTOR_ID = #{tutorId} and
</if>
<if test=”courseName != null”>
name like #{courseName} and
</if>
</trim>
</select>

prefix 表示有一个 if 成立则插入 where 语句,没有 if 成立,就会去掉 where 直接查询
suffix 表示后缀, 和 prefix 相反

suffixOverrides=”and” 表示如果最后生成的 sql 语句多一个 and, 则自动去掉.
prefixOverrides 的意思是处理前缀, 和 suffixOverrides 相反

测试方法:

@Test
public void test_searchCourses4(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Map<String,Object> map = new HashMap<String,Object>();
//    map.put(“tutorId”, 1);
//    map.put(“courseName”, “JavaSE”);

List<Course> courses = mapper.searchCourses(map);

courses.forEach(System.out::println);

} catch (Exception e) {
e.printStackTrace();
}
}

5.foreach 循环
 另外一个强大的动态 SQL 语句构造标签是 <foreach>。它可以迭代遍历一个数组或者列表,构造 AND/OR 条件或一个 IN 子句。

假设查询 tutor_id 为 1,3,6 的讲师所教授的课程,我们可以传递一个 tutor_id 组成的列表给映射语句,然后通过 <foreach> 遍历此列表构造动态 SQL。

映射文件:

<select id=”searchCoursesByTutors” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
<if test=”tutorIds != null”>
<where>
<!– 在这里的 tutorId 指的是集合中存入准备查询的 tutor_id–>
<foreach item=”tutorId” collection=”tutorIds”>
OR tutor_id = #{tutorId}
</foreach>
</where>
</if>
</select>

映射接口:

public interface DynamicSqlMapper{
List<Course> searchCoursesByTutors(Map<String,Object> map);
}

测试方法:

@Test
public void test_searchCoursesByTutors(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Map<String,Object> map = new HashMap<String,Object>();

List<Integer> tutorIds = new ArrayList<Integer>();
tutorIds.add(1);
tutorIds.add(3);
tutorIds.add(6);

map.put(“tutorIds”, tutorIds);

List<Course> courses = mapper.searchCoursesByTutors(map);

courses.forEach(System.out::println);

} catch (Exception e) {
e.printStackTrace();
}
}

和上面同样的功能,使用 <foreach> 生成 IN 子句:

<select id=”searchCoursesByTutors” parameterType=”map” resultMap=”CourseResult”>
SELECT * FROM COURSES
<if test=”tutorIds != null”>
<where>
tutor_id IN
<foreach item=”tempValue” collection=”tutorIds” open=”(” separator=”,” close=”)”>
#{tempValue}
</foreach>
</where>
</if>
</select>

测试方法保持不变。

6.set 条件,专用于 UPDATE 更新操作

<set> 元素和 <where> 元素类似,但是 set 元素只是针对 update 更新语句使用的。

<update id=”updateStudent” parameterType=”Student”>
update students
<set>
<if test=”name != null”>name=#{name},</if>
<if test=”email != null”>email=#{email},</if>
<if test=”phone != null”>phone=#{phone},</if>
</set>
where stud_id=#{studId}
</update>

这里,如果 <if> 条件返回了任何文本内容,<set> 将会插入 set 关键字和其文本内容,并且会剔除将末尾的逗号 ”,”。

测试方法:

@Test
public void test_updateStudent(){

SqlSession sqlSession = null;
try {
sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Student student = new Student();
student.setStudId(45);
student.setEmail(“xx@linuxidc.com”);

mapper.updateStudent(student);

sqlSession.commit();

} catch (Exception e) {
e.printStackTrace();
}
}

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计6650字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中