条件构造器
支持各种复杂的where条件,可以满足日常开发的所有需求
查询测试
查询出名字中带o的,存款大于等于1000元的人的id 用户名
@Test
void testQueryWrapper(){
//1.构造查询条件 查询出名字中带o的,存款大于等于1000元的人的id 用户名
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.select("id","username","info","balance")
.like("username","o")
.ge("balance",1000);//greater equal
//2.查询
List<User> users = userMapper.selectList(wrapper);
for (User user : users) {
System.out.println(user);
}
}
更新测试
更新用户名为jack的用户的余额为2000
@Test
void testUpdateByQueryWrapper(){
//更新的数据
User user =new User();
user.setBalance(2000);//给user赋值 把balance赋值为2000
//更新的条件
QueryWrapper<User> wrapper = new QueryWrapper<User>()
.eq("username","jack");
//user:要更新的数据 wrapper:更新的where条件
userMapper.update(user,wrapper);
}
更新id为1、2、4的用户的余额
@Test
void testUpdateWrapper(){
//List.of() 是一个静态方法,它接受可变数量的参数,并返回一个包含这些参数的不可变列表。
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);
}
避免硬编码
@Test
void testLambdaQueryWrapper(){
//1.构造查询条件 查询出名字中带o的,存款大于等于1000元的人的id 用户名
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>()
.select(User::getId,User::getUsername,User::getInfo,User::getBalance)
.like(User::getUsername,"o")//原理是获取到方法通过反射获取到对象 避免硬编码
.ge(User::getBalance,1000);//greater equal
//2.查询
List<User> users = userMapper.selectList(wrapper);
for (User user : users) {
System.out.println(user);
}
}
自定义SQL
我们可以利用MyBatisPlus的Wrapper来构建复杂的Where条件然后自己定义sql语句中剩下的部分
(半自动)
1.基于wrapper构建where条件
@Test
void testCustomSqlUpdate(){
//更新条件
List<Long> ids=List.of(1L,2L,4L);
int amount=200;
//2.定义条件
UpdateWrapper<User> wrapper=new UpdateWrapper<User>()
.in("id",ids);
//3.调用自定义sql方法
userMapper.updateBalanceById(wrapper,amount);
}
2.在mapper方法参数中用Param注解声明wrapper变量名称,必须是ew@Param(“ew”)
void updateBalanceById(@Param("ew") UpdateWrapper<User> wrapper,@Param("amount") int amount);
3.自定义sql,并使用wrapper条件
<update id="updateBalanceById">
update user set balance=balance-#{amount} ${ew.customSqlSegment}
</update>
Service接口
ctrl+shift+t快速生成测试类
public interface IUserService extends IService<User> {
}
实现类
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
测试
@SpringBootTest
class IUserServiceTest {
@Autowired
private IUserService userService;
@Test
void testSaveUser() {
User user = new User();
user.setId(7L);
user.setUsername("tom");
user.setPassword("123");
user.setPhone("18688990016");
user.setBalance(200);
user.setInfo("{\"age\": 24, \"intro\": \"历史老师\", \"gender\": \"female\"}");
user.setCreateTime(LocalDateTime.now());
user.setUpdateTime(LocalDateTime.now());
userService.save(user);//增
}
@Test
void testQuery(){
List<User> users =userService.listByIds(List.of(1L,2L,4L));//查
for (User user : users) {
System.out.println(user);
}
}
}
IService开发基础业务接口案例
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.AllArgsConstructor;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;
import java.util.List;
@Api(tags = "用户管理接口")
@RequestMapping("/users")
@RestController
@RequiredArgsConstructor//只会在一些一开始就需要初始化的变量中做构造
public class UserController {
private final IUserService userService;//代表是一个常量在初始化的过程中
@ApiOperation("新增用户接口")
@PostMapping
public void saveUser(@RequestBody UserFormDTO userDTO){
//1.把DTO(少)拷贝到PO(多)
User user = new User();
BeanUtils.copyProperties(userDTO,user);
//2.新增
userService.save(user);
}
@ApiOperation("删除用户接口")
@DeleteMapping("{id}")
public void deleteUserById(@ApiParam("用户id") @PathVariable("id") Long id){
userService.removeById(id);
}
@ApiOperation("根据id查询用户接口")
@GetMapping("{id}")
public UserVO queryUserById(@ApiParam("用户id") @PathVariable("id") Long id){
User user = userService.getById(id);
UserVO userVO = new UserVO();
BeanUtils.copyProperties(user,userVO);
return userVO;
}
@ApiOperation("根据id批量查询用户接口")
@GetMapping
public List<UserVO> queryUserByIds(@ApiParam("用户id集合") @RequestParam List<Long> ids){
List<User> users = userService.listByIds(ids);
// List<UserVO> userVOs = new ArrayList<>();
// for(User user : users) {
// UserVO userVO = new UserVO();
// BeanUtils.copyProperties(user, userVO);
// userVOs.add(userVO);
// }
List<UserVO> userVOS = BeanUtil.copyToList(users, UserVO.class);
return userVOS;
}
}
IService开发复杂业务接口案例
controller层
@ApiOperation("扣减用户余额接口")
@DeleteMapping("/{id}/deduction/{money}")
public void deductMoneyById(@ApiParam("用户id") @PathVariable("id") Long id,
@ApiParam("扣减的金额") @PathVariable("money") Integer money){
userService.deductBalance(id,money);
}
service层
public interface IUserService extends IService<User> {
void deductBalance(Long id, Integer money);
}
实现类
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Override
public void deductBalance(Long id, Integer money) {
//1.查询用户
User user=getById(id);
//baseMapper
//2.校验用户状态
if(user==null||user.getStatus()==2){
throw new RuntimeException("用户状态异常!");
}
//3.校验余额是否充足
if(user.getBalance()<money){
throw new RuntimeException("用户余额不足!");
}
//4.扣减余额
baseMapper.deductBalance(id,money);
}
}
mapper
@Update("UPDATE tb_user SET balance=balance-#{money} where id=#{id}")
void deductBalance(@Param("id") Long id,@Param("money") Integer money);
alt+8-> add service->configuraton Type->SpringBoot
IService的lambda方法
根据复杂条件批量查询用户接口
@Override
public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {
List<User> list = lambdaQuery()
//等价于 if name!=null username like #{name}
.like(name != null, User::getUsername, name)
.eq(status != null, User::getStatus, status)
.ge(minBalance != null, User::getBalance, minBalance)
.le(maxBalance != null, User::getBalance, maxBalance)
.list();
return list;
}
IService的批量新增
jdbc配置中加入
rewriteBatchedStatements=true