2、核心功能
条件构造器
条件构造器也就是Wrapper，构造复杂sql语句
例1：查询出名字带o的，存款大于等于1000元人的id、username、info、balance字段
@Test
void testQueryWapper1() {
QueryWrapper<User> wrapper = new QueryWrapper<User>();
wrapper.select("id", "username", "info", "balance")
.like("username", "o")
.ge("balance", 1000);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
|
例2：更改用户名为jack的存款为1000
@Test
void testQueryWapper2() {
User user = new User();
user.setBalance(1000);
QueryWrapper<User> wrapper = new QueryWrapper<User>().eq("username", "jack");
userMapper.update(user, wrapper);
}
|
例3：更新id为1，2，4的用户的余额，扣200
@Test
void testUpdateWrapper(){
UpdateWrapper<User> wrapper = new UpdateWrapper<User>();
wrapper.setSql("balance=balance-200");
wrapper.in("id",1,2,4);
userMapper.update(null,wrapper);
}
|
LambadaWrapper:解决硬编码，把字段改为get方法传参，通过反射获取字段
@Test
void testLambdaQueryWapper1() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>();
wrapper.select(User::getId,User::getUsername,User::getInfo,User::getBalance)
.like(User::getUsername, "o")
.ge(User::getBalance, 1000);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
|
自定义sq
利用wrapper构建负载where条件，自己定义sql语句中剩下的部分
例：将id在指定范围的用户（例如1，2，4）的余额扣减指定值
test：基于wrapper构建where条件
@Test
void testCustomSqlUpdate(){
List<Long> ids= List.of(1L,2L,4L);
int amount=200;
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<User>().in(User::getId,ids);
userMapper.updateBalanceByIds(wrapper,amount);
}
|
userMapper定义方法
在mapper中用Param声明wrapper变量名称，必须是ew。@Param(“ew”)
在mapper中用Param声明wrapper变量名称，必须是ew。@Param(“ew”)
在mapper中用Param声明wrapper变量名称，必须是ew。@Param(“ew”)
public interface UserMapper extends BaseMapper<User> {
void updateBalanceByIds(@Param("ew") LambdaUpdateWrapper<User> wrapper, @Param("amount") int amount);
}
|
userMapper.xml编写使用${ew.customSqlSegment}拼接
<update id="updateBalanceByIds">
update user set balance=balance-#{amount} ${ew.customSqlSegment}
</update>
|
解决了不在业务层编写sql，遵循企业规范，同时使用mp生产sql条件的特性
Service接口
自定义接口继承Iservice接口
public interface IUserService extends IService<User> {
}
|
自定义实现类，实现接口并继承ServiceImpl类
@Service
public class IuserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
|
创建测试类
@SpringBootTest
class IUserServiceTest {
@Autowired
private IUserService userService;
@Test
void testSaveUser(){
User user = new User();
user.setUsername("Awei");
user.setPassword("123");
user.setPhone("18688990011");
user.setBalance(200);
user.setInfo("{\"age\": 24, \"intro\": \"英文老师\", \"gender\": \"female\"}");
user.setCreateTime(LocalDateTime.now());
user.setUpdateTime(LocalDateTime.now());
userService.save(user);
}
}
|
案例：基于Restful风格实现接口
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.query.UserQuery;
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 javax.websocket.server.PathParam;
import java.util.List;
@Api(tags = "用户管理接口")
@RestController
@RequiredArgsConstructor
@RequestMapping("/users")
public class UserController {
private final IUserService userService;
@PostMapping
@ApiOperation("新增用户接口")
public void saveUser(@RequestBody UserFormDTO userFormDTO) {
User user = BeanUtil.copyProperties(userFormDTO, User.class);
userService.save(user);
}
@DeleteMapping("/{id}")
@ApiOperation("删除用户接口")
public void deleteUserbyId(@ApiParam("用户id") @PathVariable("id") long id) {
userService.removeById(id);
}
@GetMapping("/{id}")
@ApiOperation("根据id查询用户接口")
public UserVO queryUserbyId(@ApiParam("用户id") @PathVariable("id") long id) {
User user = userService.getById(id);
return BeanUtil.copyProperties(user, UserVO.class);
}
@GetMapping()
@ApiOperation("根据id批量查询用户接口")
public List<UserVO> queryUserbyId(@ApiParam("用户id集合") @RequestParam("ids") List<Long> ids) {
List<User> users = userService.listByIds(ids);
return BeanUtil.copyToList(users, UserVO.class);
}
@PutMapping("/{id}/deduction/{money}")
@ApiOperation("根据id扣减余额接口")
public void deductMoneyById(
@ApiParam("用户id") @PathVariable("id") long id,
@ApiParam("扣减金额") @PathVariable("money") Integer money) {
userService.deductMoneyById(id, money);
}
@GetMapping("/list")
@ApiOperation("根据复杂条件查询用户接口")
public List<UserVO> queryUsers(UserQuery userQuery) {
List<User> users = userService.queryUsers(
userQuery.getName(),
userQuery.getStatus(),
userQuery.getMinBalance(),
userQuery.getMaxBalance()
);
return BeanUtil.copyToList(users, UserVO.class);
}
}
|
Servce
package com.itheima.mp.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.itheima.mp.domain.po.User;
import java.util.List;
public interface IUserService extends IService<User> {
void deductMoneyById(long id, Integer money);
List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance);
}
|
ServiceImpl
package com.itheima.mp.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.itheima.mp.domain.po.User;
import com.itheima.mp.mapper.UserMapper;
import com.itheima.mp.service.IUserService;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class IuserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Override
public void deductMoneyById(long id, Integer money) {
User user = getById(id);
if (user.getStatus() == 2 || user == null) {
throw new RuntimeException("用户状态不正确");
}
if (user.getBalance() < money) {
throw new RuntimeException("用户余额不足");
}
int remainBanlance=user.getBalance()-money;
lambdaUpdate()
.set(User::getBalance,remainBanlance)
.set(remainBanlance==0,User::getStatus,2)
.eq(User::getId,id)
.eq(User::getBalance,user.getBalance())
.update();
}
@Override
public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {
return lambdaQuery()
.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();
}
}
|
可以发现lambda对于复杂sql语句节省了很多代码，强烈推荐
对于基础业务（CURD）直接在controller层就已经完成了代码的编写（后面的由mp自动实现）
