数据库
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;
CREATE TABLE user
(
id BIGINT (20) NOT NULL COMMENT '主键ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
age INT (11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
role_id BIGINT (20) NOT NULL COMMENT '角色ID',
PRIMARY KEY (id)
);
CREATE TABLE role
(
id BIGINT (20) NOT NULL COMMENT '主键ID',
role_name VARCHAR(30) NULL DEFAULT NULL COMMENT '角色名',
role_describe VARCHAR(30) NULL DEFAULT NULL COMMENT '角色描述',
PRIMARY KEY (id)
);
简单CRUD 示例
@PostMapping("/test1")
public void aInsert() {
// ==> Preparing: INSERT INTO user ( name, age, email ) VALUES ( ?, ?, ? )
// ==> Parameters: 小羊(String), 3(Integer), abc@mp.com(String)
User user = new User();
user.setName("小羊");
user.setAge(3);
user.setEmail("abc@mp.com");
int a = userMapper.insert(user);
System.out.println("插入返回:"+a);
// 成功直接拿会写的 ID
System.err.println("\n插入成功 ID 为:" + user.getId());
}
@PostMapping("/test2")
public void bDelete() {
// ==> Preparing: DELETE FROM user WHERE name = ?
// ==> Parameters: fr(String)
int a =userMapper.deleteById(18);
int b = userMapper.delete(new QueryWrapper<User>()
.lambda().eq(User::getName, "fr"));
System.out.println("删除返回:"+a+"删除返回:"+b);
}
@PostMapping("/test3")
public void cUpdate() {
//UPDATE user SET email=? WHERE id=?
userMapper.updateById(new User().setId((long)1).setEmail("ab@c.c"));
//UPDATE user SET name=?, age=? WHERE id = ?
userMapper.update(new User().setName("mp"),
new UpdateWrapper<User>().lambda()
.set(User::getAge, 3)
.eq(User::getId, 1));
}
@PostMapping("/test4")
public void dSelect() {
//SELECT id,name,age,email FROM user WHERE id=?
String email =userMapper.selectById(1L).getEmail();
// User user = userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, 1));
User user = userMapper.selectOne(new QueryWrapper<User>().eq("id", 1));
System.out.println("name:"+user.getName()+" age:"+user.getAge()+" email:"+email);
}
@PostMapping("/test5")
public void orderBy() {
//SELECT id,name,age,email FROM user ORDER BY age ASC
QueryWrapper<User> ew = new QueryWrapper<>();
ew.orderByAsc("age");
List<User> userList = userMapper.selectList(ew);
userList.forEach(System.out::println);
}
@PostMapping("/test6")
public void orderByLambda() {
// SELECT id,name,age,email FROM user ORDER BY age ASC
LambdaQueryWrapper<User> lw = new LambdaQueryWrapper<>();
lw.orderByAsc(User::getAge);
List<User> userList = userMapper.selectList(lw);
userList.forEach(System.out::println);
}
条件构造器示例
@RestController
@RequestMapping("/fr/role")
public class RoleController {
@Resource
private RoleMapper roleMapper;
@Resource
private UserMapper userMapper;
@PostMapping("test1")
public void tests() {
System.out.println("----- 普通查询 ------");
List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 2L));
List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
// Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
print(plainUsers);
System.out.println("----- 带子查询(sql注入) ------");
List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>()
.inSql("role_id", "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"));
// Assert.assertEquals(plainUsers2.size(), lambdaUsers2.size());
print(plainUsers2);
System.out.println("----- 带嵌套查询 ------");
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)));
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)));
// Assert.assertEquals(plainUsers3.size(), lambdaUsers3.size());
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);
// Assert.assertNull(u4.getEmail());
}
private <T> void print(List<T> list) {
if (!CollectionUtils.isEmpty(list)) {
list.forEach(System.out::println);
}
}
}