19.Mybatis之动态SQL
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
1.if语句
动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.sxt.dao.UserMapper"> <select id="query" resultType="user" parameterType="user"> select * from t_user where 1=1 <if test="name!=null"> and name=#{name} </if> <if test="age>0"> and age=#{age} </if> <if test="id!=null and id>0"> and id=#{id} </if> </select>
测试
@Test public void test1() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); user.setName("王五"); user.setId(2); user.setAge(18); List<User> list = dao.query(user); for (User user2 : list) { System.out.println(user2); } session.close(); }
结果
2.where语句
<select id="query1" resultType="user" parameterType="user"> select * from t_user <where> <if test="name!=null"> and name=#{name} </if> <if test="age>0"> and age=#{age} </if> <if test="id!=null and id>0"> and id=#{id} </if> </where> </select>
测试
@Test public void test2() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); // user.setName("王五"); // user.setId(2); // user.setAge(18); List<User> list = dao.query1(user); for (User user2 : list) { System.out.println(user2); } session.close(); }
结果
3.choose语句
<!-- 首先判断name是否为空 如果不为空根据name查询 结束 如果name为空 判断age是否传达 ，不为空大于0根据age查询 否则根据id倒叙 --> <select id="query2" resultType="user" parameterType="user"> select * from t_user where 1=1 <choose> <when test="name!=null"> and name=#{name} </when> <when test="age!=null and age>0"> and age=#{age} </when> <otherwise> order by desc </otherwise> </choose> </select>
测试
@Test public void test3() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(true); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); // user.setName("王五"); // user.setAge(18); List<User> list = dao.query2(user); for (User user2 : list) { System.out.println(user2); } session.close(); }
结果
4.set语句
<!-- set标签会截取最后一个"," --> <update id="updateUser2" parameterType="user"> update t_user <set> <if test="name!=null"> name=#{name}, </if> <if test="age!=null and age>0"> age=#{age}, </if> </set> where id=#{id} </update>
测试
@Test public void test4() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(true); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); user.setId(3); user.setName("渣渣"); user.setAge(1); dao.updateUser2(user); session.close(); }
结果
5.trim语句
trim标记是一个格式化的标记，可以完成set或者是where标记的功能
<select id="query3" resultType="user" parameterType="user"> select * from t_user <trim prefix="where" prefixOverrides="and"> <if test="name!=null"> and name=#{name} </if> <if test="age>0"> and age=#{age} </if> <if test="id!=null and id>0"> and id=#{id} </if> </trim> </select>
测试
@Test public void test5() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); // user.setName("王五"); // user.setId(2); // user.setAge(18); List<User> list = dao.query3(user); for (User user2 : list) { System.out.println(user2); } session.close(); }
结果
6.update语句
<update id="updateUser3" parameterType="user"> update t_user <trim prefix="set" suffixOverrides=","> <if test="name!=null"> name=#{name}, </if> <if test="age!=null and age>0"> age=#{age}, </if> </trim> where id=#{id} </update>
测试
@Test public void test6() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(true); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); user.setName("花花"); user.setId(5); // user.setAge(1); Integer count = dao.updateUser3(user); System.out.println(count); session.close(); }
结果
7.sql块
sql片段一般用来定义sql中的列
<sql id="baseSql"> id,name,age </sql> <select id="query4" resultType="user" parameterType="user"> select <include refid="baseSql"></include> from t_user <trim prefix="where" prefixOverrides="and"> <if test="name!=null"> and name=#{name} </if> <if test="age>0"> and age=#{age} </if> <if test="id!=null and id>0"> and id=#{id} </if> </trim> </select>
测试
@Test public void test7() throws IOException { InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); UserMapper dao = session.getMapper(UserMapper.class); User user = new User(); // user.setName("王五"); // user.setId(2); // user.setAge(18); List<User> list = dao.query4(user); for (User user2 : list) { System.out.println(user2); } session.close(); }
结果
