(五) - 动态SQL
使用动态 SQL 可以简化代码的开发, 减少开发者的工作量, 程序可以自动根据业务参数来决定 SQL 的组成.
MyBatis 采用一系列标签来实现动态 SQL:
if, where, choose, when, trim, set, foreach 等.
举例说明:
现在我们想通过对象来查询数据
创建接口文件:
public interface DataRepository { public Student dynamicFind(Student student); }
创建 mapper 文件:
<mapper namespace="com.ryan.repository.DataRepository"> <select id="dynamicFind" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> select * from student where id=#{id} and name=#{name} and phoneNumber=#{phoneNumber}; </select> </mapper>
测试文件:
public class Test { public static void main(String[] args) { InputStream inputStream = DataRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); DataRepository dataRepository = sqlSession.getMapper(DataRepository.class); //创建查询条件对象 Student student = new Student(); student.setId(4399); student.setName("Batman"); student.setPhoneNumber(666333); Student stu = dataRepository.dynamicFind(student); System.out.println(stu); sqlSession.close(); } }
以上是对象的每一个属性都指明了的, 自然可以查出来:
但在实际开发中, 我们可能需要只用其中一个或多个属性去查询(比如用 id 或姓名去查询学生信息), 此时这套 sql 就不能用了 (因为 and 是与关系, 其中一个条件不成立则整个条件不成立):
对每一种查询条件都写一个对应 sql 是不现实的, 因为可能的条件太多了, 我们需要一个动态的 sql 可以适用于这一类查询, 有如下几种解决方法:
1. if 标签
if 标签可以自动根据表达式的结果来决定是否将对应的语句添加到 SQL 中, 如果条件不成立则不添加, 如果条件成立则添加. 修改 sql 如下:
<select id="dynamicFind" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> select * from student where <if test="id != 0"> id=#{id} </if> <if test="name != null"> and name=#{name} </if> <if test="phoneNumber != 0"> and phoneNumber=#{phoneNumber}; </if> </select>
再次运行:
2. where 标签
使用 if 标签很容易遇到一个问题, 即改动条件导致的语法问题, 如下例, 如果我们把第一个条件置为空, 那么就会出问题:
使用 where 标签可以解决这个问题. where 标签可以自动判断是否要删除
语句块中的 and 关键字, 如果检测到 where 直接跟 and 拼接, 则自动删除 and, 通常情况下 if 和 where 结合起来使用. 修改 sql:
<select id="dynamicFind" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> select * from student <where> <if test="id != 0"> id=#{id} </if> <if test="name != null"> and name=#{name} </if> <if test="phoneNumber != 0"> and phoneNumber=#{phoneNumber}; </if> </where> </select>
重新运行:
3. choose, when 标签
作用与 if 类似.
<select id="dynamicFind" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> select * from student <where> <choose> <when test="id != 0"> id = #{id} </when> <when test="name != null"> name = #{name} </when> <when test="phoneNumber != 0"> phoneNumber = #{phoneNumber} </when> </choose> </where> </select>
4. trim 标签
trim 标签中的 prefix 和 suffix 属性会被用于生成实际的 SQL 语句, 回合标签内部的语句进行拼接, 如果语句前后出现了 prefixOverrides 或者 suffixOverrides 属性中指定的值, MyBatis 框架会自动将其删除. 如下 sql 所示, 语句可以正常运行, 因为 where 和 and 联系出现, and 被删除:
<select id="dynamicFind" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> select * from student <trim prefix="where" prefixOverrides="and"> <if test="id!=0"> and id = #{id} </if> <if test="name!=null"> and name = #{name} </if> <if test="phoneNumber!=0"> and phoneNumber = #{phoneNumber} </if> </trim> </select>
运行:
5. set 标签
set 标签用于 update 操作, 会自动根据参数选择生成 SQL 语句.
如下例所示, 当我们修改数据时, 如果我们只需要修改一个属性, 其实底层是把所有属性重新赋值一遍的:
<update id="update" parameterType="com.ryan.javaClass.Student"> update student set id=#{id}, name=#{name}, phoneNumber=#{phoneNumber} where id=#{id}; </update>
public static void main(String[] args) { InputStream inputStream = DataRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); DataRepository dataRepository = sqlSession.getMapper(DataRepository.class); //创建查询条件对象 Student student = new Student(); student.setId(4399); Student stu = dataRepository.dynamicFind(student); System.out.println(stu); student.setName("Spiderman"); System.out.println(dataRepository.update(student)); sqlSession.close(); }
运行:
这样会导致资源的浪费, 为了解决这个问题, 我们需要用到 set 标签, 修改 sql 如下:
<update id="update" parameterType="com.ryan.javaClass.Student"> update student <set> <if test="id!=0"> id=#{id}, </if> <if test="name!=null"> name=#{name}, </if> <if test="phoneNumber!=0"> phoneNumber=#{phoneNumber} </if> </set> where id=#{id}; </update>
重新运行:
6. foreach 标签
foreach 标签可以迭代生成一系列值, 这个标签主要用于 SQL 的 in 语句.
为对象添加一个 List 属性:
@Data public class Student { private long id; private String name; private long phoneNumber; private List<Integer> ids; public Student(long id, String name, long phoneNumber, List<Integer> ids) { this.id = id; this.name = name; this.phoneNumber = phoneNumber; this.ids = ids; } ...
在接口中定义好方法后, 添加 SQL 如下: 对照原语句,
collection 值为属性名,
open 值为 where 到迭代元素前之间的内容,
close 值为迭代元素后面的内容,
id 值为迭代元素对象名,
separator 值为分隔符
<select id="findByIds" parameterType="com.ryan.javaClass.Student" resultType="com.ryan.javaClass.Student"> # select * from student where id in (5876, 5877); select * from student <where> <foreach collection="ids" open="id in (" close=")" item="id" separator=","> #{id} </foreach> </where> </select>
测试:
public static void main(String[] args) { InputStream inputStream = DataRepository.class.getClassLoader().getResourceAsStream("mybatis-config.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); DataRepository dataRepository = sqlSession.getMapper(DataRepository.class); //创建查询条件对象 Student student = new Student(); List<Integer> list = new ArrayList<Integer>(); list.add(5876); list.add(5877); student.setIds(list); List<Student> stu = dataRepository.findByIds(student); System.out.println(stu); sqlSession.close(); }
运行:
