目录
1、使用mybatis-plus自带的lambdaQueryWrapper条件进行查询
2、使用mybatis-plus自带的lambdaQueryWrapper条件进行+page进行分页查询
前言:项目实战过程当中,一些总结,例如枚举应用、翻页、单表、多表查询、翻页、自增主键、逻辑删除、判重等
一、枚举
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 pageNum
* @param pageSize
* @param name
* @param gender
* @param begin
* @param end
* @return
*/
@GetMapping
public Result page(@RequestParam(defaultValue = "1") Integer pageNum,
@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("分页查询参数:{} {} {} {} {} {}",pageNum,pageSize,name,gender,begin,end);
PageBean pageBean = empService.pageList(pageNum,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 pageNum
* @param pageSize
* @return
*/
PageBean pageList(Integer pageNum, 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 pageNum
* @param pageSize
* @param name
* @param gender
* @param begin
* @param end
* @return
*/
@Override
public PageBean pageList(Integer pageNum, 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<>(pageNum,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 pageNum 第几页
* * @param pageSize 每页条数
* * @param realName 真实姓名
* * @param userLevel 用户级别
* @return
*/
@GetMapping("/sql/page")
public Result listSqlPage(@RequestParam(defaultValue = "1") Integer pageNum,
@RequestParam(defaultValue = "10") Integer pageSize,
String realName,Integer userLevel){
log.info("查询全部启用用户信息显示+sql多表!翻页当前第{}页,每页{},姓名{},用户级别{}",pageNum,pageSize,realName,userLevel);
PageBean pageBean = userService.userSqlPageList(pageNum,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 pageNum
* @param pageSize
* @param realName
* @param userLevel
* @return
*/
PageBean userSqlPageList(Integer pageNum, 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 pageNum
* @param pageSize
* @param realName
* @param userLevel
* @return
*/
@Override
public PageBean userSqlPageList(Integer pageNum, Integer pageSize,String realName,Integer userLevel) {
Page<UserRoleDto> pageUserRole = new Page<>(pageNum,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的方式
八、日期范围查询
1、controller
package com.bocai.controller;
import com.bocai.common.Result;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import com.bocai.service.RoleService;
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.time.LocalDateTime;
@RestController
@RequestMapping("roles")
@Slf4j
public class RoleController {
@Autowired
private RoleService roleService;
/**
* 带条件、翻页的查询全部角色
* @param page 当前第几页
* @param pageSize 每页多少条
* @param name 角色名
* @param roleType 角色类型
* @param begin 开始时间
* @param end 结束时间
* @return
*/
@GetMapping
public Result page(@RequestParam(defaultValue = "1") Integer pageNum,
@RequestParam(defaultValue = "10") Integer pageSize,
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime begin,
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime end,
String name,String roleType){
log.info("带翻页和条件的查询全部角色当前第{}页,每页{}条,角色名:{},角色类型:{},开始时间{},结束时间{}",
pageNum,pageSize,name,roleType,begin,end);
PageBean pageBean = roleService.rolePage(pageNum,pageSize,name,roleType,begin,end);
return Result.success(pageBean);
}
}
2、service
package com.bocai.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import java.time.LocalDateTime;
/**
* @author cheng
* @description 针对表【role】的数据库操作Service
* @createDate 2023-11-07 14:52:38
*/
public interface RoleService extends IService<Role> {
/**
* 带条件、翻页的查询全部角色
* @param pageNum 当前第几页
* @param pageSize 每页多少条
* @param name 角色名
* @param roleType 角色类型
* @param begin 开始时间
* @param end 结束时间
* @return
*/
PageBean rolePage(Integer pageNum, Integer pageSize, String name, String roleType, LocalDateTime begin,LocalDateTime end);
}
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.mapper.UserRoleMapper;
import com.bocai.pojo.Inhousing;
import com.bocai.pojo.PageBean;
import com.bocai.pojo.Role;
import com.bocai.service.RoleService;
import com.bocai.mapper.RoleMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
/**
* @author cheng
* @description 针对表【role】的数据库操作Service实现
* @createDate 2023-11-07 14:52:38
*/
@Service
public class RoleServiceImpl extends ServiceImpl<RoleMapper, Role>
implements RoleService{
@Autowired
private RoleMapper roleMapper;
@Autowired
private UserRoleMapper userRoleMapper;
/**
* 带条件的翻页查询全部角色
* @param pageNum 当前第几页
* @param pageSize 每页多少条
* @param name 角色名
* @param roleType 角色类型
* @param begin 开始时间
* @param end 结束时间
* @return
*/
@Override
public PageBean rolePage(Integer pageNum, Integer pageSize, String name, String roleType,LocalDateTime begin,LocalDateTime end) {
LambdaQueryWrapper<Role> lambdaQueryWrapper =new LambdaQueryWrapper<>();
lambdaQueryWrapper.like(StringUtils.isNotBlank(name),Role::getName,name)
.like(StringUtils.isNotBlank(roleType),Role::getRoletype,roleType)
.between(begin != null && end != null,Role::getUpdatetime,begin,end);
Page<Role> rolePage = new Page<>(pageNum,pageSize);
roleMapper.selectPage(rolePage,lambdaQueryWrapper);
PageBean pageBean = new PageBean(rolePage.getTotal(),rolePage.getRecords());
return pageBean;
}
也可以使用这个方式
.ge(begin != null,Role::getRoletype,begin)
.le(end != null,Role::getRoletype,end);
4、mapper
package com.bocai.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bocai.pojo.Role;
import org.apache.ibatis.annotations.Mapper;
/**
* @author cheng
* @description 针对表【role】的数据库操作Mapper
* @createDate 2023-11-07 14:52:38
* @Entity com.bocai.pojo.Role
*/
@Mapper
public interface RoleMapper extends BaseMapper<Role> {
}
九、POST请求封装DTO查询
1、POST接口请求传参
{
"roleid": 564,
"usersid": [694, 693]
}
2、封装DTO
package com.bocai.dto;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UsersRoleDto{
private Integer roleid;
private List<Integer> usersid;
}
3、controller
package com.bocai.controller;
import com.bocai.common.Result;
import com.bocai.dto.UsersRoleDto;
import com.bocai.service.UserRoleService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/userRoles")
@Slf4j
public class userRoleController {
@Autowired
private UserRoleService userRoleService;
/**
* 分配
* @param usersRoleDto
* @return
*/
@PostMapping()
public Result usersRole(@RequestBody UsersRoleDto usersRoleDto){
log.info("分配{}",usersRoleDto);
userRoleService.usersRole(usersRoleDto);
return Result.success();
}
}
4、service
package com.bocai.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.bocai.dto.UsersRoleDto;
import com.bocai.pojo.UserRole;
import java.util.List;
/**
* @author cheng
* @description 针对表【user_role】的数据库操作Service
* @createDate 2023-11-07 14:52:46
*/
public interface UserRoleService extends IService<UserRole> {
/**
* 分配
* @param usersRoleDto
*/
void usersRole(UsersRoleDto usersRoleDto);
}
5、serviceImpl
package com.bocai.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.bocai.dto.UsersRoleDto;
import com.bocai.pojo.UserRole;
import com.bocai.service.UserRoleService;
import com.bocai.mapper.UserRoleMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author cheng
* @description 针对表【user_role】的数据库操作Service实现
* @createDate 2023-11-07 14:52:46
*/
@Service
@Slf4j
public class UserRoleServiceImpl extends ServiceImpl<UserRoleMapper, UserRole>
implements UserRoleService{
@Autowired
private UserRoleMapper userRoleMapper;
/**
* 分配
* @param usersRoleDto
*/
@Override
public void usersRole(UsersRoleDto usersRoleDto) {
usersRoleDto.getUsersid().forEach(userId ->{
UserRole userRole = new UserRole();
userRole.setUserid(userId);
userRole.setRoleid(usersRoleDto.getRoleid());
LambdaQueryWrapper<UserRole> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(userId != null,UserRole::getUserid,userId);
Integer count = userRoleMapper.selectCount(lambdaQueryWrapper);
if (count > 0){
userRoleMapper.delete(lambdaQueryWrapper);
}
userRoleMapper.insert(userRole);
});
}
}
这里做了一个查询处理,针对存在的对象,先查询出来删除,再新增
6、mapper
package com.bocai.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bocai.pojo.UserRole;
import org.apache.ibatis.annotations.Mapper;
/**
* @author cheng
* @description 针对表【user_role】的数据库操作Mapper
* @createDate 2023-11-07 14:52:46
* @Entity com.bocai.pojo.UserRole
*/
@Mapper
public interface UserRoleMapper extends BaseMapper<UserRole> {
}