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

package com.bocai.enums;


import com.baomidou.mybatisplus.annotation.EnumValue;
import com.fasterxml.jackson.annotation.JsonValue;
import lombok.*;

/**
 * a表的type字段 枚举用热类型1-高温水2-低温水3-蒸汽
 * 注意配置文件yml有配置
 */
@AllArgsConstructor
@NoArgsConstructor
@Getter
public enum TypeEnum {
    HIGHTEMPERATUREWATER(1,"高温水"),
    LOWTEMPERATUREWATER(2,"低温水"),
    STEAM(3,"蒸汽")
    ;

    @EnumValue //将注解标注的数值存储到数据库中
    private  Integer hottype;
    @JsonValue
    private  String desc;




}

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

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

3、修改实体类

package com.bocai.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;

import com.bocai.enums.TypeEnum;
import lombok.Data;

/**
 * 
 * @TableName inhousing
 */
@TableName(value ="inhousing")
@Data
public class Inhousing implements Serializable {
    /**
     * 
     */
    @TableId(type = IdType.AUTO)
    private Integer uniqueid;
  

    /**
     * 用热类型1-高温水2-低温水3-蒸汽
     */
    private TypeEnum hottype;

   

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

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

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

@TableName(value ="user")
@Data
public class User implements Serializable {
    /**
     * 自增主键
     */
    @TableId(type = IdType.AUTO)
    private Integer uniqueid;

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

1、实体加注解

   /**
     * 0不启用 1启用
     */
    @TableLogic
    private Boolean isenable;

2、yml配置文件新增配置

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

四、单表查询

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

1.1 controller

package com.bocai.controller;

import com.bocai.common.Result;

import com.bocai.dto.UserRoleDto;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/niubi")
@Slf4j
public class UserController {

    @Autowired
    private UserService userService;


    /**
     * 查询全部用户--无翻页
     * @return
     */
    @GetMapping
    public Result list(){
        log.info("查询全部启用用户信息");
        List<User> list = userService.userList();
        return Result.success(list);
    }
}

1.2 servcie

package com.bocai.service;


import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service
* @createDate 2023-11-07 14:52:28
*/
public interface UserService extends IService<User> {


    /**
     * 查询全部启用用户 ==无翻页
     * @return
     */
    List<User> userList();
}

1.3 serviceImpl

package com.bocai.service.impl;

import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.bocai.dto.UserRoleDto;

import com.bocai.mapper.UserRoleMapper;
import com.bocai.pojo.Emp;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import com.bocai.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service实现
* @createDate 2023-11-07 14:52:28
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserRoleMapper userRoleMapper;

    /**
     * 启用用户
     * @return
     */
    @Override
    public List<User> userList() {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.select(User::getLoginname, User::getRealname, User::getIsenable);

        List<User> users = userMapper.selectList(lambdaQueryWrapper);

        return users;
    }

1.4 mapper

package com.bocai.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bocai.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Mapper
* @createDate 2023-11-07 14:52:28
* @Entity com.bocai.pojo.User
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {}

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

2.1 PageBean

package com.bocai.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

/**
 * 分页查询结果封装类
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {

    private Long total;//总记录数
    private List rows;//数据列表

}

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

2.2 controller

package com.bocai.controller;

import com.bocai.pojo.Emp;
import com.bocai.pojo.PageBean;
import com.bocai.common.Result;
import com.bocai.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDate;
import java.util.List;

@RestController
@RequestMapping("/emps")
@Slf4j
public class EmpController {
    @Autowired
    private EmpService empService;

    /**
     * 条件分页查询
     * @param page
     * @param pageSize
     * @param name
     * @param gender
     * @param begin
     * @param end
     * @return
     */
    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize,
                       String name, Short gender,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
        log.info("分页查询参数{}  {} {}  {}  {}  {}",page,pageSize,name,gender,begin,end);
        PageBean pageBean = empService.pageList(page,pageSize,name,gender,begin,end);
        return Result.success(pageBean);
    }
}

2.3 service

package com.bocai.service;

import com.bocai.pojo.Emp;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.pojo.PageBean;

import java.time.LocalDate;
import java.util.List;

/**
* @author cheng
* @description 针对表【emp(员工表)】的数据库操作Service
* @createDate 2023-10-31 10:44:06
*/
public interface EmpService extends IService<Emp> {

    /**
     * 分页查询
     * @param page
     * @param pageSize
     * @return
     */
    PageBean pageList(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end);
}

2.4 servicrImpl

