• 【java】【MyBatisPlus】【四】【完】MyBatisPlus一些实战总结（枚举、翻页、sql、组合条件、自增主键、逻辑删除）

    目录

    一、枚举

    1、数据库type字段是Integer 类型枚举

    2、创建一个该字段的枚举类 TypeEnum

     3、修改实体类

    4、配置文件新增mybatis-plus的配置

    5、检验：

    5.1 查询显示

    5.3 库里验证 

    二、自增主键不是id字段处理

    三、逻辑删除字段不是delete字段处理

    1、实体加注解

    2、yml配置文件新增配置

    四、单表查询 

    1、使用mybatis-plus自带的lambdaQueryWrapper条件进行查询

    1.1 controller

    1.2 servcie

    1.3 serviceImpl

    1.4 mapper

    2、使用mybatis-plus自带的lambdaQueryWrapper条件进行+page进行分页查询

    2.1 PageBean

    2.2 controller

     2.3 service

    2.4 servicrImpl

    2.5 mapper

    五、多表查询

    1、使用sql语句进行多表查询

    1.1 UserRoleDto接收返回数据

    1.2 controller

    1.3 service

    1.4 servcieImpl

    1.5 mapper

    1.6 xml

    2、翻页+组合条件查询

    2.1 controller

    2.2 service

    2.3 serviceImpl

    2.4 mapper

    2.5 xml

    六、事务处理

    1、serviceImpl

    2、yml配置事务日志

    七、判重

    1、数据库设置唯一判重

    2、代码判重 

    2.1 新增判重

    2.2 修改判重

    八、日期范围查询 

    1、controller

    2、service

    3、serviceImpl

    4、mapper

    九、POST请求封装DTO查询

    1、POST接口请求传参

     2、封装DTO

    3、controller

    4、service

    5、serviceImpl

    6、mapper

    前言：项目实战过程当中，一些总结，例如枚举应用、翻页、单表、多表查询、翻页、自增主键、逻辑删除、判重等

    一、枚举

    1、数据库type字段是Integer 类型枚举

    type字段 枚举用热类型：1-高温水；2-低温水；3-蒸汽；

    2、创建一个该字段的枚举类 TypeEnum

    1. package com.bocai.enums;
    2.  
    3.  
    4. import com.baomidou.mybatisplus.annotation.EnumValue;
    5. import com.fasterxml.jackson.annotation.JsonValue;
    6. import lombok.*;
    7.  
    8. /**
    9.  * a表的type字段 枚举用热类型：1-高温水；2-低温水；3-蒸汽；
    10.  * 注意配置文件yml有配置
    11.  */
    12. @AllArgsConstructor
    13. @NoArgsConstructor
    14. @Getter
    15. public enum TypeEnum {
    16.     HIGHTEMPERATUREWATER(1,"高温水"),
    17.     LOWTEMPERATUREWATER(2,"低温水"),
    18.     STEAM(3,"蒸汽")
    19.     ;
    20.  
    21.     @EnumValue //将注解标注的数值存储到数据库中
    22.     private  Integer hottype;
    23.     @JsonValue
    24.     private  String desc;
    25.  
    26.  
    27.  
    28.  
    29. }

    上面2个注解很重要：@EnumValue //将注解标注的数值存储到数据库中

    @JsonValue //在页面显示，不写这个就先森上面的枚举STEAM、LOWTEMPERATUREWATER、HIGHTEMPERATUREWATER

     3、修改实体类

    1. package com.bocai.pojo;
    2.  
    3. import com.baomidou.mybatisplus.annotation.IdType;
    4. import com.baomidou.mybatisplus.annotation.TableField;
    5. import com.baomidou.mybatisplus.annotation.TableId;
    6. import com.baomidou.mybatisplus.annotation.TableName;
    7. import java.io.Serializable;
    8. import java.math.BigDecimal;
    9. import java.time.LocalDateTime;
    10.  
    11. import com.bocai.enums.TypeEnum;
    12. import lombok.Data;
    13.  
    14. /**
    15.  * 
    16.  * @TableName inhousing
    17.  */
    18. @TableName(value ="inhousing")
    19. @Data
    20. public class Inhousing implements Serializable {
    21.     /**
    22.      * 
    23.      */
    24.     @TableId(type = IdType.AUTO)
    25.     private Integer uniqueid;
    26.   
    27.  
    28.     /**
    29.      * 用热类型：1-高温水；2-低温水；3-蒸汽；
    30.      */
    31.     private TypeEnum hottype;
    32.  
    33.    
    34.  
    35.     @TableField(exist = false)
    36.     private static final long serialVersionUID = 1L;
    37. }

    4、配置文件新增mybatis-plus的配置

    1. mybatis-plus:
    2.   configuration:
    3.     map-underscore-to-camel-case: true # 在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    4.     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # ?????sql
    5.     default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler  # 配置全局枚举处理器，好像还有说json的
    6.   global-config:
    7.     db-config:
    8.       id-type: auto   # 数据库id生产规则全局 配置 # ASSIGN_ID雪花算法，数据库id建议使用Long类型
    9.       logic-delete-field: deleted # 全局配置逻辑删除字段名
    10.       logic-delete-value: 0 # 全局配置# 逻辑已删除值(默认为 1)这里因为我是反的所以改成0
    11.       logic-not-delete-value: 1  # 逻辑未删除值(默认为 0）这里因为我是反的所以改成1
    12.       # table-prefix: tbl_    # 数据库表前缀全局配置
    13.     banner: false   # 关闭控制台mybatis-plus的logo
    14. #  type-enums-package: com.bocai.enums   # 扫描通用枚举包 或者使用上面那个枚举全局配置

    有两种方式：1、 default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler  # 配置全局枚举处理器，好像还有说json的

    2、#  type-enums-package: com.bocai.enums   # 扫描通用枚举包 或者使用上面那个枚举全局配置

    5、检验：

    5.1 查询显示

    5.3 库里验证 

    二、自增主键不是id字段处理

    1. @TableName(value ="user")
    2. @Data
    3. public class User implements Serializable {
    4.     /**
    5.      * 自增主键
    6.      */
    7.     @TableId(type = IdType.AUTO)
    8.     private Integer uniqueid;

    三、逻辑删除字段不是delete字段处理

    1、实体加注解

    1.    /**
    2.      * 0不启用 1启用
    3.      */
    4.     @TableLogic
    5.     private Boolean isenable;

    2、yml配置文件新增配置

    1. mybatis-plus:
    2.   configuration:
    3.     map-underscore-to-camel-case: true # 在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    4.     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # ?????sql
    5.     default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler  # 配置全局枚举处理器，好像还有说json的
    6.   global-config:
    7.     db-config:
    8.       id-type: auto   # 数据库id生产规则全局 配置 # ASSIGN_ID雪花算法，数据库id建议使用Long类型
    9.       logic-delete-field: deleted # 全局配置逻辑删除字段名
    10.       logic-delete-value: 0 # 全局配置# 逻辑已删除值(默认为 1)这里因为我是反的所以改成0
    11.       logic-not-delete-value: 1  # 逻辑未删除值(默认为 0）这里因为我是反的所以改成1
    12.       # table-prefix: tbl_    # 数据库表前缀全局配置
    13.     banner: false   # 关闭控制台mybatis-plus的logo
    14. #  type-enums-package: com.bocai.enums   # 扫描通用枚举包 或者使用上面那个枚举全局配置

    四、单表查询 

    1、使用mybatis-plus自带的lambdaQueryWrapper条件进行查询

    1.1 controller

    1. package com.bocai.controller;
    2.  
    3. import com.bocai.common.Result;
    4.  
    5. import com.bocai.dto.UserRoleDto;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8. import com.bocai.service.UserService;
    9. import lombok.extern.slf4j.Slf4j;
    10. import org.springframework.beans.factory.annotation.Autowired;
    11. import org.springframework.web.bind.annotation.*;
    12.  
    13. import java.util.List;
    14.  
    15. @RestController
    16. @RequestMapping("/niubi")
    17. @Slf4j
    18. public class UserController {
    19.  
    20.     @Autowired
    21.     private UserService userService;
    22.  
    23.  
    24.     /**
    25.      * 查询全部用户--无翻页
    26.      * @return
    27.      */
    28.     @GetMapping
    29.     public Result list(){
    30.         log.info("查询全部启用用户信息！");
    31.         List list = userService.userList();
    32.         return Result.success(list);
    33.     }
    34. }

    1.2 servcie

    1. package com.bocai.service;
    2.  
    3.  
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.extension.service.IService;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8.  
    9. import java.util.List;
    10.  
    11. /**
    12. * @author cheng
    13. * @description 针对表【user】的数据库操作Service
    14. * @createDate 2023-11-07 14:52:28
    15. */
    16. public interface UserService extends IService<User> {
    17.  
    18.  
    19.     /**
    20.      * 查询全部启用用户 ==无翻页
    21.      * @return
    22.      */
    23.     List<User> userList();
    24. }

    1.3 serviceImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.aliyun.oss.ServiceException;
    4. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    5. import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    6. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import com.bocai.dto.UserRoleDto;
    9.  
    10. import com.bocai.mapper.UserRoleMapper;
    11. import com.bocai.pojo.Emp;
    12. import com.bocai.pojo.PageBean;
    13. import com.bocai.pojo.Role;
    14. import com.bocai.pojo.User;
    15. import com.bocai.service.UserService;
    16. import com.bocai.mapper.UserMapper;
    17. import lombok.extern.slf4j.Slf4j;
    18. import org.springframework.beans.factory.annotation.Autowired;
    19. import org.springframework.stereotype.Service;
    20. import org.springframework.transaction.annotation.Transactional;
    21.  
    22. import java.util.HashMap;
    23. import java.util.List;
    24. import java.util.Map;
    25.  
    26. /**
    27. * @author cheng
    28. * @description 针对表【user】的数据库操作Service实现
    29. * @createDate 2023-11-07 14:52:28
    30. */
    31. @Service
    32. @Slf4j
    33. public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    34.     implements UserService{
    35.  
    36.     @Autowired
    37.     private UserMapper userMapper;
    38.  
    39.     @Autowired
    40.     private UserRoleMapper userRoleMapper;
    41.  
    42.     /**
    43.      * 启用用户
    44.      * @return
    45.      */
    46.     @Override
    47.     public List<User> userList() {
    48.         LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    49.         lambdaQueryWrapper.select(User::getLoginname, User::getRealname, User::getIsenable);
    50.  
    51.         List<User> users = userMapper.selectList(lambdaQueryWrapper);
    52.  
    53.         return users;
    54.     }

    1.4 mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    6. import com.bocai.pojo.User;
    7. import org.apache.ibatis.annotations.Mapper;
    8. import org.apache.ibatis.annotations.Select;
    9.  
    10. import java.util.List;
    11.  
    12. /**
    13. * @author cheng
    14. * @description 针对表【user】的数据库操作Mapper
    15. * @createDate 2023-11-07 14:52:28
    16. * @Entity com.bocai.pojo.User
    17. */
    18. @Mapper
    19. public interface UserMapper extends BaseMapper<User> {}

    2、使用mybatis-plus自带的lambdaQueryWrapper条件进行+page进行分页查询

    2.1 PageBean

    1. package com.bocai.pojo;
    2.  
    3. import lombok.AllArgsConstructor;
    4. import lombok.Data;
    5. import lombok.NoArgsConstructor;
    6. import java.util.List;
    7.  
    8. /**
    9.  * 分页查询结果封装类
    10.  */
    11. @Data
    12. @NoArgsConstructor
    13. @AllArgsConstructor
    14. public class PageBean {
    15.  
    16.     private Long total;//总记录数
    17.     private List rows;//数据列表
    18.  
    19. }

    新增一个PageBean来装载返回数据

    2.2 controller

    1. package com.bocai.controller;
    2.  
    3. import com.bocai.pojo.Emp;
    4. import com.bocai.pojo.PageBean;
    5. import com.bocai.common.Result;
    6. import com.bocai.service.EmpService;
    7. import lombok.extern.slf4j.Slf4j;
    8. import org.springframework.beans.factory.annotation.Autowired;
    9. import org.springframework.format.annotation.DateTimeFormat;
    10. import org.springframework.web.bind.annotation.*;
    11.  
    12. import java.time.LocalDate;
    13. import java.util.List;
    14.  
    15. @RestController
    16. @RequestMapping("/emps")
    17. @Slf4j
    18. public class EmpController {
    19.     @Autowired
    20.     private EmpService empService;
    21.  
    22.     /**
    23.      * 条件分页查询
    24.      * @param page
    25.      * @param pageSize
    26.      * @param name
    27.      * @param gender
    28.      * @param begin
    29.      * @param end
    30.      * @return
    31.      */
    32.     @GetMapping
    33.     public Result page(@RequestParam(defaultValue = "1") Integer page,
    34.                        @RequestParam(defaultValue = "10") Integer pageSize,
    35.                        String name, Short gender,
    36.                        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
    37.                        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
    38.         log.info("分页查询参数：{}  {} {}  {}  {}  {}",page,pageSize,name,gender,begin,end);
    39.         PageBean pageBean = empService.pageList(page,pageSize,name,gender,begin,end);
    40.         return Result.success(pageBean);
    41.     }
    42. }

     2.3 service

    1. package com.bocai.service;
    2.  
    3. import com.bocai.pojo.Emp;
    4. import com.baomidou.mybatisplus.extension.service.IService;
    5. import com.bocai.pojo.PageBean;
    6.  
    7. import java.time.LocalDate;
    8. import java.util.List;
    9.  
    10. /**
    11. * @author cheng
    12. * @description 针对表【emp(员工表)】的数据库操作Service
    13. * @createDate 2023-10-31 10:44:06
    14. */
    15. public interface EmpService extends IService<Emp> {
    16.  
    17.     /**
    18.      * 分页查询
    19.      * @param page
    20.      * @param pageSize
    21.      * @return
    22.      */
    23.     PageBean pageList(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end);
    24. }

    2.4 servicrImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    4. import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    5. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    6. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    7. import com.bocai.pojo.Emp;
    8. import com.bocai.pojo.PageBean;
    9. import com.bocai.service.EmpService;
    10. import com.bocai.mapper.EmpMapper;
    11. import com.bocai.utils.JwtUtils;
    12. import org.springframework.beans.factory.annotation.Autowired;
    13. import org.springframework.stereotype.Service;
    14.  
    15. import java.time.LocalDate;
    16. import java.time.LocalDateTime;
    17. import java.util.HashMap;
    18. import java.util.List;
    19. import java.util.Map;
    20.  
    21. /**
    22. * @author cheng
    23. * @description 针对表【emp(员工表)】的数据库操作Service实现
    24. * @createDate 2023-10-31 10:44:06
    25. */
    26. @Service
    27. public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp>
    28.     implements EmpService{
    29.  
    30.     @Autowired
    31.     private EmpMapper empMapper;
    32.  
    33.     /**
    34.      * 分页查询
    35.      * @param page
    36.      * @param pageSize
    37.      * @param name
    38.      * @param gender
    39.      * @param begin
    40.      * @param end
    41.      * @return
    42.      */
    43.     @Override
    44.     public PageBean pageList(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {
    45.         LambdaQueryWrapper<Emp> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    46.         lambdaQueryWrapper.like(StringUtils.isNotBlank(name),Emp::getName,name)
    47.                 .eq(gender != null,Emp::getGender,gender)
    48.                 .ge(begin != null,Emp::getEntrydate,begin)
    49.                 .le(end != null,Emp::getEntrydate,end);
    50.         Page<Emp> pageEmp = new Page<>(page,pageSize);
    51.         empMapper.selectPage(pageEmp,lambdaQueryWrapper);
    52.         PageBean pageBean = new PageBean(pageEmp.getTotal(),pageEmp.getRecords());
    53.  
    54.         return pageBean;
    55.     }
    56. }

    2.5 mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.bocai.pojo.Emp;
    4. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    5. import org.apache.ibatis.annotations.Mapper;
    6.  
    7. /**
    8. * @author cheng
    9. * @description 针对表【emp(员工表)】的数据库操作Mapper
    10. * @createDate 2023-10-31 10:44:06
    11. * @Entity com.bocai.pojo.Emp
    12. */
    13. @Mapper
    14. public interface EmpMapper extends BaseMapper<Emp> {
    15.  
    16. }

    五、多表查询

    1、使用sql语句进行多表查询

    1.1 UserRoleDto接收返回数据

    1. package com.bocai.dto;
    2.  
    3. import com.bocai.pojo.User;
    4. import lombok.AllArgsConstructor;
    5. import lombok.Data;
    6. import lombok.NoArgsConstructor;
    7.  
    8. @Data
    9. @NoArgsConstructor
    10. @AllArgsConstructor
    11. public class UserRoleDto extends User {
    12.     private String roleName;  //非user表字段
    13. }

    1.2 controller

    1. package com.bocai.controller;
    2.  
    3. import com.bocai.common.Result;
    4.  
    5. import com.bocai.dto.UserRoleDto;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8. import com.bocai.service.UserService;
    9. import lombok.extern.slf4j.Slf4j;
    10. import org.springframework.beans.factory.annotation.Autowired;
    11. import org.springframework.web.bind.annotation.*;
    12.  
    13. import java.util.List;
    14.  
    15. @RestController
    16. @RequestMapping("/niubi")
    17. @Slf4j
    18. public class UserController {
    19.  
    20.     @Autowired
    21.     private UserService userService;
    22.  
    23.  
    24.  
    25.     /**
    26.      * 查询全部用户--无翻页--- 使用sql以及指定显示字段
    27.      * @return
    28.      */
    29.     @GetMapping("/sql")
    30.     public Result listSql(){
    31.         log.info("查询全部启用用户信息显示+sql多表！");
    32.         List list = userService.userSqlList();
    33.         return Result.success(list);
    34.     }

    1.3 service

    1. package com.bocai.service;
    2.  
    3.  
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.extension.service.IService;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8.  
    9. import java.util.List;
    10.  
    11. /**
    12. * @author cheng
    13. * @description 针对表【user】的数据库操作Service
    14. * @createDate 2023-11-07 14:52:28
    15. */
    16. public interface UserService extends IService<User> {
    17.  
    18.  
    19.  
    20.  
    21.     /**
    22.      * 查询全部用户无翻页--("查询全部启用用户信息显示+sql多表！");
    23.      * @return
    24.      */
    25.     List<UserRoleDto> userSqlList();
    26. }

    1.4 servcieImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.aliyun.oss.ServiceException;
    4. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    5. import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    6. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import com.bocai.dto.UserRoleDto;
    9.  
    10. import com.bocai.mapper.UserRoleMapper;
    11. import com.bocai.pojo.Emp;
    12. import com.bocai.pojo.PageBean;
    13. import com.bocai.pojo.Role;
    14. import com.bocai.pojo.User;
    15. import com.bocai.service.UserService;
    16. import com.bocai.mapper.UserMapper;
    17. import lombok.extern.slf4j.Slf4j;
    18. import org.springframework.beans.factory.annotation.Autowired;
    19. import org.springframework.stereotype.Service;
    20. import org.springframework.transaction.annotation.Transactional;
    21.  
    22. import java.util.HashMap;
    23. import java.util.List;
    24. import java.util.Map;
    25.  
    26. /**
    27. * @author cheng
    28. * @description 针对表【user】的数据库操作Service实现
    29. * @createDate 2023-11-07 14:52:28
    30. */
    31. @Service
    32. @Slf4j
    33. public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    34.     implements UserService{
    35.  
    36.     @Autowired
    37.     private UserMapper userMapper;
    38.  
    39.     @Autowired
    40.     private UserRoleMapper userRoleMapper;
    41.  
    42.    
    43.  
    44.     /**
    45.      * 查询全部用户无翻页--("查询全部启用用户信息显示+sql多表！");
    46.      * @return
    47.      */
    48.     @Override
    49.     public List<UserRoleDto> userSqlList() {
    50.         List<UserRoleDto> UserRoleDto = userMapper.selectSqlList();
    51.         return UserRoleDto;
    52.     }
    53. }

    1.5 mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    6. import com.bocai.pojo.User;
    7. import org.apache.ibatis.annotations.Mapper;
    8. import org.apache.ibatis.annotations.Select;
    9.  
    10. import java.util.List;
    11.  
    12. /**
    13. * @author cheng
    14. * @description 针对表【user】的数据库操作Mapper
    15. * @createDate 2023-11-07 14:52:28
    16. * @Entity com.bocai.pojo.User
    17. */
    18. @Mapper
    19. public interface UserMapper extends BaseMapper<User> {
    20.  
    21.     /**
    22.      * 使用sql多表联查
    23.      * @return
    24.      */
    25.     List<UserRoleDto> selectSqlList();
    26. }

    1.6 xml

    1. <?xml version="1.0" encoding="UTF-8"?>
    2. <!DOCTYPE mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5. <mapper namespace="com.bocai.mapper.UserMapper">
    6.  
    7.     <resultMap id="BaseResultMap" type="com.bocai.pojo.User">
    8.             <result property="uniqueid" column="uniqueId" jdbcType="INTEGER"/>
    9.             <result property="administrationcode" column="administrationCode" jdbcType="VARCHAR"/>
    10.             <result property="fixgroupid" column="fixGroupId" jdbcType="INTEGER"/>
    11.             <result property="loginname" column="loginName" jdbcType="VARCHAR"/>
    12.             <result property="password" column="password" jdbcType="CHAR"/>
    13.             <result property="realname" column="realName" jdbcType="VARCHAR"/>     
    14.             <result property="isspecial" column="isSpecial" jdbcType="BIT"/>
    15.             <result property="isenable" column="isEnable" jdbcType="BIT"/>
    16.   
    17.     </resultMap>
    18.  
    19.     <sql id="Base_Column_List">
    20.         uniqueId,
    21.         loginName,password,realName,
    22.         
    23.     </sql>
    24.     <select id="selectSqlList" resultType="com.bocai.dto.UserRoleDto">
    25.         SELECT a.`realName`,a.`loginName`,c.`name` as roleName
    26.         FROM USER a
    27.         JOIN user_role b ON a.`uniqueId`=b.`userId`
    28.         JOIN role c ON b.`roleId`=c.`uniqueId`
    29.         WHERE a.`isEnable`='1';
    30.  
    31.     </select>
    32.  
    33.  
    34. </mapper>

    2、翻页+组合条件查询

    2.1 controller

    1. package com.bocai.controller;
    2.  
    3. import com.bocai.common.Result;
    4.  
    5. import com.bocai.dto.UserRoleDto;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8. import com.bocai.service.UserService;
    9. import lombok.extern.slf4j.Slf4j;
    10. import org.springframework.beans.factory.annotation.Autowired;
    11. import org.springframework.web.bind.annotation.*;
    12.  
    13. import java.util.List;
    14.  
    15. @RestController
    16. @RequestMapping("/niubi")
    17. @Slf4j
    18. public class UserController {
    19.  
    20.     @Autowired
    21.     private UserService userService;
    22.  
    23.  
    24.  
    25.     /**
    26.      *  使用sql多表查询分页，带条件
    27.      *      * @param page 第几页
    28.      *      * @param pageSize 每页条数
    29.      *      * @param realName 真实姓名
    30.      *      * @param userLevel 用户级别
    31.      * @return
    32.      */
    33.     @GetMapping("/sql/page")
    34.     public Result listSqlPage(@RequestParam(defaultValue = "1") Integer page,
    35.                               @RequestParam(defaultValue = "10") Integer pageSize,
    36.                               String realName,Integer userLevel){
    37.         log.info("查询全部启用用户信息显示+sql多表！翻页当前第{}页,每页{},姓名{}，用户级别{}",page,pageSize,realName,userLevel);
    38.         PageBean pageBean = userService.userSqlPageList(page,pageSize,realName,userLevel);
    39.         return Result.success(pageBean);
    40.     }
    41. }

    2.2 service

    1. package com.bocai.service;
    2.  
    3.  
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.extension.service.IService;
    6. import com.bocai.pojo.PageBean;
    7. import com.bocai.pojo.User;
    8.  
    9. import java.util.List;
    10.  
    11. /**
    12. * @author cheng
    13. * @description 针对表【user】的数据库操作Service
    14. * @createDate 2023-11-07 14:52:28
    15. */
    16. public interface UserService extends IService<User> {
    17.  
    18.  
    19.  
    20.     /**
    21.      * 多表联查+翻页+条件查询+sql
    22.      * @param page
    23.      * @param pageSize
    24.      * @param realName
    25.      * @param userLevel
    26.      * @return
    27.      */
    28.     PageBean userSqlPageList(Integer page, Integer pageSize,String realName,Integer userLevel);
    29. }

    2.3 serviceImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.aliyun.oss.ServiceException;
    4. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    5. import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    6. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import com.bocai.dto.UserRoleDto;
    9.  
    10. import com.bocai.mapper.UserRoleMapper;
    11. import com.bocai.pojo.Emp;
    12. import com.bocai.pojo.PageBean;
    13. import com.bocai.pojo.Role;
    14. import com.bocai.pojo.User;
    15. import com.bocai.service.UserService;
    16. import com.bocai.mapper.UserMapper;
    17. import lombok.extern.slf4j.Slf4j;
    18. import org.springframework.beans.factory.annotation.Autowired;
    19. import org.springframework.stereotype.Service;
    20. import org.springframework.transaction.annotation.Transactional;
    21.  
    22. import java.util.HashMap;
    23. import java.util.List;
    24. import java.util.Map;
    25.  
    26. /**
    27. * @author cheng
    28. * @description 针对表【user】的数据库操作Service实现
    29. * @createDate 2023-11-07 14:52:28
    30. */
    31. @Service
    32. @Slf4j
    33. public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    34.     implements UserService{
    35.  
    36.     @Autowired
    37.     private UserMapper userMapper;
    38.  
    39.     @Autowired
    40.     private UserRoleMapper userRoleMapper;
    41.  
    42.  
    43.     /**
    44.      * 多表联查+翻页+条件查询+sql
    45.      * @param page
    46.      * @param pageSize
    47.      * @param realName
    48.      * @param userLevel
    49.      * @return
    50.      */
    51.     @Override
    52.     public PageBean userSqlPageList(Integer page, Integer pageSize,String realName,Integer userLevel) {
    53.  
    54.         Page<UserRoleDto> pageUserRole = new Page<>(page,pageSize);
    55.         userMapper.selectUserRoleDtoPage(pageUserRole,realName,userLevel);
    56.         pageUserRole.getRecords();
    57.         log.info("sss{},{},{},{},{}",pageUserRole.getTotal());
    58.         PageBean pageBean = new PageBean(pageUserRole.getTotal(),pageUserRole.getRecords());
    59.         return pageBean;
    60.     }
    61.  
    62.  
    63. }

    2.4 mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    4. import com.bocai.dto.UserRoleDto;
    5. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    6. import com.bocai.pojo.User;
    7. import org.apache.ibatis.annotations.Mapper;
    8. import org.apache.ibatis.annotations.Select;
    9.  
    10. import java.util.List;
    11.  
    12. /**
    13. * @author cheng
    14. * @description 针对表【user】的数据库操作Mapper
    15. * @createDate 2023-11-07 14:52:28
    16. * @Entity com.bocai.pojo.User
    17. */
    18. @Mapper
    19. public interface UserMapper extends BaseMapper<User> {
    20.  
    21.    
    22.  
    23.     /**
    24.      * 多表联查+翻页+条件查询+sql
    25.      * @param pageUserRole
    26.      * @param realName
    27.      * @param userLevel
    28.      * @return
    29.      */
    30.     Page<UserRoleDto>selectUserRoleDtoPage(Page<UserRoleDto> pageUserRole, String realName,Integer userLevel);
    31.  
    32.  
    33. }
    34.

    2.5 xml

    1. <?xml version="1.0" encoding="UTF-8"?>
    2. <!DOCTYPE mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5. <mapper namespace="com.bocai.mapper.UserMapper">
    6.  
    7.     <resultMap id="BaseResultMap" type="com.bocai.pojo.User">
    8.             <result property="uniqueid" column="uniqueId" jdbcType="INTEGER"/>
    9.             <result property="administrationcode" column="administrationCode" jdbcType="VARCHAR"/>
    10.             <result property="fixgroupid" column="fixGroupId" jdbcType="INTEGER"/>
    11.             <result property="loginname" column="loginName" jdbcType="VARCHAR"/>
    12.             <result property="password" column="password" jdbcType="CHAR"/>
    13.             <result property="realname" column="realName" jdbcType="VARCHAR"/>     
    14.             <result property="isspecial" column="isSpecial" jdbcType="BIT"/>
    15.             <result property="isenable" column="isEnable" jdbcType="BIT"/>
    16.   
    17.     </resultMap>
    18.  
    19.     <sql id="Base_Column_List">
    20.         uniqueId,
    21.         loginName,password,realName,
    22.         
    23.     </sql>
    24.        <select id="selectUserRoleDtoPage" resultType="com.bocai.dto.UserRoleDto">
    25.         SELECT a.uniqueid,a.`realName`,a.`loginName`,c.`name` as roleName
    26.         FROM USER a
    27.                  JOIN user_role b ON a.`uniqueId`=b.`userId`
    28.                  JOIN role c ON b.`roleId`=c.`uniqueId`
    29.         <where>a.`isEnable`='1'
    30.             <if test="realName != null">
    31.              and   a.realName like concat('%', #{realName}, '%')
    32.             </if>
    33.             <if test="userLevel != null">
    34.             and a.userLevel=#{userLevel}
    35.             </if>
    36.         </where>
    37.  
    38.     </select>
    39.  
    40.  
    41. </mapper>

    六、事务处理

    1、serviceImpl

    1.     @Override
    2.     @Transactional
    3.     public void deleteById(Integer id) {
    4.  
    5.         userMapper.deleteById(id);
    6. //        int i = 1/0;
    7.         userMapper.updateUserById(id);
    8.  
    9.  
    10.         Map<String, Object> map = new HashMap<>();
    11.         map.put("userid",id);
    12.         userRoleMapper.deleteByMap(map); //根据用户id删除用户与角色关系
    13.  
    14.     }

    2、yml配置事务日志

    1. #spring事务管理日志
    2. logging:
    3.   level:
    4.     org.springframework.jdbc.support.JdbcTransactionManager: debug

    七、判重

    1、数据库设置唯一判重

    2、代码判重 

    2.1 新增判重

    1.  
    2.     /**
    3.      * 新增角色
    4.      * @param role
    5.      */
    6.     @Override
    7.     public void addRole(Role role) {
    8.  
    9.         LambdaQueryWrapper<Role> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    10.         lambdaQueryWrapper.eq(role.getName() != null,Role::getName, role.getName());
    11.  
    12.  
    13.         long count = roleMapper.selectCount(lambdaQueryWrapper);
    14.         if (count > 0) {
    15.             throw new ServiceException("该角色名已存在！");
    16.         }
    17.         role.setUpdatetime(LocalDateTime.now());
    18.         role.setAdduserid(2);
    19.         roleMapper.insert(role);
    20.  
    21.     }

    2.2 修改判重

    1.     /**
    2.      * 修改角色
    3.      * @param role
    4.      */
    5.     @Override
    6.     public void alterUser(Role role) {
    7.  
    8.         LambdaQueryWrapper<Role> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    9.         lambdaQueryWrapper.eq(role.getName() != null,Role::getName, role.getName())
    10.                 .ne(role.getUniqueid() != null, Role::getUniqueid,role.getUniqueid());
    11.  
    12.  
    13.         long count = roleMapper.selectCount(lambdaQueryWrapper);
    14.         if (count > 0) {
    15.             throw new ServiceException("该角色名已存在！");
    16.         }
    17.         role.setUpdatetime(LocalDateTime.now());
    18.         roleMapper.updateById(role);
    19.     }

    注意这里有个自己跟自己判重的逻辑，使用了ne的方式

    八、日期范围查询 

    1、controller

    1. package com.bocai.controller;
    2.  
    3. import com.bocai.common.Result;
    4. import com.bocai.pojo.PageBean;
    5. import com.bocai.pojo.Role;
    6. import com.bocai.service.RoleService;
    7. import lombok.extern.slf4j.Slf4j;
    8. import org.springframework.beans.factory.annotation.Autowired;
    9. import org.springframework.format.annotation.DateTimeFormat;
    10. import org.springframework.web.bind.annotation.*;
    11.  
    12. import java.time.LocalDate;
    13. import java.time.LocalDateTime;
    14.  
    15. @RestController
    16. @RequestMapping("roles")
    17. @Slf4j
    18. public class RoleController {
    19.  
    20.     @Autowired
    21.     private RoleService roleService;
    22.  
    23.     /**
    24.      * 带条件、翻页的查询全部角色
    25.      * @param page 当前第几页
    26.      * @param pageSize 每页多少条
    27.      * @param name 角色名
    28.      * @param roleType 角色类型
    29.      * @param begin 开始时间
    30.      * @param end 结束时间
    31.      * @return
    32.      */
    33.     @GetMapping
    34.     public Result page(@RequestParam(defaultValue = "1") Integer page,
    35.                        @RequestParam(defaultValue = "10") Integer pageSize,
    36.                        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime begin,
    37.                        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime end,
    38.                        String name,String roleType){
    39.         log.info("带翻页和条件的查询全部角色当前第{}页,每页{}条,角色名：{},角色类型：{},开始时间{}，结束时间{}",
    40.                 page,pageSize,name,roleType,begin,end);
    41.         PageBean pageBean = roleService.rolePage(page,pageSize,name,roleType,begin,end);
    42.         return Result.success(pageBean);
    43.     }
    44. }

    2、service

    1. package com.bocai.service;
    2.  
    3. import com.baomidou.mybatisplus.extension.service.IService;
    4. import com.bocai.pojo.PageBean;
    5. import com.bocai.pojo.Role;
    6.  
    7. import java.time.LocalDateTime;
    8.  
    9. /**
    10. * @author cheng
    11. * @description 针对表【role】的数据库操作Service
    12. * @createDate 2023-11-07 14:52:38
    13. */
    14. public interface RoleService extends IService<Role> {
    15.  
    16.     /**
    17.      * 带条件、翻页的查询全部角色
    18.      * @param page 当前第几页
    19.      * @param pageSize 每页多少条
    20.      * @param name 角色名
    21.      * @param roleType 角色类型
    22.      * @param begin 开始时间
    23.      * @param end 结束时间
    24.      * @return
    25.      */
    26.     PageBean rolePage(Integer page, Integer pageSize, String name, String roleType, LocalDateTime begin,LocalDateTime end);
    27. }

    3、serviceImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.aliyun.oss.ServiceException;
    4. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    5. import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    6. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import com.bocai.mapper.UserRoleMapper;
    9. import com.bocai.pojo.Inhousing;
    10. import com.bocai.pojo.PageBean;
    11. import com.bocai.pojo.Role;
    12.  
    13. import com.bocai.service.RoleService;
    14. import com.bocai.mapper.RoleMapper;
    15. import org.springframework.beans.factory.annotation.Autowired;
    16. import org.springframework.stereotype.Service;
    17. import org.springframework.transaction.annotation.Transactional;
    18.  
    19. import java.time.LocalDateTime;
    20. import java.util.HashMap;
    21. import java.util.Map;
    22.  
    23. /**
    24. * @author cheng
    25. * @description 针对表【role】的数据库操作Service实现
    26. * @createDate 2023-11-07 14:52:38
    27. */
    28. @Service
    29. public class RoleServiceImpl extends ServiceImpl<RoleMapper, Role>
    30.     implements RoleService{
    31.  
    32.     @Autowired
    33.     private RoleMapper roleMapper;
    34.  
    35.  
    36.     @Autowired
    37.     private UserRoleMapper userRoleMapper;
    38.     /**
    39.      * 带条件的翻页查询全部角色
    40.      * @param page 当前第几页
    41.      * @param pageSize 每页多少条
    42.      * @param name 角色名
    43.      * @param roleType 角色类型
    44.      * @param begin 开始时间
    45.      * @param end 结束时间
    46.      * @return
    47.      */
    48.     @Override
    49.     public PageBean rolePage(Integer page, Integer pageSize, String name, String roleType,LocalDateTime begin,LocalDateTime end) {
    50.         LambdaQueryWrapper<Role> lambdaQueryWrapper =new LambdaQueryWrapper<>();
    51.         lambdaQueryWrapper.like(StringUtils.isNotBlank(name),Role::getName,name)
    52.                 .like(StringUtils.isNotBlank(roleType),Role::getRoletype,roleType)
    53.                 .between(begin != null && end != null,Role::getUpdatetime,begin,end);
    54.         Page<Role> rolePage = new Page<>(page,pageSize);
    55.         roleMapper.selectPage(rolePage,lambdaQueryWrapper);
    56.         PageBean pageBean = new PageBean(rolePage.getTotal(),rolePage.getRecords());
    57.         return pageBean;
    58.     }

    也可以使用这个方式

    .ge(begin != null,Role::getRoletype,begin)
    .le(end != null,Role::getRoletype,end);

     

    4、mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    4. import com.bocai.pojo.Role;
    5. import org.apache.ibatis.annotations.Mapper;
    6.  
    7. /**
    8. * @author cheng
    9. * @description 针对表【role】的数据库操作Mapper
    10. * @createDate 2023-11-07 14:52:38
    11. * @Entity com.bocai.pojo.Role
    12. */
    13. @Mapper
    14. public interface RoleMapper extends BaseMapper<Role> {
    15.  
    16. }

    九、POST请求封装DTO查询

    1、POST接口请求传参

    1. {
    2. 	"roleid": 564,
    3. 	"usersid": [694, 693]
    4. }

     2、封装DTO

    1. package com.bocai.dto;
    2.  
    3.  
    4. import lombok.AllArgsConstructor;
    5. import lombok.Data;
    6. import lombok.NoArgsConstructor;
    7.  
    8. import java.util.List;
    9.  
    10. @Data
    11. @NoArgsConstructor
    12. @AllArgsConstructor
    13. public class UsersRoleDto{
    14.     private Integer roleid;
    15.     private List usersid;
    16. }

    3、controller

    1. package com.bocai.controller;
    2.  
    3.  
    4. import com.bocai.common.Result;
    5. import com.bocai.dto.UsersRoleDto;
    6. import com.bocai.service.UserRoleService;
    7. import lombok.extern.slf4j.Slf4j;
    8. import org.springframework.beans.factory.annotation.Autowired;
    9. import org.springframework.web.bind.annotation.*;
    10.  
    11. import java.util.List;
    12.  
    13. @RestController
    14. @RequestMapping("/userRoles")
    15. @Slf4j
    16. public class userRoleController {
    17.  
    18.     @Autowired
    19.     private UserRoleService userRoleService;
    20.  
    21.  
    22.     /**
    23.      * 分配
    24.      * @param usersRoleDto
    25.      * @return
    26.      */
    27.     @PostMapping()
    28.     public Result usersRole(@RequestBody UsersRoleDto usersRoleDto){
    29.  
    30.         log.info("分配{}",usersRoleDto);
    31.         userRoleService.usersRole(usersRoleDto);
    32.         return  Result.success();
    33.     }
    34. }

    4、service

    1. package com.bocai.service;
    2.  
    3. import com.baomidou.mybatisplus.extension.service.IService;
    4. import com.bocai.dto.UsersRoleDto;
    5. import com.bocai.pojo.UserRole;
    6.  
    7. import java.util.List;
    8.  
    9. /**
    10. * @author cheng
    11. * @description 针对表【user_role】的数据库操作Service
    12. * @createDate 2023-11-07 14:52:46
    13. */
    14. public interface UserRoleService extends IService<UserRole> {
    15.  
    16.  
    17.     /**
    18.      * 分配
    19.      * @param usersRoleDto
    20.      */
    21.     void usersRole(UsersRoleDto usersRoleDto);
    22.  
    23. }

    5、serviceImpl

    1. package com.bocai.service.impl;
    2.  
    3. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    4. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    5. import com.bocai.dto.UsersRoleDto;
    6. import com.bocai.pojo.UserRole;
    7. import com.bocai.service.UserRoleService;
    8. import com.bocai.mapper.UserRoleMapper;
    9. import lombok.extern.slf4j.Slf4j;
    10. import org.springframework.beans.factory.annotation.Autowired;
    11. import org.springframework.stereotype.Service;
    12.  
    13. import java.util.List;
    14.  
    15. /**
    16. * @author cheng
    17. * @description 针对表【user_role】的数据库操作Service实现
    18. * @createDate 2023-11-07 14:52:46
    19. */
    20. @Service
    21. @Slf4j
    22. public class UserRoleServiceImpl extends ServiceImpl<UserRoleMapper, UserRole>
    23.     implements UserRoleService{
    24.  
    25.     @Autowired
    26.     private UserRoleMapper userRoleMapper;
    27.  
    28.     /**
    29.      * 分配
    30.      * @param usersRoleDto
    31.      */
    32.     @Override
    33.     public void usersRole(UsersRoleDto usersRoleDto) {
    34.  
    35.         usersRoleDto.getUsersid().forEach(userId ->{
    36.             UserRole userRole = new UserRole();
    37.             userRole.setUserid(userId);
    38.             userRole.setRoleid(usersRoleDto.getRoleid());
    39.             LambdaQueryWrapper<UserRole> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    40.             lambdaQueryWrapper.eq(userId != null,UserRole::getUserid,userId);
    41.             Integer count = userRoleMapper.selectCount(lambdaQueryWrapper);
    42.             if (count > 0){
    43.                 userRoleMapper.delete(lambdaQueryWrapper);
    44.             }
    45.             userRoleMapper.insert(userRole);
    46.         });
    47.  
    48.     }
    49.  
    50. }

    这里做了一个查询处理，针对存在的对象，先查询出来删除，再新增 

    6、mapper

    1. package com.bocai.mapper;
    2.  
    3. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    4. import com.bocai.pojo.UserRole;
    5. import org.apache.ibatis.annotations.Mapper;
    6.  
    7. /**
    8. * @author cheng
    9. * @description 针对表【user_role】的数据库操作Mapper
    10. * @createDate 2023-11-07 14:52:46
    11. * @Entity com.bocai.pojo.UserRole
    12. */
    13. @Mapper
    14. public interface UserRoleMapper extends BaseMapper<UserRole> {
    15.  
    16. }

  • 相关阅读:
    JAVA初阶——程序逻辑控制
    基于Java后端与Typescript前端的代码自动生成 - malcolmcrum
    C++：多态 详解
    C# Onnx Yolov8 Detect 路面坑洼检测
    前端常用的开发工具有哪些？
    常见加解密算法04 - 分组密码DES
    每日一题~中序后序遍历构造二叉树
    C++设计模式---模板方法模式
    微信小程序scroll-view真机出现滚动条如何隐藏
    性能测试 —— Jmeter 常用三种定时器
  • 原文地址：https://blog.csdn.net/legend818/article/details/134306464