mybatis的动态SQL
🍅 Java学习路线:搬砖工的Java学习路线
🍅 作者:程序员小王
🍅 程序员小王的博客:https://www.wolai.com/wnaghengjie/ahNwvAUPG2Hb1Sy7Z8waaF
🍅 扫描主页左侧二维码,加我微信 一起学习、一起进步
🍅 欢迎点赞 👍 收藏 ⭐留言 📝
目录
这就是本篇所讲的使用 mybatis 动态SQL,通过 if, choose, when, otherwise, trim, where, set, foreach等标签,可组合成非常灵活的SQL语句,从而在提高 SQL 语句的准确性的同时,也大大提高了开发人员的效率。
这里是引用
1、动态SQL: SQL 片段之 抽取SQL查询语句
2、动态SQL:if+where 语句 之多种类型查询
3、动态SQL:if+set 语句 之多种类型修改语句
4、动态SQL: foreach 语句之进行批量删除
5、动态SQL: foreach 语句之进行批量查询
🐵此文都是干货,有点长,建议先收藏 !
一、动态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:该错误可能涉及默认参数映射
文末
👉在此,鸣谢:刘浩老师讲解
📌 作者:王恒杰
📃 更新: 2021.10.14
❌ 勘误: 无
📜 声明: 由于作者水平有限,本文有错误和不准确之处在所难免,本人也很想知道这些错误,恳望读者批评指正!
🍅 欢迎点赞 👍 收藏 ⭐留言 📝