<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.6.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-webflux</artifactId>
<version>2.6.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.assertj</groupId>
<artifactId>assertj-core</artifactId>
<version>3.21.0</version>
<scope>compile</scope>
</dependency>
</dependencies>
server:
port: 8080
spring:
application:
name: demo06
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT&useSSL=false
username: root
password: 123456
main:
allow-circular-references: true
devtools:
restart:
enabled: true
mybatis:
configuration:
map-underscore-to-camel-case: true
logging:
level:
com:
ychen:
mybatis: debug
@SpringBootApplication
@MapperScan("com.ychen.mybatis.mapper")
public class Demo06Application {
public static void main(String[] args) {
SpringApplication.run(Demo06Application.class, args);
}
}
@Data
public class User {
/**
* 主键ID
*/
private Long id;
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 邮箱
*/
private String email;
/**
* 角色ID
*/
private Long roleId;
}
@Data
public class Role {
/**
* 主键ID
*/
private Long id;
/**
* 角色名
*/
private String roleName;
/**
* 角色描述
*/
private String roleDescribe;
}
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
@Mapper
public interface RoleMapper extends BaseMapper<Role> {
}
package com.ychen.mybatis.controller;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ychen.mybatis.entity.User;
import com.ychen.mybatis.mapper.RoleMapper;
import com.ychen.mybatis.mapper.UserMapper;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @Author: chenyuanqing
* @Description:
* @Date: Created in 15:20 2022/3/5
*/
@RestController
public class TestController {
@Resource
private UserMapper userMapper;
@Resource
private RoleMapper roleMapper;
/**
* 打印的方法
* @param list
* @param <T>
*/
private <T> void print(List<T> list) {
if (!CollectionUtils.isEmpty(list)) {
list.forEach(System.out::println);
}
}
/**
* 更新单表数据
* @return
*/
@RequestMapping("/test0")
@ResponseBody
public String test0(){
// 方式一
User user = new User();
user.setAge(29);
user.setEmail("test3update@baomidou.com");
userMapper.update(user,new UpdateWrapper<User>().eq("name","Tom"));
//方式二：不创建User对象
userMapper.update(null,new UpdateWrapper<User>()
// 更新name为Tom的数据
.set("age",29).set("email","test3update@baomidou.com").eq("name","Tom"));
// 方式三：使用lambda条件构造器
userMapper.update(null,new LambdaUpdateWrapper<User>()
.set(User::getAge,29).set(User::getEmail,"test3update@baomidou.com").eq(User::getName,"Tom"));
return "success";
}
/**
* 子查询
* @return
*/
@RequestMapping("/test1")
@ResponseBody
public String test1(){
System.out.println("----- 普通查询 ------");
// 查询role_id为2的对象
List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 1L));
List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
System.out.println(plainUsers);
System.out.println(lambdaUsers);
System.out.println("----- 带子查询(sql注入) ------");
// 查询role_id为2的对象
List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>()
.inSql("role_id", "select id from role where id = 2"));
// 使用lambda表达式
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
// junit提供的断言方法，判断两者是否一致
// Assertions.assertEquals(plainUsers2.size(), lambdaUsers2.size());
// print(plainUsers2);
System.out.println("----- 带嵌套查询 ------");
// 查询role_id为2或role_id为3，同时age大于等于20的数据
List<User> plainUsers3 = userMapper.selectList(new QueryWrapper<User>()
.nested(i -> i.eq("role_id", 2L).or().eq("role_id", 3L))
.and(i -> i.ge("age", 20)));
// 使用lambda表达式
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
System.out.println("----- 自定义(sql注入) ------");
// 方式一: 查询role_id为2的数据
List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = 2"));
/* List<User> lambdaUsers4 = userMapper.selectList(new QueryWrapper<User>().lambda()
.apply("role_id = 2"));*/
// 方式二
List<User> plainUsers5 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = {0}",2));
/* List<User> lambdaUsers5 = userMapper.selectList(new QueryWrapper<User>().lambda()
.apply("role_id = {0}",2));*/
System.out.println(plainUsers4);
// 将id为4的数据，邮箱设置为null
UpdateWrapper<User> uw = new UpdateWrapper<>();
uw.set("email", null);
uw.eq("id", 4);
userMapper.update(new User(), uw);
User u4 = userMapper.selectById(4);
// junit提供的断言方法，测试是否为空
// Assertions.assertNull(u4.getEmail());
// end
return "success";
}
@RequestMapping("/test5")
@ResponseBody
public String test5() {
List<User> plainUsers3 = userMapper.selectList(new LambdaQueryWrapper<User>()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
return "success";
}
@RequestMapping("/test2")
@ResponseBody
public String lambdaQueryWrapper(){
System.out.println("----- 普通查询 ------");
// 查询role_id为2的数据
List<User> plainUsers = userMapper.selectList(new LambdaQueryWrapper<User>().eq(User::getRoleId, 2L));
// 写法2
List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
print(plainUsers);
System.out.println("----- 带子查询(sql注入) ------");
List<User> plainUsers2 = userMapper.selectList(new LambdaQueryWrapper<User>()
.inSql(User::getRoleId, "select id from role where id = 2"));
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
print(plainUsers2);
System.out.println("----- 带嵌套查询 ------");
List<User> plainUsers3 = userMapper.selectList(new LambdaQueryWrapper<User>()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
print(plainUsers3);
System.out.println("----- 自定义(sql注入) ------");
List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = 2"));
print(plainUsers4);
UpdateWrapper<User> uw = new UpdateWrapper<>();
uw.set("email", null);
uw.eq("id", 4);
userMapper.update(new User(), uw);
User u4 = userMapper.selectById(4);
// end
return "success";
}
/**
* 条件查询
* SELECT id,name,age,email,role_id FROM user
* WHERE ( 1 = 1 ) AND ( ( name = 1 AND age = 2 ) OR ( name = b AND age = 2 ) )
*/
@RequestMapping("/test3")
@ResponseBody
public String test3(){
QueryWrapper<User> w = new QueryWrapper<>();
w.and(i -> i.eq("1", 1))
.nested(i ->
i.and(j -> j.eq("name", "a").eq("age", 2))
.or(j -> j.eq("name", "b").eq("age", 2)));
userMapper.selectList(w);
return "success";
}
/**
* 排序
* SELECT id,name FROM user
* WHERE (age BETWEEN ? AND ?) ORDER BY role_id ASC,id ASC
*/
@RequestMapping("/test4")
@ResponseBody
public String test4(){
QueryWrapper<User> qw = new QueryWrapper<>();
qw.select("id","name").between("age",20,25)
.orderByAsc("role_id","id");
List<User> plainUsers = userMapper.selectList(qw);
// 方式2
LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
lwq.select(User::getId,User::getName).between(User::getAge,20,25)
.orderByAsc(User::getRoleId,User::getId);
List<User> lambdaUsers = userMapper.selectList(lwq);
return "success";
}
}
