前置准备
实体类
//实体类,对应数据库表
@Data
public class Student {
private Integer id;
private String name;
private Integer age;
private Integer gender;
private String createTime;
}
//前端传来的参数
@Data
public class StudentValidate {
private Integer id;
private String name;
private Integer age;
private Integer gender;
private String createTimeBegin;
private String createTimeEnd;
}
@RestController
@RequestMapping("sql")
public class SqlController {
@Autowired
private SqlService sqlService;
}
@Service
public class SqlService {
}
@Mapper
public interface SqlMapper {
}
1.if标签
- 需求:多条件查询。
- 可能的条件
controller -> service -> mapper
@GetMapping("testIf")
public Object getAll(@RequestBody StudentValidate studentValidate){
List<Student> studentList = sqlService.getAll(studentValidate);
return studentList;
}
@Autowired
private SqlMapper sqlMapper;
public List<Student> getAll(StudentValidate studentValidate) {
List<Student> studentList = sqlMapper.getByStudent(studentValidate);
return studentList;
}
List<Student> getByStudent(@Param("studentValidate") StudentValidate studentValidate);
SqlMapper.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.xwz.dynamicSql.SqlMapper">
<select id="getByStudent" resultType="com.xwz.dynamicSql.Student">
select * from student
where 1 = 1
<if test="studentValidate.id!=null">
and id = #{studentValidate.id}
</if>
<if test="studentValidate.name!=null and studentValidate.name!=''">
and name like concat('%',concat(#{studentValidate.name},'%'))
</if>
<if test="studentValidate.age!=null and studentValidate.age>0">
and age = #{studentValidate.age}
</if>
<if test="studentValidate.gender!=null">
and gender = #{studentValidate.gender}
</if>
<if test="studentValidate.createTimeBegin!=null and studentValidate.createTimeBegin!=''">
and create_time >= #{studentValidate.createTimeBegin}
</if>
<if test="studentValidate.createTimeEnd!=null and studentValidate.createTimeEnd!=''">
and create_time <= #{studentValidate.createTimeBegin}
</if>
</select>
</mapper>
postman测试数据
{
"id":"7",
"name":"jesse",
"age":"18",
"gender":"1",
"createTimeBegin":"2024-04-03",
"createTimeEnd":"2024-04-04"
}
补充
//1.数值类型的判断
<if test="studentValidate.id!=null">
and id = #{studentValidate.id}
</if>
<if test="studentValidate.id == 2">
and id = #{studentValidate.id}
</if>
//2.字符串类型的判断
<if test="studentValidate.name!=null and studentValidate.name!=''">
and name = #{studentValidate.name}
</if>
<if test="studentValidate.name!=null and studentValidate.name == 'jesse'.toString()">
and name = #{studentValidate.name}
</if>
//3.list类型的判断
<if test="list!=null and list.size()>0"</if>
..............
</if>
<if test="list==null or list.size()==0"</if>
..............
</if>
//4.布尔类型
<if test="isDelete"</if>
..............
</if>
2.where标签
- where标签的作⽤:让where⼦句更加动态智能。
- 所有条件都为空时,where标签保证不会⽣成where⼦句。
- ⾃动去除某些条件前⾯多余的and或or。
controller -> service -> mapper
@GetMapping("testWhere")
public Object getByIdOrName(@RequestBody StudentValidate studentValidate){
List<Student> studentList = sqlService.getByIdOrName(studentValidate);
return studentList;
}
public List<Student> getByIdOrName(StudentValidate studentValidate) {
List<Student> studentList = sqlMapper.queryByIdOrName(studentValidate);
return studentList;
}
List<Student> queryByIdOrName(@Param("studentValidate") StudentValidate studentValidate);
SqlMapper.xml
<select id="queryByIdOrName" resultType="com.xwz.dynamicSql.Student">
select * from student
<where>
<if test="studentValidate.id!=null">
and id = #{studentValidate.id}
</if>
<if test="studentValidate.name!=null and studentValidate.name!=''">
and name like concat('%',concat(#{studentValidate.name},'%'))
</if>
<if test="studentValidate.age!=null and studentValidate.age>0">
and age = #{studentValidate.age}
</if>
</where>
</select>
postman测试数据
{
"id":"7",
"name":"jesse",
"age":"18"
}
3.trim标签
- trim标签的属性:
- prefix:在trim标签中的语句前添加内容
- suffix:在trim标签中的语句后添加内容
- prefixOverrides:前缀覆盖掉(去掉)
- suffixOverrides:后缀覆盖掉(去掉)
controller -> service -> mapper
@GetMapping("testTrim")
public Object getStudentWithTrim(@RequestBody StudentValidate studentValidate){
List<Student> studentList = sqlService.getStudentWithTrim(studentValidate);
return studentList;
}
public List<Student> getStudentWithTrim(StudentValidate studentValidate) {
List<Student> studentList = sqlMapper.queryWithTrim(studentValidate);
return studentList;
}
List<Student> queryWithTrim(@Param("studentValidate") StudentValidate studentValidate);
SqlMapper.xml
<select id="queryWithTrim" resultType="com.xwz.dynamicSql.Student">
select * from student
<trim prefix="where" suffixOverrides="and">
<if test="studentValidate.id!=null">
id = #{studentValidate.id} and
</if>
<if test="studentValidate.name!=null and studentValidate.name!=''">
name like concat('%',concat(#{studentValidate.name},'%')) and
</if>
<if test="studentValidate.age!=null and studentValidate.age>0">
age = #{studentValidate.age}
</if>
</trim>
</select>
postman测试数据
{
"id":"7",
"name":"",
"age":""
}
4.set标签
- 主要使⽤在update语句当中,⽤来⽣成set关键字,同时去掉最后多余的“,”
controller -> service -> mapper
@PostMapping("testSet")
public Object updateWithSet(@RequestBody StudentValidate studentValidate){
boolean flag = sqlService.updateWithSet(studentValidate);
return flag ? "success" : "fail";
}
public boolean updateWithSet(StudentValidate studentValidate) {
int changeRow = sqlMapper.updateWithSet(studentValidate);
return changeRow > 0;
}
int updateWithSet(@Param("studentValidate") StudentValidate studentValidate);
SqlMapper.xml
<update id="updateWithSet">
update student
<set>
<if test="studentValidate.name!=null and studentValidate.name!=''">
name = #{studentValidate.name} ,
</if>
<if test="studentValidate.age!=null and studentValidate.age>0">
age = #{studentValidate.age} ,
</if>
<if test="studentValidate.gender!=null">
gender = #{studentValidate.gender} ,
</if>
</set>
where id = #{studentValidate.id}
</update>
postman测试数据
{
"id":"7",
"name":"jesse1",
"age":"",
"gender":"",
"createTimeBegin":"2024-04-03",
"createTimeEnd":"2024-04-04"
}
5.choose when otherwise标签
-
只有⼀个分⽀会被选择
controller -> service -> mapper
@GetMapping("testChoose")
public Object getWithChoose(@RequestBody StudentValidate studentValidate){
List<Student> studentList = sqlService.getWithChoose(studentValidate);
return studentList;
}
public List<Student> getWithChoose(StudentValidate studentValidate) {
List<Student> studentList = sqlMapper.queryWithChoose(studentValidate);
return studentList;
}
List<Student> queryWithChoose(@Param("studentValidate") StudentValidate studentValidate);
SqlMapper.xml
<select id="queryWithChoose" resultType="com.xwz.dynamicSql.Student">
select * from student
<where>
<choose>
<when test="studentValidate.id!=null">id = #{studentValidate.id}</when>
<when test="studentValidate.name!=null and studentValidate.name!=''">name = #{studentValidate.name}</when>
<when test="studentValidate.age!=null"> age = studentValidate.age</when>
<otherwise>gender = #{studentValidate.gender}</otherwise>
</choose>
</where>
</select>
postman测试数据
{
"id":"7",
"name":"",
"age":"",
"gender":"1"
}
6.foreach标签
- 和java中地foreach类似,一共有五个参数,解释如下
- collection:集合或数组
- item:集合或数组中的元素
- separator:分隔符
- open:foreach标签中所有内容的开始
- close:foreach标签中所有内容的结束
controller -> service -> mapper
@PostMapping("testForeach")
public Object addWithForeach(@RequestBody List<Student> studentList){
boolean flag = sqlService.addWithForeach(studentList);
return flag ? "success" : "fail";
}
public boolean addWithForeach(List<Student> studentList) {
int changeRow = sqlMapper.insertWithForeach(studentList);
return changeRow > 0;
}
int insertWithForeach(@Param("studentList") List<Student> studentList);
SqlMapper.xml
<insert id="insertWithForeach">
insert into student(name,age,gender,create_time)
values
<foreach collection="studentList" item="student" separator=",">
(#{student.name},#{student.age},#{student.gender},#{student.createTime})
</foreach>
</insert>
//测试二
<select id="queryWithForeach" resultType="com.xwz.dynamicSql.Student">
select * from student
where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
//测试三
<delete id="deleteWithForeach">
delete from student
where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
//测试四
<update id="updateWithSet">
<foreach collection="studentList" item="studentValidate" separator=";">
update student
<set>
<if test="studentValidate.name!=null and studentValidate.name!=''">
name = #{studentValidate.name} ,
</if>
<if test="studentValidate.age!=null and studentValidate.age>0">
age = #{studentValidate.age} ,
</if>
<if test="studentValidate.gender!=null">
gender = #{studentValidate.gender} ,
</if>
</set>
where id = #{studentValidate.id}
</foreach>
</update>
postman测试数据
[
{
"name":"jesse2",
"age":"1",
"gender":"1",
"createTime":"2024-04-03"
} ,
{
"name":"jesse3",
"age":"1",
"gender":"1",
"createTime":"2024-04-03"
} ,
{
"name":"jesse4",
"age":"1",
"gender":"1",
"createTime":"2024-04-03"
}
]
//测试二
[
223,224,225,226
]
//测试三
[
223,224,225,226
]
7.sql标签和include标签
- sql标签⽤来声明sql⽚段
- include标签⽤来将声明的sql⽚段包含到某个sql语句当中
controller -> service -> mapper
@GetMapping("testInclude")
public Object getWithInclude(){
List<Student> studentList = sqlService.getAllStudent();
return studentList;
}
public List<Student> getAllStudent() {
List<Student> studentList = sqlMapper.queryWithInclude();
return studentList;
}
List<Student> queryWithInclude();
SqlMapper.xml
<sql id="all">id,name,age,gender,create_time</sql>
<select id="queryWithInclude" resultType="com.xwz.dynamicSql.Student">
select <include refid="all"/>
from student;
</select>