JavaSpringBoot中，Mybatis plus 语法以及多表分页展示

春来我不先开口，哪个虫儿敢作声?

已于 2024-03-08 16:16:55 修改

阅读量489

点赞数 2

分类专栏：有关Java项目的参考文章文章标签： mybatis java 数据库

于 2024-02-19 15:43:53 首次发布

本文链接：https://blog.csdn.net/weixin_67573348/article/details/136162647

版权

有关Java项目的参考文章专栏收录该内容

15 篇文章 0 订阅

订阅专栏

Myabtis plus语法展示

@Mapper
public interface UserMapper extends BaseMapper<User> {
    
}

public interface UserService extends IService<User> {

}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    @Autowired
    private UserMapper userMapper;
}

@RestController
@RequestMapping("/test/user")
public class UserController {
    @Autowired
    private UserService userService;
}

基础的增删改查

查询所有，参数为构造器 : userMapper.selectList(null) 

根据id添加数据，参数为实体类对象：  userMapper.insert(user);

根据id修改数据，参数为实体类对象:   userMapper.updateById(user)

根据id删除数据，参数为int / Long :  userMapper.deleteById(5L)

根据id查询数据，餐位数int / Long ： userMapper.selectById(4L)

分页查询

service层

    IPage<User> getUsersByName(Page<User> page, String name);

serviceimpl

    @Override
    public R<IPage<User>> getUsersByNamec(Page<User> page, String name) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //条件
        queryWrapper.like(StringUtils.isNotBlank(name), "name", name);

        // 分页
        Page<User> userPage = userMapper.selectPage(page, queryWrapper);
//        List<User> records = page.getRecords();
        return R.success(userPage);
    }

controller

    // 根据姓名分页查询用户
    @GetMapping("/getUsersByName")
    public IPage<User> getUsersByName(@RequestParam(defaultValue = "1") Long current,
                                      @RequestParam(defaultValue = "2") Long size,
                                      @RequestParam(required = false) String name) {
        // 构建分页对象
        Page<User> page = new Page<>(current, size);
        // 调用服务方法进行分页查询
        return userService.getUsersByName(page, name);
    }

语法指导


     * mp复杂查询
     * eq 就是 equal等于
     * ne就是 not equal不等于
     *
     * gt 就是 greater than大于
     * ge 就是 greater than or equal 大于等于
     * lt 就是 less than小于
     * le 就是 less than or equal 小于等于
     *
     * in 就是 in 包含（数组）
     * between 就是 在2个条件之间(包括边界值)
     * notBetween
     *
     * isNull 就是 等于null
     * isNotNull 不等于
     *
     * like 就是 模糊查询
     * notLike
     * likeLeft
     * likeRight
     *
     * orderBy
     * orderByDesc
     * orderByAsc

删除操作

批量删除： userMapper.deleteBatchIds(Arrays.asList(8, 9, 10));

简单删除:

    //简单条件删除
    @Test
    public void testDeleteByMap() {
        HashMap<String, Object> map = new HashMap<>();
        //  name,age 为字段 ---> lucy,20 为值
        map.put("name", "lucy");
        map.put("age", 20);
        int result = userMapper.deleteByMap(map);
        System.out.println(result);
    }

条件操作

 // 查询年龄  > 21 的  
  public void testQuery() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper
                .ge("age", 21);
        List<User> users = userMapper.selectList(queryWrapper);
        System.out.println(users);
    }


// 查询name 是 Tom的
    @Test
    public void testSelectOne() {
        QueryWrapper<User>queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name", "Tom");
        User user = userMapper.selectOne(queryWrapper);//只能返回一条记录，多余一条则抛出异常
        System.out.println(user);
    }


// 查询年龄在20 ~ 30之间的
    @Test
    public void testSelectCount() {
        QueryWrapper<User>queryWrapper = new QueryWrapper<>();
        queryWrapper.between("age", 20, 30);
        Integer count = userMapper.selectCount(queryWrapper); //返回数据数量
        System.out.println(count);
    }

查询条件构建：使用 QueryWrapper 对象 queryWrapper 构建查询条件。在这个例子中，通过 .select("name", "age") 指定要查询的字段，.like("name", "e") 表示名字中包含字母 "e"，.likeRight("email", "5") 表示邮箱以数字 "5" 结尾。

    @Test
    public void testSelectMaps() {
        QueryWrapper<User>queryWrapper = new QueryWrapper<>();
        queryWrapper
                .select("name", "age")
                .like("name", "y")
                .likeRight("email", "5");
        List<Map<String, Object>>maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
        maps.forEach(System.out::println);
    }

