MybatisPlus核心功能
条件构造器
基于QueryWrapper的查询
需求:
查询出名字中带o的,存款大于等于500元的人的id、username、info、balance字段
@Test
public void testQueryWrapper() {
//构建查询条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.select("id", "username", "info", "balance")
.like("username", "o")
.ge("balance", 500);
//查询
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
更新用户名为jack的用户的余额为2000
@Test
public void testUpdateByQueryWrapper() {
//要新的数据
User user = new User();
user.setBalance(2000);
//要更新的条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.eq("username","jack");
userMapper.update(user,wrapper);
}
}
需求:更新id为1,2,4的用户的余额,扣200
@Test
public void testUpdateQueryWrapper() {
List<Long> ids = List.of(1L,2L,4L);
UpdateWrapper<User> wrapper = new UpdateWrapper<User>()
.setSql("balance = balance - 200")
.in("id",ids);
userMapper.update(null,wrapper);
}
条件构造器的用法:
- QueryWrapper和LambdaQueryWrapper通常用来构建select、delete、update的where条件部分
- UpdateWrapper和LambdaUpdateWrapper通常只有在set语句比较特殊才使用
- 尽量使用LambdaQueryWrapper和LambdaUpdateWrapper,避免硬编码
自定义SQL
我们可以利用MyBatisPlus的Wrapper来构建复杂的Where条件,然后自己定义SQL语句中剩下的部分。
基于Wrapper构建where条件
List<Long> ids = List.of(1L, 2L, 4L);
int amount = 200;
// 1.构建条件
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>().in(User::getId, ids);
// 2.自定义SQL方法调用
userMapper.updateBalanceByIds(wrapper, amount);
在mapper方法参数中用Param注解声明wrapper变量名称,必须是ew
void updateBalanceByIds(@Param("ew") LambdaQueryWrapper<User> wrapper, @Param("amount") int amount);
自定义SQL,并使用Wrapper条件
<update id="updateBalanceByIds">
UPDATE tb_user SET balance = balance - #{amount} ${ew.customSqlSegment}
</update>
需求:
将id在指定范围的用户(例如1、2、4 )的余额扣减指定值
@Test
public void testCustomSqlUpdate() {
//更新条件
List<Long> ids = List.of(1L, 2L, 4L);
int amount = 200;
//定义条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.in("id",ids);
//调用自定义的sql方法
userMapper.updateBalanceByIds(wrapper,amount);
}
}
IService接口
![外
MP的Service接口使用流程
- 自定义Service接口继承IService接口
public interface IUserService
extends IService<User> {}
- 自定义Service实现类,实现自定义接口并继承ServiceImpl类
public class UserServiceImpl extends ServiceImpl<UserMapper, User>implements IUserService {}
基本用法
导入依赖
<!--swagger-->
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-openapi2-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
配置环境
knife4j:
enable: true
openapi:
title: 用户管理接口文档
description: "用户管理接口文档"
email: zhanghuyi@itcast.cn
concat: xxx
url: https://www.itcast.cn
version: v1.0.0
group:
default:
group-name: default
api-rule: package
api-rule-resources:
- com.itheima.mp.controller
package com.itheima.mp.controller;
import cn.hutool.core.bean.BeanUtil;
import com.itheima.mp.domain.dto.UserFormDTO;
import com.itheima.mp.domain.po.User;
import com.itheima.mp.domain.vo.UserVO;
import com.itheima.mp.service.IUserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@Api(tags = "用户管理接口")
@RestController
@RequestMapping("/users")
@RequiredArgsConstructor
public class UserController {
private final IUserService userService;
@ApiOperation("新增用户接口")
@PostMapping
public void saveUser(@RequestBody UserFormDTO userFormDTO) {
//DTO拷贝到PO
User user = BeanUtil.copyProperties(userFormDTO, User.class);
//新增
boolean result = userService.save(user);
if (!result){
throw new RuntimeException("新增用户失败");
}else {
System.out.println("新增用户成功");
}
}
@ApiOperation("根据id删除用户接口")
@DeleteMapping
public void deleteUserById(@ApiParam("用户id") @PathVariable("id") Long id) {
//删除
boolean result = userService.removeById(id);
if (!result){
throw new RuntimeException("删除用户失败");
}else {
System.out.println("删除用户成功");
}
}
@ApiOperation("根据id查询用户接口")
@GetMapping("{id}")
public UserVO selectUserById(@ApiParam("用户id") @PathVariable("id") Long id) {
//根据id查询
User user = userService.getById(id);
if (user == null){
throw new RuntimeException("查询用户失败");
}else {
return BeanUtil.copyProperties(user, UserVO.class);
}
}
@ApiOperation("根据id批量查询用户接口")
@GetMapping
public List<UserVO> selectUserByIds(@ApiParam("用户id集合") @RequestParam("ids") List<Long> ids) {
//批量查询
List<User> users = userService.listByIds(ids);
if (users == null){
throw new RuntimeException("查询用户失败");
}else {
return BeanUtil.copyToList(users, UserVO.class);
}
}
@ApiOperation("扣减用户金额接口")
@PutMapping("/{id}/deduction/{money}")
public void deductMoneyById(
@ApiParam("用户id") @PathVariable("id") Long id,
@ApiParam("扣减的金额") @PathVariable("money") Integer money) {
userService.deductMoneyById(id,money);
}
}
public interface IUserService extends IService<User> {
void deductMoneyById(Long id, Integer money);
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Override
public void deductMoneyById(Long id, Integer money) {
//先查询
// User user = baseMapper.selectById(id);
User user = getById(id);
//检验用户状态
if (user.getStatus() == 2 || user == null ) {
throw new RuntimeException("用户已被冻结");
}
//校验余额是否充足
if (user.getBalance() < money) {
throw new RuntimeException("余额不足");
}
//扣减余额
baseMapper.deductMoneyById(id,money);
// user.setBalance(user.getBalance() - 200);
}
}
@Mapper
public interface UserMapper extends BaseMapper<User> {
@Update("update user set balance = balance - #{money} where id = #{id}")
void deductMoneyById(@Param("id") Long id,@Param("money") Integer money);
}
需求:实现一个根据复杂条件查询用户的接口,查询条件如下:
name:用户名关键字,可以为空
status:用户状态,可以为空
minBalance:最小余额,可以为空
maxBalance:最大余额,可以为空
@Override
public List<User> queryUsers(String name, Integer status, Integer maxBalance, Integer minBalance) {
List<User> list = lambdaQuery()
.like(name != null, User::getUsername, name)
.eq(status != null, User::getStatus, status)
.lt(maxBalance != null, User::getBalance, maxBalance)
.gt(minBalance != null, User::getBalance, minBalance)
.list();
return list;
}
List<User> queryUsers(String name, Integer status, Integer maxBalance, Integer minBalance);
@ApiOperation("根据复杂条件查询接口")
@GetMapping("/list")
public List<UserVO> queryUsers(UserQuery query){
List<User> users = userService.queryUsers(query.getName(),query.getStatus(),query.getMaxBalance(),query.getMinBalance());
return BeanUtil.copyToList(users, UserVO.class);
}
query
@Data
@ApiModel(description = "用户查询条件实体")
public class UserQuery {
@ApiModelProperty("用户名关键字")
private String name;
@ApiModelProperty("用户状态:1-正常,2-冻结")
private Integer status;
@ApiModelProperty("余额最小值")
private Integer minBalance;
@ApiModelProperty("余额最大值")
private Integer maxBalance;
}
需求:改造根据id修改用户余额的接口,要求如下
完成对用户状态校验
完成对用户余额校验
如果扣减后余额为0,则将用户status修改为冻结状态(2)
@Override
@Transactional
public void deductMoneyById(Long id, Integer money) {
//先查询
// User user = baseMapper.selectById(id);
User user = getById(id);
//检验用户状态
if (user.getStatus() == 2 || user == null) {
throw new RuntimeException("用户已被冻结");
}
//校验余额是否充足
if (user.getBalance() < money) {
throw new RuntimeException("余额不足");
}
//扣减余额
// baseMapper.deductMoneyById(id,money);
// user.setBalance(user.getBalance() - 200);
int remainBalance = user.getBalance() - money;
lambdaUpdate()
.set(User::getBalance, remainBalance)
.set(remainBalance == 0, User::getStatus, 2)
.eq(User::getId, id)
//乐观锁
.eq(User::getBalance, user.getBalance())
.update();
}
实现批量自增
需求:
批量插入10万条用户数据,
并作出对比:普通for循环插入IService的批量插入
开启rewriteBatchedStatements=true参数
// 构建一个User对象的辅助方法,用于测试批量插入
private User buildUser(int i) {
User user = new User();
user.setUsername("user_" + i);
user.setPassword("123");
user.setPhone("" + (18688190000L + i));
user.setBalance(2000);
user.setInfo("{\"age\": 24, \"intro\": \"英文老师\", \"gender\": \"female\"}");
user.setCreateTime(LocalDateTime.now());
user.setUpdateTime(user.getCreateTime());
return user;
}
/**
* 测试逐个插入用户的方法,效率低
*/
@Test
void testSaveOneByOne() {
long b = System.currentTimeMillis();
// 循环调用userService的保存方法,逐个插入用户
for (int i = 1; i < 100000; i++) {
userService.save(buildUser(i));
}
long e = System.currentTimeMillis();
System.out.println("耗时:" + (e - b));
}
/**
* 测试批量插入用户的方法,效率高
*/
@Test
void testSaveBatch() {
// 准备一个容量为1000的集合
List<User> list = new ArrayList<>(1000);
long b = System.currentTimeMillis();
// 循环构建用户并添加到集合中
for (int i = 1; i <= 100000; i++) {
// 添加一个User
list.add(buildUser(i));
// 每1000条批量插入一次
if (i % 1000 == 0) {
userService.saveBatch(list);
// 清空集合,准备下一批数据
list.clear();
}
}
long e = System.currentTimeMillis();
System.out.println("耗时:" + (e - b));
}
//yaml
url: jdbc:mysql://localhost:3306/mp?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
rewriteBatchedStatements=true在驱动加上这个,mysql会把多个数据库语句合并成一个