本文深入探讨了动态SQL的概念及其在MyBatis中的应用，包括<if>、<where>、<choose>、<foreach>等标签的使用方法，以及如何提高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 > #{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();
}
}
}
扫一扫
463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