Mybatis plus多表分页查询

实体类

info表关联oder表。

@Data
@TableName("sc_info")
public class scInfo extends BaseDomain {
    @TableId(type = IdType.AUTO)
    private Long id;
    private Integer orderId;
    private String carrier;
    private Date shippingDate;
    private String shippingStatus;
    private String shippingNotes;
    private Date createTime;
    private String createBy;
    private Date updateTime;
    private String updateBy;
    private Integer isDel;
    @TableField(exist = false)  // 不是数据库的字段
    private ScOrders OrdersName;
}

@Data
public class ScOrders implements Serializable {
    private Integer id;
    private Integer userId;
    private Integer productsId;
    private Double totalAmount;
    private String shippingAddress;
    private Integer shippingStatus;
    private String orderNotes;
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date createTime;
    private String createBy;
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date updateTime;
    private String updateBy;
    private Integer isDel;
}

service层

    IPage<scInfo> selectInfo(scInfo scInfo, Pageable pageable);

serviceImpl层

    private final scInfoMapper scInfoMapper;

    private final ScOrdersMapper scOrdersMapper;

    @Override
    public IPage<scInfo> selectInfo(scInfo scInfo, Pageable pageable) {
        Page<scInfo> page = new Page<>(pageable.getPageNumber(), pageable.getPageSize());
        //构造器
        QueryWrapper<scInfo> wrapper = new QueryWrapper<>();
        //条件
        wrapper.eq("is_del", 0);
        wrapper.like(!Objects.isNull(scInfo.getOrdersName().getShippingAddress()), "shopping_address", scInfo.getOrdersName().getShippingAddress());
          // 分页
        Page<scInfo> scInfoPage = scInfoMapper.selectPage(page, wrapper);
        // 设置order数据到info
        for (scInfo record : scInfoPage.getRecords()) {
            LambdaQueryWrapper<ScOrders> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(ScOrders::getId, record.getOrderId());
            ScOrders scOrders = scOrdersMapper.selectOne(lambdaQueryWrapper);
            record.setOrdersName(scOrders);
        }

        return scInfoPage;
    }

controller层

    @GetMapping("/selectInfo")
    public ResponseEntity<IPage<scInfo>> selectInfo(scInfo scInfo, Pageable pageable) {
        return new ResponseEntity<>(scInfoService.selectInfo(scInfo, pageable), HttpStatus.OK);
    }

分页优化

    private final scInfoMapper scInfoMapper;

    private final ScOrdersMapper scOrdersMapper;

    @Override
    public IPage<scInfo> selectInfo(scInfo scInfo, Pageable pageable) {
        Page<scInfo> page = new Page<>(pageable.getPageNumber(), pageable.getPageSize());
        QueryWrapper<scInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("is_del", 0)
                .like(!Objects.isNull(scInfo.getOrdersName().getShippingAddress()), "shopping_address", scInfo.getOrdersName().getShippingAddress());

        Page<scInfo> scInfoPage = scInfoMapper.selectPage(page, wrapper);

        scInfoPage.getRecords().forEach(record -> {
            LambdaQueryWrapper<ScOrders> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(ScOrders::getId, record.getOrderId());
            ScOrders scOrders = scOrdersMapper.selectOne(lambdaQueryWrapper);
            record.setOrdersName(scOrders);
        });

        return scInfoPage;
    }

Mybatis plus的一套增删改查

service层

public interface scInfoService {

    IPage<scInfo> selectInfo(scInfo scInfo, Pageable pageable);

    ResponseEntity<scInfo> getByIdInfo(Long id);

    ResponseEntity<String> saveOrUpdateInfo(scInfo scInfo);

    ResponseEntity<String>  removeByIdInfo(Long id);

    ResponseEntity<String>  insertInfo(scInfo scInfo);

}

ServiceImpl层

@Service
@AllArgsConstructor
public class scInfoServiceImpl extends BaseServiceImpl<scInfoMapper, scInfo> implements scInfoService {

    private final scInfoMapper scInfoMapper;

    private final ScOrdersMapper scOrdersMapper;

