合肥网站建,电子商务seo优化,德州金航网络公司网站建设,深圳最繁华的三个区目录
一、枚举
1、数据库type字段是Integer 类型枚举
2、创建一个该字段的枚举类 TypeEnum 3、修改实体类
4、配置文件新增mybatis-plus的配置
5、检验#xff1a;
5.1 查询显示
5.3 库里验证
二、自增主键不是id字段处理
三、逻辑删除字段不是delete字段处理
1、实…目录
一、枚举
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
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;JsonValueprivate 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 # ?????sqldefault-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)这里因为我是反的所以改成0logic-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启用*/TableLogicprivate Boolean isenable;
2、yml配置文件新增配置
mybatis-plus:configuration:map-underscore-to-camel-case: true # 在映射实体或者属性时将数据库中表名和字段名中的下划线去掉按照驼峰命名法映射log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # ?????sqldefault-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)这里因为我是反的所以改成0logic-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 {Autowiredprivate UserService userService;/*** 查询全部用户--无翻页* return*/GetMappingpublic Result list(){log.info(查询全部启用用户信息);ListUser 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 IServiceUser {/*** 查询全部启用用户 无翻页* return*/ListUser 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 ServiceImplUserMapper, Userimplements UserService{Autowiredprivate UserMapper userMapper;Autowiredprivate UserRoleMapper userRoleMapper;/*** 启用用户* return*/Overridepublic ListUser userList() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.select(User::getLoginname, User::getRealname, User::getIsenable);ListUser 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 BaseMapperUser {}
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 {Autowiredprivate EmpService empService;/*** 条件分页查询* param page* param pageSize* param name* param gender* param begin* param end* return*/GetMappingpublic 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 IServiceEmp {/*** 分页查询* 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 ServiceImplEmpMapper, Empimplements EmpService{Autowiredprivate EmpMapper empMapper;/*** 分页查询* param page* param pageSize* param name* param gender* param begin* param end* return*/Overridepublic PageBean pageList(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {LambdaQueryWrapperEmp 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);PageEmp 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 BaseMapperEmp {}五、多表查询
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 {Autowiredprivate UserService userService;/*** 查询全部用户--无翻页--- 使用sql以及指定显示字段* return*/GetMapping(/sql)public Result listSql(){log.info(查询全部启用用户信息显示sql多表);ListUserRoleDto 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 IServiceUser {/*** 查询全部用户无翻页--(查询全部启用用户信息显示sql多表);* return*/ListUserRoleDto 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 ServiceImplUserMapper, Userimplements UserService{Autowiredprivate UserMapper userMapper;Autowiredprivate UserRoleMapper userRoleMapper;/*** 查询全部用户无翻页--(查询全部启用用户信息显示sql多表);* return*/Overridepublic ListUserRoleDto userSqlList() {ListUserRoleDto 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 BaseMapperUser {/*** 使用sql多表联查* return*/ListUserRoleDto selectSqlList();
}
1.6 xml
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.bocai.mapper.UserMapperresultMap idBaseResultMap typecom.bocai.pojo.Userresult propertyuniqueid columnuniqueId jdbcTypeINTEGER/result propertyadministrationcode columnadministrationCode jdbcTypeVARCHAR/result propertyfixgroupid columnfixGroupId jdbcTypeINTEGER/result propertyloginname columnloginName jdbcTypeVARCHAR/result propertypassword columnpassword jdbcTypeCHAR/result propertyrealname columnrealName jdbcTypeVARCHAR/ result propertyisspecial columnisSpecial jdbcTypeBIT/result propertyisenable columnisEnable jdbcTypeBIT//resultMapsql idBase_Column_ListuniqueId,loginName,password,realName,/sqlselect idselectSqlList resultTypecom.bocai.dto.UserRoleDtoSELECT a.realName,a.loginName,c.name as roleNameFROM USER aJOIN user_role b ON a.uniqueIdb.userIdJOIN role c ON b.roleIdc.uniqueIdWHERE a.isEnable1;/select/mapper2、翻页组合条件查询
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 {Autowiredprivate 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 IServiceUser {/*** 多表联查翻页条件查询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 ServiceImplUserMapper, Userimplements UserService{Autowiredprivate UserMapper userMapper;Autowiredprivate UserRoleMapper userRoleMapper;/*** 多表联查翻页条件查询sql* param page* param pageSize* param realName* param userLevel* return*/Overridepublic PageBean userSqlPageList(Integer page, Integer pageSize,String realName,Integer userLevel) {PageUserRoleDto 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 BaseMapperUser {/*** 多表联查翻页条件查询sql* param pageUserRole* param realName* param userLevel* return*/PageUserRoleDtoselectUserRoleDtoPage(PageUserRoleDto pageUserRole, String realName,Integer userLevel);}
2.5 xml
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.bocai.mapper.UserMapperresultMap idBaseResultMap typecom.bocai.pojo.Userresult propertyuniqueid columnuniqueId jdbcTypeINTEGER/result propertyadministrationcode columnadministrationCode jdbcTypeVARCHAR/result propertyfixgroupid columnfixGroupId jdbcTypeINTEGER/result propertyloginname columnloginName jdbcTypeVARCHAR/result propertypassword columnpassword jdbcTypeCHAR/result propertyrealname columnrealName jdbcTypeVARCHAR/ result propertyisspecial columnisSpecial jdbcTypeBIT/result propertyisenable columnisEnable jdbcTypeBIT//resultMapsql idBase_Column_ListuniqueId,loginName,password,realName,/sqlselect idselectUserRoleDtoPage resultTypecom.bocai.dto.UserRoleDtoSELECT a.uniqueid,a.realName,a.loginName,c.name as roleNameFROM USER aJOIN user_role b ON a.uniqueIdb.userIdJOIN role c ON b.roleIdc.uniqueIdwherea.isEnable1if testrealName ! nulland a.realName like concat(%, #{realName}, %)/ifif testuserLevel ! nulland a.userLevel#{userLevel}/if/where/select/mapper六、事务处理
1、serviceImpl OverrideTransactionalpublic void deleteById(Integer id) {userMapper.deleteById(id);
// int i 1/0;userMapper.updateUserById(id);MapString, 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*/Overridepublic void addRole(Role role) {LambdaQueryWrapperRole 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*/Overridepublic void alterUser(Role role) {LambdaQueryWrapperRole 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 {Autowiredprivate RoleService roleService;/*** 带条件、翻页的查询全部角色* param page 当前第几页* param pageSize 每页多少条* param name 角色名* param roleType 角色类型* param begin 开始时间* param end 结束时间* return*/GetMappingpublic Result page(RequestParam(defaultValue 1) Integer page,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(带翻页和条件的查询全部角色当前第{}页,每页{}条,角色名{},角色类型{},开始时间{}结束时间{},page,pageSize,name,roleType,begin,end);PageBean pageBean roleService.rolePage(page,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 IServiceRole {/*** 带条件、翻页的查询全部角色* param page 当前第几页* param pageSize 每页多少条* param name 角色名* param roleType 角色类型* param begin 开始时间* param end 结束时间* return*/PageBean rolePage(Integer page, 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 ServiceImplRoleMapper, Roleimplements RoleService{Autowiredprivate RoleMapper roleMapper;Autowiredprivate UserRoleMapper userRoleMapper;/*** 带条件的翻页查询全部角色* param page 当前第几页* param pageSize 每页多少条* param name 角色名* param roleType 角色类型* param begin 开始时间* param end 结束时间* return*/Overridepublic PageBean rolePage(Integer page, Integer pageSize, String name, String roleType,LocalDateTime begin,LocalDateTime end) {LambdaQueryWrapperRole 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);PageRole rolePage new Page(page,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 BaseMapperRole {}
九、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 ListInteger 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 {Autowiredprivate 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 IServiceUserRole {/*** 分配* 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 ServiceImplUserRoleMapper, UserRoleimplements UserRoleService{Autowiredprivate UserRoleMapper userRoleMapper;/*** 分配* param usersRoleDto*/Overridepublic void usersRole(UsersRoleDto usersRoleDto) {usersRoleDto.getUsersid().forEach(userId -{UserRole userRole new UserRole();userRole.setUserid(userId);userRole.setRoleid(usersRoleDto.getRoleid());LambdaQueryWrapperUserRole 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 BaseMapperUserRole {}
