MyBatis 动态 SQL

MyBatis通过 OGNL 来进行动态 SQL 的使用的。目前， 动态 SQL 支持以下几种标签：

创建数据库和表

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别',
  `locked` tinyint(4) DEFAULT NULL COMMENT '状态(0:正常,1:锁定)',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '存入数据库的时间',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改的时间',
  `delete` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

接口函数

/**
 * 根据输入的学生信息进行条件检索
 * 1. 当只输入用户名时， 使用用户名进行模糊检索；
 * 2. 当只输入邮箱时， 使用性别进行完全匹配
 * 3. 当用户名和性别都存在时， 用这两个条件进行查询匹配的用
 * @param student
 * @return
 */
List<Student> selectByStudentSelective(Student student);
1
2
3
4
5
6
7
8
9

/**
 * 更新非空属性
 */
int updateByPrimaryKeySelective(Student record);
1
2
3
4

/**
 * 非空字段才进行插入
 */
int insertSelective(Student record);
1
2
3
4

/**
 * - 当 studen_id 有值时， 使用 studen_id 进行查询；
 * - 当 studen_id 没有值时， 使用 name 进行查询；
 * - 否则返回空
 */
Student selectByIdOrName(Student record);
1
2
3
4
5
6

/**
 * 根据输入的学生信息进行条件检索
 * 1. 当只输入用户名时， 使用用户名进行模糊检索；
 * 2. 当只输入邮箱时， 使用性别进行完全匹配
 * 3. 当用户名和性别都存在时， 用这两个条件进行查询匹配的用
 */
List<Student> selectByStudentSelectiveWhereTag(Student student);
1
2
3
4
5
6
7

/**
 * 获取 id 集合中的用户信息
 * @param ids
 * @return
 */
List<Student> selectByStudentIdList(List<Integer> ids);
1
2
3
4
5
6

/**
 * 批量插入学生
 */
int insertList(List<Student> students);
1
2
3
4

• if 标签

<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
  select
  <include refid="Base_Column_List" />
  from student
  where 1=1
  <if test="name != null and name !=''">
    and name like concat('%', #{name}, '%')
  if>
  <if test="sex != null">
    and sex=#{sex}
  if>
select>
1
2
3
4
5
6
7
8
9
10
11
12
13

<update id="updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student">
update student
<set>
  <if test="name != null">
    `name` = #{name,jdbcType=VARCHAR},
  if>
  <if test="phone != null">
    phone = #{phone,jdbcType=VARCHAR},
  if>
  <if test="email != null">
    email = #{email,jdbcType=VARCHAR},
  if>
  <if test="sex != null">
    sex = #{sex,jdbcType=TINYINT},
  if>
  <if test="locked != null">
    locked = #{locked,jdbcType=TINYINT},
  if>
  <if test="gmtCreated != null">
    gmt_created = #{gmtCreated,jdbcType=TIMESTAMP},
  if>
  <if test="gmtModified != null">
    gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
  if>
set>
where student_id = #{studentId,jdbcType=INTEGER}
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

<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student">
   insert into student
   <trim prefix="(" suffix=")" suffixOverrides=",">
     <if test="studentId != null">
       student_id,
     if>
     <if test="name != null">
       `name`,
     if>
     <if test="phone != null">
       phone,
     if>
     <if test="email != null">
       email,
     if>
     <if test="sex != null">
       sex,
     if>
     <if test="locked != null">
       locked,
     if>
     <if test="gmtCreated != null">
       gmt_created,
     if>
     <if test="gmtModified != null">
       gmt_modified,
     if>
   trim>
   <trim prefix="values (" suffix=")" suffixOverrides=",">
     <if test="studentId != null">
       #{studentId,jdbcType=INTEGER},
     if>
     <if test="name != null">
       #{name,jdbcType=VARCHAR},
     if>
     <if test="phone != null">
       #{phone,jdbcType=VARCHAR},
     if>
     <if test="email != null">
       #{email,jdbcType=VARCHAR},
     if>
     <if test="sex != null">
       #{sex,jdbcType=TINYINT},
     if>
     <if test="locked != null">
       #{locked,jdbcType=TINYINT},
     if>
     <if test="gmtCreated != null">
       #{gmtCreated,jdbcType=TIMESTAMP},
     if>
     <if test="gmtModified != null">
       #{gmtModified,jdbcType=TIMESTAMP},
     if>
   trim>
 insert>
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

• choose 标签

 <select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    <include refid="Base_Column_List" />
    from student
    where 1=1
    <choose>
      <when test="studentId != null">
        and student_id=#{studentId}
      when>
      <when test="name != null and name != ''">
        and name=#{name}
      when>
      <otherwise>
        and 1=2
      otherwise>
    choose>
  select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• trim(set、where)

<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
   select
   <include refid="Base_Column_List" />
   from student
  <where>
   <if test="name != null and name !=''">
     and name like concat('%', #{name}, '%')
   if>
   <if test="sex != null">
     and sex=#{sex}
   if>
  where>
 select>
1
2
3
4
5
6
7
8
9
10
11
12
13

<trim prefix="where" prefixOverrides="AND |OR">
trim>
1
2
3

<trim prefix="SET" suffixOverrides=",">
trim>
1
2
3

• prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀
• prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀
• suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀
• suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀

• foreach 标签

• collection: 必填， 集合/数组/Map的名称.
• item: 变量名。即从迭代的对象中取出的每一个值
• index: 索引的属性名。当迭代的对象为 Map 时， 该值为 Map 中的 Key.
• open: 循环开头的字符串
• close: 循环结束的字符串
• separator: 每次循环的分隔符

多参数请使用 @Param 来指定参数的名称， 如果参数是Map指定为 Map 中的对应的 Key 即可。其实上面的 @Param 最后也是转化为 Map 的。

<select id="selectByStudentIdList" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from student
  where student_id in
  <foreach collection="list" item="id" open="(" close=")" separator="," index="i">
    #{id}
  foreach>
select>
1
2
3
4
5
6
7
8
9
10

<insert id="insertList">
  insert into student(name, phone, email, sex, locked)
  values
  <foreach collection="list" item="student" separator=",">
    (
    #{student.name}, #{student.phone},#{student.email},
    #{student.sex},#{student.locked}
    )
  foreach>
insert>
1
2
3
4
5
6
7
8
9
10
11

• bind 标签
  bind 标签是通过 OGNL 表达式去定义一个上下文的变量， 这样方便我们使用。

<if test="name != null and name !=''">
  and name like concat('%', #{name}, '%')
if>
1
2
3

在 MySQL 中， 该函数支持多参数， 但在 Oracle 中只支持两个参数。那么我们可以使用 bind 来让该 SQL 达到支持两个数据库的作用

<if test="name != null and name !=''">
     <bind name="nameLike" value="'%'+name+'%'"/>
     and name like #{nameLike}
if>
1
2
3
4

测试

@Test
public void selectByStudent() {
   SqlSession sqlSession = null;
   sqlSession = sqlSessionFactory.openSession();
   StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

   Student search = new Student();
   search.setName("明");

   System.out.println("只有名字时的查询");
   List<Student> studentsByName = studentMapper.selectByStudentSelective(search);
   for (int i = 0; i < studentsByName.size(); i++) {
       System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
   }

   search.setName(null);
   search.setSex((byte) 1);
   System.out.println("只有性别时的查询");
   List<Student> studentsBySex = studentMapper.selectByStudentSelective(search);
   for (int i = 0; i < studentsBySex.size(); i++) {
       System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
   }

   System.out.println("姓名和性别同时存在的查询");
   search.setName("明");
   List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelective(search);
   for (int i = 0; i < studentsByNameAndSex.size(); i++) {
       System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
   }

   sqlSession.commit();
   sqlSession.close();
}
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

@Test
public void updateByStudentSelective() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    Student student = new Student();
    student.setStudentId(1);
    student.setName("明明");
    student.setPhone("13838438888");
    System.out.println(studentMapper.updateByPrimaryKeySelective(student));

    sqlSession.commit();
    sqlSession.close();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

@Test
public void insertByStudentSelective() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    Student student = new Student();
    student.setName("小飞机");
    student.setPhone("13838438899");
    student.setEmail("xiaofeiji@qq.com");
    student.setLocked((byte) 0);

    System.out.println(studentMapper.insertSelective(student));

    sqlSession.commit();
    sqlSession.close();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@Test
public void selectByIdOrName() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    Student student = new Student();
    student.setName("小飞机");
    student.setStudentId(1);

    Student studentById = studentMapper.selectByIdOrName(student);
    System.out.println("有 ID 则根据 ID 获取");
    System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE));

    student.setStudentId(null);
    Student studentByName = studentMapper.selectByIdOrName(student);
    System.out.println("没有 ID 则根据 name 获取");
    System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE));

    student.setName(null);
    Student studentNull = studentMapper.selectByIdOrName(student);
    System.out.println("没有 ID 和 name, 返回 null");
    Assert.assertNull(studentNull);

    sqlSession.commit();
    sqlSession.close();
}
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

