目录
1.什么是动态sql
MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作,并动态拼接成完整的SQL之后再执行,以达到SQL复用、简化编程的效果。
Mybatis 动态 sql 可以让我们在 Xml 映射文件内,以标签的形式编写动态 sql,完成逻辑判断和动态拼接 sql 的功能。
2.if标签
我们根据实体类的不同取值,使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询,如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
if 语句使用方法简单,常常与 test 属性联合使用。语法如下。
<if test="判断条件"> SQL语句</if>
mapper接口:
public interface UserDao {
//复杂条件查询
public List<User> findByUser(User user);
}
xml映射文件:
<?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.by.dao.UserDao">
<select id="findByUser" resultType="user">
select * from user where 1=1
<if test="username!=null and username != ''">
and username=#{username}
</if>
<if test="birthday!=null">
and birthday=#{birthday}
</if>
<if test="sex!=null and sex != ''">
and sex=#{sex}
</if>
<if test="address!=null and address != ''">
and address=#{address}
</if>
</select>
</mapper>
where的条件为1=1
测试类:
@Test
public void testFindAll(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setSex("男");
user.setAddress("香港");
List<User> userList = userDao.findByUser(user);
for(User u : userList){
System.out.println(u);
}
}
3.where标签
为了简化上面where 1=1的条件拼装,我们可以使用where标签将if标签代码块包起来,将1=1条件去掉。
若查询条件的开头为 “AND” 或 “OR”,where会将他们去掉
mapper映射文件:
<?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.by.dao.UserDao">
<select id="findByUser" resultType="User">
select * from user where and username=#{username}
<!--where标签将if标签代码块包起来,并去掉开头 “AND” 或 “OR”-->
<where>
<if test="username!=null and username != ''">
and username=#{username}
</if>
<if test="birthday!=null">
and birthday=#{birthday}
</if>
<if test="sex!=null and sex != ''">
and sex=#{sex}
</if>
<if test="address!=null and address != ''">
and address=#{address}
</if>
</where>
</select>
</mapper>
4.set标签
set标签用于动态包含需要更新的列,并会删掉额外的逗号
mapper映射文件:
<update id="updateByUser" parameterType="user">
update user
<set>
<if test="username!=null and username != '' ">
username=#{username},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null and sex != '' ">
sex=#{sex},
</if>
<if test="address!=null and address != '' ">
address=#{address},
</if>
</set>
where id=#{id}
</update>
5.trim标签
5.1trim标签可以代替where标签、set标签
但个人觉得直接用where或者set,无需用trim代替,trim主要用于动态插入出局比较合适
mapper映射文件:
<update id="updateByUser2" parameterType="User">
update user
<!-- 增加SET前缀,忽略,后缀 -->
<trim prefix="SET" suffixOverrides=",">
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null and username != '' ">
sex=#{sex},
</if>
<if test="address!=null and username != '' ">
address=#{address},
</if>
</trim>
where id=#{id}
</update>
5.2trim主要用于动态向表中插入数据
mapper映射文件:
<insert id="addUser" parameterType="com.by.pojo.User">
INSERT INTO user
<!--
set标签一定要包括if标签,作用:
prefix:加上前缀,“(”
suffix:加上后缀,“)”
prefixOverrides:去除多余的前缀内容
suffixOverrides:去除多余的后缀内容,“,”
-->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username!=null and username!=''">
username,
</if>
<if test="birthday!=null">
birthday,
</if>
<if test="sex!=null and username != '' ">
sex,
</if>
<if test="address!=null and username != '' ">
address,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
<if test="username!=null and username!=''">
#{username},
</if>
<if test="birthday!=null">
#{birthday},
</if>
<if test="sex!=null and username != '' ">
#{sex},
</if>
<if test="address!=null and username != '' ">
#{address},
</if>
</trim>
</insert>
set标签一定要包括if标签,作用:
prefix | 加上前缀,“(” |
suffix | 加上后缀,“)” |
prefixOverrides: | 去除多余的前缀内容 |
suffixOverrides: | 去除多余的后缀内容,“,” |
6.foreach标签
foreach标签的常见使用场景是集合进行遍历
foreach 标签可以对数组, Map 或实现 Iterable 接口。
foreach 中有以下几个属性
- collection: 必填, 集合/数组/Map的名称.
- item: 变量名。即从迭代的对象中取出的每一个值
- index: 索引的属性名。当迭代的对象为 Map 时, 该值为 Map 中的 Key.
- open: 循环开头的字符串
- close: 循环结束的字符串
- separator: 每次循环的分隔符
mapper接口:
void deleteUserByIds(@Param("idArr") Integer[] idArr);
mapper映射文件:
<delete id="deleteUserByIds" parameterType="list">
DELETE FROM user WHERE id in
<!--
collection:取值list、array、@Param("keyName")
item="id":循环每次取出的具体对象
open="(" :加上前缀
close=")" :加上后缀
separator=",":分隔符
-->
<foreach collection="idArr" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
测试代码:
@Test
public void testForeach2(){
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = new ArrayList<>();
long beginTime = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
User user = new User();
user.setUsername("刘德华");
user.setBirthday(new Date());
user.setSex("男");
user.setAddress("香港");
//userMapper.addUser(user); //io我们的mysql一万次
userList.add(user);
}
userMapper.addUser2(userList);
long endTime = System.currentTimeMillis();
System.out.println("插入一万条记录需要:" + (endTime-beginTime) + "ms");
}
如果直接在for里每加入一个都要访问一次mysql,添加一万次,又耗资源,又浪费时间,foreach就很好的解决了这个问题。
7.sql标签
在实际开发中会遇到许多相同的SQL,比如根据某个条件筛选,这个筛选很多地方都能用到,我们可以将其抽取出来成为一个公用的部分,这样修改也方便,一旦出现了错误,只需要改这一处便能处处生效了,此时就用到了<sql>这个标签了。
当多种类型的查询语句的查询字段或者查询条件相同时,可以将其定义为常量,方便调用。为求 <select> 结构清晰也可将 sql 语句分解。
mapper:
//复杂条件查询
public List<User> findByUser3(User user);
<!-- 定义SQL片段 -->
<sql id="query_user_where">
<if test="username!=null and username != ''">
and username=#{username}
</if>
<if test="birthday!=null">
and birthday=#{birthday}
</if>
<if test="sex!=null and sex != ''">
and sex=#{sex}
</if>
<if test="address!=null and address != ''">
and address=#{address}
</if>
</sql>
<select id="findByUser3" resultType="User">
select * from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
8.include
这个标签和<sql>是绝配,是共生的,include用于引用sql标签定义的常量。比如引用上面sql标签定义的常量。
<select id="findbyid" resultType="student">
<include refid="selectvp"/>
WHERE 1=1
<if test="sid != null">
AND sid like #{sid}
</if>
</select>
9.choose(when,otherwise) 语句
有时候,我们不想用到所有的查询条件,只想选择其中的一个,查询条件有一个满足即可,使用 choose 标签可以解决此类问题,类似于 Java 的 switch 语句
<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
select * from user
<where>
<choose>
<when test="id !='' and id != null">
id=#{id}
</when>
<when test="username !='' and username != null">
and username=#{username}
</when>
<otherwise>
and sex=#{sex}
</otherwise>
</choose>
</where>
</select>
也就是说,这里我们有三个条件,id,username,sex,只能选择一个作为查询条件
如果 id 不为空,那么查询语句为:select * from user where id=?
如果 id 为空,那么看username 是否为空,如果不为空,那么语句为 select * from user where username=?;
如果 username 为空,那么查询语句为 select * from user where sex=?
10. bind 标签
bind 标签是通过 OGNL 表达式去定义一个上下文的变量, 这样方便我们使用。
如在 selectByStudentSelective
方法中, 有如下:
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
在 MySQL 中, 该函数支持多参数, 但在 Oracle 中只支持两个参数。那么我们可以使用 bind 来让该 SQL 达到支持两个数据库的作用
<if test="name != null and name !=''">
<bind name="nameLike" value="'%'+name+'%'"/>
and name like #{nameLike}
</if>