MyBatis实战-7-XML标签动态 SQL

MyBatis的XML标签

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

代码示例

这里就不做过多讲解啦,需要说的都在代码中进行了注释,希望大家仔细阅读。

TeacherMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.qinjiangbo.dao;

import com.qinjiangbo.pojo.Teacher;

import java.util.List;
import java.util.Map;

/**
* Date: 24/09/2016
* Author: [email protected]
*/
public interface TeacherMapper {

/**
* 调用存储过程
*
* @param map
* @return
*/
public void countTeacherNumber(Map map);

/**
* 根据编号列表查找教师信息
*
* @param ids List
* @return
*/
public List<Teacher> findTeachersByIds(List<Integer> ids);

/**
* 根据编号列表查找教师信息
*
* @param ids Array
* @return
*/
public List<Teacher> findTeachersByIds2(int[] ids);

/**
* 根据名称前缀获取教师信息
*
* @param prefix
* @return
*/
public List<Teacher> findTeacherByNamePrefix(String prefix);

/**
* 更新教师信息
*
* @param teacher
* @return
*/
public int updateTeacherInfo(Teacher teacher);
}

TeacherMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qinjiangbo.dao.TeacherMapper">
<select id="countTeacherNumber" parameterMap="countTeacherNumberMap" statementType="CALLABLE">
CALL mybatis.count_teacher_procedure(?, ?)
</select>

<parameterMap type="java.util.Map" id="countTeacherNumberMap">
<parameter property="t_sex" jdbcType="INTEGER" mode="IN"/>
<parameter property="user_count" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>

<select id="findTeachersByIds" resultMap="teacherInfoMap">
SELECT * FROM teacher WHERE t_id in
<!-- collection这里指定参数的集合类型 list, array等-->
<foreach collection="list" item="ids" index="index" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>

<select id="findTeachersByIds2" resultMap="teacherInfoMap">
SELECT * FROM teacher WHERE t_id in
<!-- collection这里指定参数的集合类型 list, array等-->
<foreach collection="array" item="ids" index="index" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>

<!--
!!注意!!
这个地方特别容易出错!当使用if标签的时候,需要注意这个问题,
就是单个参数进来判断为空,必须使用_parameter来进行判断,否则
会报错。如果是多个参数的话建议使用一个参数对象封装起来,然后直接使用
#{参数名}来判断!切记!
-->
<select id="findTeacherByNamePrefix" parameterType="java.lang.String" resultMap="teacherInfoMap">
SELECT * FROM teacher
<if test="_parameter != null and _parameter != ''">
WHERE t_name LIKE CONCAT(#{prefix}, '%')
</if>
</select>

<update id="updateTeacherInfo" parameterType="com.qinjiangbo.pojo.Teacher">
UPDATE teacher
<set>
<if test="name != null and name != ''">
t_name = #{name}
</if>
<if test="office != null and office != ''">
t_office = #{office}
</if>
<if test="gender != null and gender != ''">
t_gender = #{gender}
</if>
</set>
WHERE t_id = #{id}
</update>

<resultMap id="teacherInfoMap" type="com.qinjiangbo.pojo.Teacher">
<id column="t_id" property="id"/>
<result column="t_name" property="name"/>
<result column="t_office" property="office"/>
<result column="t_gender" property="gender"/>
</resultMap>
</mapper>

测试用例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
package com.qinjiangbo.test;

import com.qinjiangbo.dao.TeacherMapper;
import com.qinjiangbo.pojo.Teacher;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
* Date: 25/09/2016
* Author: [email protected]
*/
public class MyBatisTest6 {

private SqlSession sqlSession = null;

@Before
public void init() {
String config = "com/qinjiangbo/conf/configure.xml";
InputStream inputStream = MyBatisTest2.class.getClassLoader().getResourceAsStream(config);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
}

@Test
public void testFindTeacherByIds() {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1002);
ids.add(1003);
ids.add(1006);
List<Teacher> teachers = teacherMapper.findTeachersByIds(ids);
Iterator<Teacher> iterator = teachers.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}

@Test
public void testFindTeacherByIds2() {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
int[] ids = new int[]{1002, 1004, 1005};
List<Teacher> teachers = teacherMapper.findTeachersByIds2(ids);
Iterator<Teacher> iterator = teachers.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}

@Test
public void testFindTeacherByNamePrefix() {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = teacherMapper.findTeacherByNamePrefix("s");
Iterator<Teacher> iterator = teachers.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}

@Test
public void testUpdateTeacherInfo() {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = new Teacher();
teacher.setId(1003);
teacher.setName("QinJiangbo");
int result = teacherMapper.updateTeacherInfo(teacher);
System.out.println(result);
}

@After
public void commit() {
sqlSession.commit();
}
}

以上代码均通过严格的测试,大家可以根据自己的代码再跑一遍。

分享到