package com.bocai.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.bocai.pojo.Emp;
import com.bocai.pojo.PageBean;
import com.bocai.service.EmpService;
import com.bocai.mapper.EmpMapper;
import com.bocai.utils.JwtUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author cheng
* @description 针对表【emp(员工表)】的数据库操作Service实现
* @createDate 2023-10-31 10:44:06
*/
@Service
public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp>
    implements EmpService{

    @Autowired
    private EmpMapper empMapper;

    /**
     * 分页查询
     * @param page
     * @param pageSize
     * @param name
     * @param gender
     * @param begin
     * @param end
     * @return
     */
    @Override
    public PageBean pageList(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {
        LambdaQueryWrapper<Emp> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(StringUtils.isNotBlank(name),Emp::getName,name)
                .eq(gender != null,Emp::getGender,gender)
                .ge(begin != null,Emp::getEntrydate,begin)
                .le(end != null,Emp::getEntrydate,end);
        Page<Emp> pageEmp = new Page<>(page,pageSize);
        empMapper.selectPage(pageEmp,lambdaQueryWrapper);
        PageBean pageBean = new PageBean(pageEmp.getTotal(),pageEmp.getRecords());

        return pageBean;
    }
}

2.5 mapper

package com.bocai.mapper;

import com.bocai.pojo.Emp;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
* @author cheng
* @description 针对表【emp(员工表)】的数据库操作Mapper
* @createDate 2023-10-31 10:44:06
* @Entity com.bocai.pojo.Emp
*/
@Mapper
public interface EmpMapper extends BaseMapper<Emp> {

}

五、多表查询

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

1.1 UserRoleDto接收返回数据

package com.bocai.dto;

import com.bocai.pojo.User;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserRoleDto extends User {
    private String roleName;  //非user表字段
}

1.2 controller

package com.bocai.controller;

import com.bocai.common.Result;

import com.bocai.dto.UserRoleDto;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/niubi")
@Slf4j
public class UserController {

    @Autowired
    private UserService userService;



    /**
     * 查询全部用户--无翻页--- 使用sql以及指定显示字段
     * @return
     */
    @GetMapping("/sql")
    public Result listSql(){
        log.info("查询全部启用用户信息显示+sql多表");
        List<UserRoleDto> list = userService.userSqlList();
        return Result.success(list);
    }

1.3 service

package com.bocai.service;


import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service
* @createDate 2023-11-07 14:52:28
*/
public interface UserService extends IService<User> {




    /**
     * 查询全部用户无翻页--("查询全部启用用户信息显示+sql多表");
     * @return
     */
    List<UserRoleDto> userSqlList();
}

1.4 servcieImpl

package com.bocai.service.impl;

import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.bocai.dto.UserRoleDto;

import com.bocai.mapper.UserRoleMapper;
import com.bocai.pojo.Emp;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import com.bocai.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service实现
* @createDate 2023-11-07 14:52:28
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserRoleMapper userRoleMapper;

   

    /**
     * 查询全部用户无翻页--("查询全部启用用户信息显示+sql多表");
     * @return
     */
    @Override
    public List<UserRoleDto> userSqlList() {
        List<UserRoleDto> UserRoleDto = userMapper.selectSqlList();
        return UserRoleDto;
    }
}

1.5 mapper

package com.bocai.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bocai.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Mapper
* @createDate 2023-11-07 14:52:28
* @Entity com.bocai.pojo.User
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {

    /**
     * 使用sql多表联查
     * @return
     */
    List<UserRoleDto> selectSqlList();
}

1.6 xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bocai.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.bocai.pojo.User">
            <result property="uniqueid" column="uniqueId" jdbcType="INTEGER"/>
            <result property="administrationcode" column="administrationCode" jdbcType="VARCHAR"/>
            <result property="fixgroupid" column="fixGroupId" jdbcType="INTEGER"/>
            <result property="loginname" column="loginName" jdbcType="VARCHAR"/>
            <result property="password" column="password" jdbcType="CHAR"/>
            <result property="realname" column="realName" jdbcType="VARCHAR"/>     
            <result property="isspecial" column="isSpecial" jdbcType="BIT"/>
            <result property="isenable" column="isEnable" jdbcType="BIT"/>
  
    </resultMap>

    <sql id="Base_Column_List">
        uniqueId,
        loginName,password,realName,
        
    </sql>
    <select id="selectSqlList" resultType="com.bocai.dto.UserRoleDto">
        SELECT a.`realName`,a.`loginName`,c.`name` as roleName
        FROM USER a
        JOIN user_role b ON a.`uniqueId`=b.`userId`
        JOIN role c ON b.`roleId`=c.`uniqueId`
        WHERE a.`isEnable`='1';

    </select>


</mapper>

2、翻页+组合条件查询

2.1 controller

package com.bocai.controller;

import com.bocai.common.Result;

import com.bocai.dto.UserRoleDto;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/niubi")
@Slf4j
public class UserController {

    @Autowired
    private UserService userService;



    /**
     *  使用sql多表查询分页带条件
     *      * @param page 第几页
     *      * @param pageSize 每页条数
     *      * @param realName 真实姓名
     *      * @param userLevel 用户级别
     * @return
     */
    @GetMapping("/sql/page")
    public Result listSqlPage(@RequestParam(defaultValue = "1") Integer page,
                              @RequestParam(defaultValue = "10") Integer pageSize,
                              String realName,Integer userLevel){
        log.info("查询全部启用用户信息显示+sql多表翻页当前第{}页,每页{},姓名{}用户级别{}",page,pageSize,realName,userLevel);
        PageBean pageBean = userService.userSqlPageList(page,pageSize,realName,userLevel);
        return Result.success(pageBean);
    }
}

2.2 service

package com.bocai.service;


import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.User;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service
* @createDate 2023-11-07 14:52:28
*/
public interface UserService extends IService<User> {



    /**
     * 多表联查+翻页+条件查询+sql
     * @param page
     * @param pageSize
     * @param realName
     * @param userLevel
     * @return
     */
    PageBean userSqlPageList(Integer page, Integer pageSize,String realName,Integer userLevel);
}

2.3 serviceImpl

package com.bocai.service.impl;

import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.bocai.dto.UserRoleDto;

import com.bocai.mapper.UserRoleMapper;
import com.bocai.pojo.Emp;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import com.bocai.pojo.User;
import com.bocai.service.UserService;
import com.bocai.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author cheng
* @description 针对表【user】的数据库操作Service实现
* @createDate 2023-11-07 14:52:28
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserRoleMapper userRoleMapper;


    /**
     * 多表联查+翻页+条件查询+sql
     * @param page
     * @param pageSize
     * @param realName
     * @param userLevel
     * @return
     */
    @Override
    public PageBean userSqlPageList(Integer page, Integer pageSize,String realName,Integer userLevel) {

        Page<UserRoleDto> pageUserRole = new Page<>(page,pageSize);
        userMapper.selectUserRoleDtoPage(pageUserRole,realName,userLevel);
        pageUserRole.getRecords();
        log.info("sss{},{},{},{},{}",pageUserRole.getTotal());
        PageBean pageBean = new PageBean(pageUserRole.getTotal(),pageUserRole.getRecords());
        return pageBean;
    }


}

