mybatis的动态SQL
一、动态sql语句概述
Mybatis 的映射文件中，前面我们的 SQL 都是比较简单的，有些时候业务逻辑复杂时，我们的 SQL是动态变化的，此时在前面的学习中我们的 SQL 就不能满足要求了。
问题来了： 什么是动态SQL? 动态SQL有什么作用？
传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接， 稍不注意哪怕少了个空格，都会导致错误。 Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签， 可组合成非常灵活的SQL语句，从而提高开发人员的效率。
下面就去跟着我来感受Mybatis动态SQL的魅力吧。
二、 include 实现SQL片段抽取
1、SQL片段的作用
Sql 中可将重复的 sql 提取出来，使用时用 include 引用即可， 最终达到 sql 重用的目的 减少Sql语句的冗余，简化开发
2、单表查询时实现SQL片段抽取
<!--抽取sql片段简化编写--> <sql id="selectStudent"> select * from student</sql> <select id="findById" parameterType="int" resultType="student"> <include refid="selectStudent"></include> where id=#{id} </select> <select id="findByIds" parameterType="list" resultType="student"> <!-- 使用sql片段 refid:引用--> <include refid="selectStudent"></include> <where> <foreach collection="array" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select>
3、级联查询时实现SQL片段抽取
<!-- sql片段 减少Sql语句的冗余，简化开发 --> <sql id="selectEmp_Dep"> select e.id as emp_id, e.name as emp_name, e.salary as emp_salary, d.id as dep_id, d.name as dep_name, d.address as dep_address </sql> <!--引用SQL片段--> <select id="selectEmpById" resultMap="empResultMap"> <include refid="selectEmp_Dep"/> from emp e inner join dep d on e.id = d.id where e.id = #{id}; </select>
4、总结语法:
我们可以将一些重复性的 SQL 语句进行抽取，以达到复用的效果。
<sql>：抽取 SQL 语句标签。 <include>：引入 SQL 片段标签。 <sql id=“片段唯一标识”>抽取的 SQL 语句</sql> <include refid=“片段唯一标识”/>
三、多条件组合查询之<where-if>
我们根据实体类的不同取值，使用不同的 SQL语句来进行查询。比如在 id如果不为空时可以根据id查询，如果username 不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
1、原始的SQL语句示意
2、使用动态SQL语句if之后
当id=1时，name,password,birthday为空时 #sql语句只执行: select id,name,password,birthday form user where id=1;
示意图：
动态条件查询，就是通过条件判断动态筛选查询条件：
<select> select id,name,password,birthday from t_user <where> 注意：int类型的主键不能用0作为判断条件， 只能用null作为判断值（int类型可以是0或者是负数） 为了数据库表能与实体类映射，所有的实体类都只能是包装类 <if test="id!=null"> id = #{id} </if> <if test="name!=null"> and name = #{name} </if> <if test="password!=null"> and password= #{password} </if> <if test="birthday!=null"> and birthday= #{birthday} </if> </where> </select> 注意：Where标签可以忽略其后的第一个and
UserDao接口
1、if标签中的test里 判断的条件是当前参数名， 如果参数是对象的话，书写的是对象的属性名 2、 注意：int类型的主键不能用0作为判断条件， 只能用null作为判断值（int类型可以是0或者是负数） 为了数据库表能与实体类映射，所有的实体类都只能是包装类 /** * 通过实体类查询所有用户 * @param user * @return List<User> */ public List<User> selectUserAll(User user);
UserDao两种写法示意图：
UserDaoImpl.xml
<mapper namespace="com.tjcu.dao.UserDao"> <!--SQL片段抽取--> <sql id="selectUser"> select id, username, password, age, birthday </sql> <!-- 实体类属性： id,username, password,age,birthday --> <select id="selectUserAll" resultType="user"> <include refid="selectUser"/> from t_user <where> <if test="id!=null"> id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> <if test="age!=null"> and age=#{age} </if> <if test="birthday!=null"> and birthday=#{birthday} </if> </where> </select> </mapper>
3、不同条件下的执行结果
1.当查询条件id和username都存在时，控制台打印的sql语句如下：
@Test public void selectTest(){ UserDao mapper = (UserDao) MybatisUtil.getMapper(UserDao.class); User user = new User(null,"王恒杰","123456",null,null); List<User> users = mapper.selectUserAll(user); for (User u : users) { System.out.println("查看登录的用户"+u); } MybatisUtil.close(); }
2、当查询条件是查询所有时，控制台打印的sql语句如下：
@Test public void selectTest(){ UserDao mapper = (UserDao) MybatisUtil.getMapper(UserDao.class); User user = new User(); List<User> users = mapper.selectUserAll(user); for (User u : users) { System.out.println("查询所有"+u); } MybatisUtil.close(); }
动态Sql之where-if示意图
4、总结语法:
<where>：条件标签。如果有动态条件，则使用该标签代替 where 关键字。 <if>：条件判断标签。 <if test=“条件判断”> 查询条件拼接 </if>
5、注意 事项：
1、if标签中的test里 判断的条件是当前参数名， 如果参数是对象的话，书写的是对象的属性名 2、注意：int类型的主键不能用0作为判断条件， 只能用null作为判断值（int类型可以是0或者是负数） 如果用到SQL语句，为了数据库表能与实体类映射，所有的实体类都只能是包装类
四、动态修改SQL【Update字句】
1、调用修改方法：
sql: update t_yser set name=#{name} where id=#{id} update t_yser set name=#{name},password=#{password} where id=#{id} update t_yser set name=#{name},password=#{password},birthday=#{birthday} where id=#{id} 如果传递的参数是空值，sql语句会将表中对应的行数数据所有列置空 update(new User(1,null,null,null));
2、动态修改：通过判断动态确认要修改的列
**# 修改1号用户的密码** update(new User(2,null,'123456',null)); **#UserDaoMapper.xml动态修改实现** <update id="updateUser"> update t_user set <if test="username!=null"> username=#{username}, </if> <if test="password!=null"> password=#{password}, </if> <if test="age!=null"> age=#{age}, </if> <if test="birthday!=null"> birthday=#{birthday} </if> where id=#{id} </update>
问题又来了： “如果我只有name不为null, 那么这SQL不就成了 update set name = #{name}, where … ? 你那name后面那逗号会导致出错啊！”
是的，这时候，就可以用mybatis为我们提供的set 标签了。下面是通过set标签改造后：
**# 修改2号用户的密码** update(new User(2,null,'123456',null)); **#UserDaoMapper.xml动态修改实现** <update id="updateUser"> update t_user <set> <if test="username!=null"> username=#{username}, </if> <if test="password!=null"> password=#{password}, </if> <if test="age!=null"> age=#{age}, </if> <if test="birthday!=null"> birthday=#{birthday} </if> </set> where id=#{id} </update>
测试代码：
@Test public void updateTest(){ UserDao mapper = (UserDao) MybatisUtil.getMapper(UserDao.class); User user = new User(2, null,"123456",null,null); mapper.updateUser(user); }
3、注意事项：
1. set标签自动忽略最后一个"," 2.修改时不要全部都为空，最少修改一列 3.动态修改可以在前端用数据回显代替
五、批量操作——批量删除
1、原始批量删除示意图：
2、在service层批量删除
将原来在Action(Controller)层的批量删除，转移到Service层
在Service层进行批量删除的缺点：
*效率低，每次删除都需要获取链接操作数据库
3、在Dao层使用mybatis批量删除
1、解决service层批量删除效率低方案：
解决办法：最好一次性全部删除 SQL语句： 一次性删除多个用户 delete from t_user where id in(1,2,3,4);
2、mybatis中SQL动态语句实现批量删除：
delete from t_user where id in(`1,2,3,4`); <!-- collection:指定当前参数是数组还是集合 数组：array 集合：list open:开始符号 close:结束符号 item:遍历时当前元素的名字 separator:元素之间的分隔符 【英语单词：separator:离析器】 --> delete from t_user where id in <foreach collection="list" open="id in(" close=")" item="id" separator="," > #{id} </foreach>
4、批量删除案例：
（1）批量删除示意图：
（2）实体类+sql数据库
（3）dao接口
/** * 批量删除 * @param ids */ public void deleteUser(List<Integer> ids);
（4）UserDaoMapper.xml[核心代码]
<!-- foreach：遍历集合来组装sql collection: map集合的key 如果是数组：array 集合：list open: 以某种字符开始 close:以某种字符结束 item: 集合中的元素 separator:以某种字符分割 index: 当前所遍历到的索引号 --> <delete id="deleteUser"> delete from t_user where id <foreach collection="list" open="in(" close=")" item="id" separator=","> #{id} </foreach> </delete>
（5）测试类
@Test public void deleteTest(){ UserDao mapper = (UserDao) MybatisUtil.getMapper(UserDao.class); List<Integer> ids=new ArrayList<Integer>(); ids.add(7); ids.add(8); ids.add(9); mapper.deleteUser(ids); MybatisUtil.close(); }
5、注意事项：
1.如果参数进行了参数绑定，可以使用绑定之后的名字
6、dao层进行批量删除的好处：
减少与数据库的连接次数，提高程序运行效率
六、批量查询 之<foreach>
循环执行sql的拼接操作，例如：SELECT * FROM student WHERE id IN (1,3,4)。
UserDao
/** * foreach查询多个id * @param id * @return List<User> */ public List<User> selectByIds(List<Integer> id);
UserDaoImpl.xml配置
<!-- foreach查询多个id collection：参数容器类型， (list-集合， array-数组)。 open：开始的 SQL 语句。 close：结束的 SQL 语句。 item：参数变量名。 separator：分隔符。 --> <select id="selectByIds" resultType="user"> select * from t_user <where> <foreach collection="list" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select>
测试类
@Test public void selectByIts() throws IOException { SqlSession sqlSession = MybatisUtil.openSession(); UserDao mapper = sqlSession.getMapper(UserDao.class); ArrayList<Integer> list = new ArrayList<>(); list.add(1); list.add(3); list.add(4); List<User> users = mapper.selectByIds(list); for (User user : users) { System.out.println(user); } MybatisUtil.close(sqlSession); }
总结语法:
<foreach>：循环遍历标签。适用于多个参数或者的关系。 <foreach collection=“”open=“”close=“”item=“”separator=“”> 获取参数 </foreach>
属性
collection：参数容器类型， (list-集合， array-数组)。
open：开始的 SQL 语句。
close：结束的 SQL 语句。
item：参数变量名。
separator：分隔符。
七、知识小结
MyBatis映射文件配置：
<select>：查询 <insert>：插入 <update>：修改 <delete>：删除 <where>：where条件 <if>：if判断 <foreach>：循环 <sql>：sql片段抽取 SQLSyntaxErrorException：SQL语法错误异常 check the manual that corresponds to your MySQL server version for the right syntax to use near 'in： 查看与您的MySQL服务器版本相对应的手册，了解在“in”附近使用的正确语法 The error may involve defaultParameterMap：该错误可能涉及默认参数映射
