开发工具:STS
前言:
mybatis框架中最具特色的便是sql语句中的自定义,而动态sql的使用又使整个框架更加灵活。
动态sql中的语法:
where标签if标签trim标签set标签switch\when标签foreach标签
一、搭建项目
1.创建用户表:
2.添加实体:
package com.xm.pojo;
/**
* 用户实体
* @author xm
*
*/
public class User {
private int id;
private String name;
private String username;
private int age;
private String phone;
private String email;
//无参构造函数必须有,ORM框架调用的就是无参构造函数
public User() {
}
public User(int id, String name, String username, int age, String phone, String email) {
super();
this.id = id;
this.name = name;
this.username = username;
this.age = age;
this.phone = phone;
this.email = email;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", username=" + username + ", age=" + age + ", phone=" + phone
+ ", email=" + email + "]";
}
}
User.java
3.添加mapper接口:
package com.xm.mapper;
/**
* 用户mapper接口
* @author xm
*
*/
public interface UserMapper {
}
UserMapper.java
4.添加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关系映射 -->
<mapper namespace="com.xm.mapper.UserMapper">
</mapper>
UserMapper.xml
5.添加测试类:
package com.xm;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.xm.mapper.UserMapper;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTest {
@Autowired
private UserMapper userMapper;
}
UserText.java
二、动态sql
1.if标签的使用
(1)添加mapper接口:
/**
* 根据条件查出学生
* @param user
* @return
*/
public List<User> getUser(User user);
(2)添加mapper映射:
<select id="getUser" resultType="user">
select * from user where 1=1
<if test="id != null">and id=#{id} </if>
<if test="age != null">and age=#{age} </if>
<if test="name != null">and name=#{name}</if>
</select>
(3)添加测试:
@Autowired
private UserMapper userMapper;
@Test
public void getUserTest() {
User user = new User(null , "小明" , null , 12 , null , null);
List<User> users = userMapper.getUser(user);
System.out.println(users);
}
(4)测试结果:
(5)思考:
where 1=1 在这里的作用是什么呢?可以不用它吗?
2.where标签的使用
(1)更改mapper映射:
<select id="getUser" resultType="user">
select * from user
<where>
<if test="id != null">and id=#{id} </if>
<if test="age != null">and age=#{age} </if>
<if test="name != null">and name=#{name}</if>
</where>
</select>
(2)测试结果:
(3)思考:
and id=# 换成 id=# and 会怎样?
3.trim标签的使用
(1)更改mapper映射:
<select id="getUser" resultType="user">
select * from user
<trim prefix="where" suffixOverrides="and">
<if test="id != null">id=#{id} and</if>
<if test="age != null">age=#{age} and</if>
<if test="name != null">name=#{name} and</if>
</trim>
</select>
(2)测试结果:
(3)分析:
trim标签下的四个属性:
prefix:在标签开始添加上该字符串
suffixOverrides:在标签末尾去除上该字符串
suffix:在标签末尾添加上该字符串
prefixOverrides:在标签开始去除上该字符串
4.set标签的使用
(1)描述需求:
用在update语句中,如果字段参数不为null,则修改此参数
(2)添加mapper接口:
/**
* 根据id修改所有非空字段
* @param user
*/
public void updateUserById(User user);
(3)添加mapper映射:
<update id="updateUserById">
update user
<set>
<if test="name != null">name=#{name},</if>
<if test="age != null">age=#{age},</if>
<if test="username != null">username=#{username},</if>
<if test="email != null">email=#{email},</if>
<if test="phone != null">phone=#{phone}</if>
</set>
<where>
id=#{id}
</where>
</update>
(4)添加测试方法:
@Test
public void updateUserTest() {
User user = new User(1, null, null, null, "12545564454", "14548445@qq.com");
userMapper.updateUserById(user);
}
(5)测试结果
5.switch\when标签的使用
(1)描述需求:
满足id!=null查询id,
否则,看满足age否,
接着,看name是否满足,
最后,按age>10查询
(2)更改mapper映射:
<select id="getUser" resultType="user">
select * from user where
<choose>
<when test="id != null">id=#{id} </when>
<when test="age != null">age=#{age}</when>
<when test="name != null">name=#{name} </when>
<otherwise>age>10</otherwise>
</choose>
</select>
(3)测试结果:
6.foreach标签的使用
(1)需求描述:
查出多个id的user
(2)添加mapper接口:
/**
* 根据id批量查询
* @param ids
* @return
*/
public List<User> listById(List<Integer> ids);
(3)添加mapper映射:
<select id="listById" resultType="user" parameterType="list">
select * from user where id in
<foreach collection="list" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
(4)添加测试用例:
@Test
public void listTest() {
List<User> users = userMapper.listById(Arrays.asList(1));
System.out.println(users);
}
(5)分析:
foreach标签下的所有属性:
collection:获取的集合名,如果是list集合,springboot会把它的key值默认封装为list
item:遍历的单个属性值
separator:拼接隔离的字符串
open:在循环的开始拼接的字符串
close:在循环的结束拼接的字符串
index:索引,在map中作为key
2018-07-04