2.4 mapper

package com.bocai.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.bocai.dto.UserRoleDto;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bocai.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author cheng
* @description 针对表【user】的数据库操作Mapper
* @createDate 2023-11-07 14:52:28
* @Entity com.bocai.pojo.User
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {

   

    /**
     * 多表联查+翻页+条件查询+sql
     * @param pageUserRole
     * @param realName
     * @param userLevel
     * @return
     */
    Page<UserRoleDto>selectUserRoleDtoPage(Page<UserRoleDto> pageUserRole, String realName,Integer userLevel);


}

2.5 xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bocai.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.bocai.pojo.User">
            <result property="uniqueid" column="uniqueId" jdbcType="INTEGER"/>
            <result property="administrationcode" column="administrationCode" jdbcType="VARCHAR"/>
            <result property="fixgroupid" column="fixGroupId" jdbcType="INTEGER"/>
            <result property="loginname" column="loginName" jdbcType="VARCHAR"/>
            <result property="password" column="password" jdbcType="CHAR"/>
            <result property="realname" column="realName" jdbcType="VARCHAR"/>     
            <result property="isspecial" column="isSpecial" jdbcType="BIT"/>
            <result property="isenable" column="isEnable" jdbcType="BIT"/>
  
    </resultMap>

    <sql id="Base_Column_List">
        uniqueId,
        loginName,password,realName,
        
    </sql>
       <select id="selectUserRoleDtoPage" resultType="com.bocai.dto.UserRoleDto">
        SELECT a.uniqueid,a.`realName`,a.`loginName`,c.`name` as roleName
        FROM USER a
                 JOIN user_role b ON a.`uniqueId`=b.`userId`
                 JOIN role c ON b.`roleId`=c.`uniqueId`
        <where>a.`isEnable`='1'
            <if test="realName != null">
             and   a.realName like concat('%', #{realName}, '%')
            </if>
            <if test="userLevel != null">
            and a.userLevel=#{userLevel}
            </if>
        </where>

    </select>


</mapper>

六、事务处理

1、serviceImpl

    @Override
    @Transactional
    public void deleteById(Integer id) {

        userMapper.deleteById(id);
//        int i = 1/0;
        userMapper.updateUserById(id);


        Map<String, Object> map = new HashMap<>();
        map.put("userid",id);
        userRoleMapper.deleteByMap(map); //根据用户id删除用户与角色关系

    }

2、yml配置事务日志

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

七、判重

1、数据库设置唯一判重

2、代码判重

2.1 新增判重


    /**
     * 新增角色
     * @param role
     */
    @Override
    public void addRole(Role role) {

        LambdaQueryWrapper<Role> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(role.getName() != null,Role::getName, role.getName());


        long count = roleMapper.selectCount(lambdaQueryWrapper);
        if (count > 0) {
            throw new ServiceException("该角色名已存在");
        }
        role.setUpdatetime(LocalDateTime.now());
        role.setAdduserid(2);
        roleMapper.insert(role);

    }

2.2 修改判重

    /**
     * 修改角色
     * @param role
     */
    @Override
    public void alterUser(Role role) {

        LambdaQueryWrapper<Role> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(role.getName() != null,Role::getName, role.getName())
                .ne(role.getUniqueid() != null, Role::getUniqueid,role.getUniqueid());


        long count = roleMapper.selectCount(lambdaQueryWrapper);
        if (count > 0) {
            throw new ServiceException("该角色名已存在");
        }
        role.setUpdatetime(LocalDateTime.now());
        roleMapper.updateById(role);
    }