    @Override
    public IPage<scInfo> selectInfo(scInfo scInfo, Pageable pageable) {
        Page<scInfo> page = new Page<>(pageable.getPageNumber(), pageable.getPageSize());
        QueryWrapper<scInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("is_del", 0)
                .like(!Objects.isNull(scInfo.getOrdersName().getShippingAddress()), "shopping_address", scInfo.getOrdersName().getShippingAddress());

        Page<scInfo> scInfoPage = scInfoMapper.selectPage(page, wrapper);

        scInfoPage.getRecords().forEach(record -> {
            LambdaQueryWrapper<ScOrders> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(ScOrders::getId, record.getOrderId());
            ScOrders scOrders = scOrdersMapper.selectOne(lambdaQueryWrapper);
            record.setOrdersName(scOrders);
        });

        return scInfoPage;
    }

    @Override
    public ResponseEntity<scInfo> getByIdInfo(Long id) {

        scInfo result = scInfoMapper.selectById(id);
        return result != null
                ? ResponseEntity.ok(result) // 如果查询结果不为 null，返回 OK 状态码和查询到的数据
                : ResponseEntity.status(HttpStatus.NOT_FOUND).body(null); // 如果查询结果为 null，返回 404 NOT FOUND 状态码
    }

    @Override
    public ResponseEntity<String> saveOrUpdateInfo(scInfo scInfo) {
        int i = scInfoMapper.updateById(scInfo);
        return i > 0 ? ResponseEntity.ok("更新成功") : ResponseEntity.status(HttpStatus.BAD_REQUEST).body("更新失败");
    }

    @Override
    public ResponseEntity<String> removeByIdInfo(Long id) {
        int i = scInfoMapper.deleteById(id);
        return i > 0 ? ResponseEntity.ok("删除成功") : ResponseEntity.status(HttpStatus.NOT_FOUND).body("删除失败");
    }

    @Override
    public ResponseEntity<String> insertInfo(scInfo scInfo) {
        int i = scInfoMapper.insert(scInfo);
        return i > 0 ? ResponseEntity.ok("添加成功") : ResponseEntity.status(HttpStatus.NOT_FOUND).body("添加失败");
    }


}

controller

@RestController
@RequestMapping("/info")
@AllArgsConstructor
public class scInfoController {

    private final scInfoService scInfoService;

    @PostMapping("/update")
    public ResponseEntity<String> saveOrUpdateInfo(@RequestBody scInfo scInfo) {
        return scInfoService.saveOrUpdateInfo(scInfo);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<String> removeByIdInfo(@PathVariable Long id) {
        return scInfoService.removeByIdInfo(id);
    }

    @GetMapping("/{id}")
    public ResponseEntity<scInfo> getByIdInfo(@PathVariable Long id) {
        return scInfoService.getByIdInfo(id);
    }
    
    
    @GetMapping("/selectInfo")
    public ResponseEntity<IPage<scInfo>> selectInfo(scInfo scInfo, Pageable pageable) {
        return new ResponseEntity<>(scInfoService.selectInfo(scInfo, pageable), HttpStatus.OK);
    }

    @PostMapping("/insert")
    public ResponseEntity<String> insertInfo(@RequestBody scInfo scInfo) {
        return scInfoService.insertInfo(scInfo);
    }


}

plus语法优化

    @Override
    public IPage<ScPopularize> selectInfo(ScPopularize scPromotions, Pageable pageable) {
        // 直接在selectPage方法中构建QueryWrapper，并执行分页查询
        Page<ScPopularize> scPopularizePage = scPopularizeMapper.selectPage(new Page<>(pageable.getPageNumber(), pageable.getPageSize()), new QueryWrapper<ScPopularize>()
                .eq("is_del", 0) // 确保查询未被删除的记录
                .like(Objects.nonNull(scPromotions.getPopularizeDesc()), "popularize_desc", scPromotions.getPopularizeDesc())); // 如果popularizeDesc非空，则添加模糊查询条件

        scPopularizePage.getRecords().forEach(record -> {
            record.setUser(scPopularizeMapper.getUserById(record.getUserId()));
            record.setScAdverts(scAdvertsMapper.selectById(scPromotions.getAdvertsId()));
        });

        return scPopularizePage;
    }

春来我不先开口，哪个虫儿敢作声?

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JavaSpringBoot中，Mybatis plus 语法以及多表分页展示

查询条件构建：使用 QueryWrapper 对象 queryWrapper 构建查询条件。在这个例子中，通过 .select("name", "age") 指定要查询的字段，.like("name", "e") 表示名字中包含字母 "e"，.likeRight("email", "5") 表示邮箱以数字 "5" 结尾。
复制链接

扫一扫