5 mybatis动态SQL

什么是动态SQL

动态sql，主要用于解决查询条件不确定的情况，在程序运行期间，根据用户提交的查询条件进行查询，提交的查询条件不同，执行的sql语句不同，若将每种可能的情况均逐一列出，对所有条件进行排列组合，将会出现大量的sql语句。此时可以使用动态sql来解决这样的问题。

动态sql，通过mybatis提供的各种标签对条件做出判断以实现动态拼接SQL语句。

---

注意事项 在mapper的动态sql中若出现大于号(>)、小于号(<)、大于等于号(>=)、小于等于号(<=)等符号，最好将其转换为实体符号，否则，XML可能会出现解析出错问题

特别对于小于号(<)，在xml中是绝对不能出现的，否则，一定出错

原符号<<=>>=&’"替换符号& lt;& lt;=& gt;& gt;=& amp;& apos;& quot;

<if/>标签

当test的值为true，会将其所包含的sql片断拼接到其所在的sql语句中 当查询条件不确定，查询条件依赖用户提交的内容，此时可使用动态sql语句，根据用户提交内容对将要执行的sql进行拼接。

定义Dao接口 public interface IStudentDao{ List<Student> selectStudentsByIf(Student student); } 定义映射文件 <select id="selectStudentsByIf" resultType="Student"> SELECT id, name, age, score FROM student WHERE 1 = 1 <if test="name != null and name != ''"> AND name LIKE '%' #{name} '%' </if> <if test="age > 0"> AND age &gt; #{age} </if> </select> 测试 @Test public void testSelectStudentsByIf() { SqlSession sqlSession = null; try { Student stu = new Student("张", 25, 0); sqlSession = MyBatisUtils.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> students = mapper.selectStudentsByIf(stu); for (Student student : students) { System.out.println(student); } }finally { if (sqlSession != null) { sqlSession.close(); } } }

---

为了解决if标签中 where 1 = 1 造成系统运行效率下降所以使用下面的标签

<where>标签

dao接口

List<Student> selectStudentsByWhere(Student student);

mapper配置文件

<select id="selectStudentsByWhere" resultType="Student"> SELECT id, name, age, score FROM student <where> <if test="name != null and name != ''"> and name like '%' #{name} '%' </if> <if test="age > 0"> and age > #{age} </if> </where> </select>

---

<choose/>标签

该标签中只可以包含<when/><otherwise/>,可以包含多个<when/>与一个<otherwise/>。他们联合使用，完成java中的开关语句switch…case功能 本例要完成的需求是，若姓名不空，则按照姓名查询；若姓名为空，则按照年龄查询；若没有查询条件，则没有查询结果

List<Student> selectStudentsByChoose(Student student); <select id="selectStudentsByChoose" resultType="Student"> SELECT id, name, age, score FROM student <where> <choose> <when test="name != null and name != ''"> and name like '%' #{name} '%' </when> <when test="age > 0"> and age > #{age} </when> <otherwise> 1 = 2 </otherwise> </choose> </where> </select>

---

<foreach/>标签–遍历数组

<foreach/>标签用户实现对数组与集合的遍历，对其使用，需要注意：

collection表示要遍历的集合类型，这里是数组，即array。open、close、separator为对遍历内容的sql拼接

本例实现的需求是，查询id为1与3的学生信息。 接口

List<Student> selectStudentsByForeach(int[] ids);

mapper配置

<select id="selectStudentsByForeach" resultType="Student"> SELECT id, name, age, score FROM student <if test="array.length > 0"> WHERE id IN <foreach collection="array" item="myid" open="(" close=")" separator=","> #{myid} </foreach> </if> </select>

测试

public void testSelectStudentsByForeach() { int[] ids = {1, 3}; SqlSession sqlSession = null; try { sqlSession = MyBatisUtils.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> students = mapper.selectStudentsByForeach(ids); for (Student student : students) { System.out.println(student); } }finally { if (sqlSession != null) { sqlSession.close(); } } }

<foreach/>标签–遍历List

接口

List<Student> selectStudentsByForeachList(List<Integer> ids);

mapper配置

<select id="selectStudentsByForeachList" resultType="Student"> SELECT id, name, age, score FROM student <if test="list.size > 0"> WHERE id IN <foreach collection="list" item="myid" open="(" close=")" separator=","> #{myid} </foreach> </if> </select>

测试

@Test public void testSelectStudentsByForeachList() { List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(3); ids.add(5); SqlSession sqlSession = null; try { sqlSession = MyBatisUtils.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> students = mapper.selectStudentsByForeachList(ids); for (Student student : students) { System.out.println(student); } }finally { if (sqlSession != null) { sqlSession.close(); } } }

<foreach/>标签–遍历自定义List

接口

List<Student> selectStudentsByForeachCustom(List<Student> ids);

mapper配置

<select id="selectStudentsByForeachCustom" resultType="Student"> SELECT id, name, age, score FROM student <if test="list.size > 0"> WHERE id IN <foreach collection="list" item="stu" open="(" close=")" separator=","> #{stu.id} </foreach> </if> </select>

测试

@Test public void testSelectStudentsByForeachCustom() { Student stu1 = new Student(); stu1.setId(1); Student stu3 = new Student(); stu3.setId(3); List<Student> stus = new ArrayList<>(); stus.add(stu1); stus.add(stu3); SqlSession sqlSession = null; try { sqlSession = MyBatisUtils.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> students = mapper.selectStudentsByForeachCustom(stus); for (Student student : students) { System.out.println(student); } } finally { if (sqlSession != null) { sqlSession.close(); } } }

---

sql片断

便于sql语句修改，但是可读性较差 接口

List<Student> selectStudentsBySqlFragment(List<Student> ids);

mapper

<select id="selectStudentsBySqlFragment" resultType="Student"> SELECT <include refid="selectColumns"/> FROM student <if test="list.size > 0"> WHERE id IN <foreach collection="list" item="stu" open="(" close=")" separator=","> #{stu.id} </foreach> </if> </select>

测试

public void testSelectStudentsBySqlFragment() { Student stu1 = new Student(); stu1.setId(1); Student stu3 = new Student(); stu3.setId(3); List<Student> stus = new ArrayList<>(); stus.add(stu1); stus.add(stu3); SqlSession sqlSession = null; try { sqlSession = MyBatisUtils.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> students = mapper.selectStudentsBySqlFragment(stus); for (Student student : students) { System.out.println(student); } } finally { if (sqlSession != null) { sqlSession.close(); } } }