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. 当用户名和性别都存在时， 用这两个条件进行查询匹配的用* @param student* @return*/
List selectByStudentSelective(Student student);

/*** 更新非空属性*/
int updateByPrimaryKeySelective(Student record);

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

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

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

/*** 获取 id 集合中的用户信息* @param ids* @return*/
List selectByStudentIdList(List ids);

/*** 批量插入学生*/
int insertList(List students);

• if 标签

selectfrom studentwhere 1=1and name like concat('%', #{name}, '%')and sex=#{sex}

update student
`name` = #{name,jdbcType=VARCHAR},phone = #{phone,jdbcType=VARCHAR},email = #{email,jdbcType=VARCHAR},sex = #{sex,jdbcType=TINYINT},locked = #{locked,jdbcType=TINYINT},gmt_created = #{gmtCreated,jdbcType=TIMESTAMP},gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},

where student_id = #{studentId,jdbcType=INTEGER}

insert into studentstudent_id,`name`,phone,email,sex,locked,gmt_created,gmt_modified,#{studentId,jdbcType=INTEGER},#{name,jdbcType=VARCHAR},#{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{sex,jdbcType=TINYINT},#{locked,jdbcType=TINYINT},#{gmtCreated,jdbcType=TIMESTAMP},#{gmtModified,jdbcType=TIMESTAMP},

• choose 标签

 selectfrom studentwhere 1=1and student_id=#{studentId}and name=#{name}and 1=2

• trim(set、where)

selectfrom studentand name like concat('%', #{name}, '%')and sex=#{sex}

• 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 的。

selectfrom studentwhere student_id in#{id}

insert into student(name, phone, email, sex, locked)values(#{student.name}, #{student.phone},#{student.email},#{student.sex},#{student.locked})

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

and name like concat('%', #{name}, '%')

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

and name like #{nameLike}

测试

@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 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 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 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();
}

@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();
}

@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();
}

@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();
}

@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 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 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 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();
}

@Test
public void selectByStudentIdList() {SqlSession sqlSession = null;sqlSession = sqlSessionFactory.openSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List ids = new LinkedList<>();ids.add(1);ids.add(3);List 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();
}

@Test
public void insertList() {SqlSession sqlSession = null;sqlSession = sqlSessionFactory.openSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List 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();
}