@Test
public void selectByStudentWhereTag() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    Student search = new Student();
    search.setName("明");

    System.out.println("只有名字时的查询");
    List<Student> studentsByName = studentMapper.selectByStudentSelectiveWhereTag(search);
    for (int i = 0; i < studentsByName.size(); i++) {
        System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
    }

    search.setSex((byte) 1);
    System.out.println("姓名和性别同时存在的查询");
    List<Student> studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search);
    for (int i = 0; i < studentsBySex.size(); i++) {
        System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
    }

    System.out.println("姓名和性别都不存在时查询");
    search.setName(null);
    search.setSex(null);
    List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelectiveWhereTag(search);
    for (int i = 0; i < studentsByNameAndSex.size(); i++) {
        System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
    }

    sqlSession.commit();
    sqlSession.close();
}
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

@Test
public void selectByStudentIdList() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    List<Integer> ids = new LinkedList<>();
    ids.add(1);
    ids.add(3);

    List<Student> students = studentMapper.selectByStudentIdList(ids);
    for (int i = 0; i < students.size(); i++) {
        System.out.println(ToStringBuilder.reflectionToString(students.get(i), ToStringStyle.MULTI_LINE_STYLE));
    }

    sqlSession.commit();
    sqlSession.close();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

@Test
public void insertList() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> students = new LinkedList<>();
    Student stu1 = new Student();
    stu1.setName("批量01");
    stu1.setPhone("13888888881");
    stu1.setLocked((byte) 0);
    stu1.setEmail("13888888881@138.com");
    stu1.setSex((byte) 1);
    students.add(stu1);

    Student stu2 = new Student();
    stu2.setName("批量02");
    stu2.setPhone("13888888882");
    stu2.setLocked((byte) 0);
    stu2.setEmail("13888888882@138.com");
    stu2.setSex((byte) 0);
    students.add(stu2);

    System.out.println(studentMapper.insertList(students));
    sqlSession.commit();
    sqlSession.close();
}
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