Mybatis可以帮助我们方便的在SQL语句中实现某些逻辑。Mybatis动态sql语法常用的标签有:
- 使用_parameter
- if判断
- switch判断(使用choose…when…otherwise语法标签 )
- where子句
- set子句
- trim语法
- foreach迭代变量
_parameter的使用
_parameter 表示当前传入的参数,如果查询的时候传入的参数只有一个,则使用 _parameter
数据库mybatis1中的表users中现在有记录如下:
User [id=1, name=aa, phone=13411111111, email=aa@163.com]
User [id=2, name=bb, phone=13422222222, email=bb@163.com]
User [id=3, name=cc, phone=13433333333, email=cc@126.com]
User [id=4, name=dd, phone=13444444444, email=dd@126.com]
现要查询id值为4的user信息,返回的结果集为User对象,userMapper.xml文件中写法如下:
<select id="getUser1" parameterType="int" resultType="User">
select id, name, phone, email from users where id = #{_parameter}
</select>
测试代码:
@Test
public void getUser1(){
SqlSession session = MybatisUtils.getSession(false);
User user = session.selectOne("com.qcc.mapping.userMapper.getUser1", 4);
System.out.println(user);
}
运行结果:User [id=4, name=dd, phone=13444444444, email=dd@126.com]
if判断
if其实就是简单的条件判断,条件成立,就拼接sql语句,不成立就不拼接。
<!--
获取所有id值大于2的User对象
-->
<select id="getUsersByIf" parameterType="int" resultType="User">
select id, name, phone, email from users where 1=1
<if test="_parameter != null">
and id > #{_parameter}
</if>
</select>
测试代码:
@Test
public void get1(){
SqlSession session = MybatisUtils.getSession(false);
List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByIf", 2);
for (User user : userList) {
System.out.println(user);
}
}
运行结果:
User [id=3, name=cc, phone=13433333333, email=cc@126.com]
User [id=4, name=dd, phone=13444444444, email=dd@126.com]
在以上两个示例当中,都是选取id, name, phone, email这四个字段,以下还会多次出现,实际开发当中选择的字段更多,更复杂,浪费时间且容易写错,于是使用一个叫sql的标签定义这些字段,只需要在使用的时候引入即可。
<sql id="columns">
id, name, phone, email
</sql>
引入的写法是:<include refid="columns"/>
,它就相当于sqls标签中id, name, phone, email
switch判断
类似Java中的switch语法,在这里使用的是choose…when…otherwise的语法
使用示例如下:
<!--
与java中的switch(value)...case;break;...default格式类似,
当when元素中的条件满足的时候就拼接其中的内容到sql语句,同时就会跳出choose语句,
即所有的when和otherwise条件中,只有一个会输出,
当所有条件都不满足的时候就拼接otherwise中的内容。
也就是说when和otherwise中的多个语句只会拼接上一个。所以这个语法用的不多。
-->
<select id="getUser2" parameterType="User" resultType="User">
select <include refid="columns"/> from users where 1=1
<choose>
<when test="id!=0">
and id = #{id}
</when>
<when test="name!=null">
and name = #{name}
</when>
<otherwise>
and phone = #{phone}
</otherwise>
</choose>
</select>
测试代码:
@Test
public void getUser2(){
SqlSession session = MybatisUtils.getSession(false);
User user = new User();
user.setId(2);
user.setName("aa");
List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser2", user);
System.out.println(list);
}
运行结果:
[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]
发现查询出来的集合当中有一个User对象,对象的name属性值是bb,而不是aa,这就验证了有多个when条件成立的时候,它会自上往下执行拼接,一旦拼接上一个when中的语句后就不再继续执行了,跳出choose语句。
where子句
where语句的作用主要是简化SQL语句中where中的条件判断的
上边的if和switch的两个示例中select标签中写的sql语句中,在where关键字后面都有个1=1,这个是必需的,
如果没有,拼接上对应的条件后sql语法就不正确了,多了个and,
没有1=1时:select * from users where and id > #{_parameter}
语法错误,多个and。
有了1=1后就是select * from users where 1=1 and id > #{_parameter}
语法正确。
示例:
<!--
1.where元素的作用是会在写入where元素的地方输出一个where,
2.where子句会自动把拼接后的语句中多余的and去掉
3.另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的,MyBatis会智能的帮你处理,
如果所有的条件都不满足那么MyBatis就会查出所有的记录,
4.如果输出后是and开头的,MyBatis会把第一个and忽略,当然如果是or开头的,MyBatis也会把它忽略;
5.在where元素中你不需要考虑空格的问题,MyBatis会智能的帮你加上。
-->
<select id="getUser3" parameterType="User" resultType="User">
select <include refid="columns"/> from users
<where>
<if test="id!=0">
and id = #{id}
</if>
<if test="name!=null">
and name = #{name}
</if>
</where>
</select>
测试代码:
@Test
public void getUser3(){
SqlSession session = MybatisUtils.getSession(false);
User user = new User();
user.setId(2);
// user.setName("aa");
List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser3", user);
System.out.println(list);
}
测试结果:
[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]
set子句
set子句一般更新数据库记录的操作
示例如下:
<!--
set标签的作用主要是在包含的语句前输出一个set,
然后如果包含的语句是以逗号结束的话将会把该逗号忽略,(即忽略赋值语句中最后一个,)
如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段。
-->
<update id="update1" parameterType="User">
update users
<set>
<if test="name != null">
name = #{name},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="email != null">
email = #{email},
</if>
</set>
where id = #{id}
</update>
测试代码:
@Test
public void update1(){
SqlSession session = MybatisUtils.getSession(true);//修改操作需要使用事务,提交事务
User user = new User();
user.setId(4);
user.setName("update_4");
user.setEmail("newUser@qq.com");
System.out.println("传入的user对象:" + user);
User user1 = session.selectOne("com.qcc.mapping.userMapper.getUser", 4);
System.out.println("修改前:" + user1);
/*用户的电话号码未重新赋值,则user的phone属性为null,因此保留不做修改*/
session.update("com.qcc.mapping.userMapper.update1", user);
//根据传入的id查询对应的实体对象,查询修改后的user对象
User user2 = session.selectOne("com.qcc.mapping.userMapper.getUser", 4);
System.out.println("修改后:" + user2);
}
测试结果:
传入的user对象:User [id=4, name=update_4, phone=null, email=newUser@qq.com]
修改前:User [id=4, name=dd, phone=13444444444, email=dd@126.com]
修改后:User [id=4, name=update_4, phone=13444444444, email=newUser@qq.com]
trim语法
trim添加需要的内容,去掉多余的内容
trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;
可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides;
正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where子句、set子句的功能,
语法格式如下:
<trim prefix="where" prefixOverrides="and |or ">
<if ...>
AND ...
</if>
<if ...>
AND ...
</if>
<if ...>
AND ...
</if>
</trim>
它的作用是将生成的sql中添加 where 前缀,并将 sql 的 and 或者 or 前缀删除掉
在更新操作时:
<trim prefix="set" suffixOverrides=",">
<if ...>
account = #{account},
</if>
<if ...>
password = #{password},
</if>
<if ...>
name = #{name},
</if>
</trim>
它的作用是将生成的sql中添加 set 前缀,并将 sql 的 最后一个,后缀去掉
查询时使用trim子句示例:
<select id="getUser4" parameterType="User" resultType="User">
select <include refid="columns"/> from users
<!-- 添加 where 关键字,去掉最前面的 and 关键字 -->
<trim prefix="where" prefixOverrides="and">
<if test="id != 0">
and id = #{id}
</if>
<if test="name != null">
and name = #{name}
</if>
</trim>
</select>
测试代码:
@Test
public void getUser4(){
SqlSession session = MybatisUtils.getSession(false);
User user = new User();
// user.setId(1);
user.setName("aa");
List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser4", user);
System.out.println(list);
}
测试结果:
[User [id=1, name=aa, phone=13411111111, email=aa@163.com]]
可见与使用where 语法的情况是一样的。使用trim更新的操作类似,略。
foreach迭代变量
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合,比如批量删除。也可以使用于比如批量插入操作。
foreach元素的属性主要有item,index,collection,open,separator,close。
1、item表示集合中每一个元素进行迭代时的别名,
2、index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,
3、open表示该语句以什么开始,
4、separator表示在每次进行迭代之间以什么符号作为分隔符,
5、close表示以什么结束,
6、collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
(1)如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
(2)如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
(3)如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,
实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,
所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key。
foreach示例1:使用在in条件中,批量删除:
userMapper.xml 文件中映射 sql 的代码如下:
<!-- 传入数组ids进行批量删除操作 -->
<delete id="delBatch">
delete from users where id in
<!--如果传入的是单参数且参数类型是一个Array的时候,collection属性值为array-->
<foreach collection="array" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
测试代码:(删除前先查询数据库中的所有记录并输出,进行删除操作后,再查询数据库中所有记录并输出)
@Test
public void delBatchDemo(){
SqlSession session = MybatisUtils.getSession(true);
/*查询数据库中所有记录*/
List<User> list = session.selectList("com.qcc.mapping.userMapper.getAll");
System.out.println("删除前:" + list);
/*以下是批量删除操作*/
int[] ids = new int[]{1, 3, 4};
int result = session.delete("com.qcc.mapping.userMapper.delBatch", ids);
if(result>0){
System.out.println("删除" + result + "条记录成功!");
}else{
System.out.println("删除失败!");
}
/*批量删除操作完成后,再次查询数据库中所有记录*/
list = session.selectList("com.qcc.mapping.userMapper.getAll");
System.out.println("删除后:" + list);
}
测试结果:
删除前:[User [id=1, name=aa, phone=13411111111, email=aa@163.com], User [id=2, name=bb, phone=13422222222, email=bb@163.com], User [id=3, name=cc, phone=13433333333, email=cc@126.com], User [id=4, name=update_4, phone=13444444444, email=newUser@qq.com]]
删除3条记录成功!
删除后:[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]
foreach示例2:使用foreach执行批量插入操作:
userMapper.xml 文件中批量插入的映射 sql 的代码如下:
<insert id="addBatchByList">
insert into users(name, phone, email) values
<!--如果传入的是单参数且参数类型是一个List的时候,collection属性值为list-->
<foreach item="user" collection="list" separator=",">
(#{user.name}, #{user.phone}, #{user.email})
</foreach>
</insert>
在User实体类中添加无参和带参的构造方法,以便初始化User对象。
public User(String name, String phone, String email) {
this.name = name;
this.phone = phone;
this.email = email;
}
public User() {}
批量插入的测试代码如下:
@Test
public void addBatch(){
List<User> userList = new ArrayList<>();
User user1 = new User("关羽", "13333333333", "GuanYu@shu.com");
User user2 = new User("张飞", "13344444444", "ZhangFei@shu.com");
User user3 = new User("赵云", "13355555555", "ZhaoYun@shu.com");
User user4 = new User("黄忠", "13366666666", "HuangZhong@shu.com");
userList.add(user1);
userList.add(user2);
userList.add(user3);
userList.add(user4);
SqlSession session = MybatisUtils.getSession(false);//传入false,意味着不会自动提交事务,等批量操作完成后再提交事务。提升操作数据库的效率。
int result = session.insert("com.qcc.mapping.userMapper.addBatchByList", userList);
if (result>0) {
System.out.println("批量插入" + result + "条记录成功!");
} else {
System.out.println("批量插入失败!");
}
session.commit();//提交事务
System.out.println("遍历数据库中所有的user信息");
userList = session.selectList("com.qcc.mapping.userMapper.getAll");
for (User user : userList) {
System.out.println(user);
}
}
批量插入的测试运行结果:
批量插入4条记录成功!
遍历数据库中所有的user信息
User [id=2, name=bb, phone=13422222222, email=bb@163.com]
User [id=5, name=关羽, phone=13333333333, email=GuanYu@shu.com]
User [id=6, name=张飞, phone=13344444444, email=ZhangFei@shu.com]
User [id=7, name=赵云, phone=13355555555, email=ZhaoYun@shu.com]
User [id=8, name=黄忠, phone=13366666666, email=HuangZhong@shu.com]
Mybatis动态sql常用的就以上这些了。