目录

一、枚举

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、数据库type字段是Integer 类型枚举

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

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

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);
    }

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