目录
添加依赖
<!-- SpringBoot版本 -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<!-- Mybatis版本 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
建表
【用户表】
CREATE TABLE `t_user` (
`ID` bigint NOT NULL COMMENT '主键ID',
`GENDER` tinyint DEFAULT NULL COMMENT '性别',
`USER_NAME` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户姓名',
`ACCOUNT_NAME` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户账号',
`NICK_NAME` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户昵称',
`ACCOUNT_PASSWORD` varchar(8) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '账户密码',
`JOIN_COMPANY_TIME` datetime DEFAULT NULL COMMENT '入职时间',
`OUT_COMPANY_TIME` datetime DEFAULT NULL COMMENT '离职时间',
`PHONE_NO` varchar(11) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号码',
`MAIL_ACCOUNT` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱账号',
`RELATION_FILE` json DEFAULT NULL COMMENT '关联附件',
`TENANT_ID` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '租户号',
`REVISION` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '乐观锁',
`CREATED_BY` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
`CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
`UPDATED_BY` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
`UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';
【角色表】
CREATE TABLE `t_role` (
`ID` bigint NOT NULL COMMENT '主键ID',
`TENANT_ID` bigint DEFAULT NULL COMMENT '租户号',
`ROLE_CODE` tinyint DEFAULT NULL COMMENT '角色编码',
`ROLE_NAME` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '角色名称',
`ROLE_PARENT_ID` bigint DEFAULT NULL COMMENT '父级角色ID',
`REVISION` bigint DEFAULT NULL COMMENT '乐观锁',
`CREATED_BY` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
`CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
`UPDATED_BY` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
`UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='角色表';
【用户角色关联表】
CREATE TABLE `t_user_role` (
`ID` bigint NOT NULL COMMENT '主键ID',
`USER_ID` bigint DEFAULT NULL COMMENT '用户ID',
`ROLE_ID` bigint DEFAULT NULL COMMENT '角色ID',
`TENANT_ID` bigint DEFAULT NULL COMMENT '租户号',
`REVISION` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '乐观锁',
`CREATED_BY` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
`CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
`UPDATED_BY` varchar(90) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
`UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户角色关联表';
相关实体类
[User.java]
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "用户表",description = "用户表")
@TableName(value = "t_user",autoResultMap = true)
public class User extends BaseDo {
private static final long serialVersionUID = 9004215942145470512L;
/** 性别 */
@ApiModelProperty(name = "性别",notes = "性别 0-男 1-女 2-其他")
@Dict(type = "GENDER")
private Integer gender ;
/** 用户姓名 */
@ApiModelProperty(name = "用户姓名",notes = "用户姓名")
private String userName ;
/** 用户账号 */
@ApiModelProperty(name = "用户账号",notes = "用户账号")
private String accountName ;
/** 用户昵称 */
@ApiModelProperty(name = "用户昵称",notes = "用户昵称")
private String nickName ;
/** 账户密码 */
@ApiModelProperty(name = "账户密码",notes = "账户密码")
private String accountPassword ;
/** 入职时间 */
@ApiModelProperty(name = "入职时间",notes = "入职时间")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime joinCompanyTime ;
/** 离职时间 */
@ApiModelProperty(name = "离职时间",notes = "离职时间")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime outCompanyTime ;
/** 手机号码 */
@ApiModelProperty(name = "手机号码",notes = "手机号码")
private String phoneNo ;
/** 邮箱账号 */
@ApiModelProperty(name = "邮箱账号",notes = "邮箱账号")
private String mailAccount ;
/** 关联附件 */
@ApiModelProperty(name = "相关附件",notes = "相关附件")
@TableField(typeHandler = FastJson2TypeHandler.class)
private List<SysFile> relationFile;
}
[Role.java]
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "角色表", description = "角色表")
@TableName("t_role")
public class Role extends BaseDo {
private static final long serialVersionUID = -8342010020272298239L;
/**
* 角色编码
*/
@ApiModelProperty(name = "角色编码", notes = "角色编码")
private Integer roleCode;
/**
* 角色名称
*/
@ApiModelProperty(name = "角色名称", notes = "角色名称")
private String roleName;
/**
* 父级角色ID
*/
@ApiModelProperty(name = "父级角色ID", notes = "父级角色ID")
private Long roleParentId;
}
[UserRole.java]
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "用户角色关联表", description = "用户角色关联表")
@TableName("t_user_role")
public class UserRole extends BaseDo {
private static final long serialVersionUID = -3847558162524282787L;
/**
* 用户ID
*/
@ApiModelProperty(name = "用户ID", notes = "用户ID")
private Long userId;
/**
* 角色ID
*/
@ApiModelProperty(name = "角色ID", notes = "角色ID")
private Long roleId;
}
[UserQueryDTO.java]
@Data
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "UserQueryDTO",description = "UserQueryDTO")
public class UserQueryDTO extends BasePage {
private static final long serialVersionUID = 6585437296666914606L;
/**
* 角色id
*/
@ApiModelProperty(name = "角色id",notes = "角色id")
private Long roleId;
/** 用户姓名 */
@ApiModelProperty(name = "用户姓名",notes = "用户姓名")
private String userName ;
}
[UserVO.java]
@Data
@ApiModel(value = "UserVO",description = "UserVO")
public class UserVO {
/** 主键ID */
@ApiModelProperty(name = "主键ID",notes = "主键ID")
private Long id ;
/** 用户姓名 */
@ApiModelProperty(name = "用户姓名",notes = "用户姓名")
private String userName ;
/** 用户账号 */
@ApiModelProperty(name = "用户账号",notes = "用户账号")
private String accountName ;
/**用户对应的角色信息*/
@ApiModelProperty(name = "用户角色信息",notes = "用户角色信息")
private List<Role> roleList;
}
Mapper层
【UserMapper.java】
@Mapper
public interface UserMapper extends BaseMapper<User> {
Page<UserVO> pageQuery(@Param("page") Page<?> page, @Param(Constants.WRAPPER) Wrapper<?> wrapper);
Role getRoleById(@Param("roleId") Long roleId);
}
【UserMapper.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.cl.study.demo.study.mapper.UserMapper">
<!--设置resultMap-->
<resultMap id="userPageQuery" type="com.cl.study.demo.study.pojo.vo.UserVO">
<id property="id" column="ID"/>
<result property="userName" column="USER_NAME"/>
<result property="accountName" column="ACCOUNT_NAME"/>
<collection property="roleList" ofType="com.cl.study.demo.study.pojo.dos.Role" column="id=ROLE_ID" select="getRoleById">
<id column="ID" jdbcType="INTEGER" property="id"/>
<result column="ROLE_NAME" jdbcType="VARCHAR" property="roleName"/>
</collection>
</resultMap>
<select id="pageQuery" resultMap="userPageQuery">
SELECT
u.ID,
u.USER_NAME,
u.ACCOUNT_NAME,
ur.ROLE_ID
FROM
t_user u
LEFT JOIN t_user_role ur ON u.id = ur.USER_ID ${ew.customSqlSegment}
</select>
<select id="getRoleById" resultType="com.cl.study.demo.study.pojo.dos.Role">
SELECT
r.ID,
r.ROLE_NAME
FROM
t_role r
WHERE
r.ID = #{id}
</select>
</mapper>
service层
[UserService.java]
/**
* 分页查询用户信息
* @param queryDTO 查询参数封装
* @return 分页结果
*/
PageResult<UserVO> pageQueryListUser(UserQueryDTO queryDTO);
[UserServiceImpl.java]
/**
* 分页查询用户信息
* @param queryDTO 查询参数封装
* @return 分页结果
*/
@Override
public PageResult<UserVO> pageQueryListUser(UserQueryDTO queryDTO) {
QueryWrapper<?> wrapper = new QueryWrapper<>();
if (ObjectUtil.isNotNull(queryDTO.getRoleId())) {
wrapper.eq("ur.ROLE_ID",queryDTO.getRoleId());
}
if (StrUtil.isNotBlank(queryDTO.getUserName())) {
wrapper.like("u.USER_NAME",queryDTO.getRoleId());
}
Page<User> page = Page.of(queryDTO.getPageNum(),queryDTO.getPageSize());
Page<UserVO> userVOPage = this.userMapper.pageQuery(page, wrapper);
return PageResult.pageResult(userVOPage);
}
controller层
[UserController.java]
/**
* 用户分页查询
* @param queryDTO 查询参数封装对象
* @return 是否成功
*/
@PostMapping(DemoUrls.USER_PAGE_QUERY)
@ApiOperation("用户分页查询")
public ResultBean<PageResult<UserVO>> pageQuery(@RequestBody UserQueryDTO queryDTO) {
return ResultBean.ok(this.userService.pageQueryListUser(queryDTO));
}
接口测试
查询结果
{
"code": "0",
"msg": "成功",
"data": {
"total": 12,
"current": 1,
"list": [
{
"id": 1547852965484343300,
"userName": "helloword",
"accountName": "HF_2022122",
"roleList": [
{
"id": 2,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"roleCode": null,
"roleName": "customer",
"roleParentId": null
}
]
},
{
"id": 1547852965484343300,
"userName": "helloword",
"accountName": "HF_2022122",
"roleList": [
{
"id": 1,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"roleCode": null,
"roleName": "admin",
"roleParentId": null
}
]
},
{
"id": 1551826864240750600,
"userName": "zhangsan",
"accountName": "HF_20220726",
"roleList": null
},
{
"id": 1551827035011838000,
"userName": "lisi",
"accountName": "HF_20220727",
"roleList": null
},
{
"id": 1551827113835393000,
"userName": "wangwu",
"accountName": "HF_20220728",
"roleList": null
},
{
"id": 1551827281171345400,
"userName": "张",
"accountName": "HF_20220729",
"roleList": null
},
{
"id": 1551827359680327700,
"userName": "王",
"accountName": "HF_20220730",
"roleList": null
},
{
"id": 1551827449149026300,
"userName": "李",
"accountName": "HF_20220731",
"roleList": null
},
{
"id": 1551827560725901300,
"userName": "周",
"accountName": "HF_20220732",
"roleList": null
},
{
"id": 1551827688291463200,
"userName": "郑",
"accountName": "HF_20220733",
"roleList": null
}
]
}
}
PS:过程中遇到以下的坑
问题描述
【使用mybatis-plus进行连表分页查询时出现以下问题,但是不影响查询结果】
2022-07-26 16:20:20.455 WARN 17244 --- [nio-8999-exec-2] c.b.m.e.p.i.PaginationInnerInterceptor : optimize this sql to a count sql has exception, sql:"SELECT u.ID,
u.USER_NAME,
u.ACCOUNT_NAME,
ur.ROLE_ID
FROM t_user u
LEFT JOIN t_user_role ur ON u.id = ur.USER_ID", exception:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "," ","
at line 3, column 30.
Was expecting one of:
"&"
"::"
";"
"<<"
">>"
"ACTION"
"ACTIVE"
"ALGORITHM"
"ARCHIVE"
"ARRAY"
"AS"
"AT"
"BYTE"
"CASCADE"
"CASE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"CHECKPOINT"
"COLLATE"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"COSTS"
"CYCLE"
"DBA_RECYCLEBIN"
"DEFAULT"
"DESC"
"DESCRIBE"
"DISABLE"
"DISCONNECT"
"DIV"
"DO"
"DUMP"
"DUPLICATE"
"EMIT"
"ENABLE"
"END"
"EXCLUDE"
"EXTRACT"
"FALSE"
"FILTER"
"FIRST"
"FLUSH"
"FN"
"FOLLOWING"
"FORMAT"
"FULLTEXT"
"GROUP"
"HAVING"
"HISTORY"
"INDEX"
"INSERT"
"INTERVAL"
"INTO"
"ISNULL"
"JSON"
"KEY"
"LAST"
"LEADING"
"LINK"
"LOCAL"
"LOG"
"MATERIALIZED"
"NO"
"NOLOCK"
"NULLS"
"OF"
"OPEN"
"OVER"
"PARALLEL"
"PARTITION"
"PATH"
"PERCENT"
"PRECISION"
"PRIMARY"
"PRIOR"
"QUERY"
"QUIESCE"
"RANGE"
"READ"
"RECYCLEBIN"
"REGISTER"
"REPLACE"
"RESTRICTED"
"RESUME"
"ROW"
"ROWS"
"SCHEMA"
"SEPARATOR"
"SEQUENCE"
"SESSION"
"SHUTDOWN"
"SIBLINGS"
"SIGNED"
"SIZE"
"SKIP"
"START"
"SUSPEND"
"SWITCH"
"SYNONYM"
"SYSTEM"
"TABLE"
"TABLESPACE"
"TEMP"
"TEMPORARY"
"TIMEOUT"
"TO"
"TOP"
"TRUE"
"TRUNCATE"
"TRY_CAST"
"TYPE"
"UNQIESCE"
"UNSIGNED"
"USER"
"VALIDATE"
"VALUE"
"VALUES"
"VIEW"
"WINDOW"
"XML"
"ZONE"
"["
"^"
"|"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<K_NEXTVAL>
<K_STRING_FUNCTION_NAME>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>
查询语句
[UserMapper.java]
@Mapper
public interface UserMapper extends BaseMapper<User> {
Page<UserVO> pageQuery(@Param("page") Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);
Role getRoleById(@Param("roleId") Long roleId);
}
<select id="pageQuery" resultMap="userPageQuery">
SELECT
u.ID ,
u.USER_NAME,
u.ACCOUNT_NAME,
u.NICK_NAME,
ur.ROLE_ID
FROM
t_user AS u
LEFT JOIN t_user_role AS ur ON u.id = ur.USER_ID ${ew.customSqlSegment}
</select>
<select id="getRoleById" resultType="com.cl.study.demo.study.pojo.dos.Role">
SELECT
r.ID,
r.ROLE_NAME
FROM
t_role AS r
WHERE
r.ID = #{id}
</select>
[UserServiceImpl.java]
@Override
public PageResult<UserVO> pageQueryListUser(UserQueryDTO queryDTO) {
QueryWrapper<User> wrapper = new QueryWrapper<>();
if (ObjectUtil.isNotNull(queryDTO.getRoleId())) {
wrapper.eq("ur.ROLE_ID",queryDTO.getRoleId());
}
if (StrUtil.isNotBlank(queryDTO.getUserName())) {
wrapper.like("u.USER_NAME",queryDTO.getUserName());
}
Page<User> page = Page.of(queryDTO.getPageNum(),queryDTO.getPageSize());
Page<UserVO> userVOPage = this.userMapper.pageQuery(page, wrapper);
return PageResult.pageResult(userVOPage);
}
原因分析
【因为别名ur可能是mybatisplus的关键字或者保留字,导致无法进行sql优化】
解决方案
1、更换别名
2、使用 【`ur`】
验证结果
请求成功,并且控制台不会再出现之前的报错信息