接口说明
SQL思路分析：
首先我们需要查询sys_user表中的所有数据，然后根据条件去添加动态SQL去查数据。
<select id="pageQuery" resultType="com.itheima.stock.pojo.entity.SysUser">
SELECT ssu.*
FROM sys_user as ssu
<where>
<if test="username!= null and username!= ''">
AND ssu.username like concat('%',#{username},'%')
</if>
<if test="nickName!= null and nickName!= ''">
AND ssu.nick_name like concat('%',#{nickName},'%')
</if>
<if test="startTime!= null and startTime!= ''">
AND ssu.create_time >= STR_TO_DATE(#{startTime}, '%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!= null and endTime!= ''">
AND ssu.create_time <=STR_TO_DATE(#{endTime}, '%Y-%m-%d %H:%i:%s')
</if>
</where>
</select>但是这样查出来的数据缺失createUserNam ，updateUserName。我们上面查出了create_id ，和update_id。我们需要根据这两个Id分别匹配其操作人的Id。 实现方法：进行表的左链接
SELECT ssu.*, create_su.username AS createId, update_su.username AS updateId：选择sys_user表（别名为ssu）的所有列，并通过连接获取的用户名分别命名为createId和updateId。FROM sys_user as ssu LEFT JOIN sys_user create_su ON ssu.create_id = create_su.id LEFT JOIN sys_user update_su ON ssu.update_id = update_su.id：从sys_user表进行两次左连接，一次用于获取创建用户的信息，一次用于获取更新用户的信息。如果create_id或update_id为空，连接结果中对应的createId或updateId将为NULL。
封装对象：
/**
* 用户表
*
* @TableName sys_user
*/
@Data
@ApiModel(description = "用户基本信息")
@AllArgsConstructor
@NoArgsConstructor
public class SysUser implements Serializable {
/**
* 用户id
*/
@ApiModelProperty("主键Id")
private Long id;
/**
* 账户
*/
private String username;
/**
* 用户密码密文
*/
private String password;
/**
* 手机号码
*/
private String phone;
/**
* 真实名称
*/
private String realName;
/**
* 昵称
*/
private String nickName;
/**
* 邮箱(唯一)
*/
private String email;
/**
* 账户状态(1.正常 2.锁定 )
*/
private Integer status;
/**
* 性别(1.男 2.女)
*/
private Integer sex;
/**
* 是否删除(1未删除；0已删除)
*/
private Integer deleted;
/**
* 创建人
*/
private Long createId;
/**
* 更新人
*/
private Long updateId;
/**
* 创建来源(1.web 2.android 3.ios )
*/
private Integer createWhere;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新时间
*/
private Date updateTime;
/**
* 创建人姓名
*/
private String createUserName;
/**
* 更新人姓名
*/
private String updateUserName;
private static final long serialVersionUID = 1L;
}/**
* 分页工具类
*/
@ApiModel(description = "分页工具类")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> implements Serializable {
/**
* 总记录数
*/
@ApiModelProperty(value = "总记录数", required = true)
private Integer totalRows;
/**
* 总页数
*/
@ApiModelProperty(value = "总页数", required = true)
private Integer totalPages;
/**
* 当前第几页
*/
@ApiModelProperty(value = "当前第几页", required = true)
private Integer pageNum;
/**
* 每页记录数
*/
@ApiModelProperty(value = "每页记录数", required = true)
private Integer pageSize;
/**
* 当前页记录数
*/
@ApiModelProperty(value = "当前页记录数", required = true)
private Integer size;
/**
* 结果集
*/
@ApiModelProperty(value = "结果集", required = true)
private List<T> rows;
/**
* 分页数据组装
* @param pageInfo
* @return
*/
public PageResult(PageInfo<T> pageInfo) {
totalRows = (int) pageInfo.getTotal();
totalPages = pageInfo.getPages();
pageNum = pageInfo.getPageNum();
pageSize = pageInfo.getPageSize();
size = pageInfo.getSize();
rows = pageInfo.getList();
}
}/**
* 前端响应对象
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SearchParams implements Serializable {
private String pageNum;
private String pageSize;
private String username;
private String nickName;
private String startTime;
private String endTime;
}controller层：
@ApiImplicitParams({
@ApiImplicitParam(paramType = "body", dataTypeClass = SearchParams.class, required = true, name = "searchParams", value = "")
})
@ApiOperation(value = "多条件综合查询用户分页信息", notes = "多条件综合查询用户分页信息", httpMethod = "POST")
@PostMapping("/users")
public R<PageResult<SysUser>> selectUsers(@RequestBody SearchParams searchParams){
return userService.selectUsers(searchParams);
}service层：
R<PageResult<SysUser>> selectUsers(SearchParams searchParams); @Override
public R<PageResult<SysUser>> selectUsers(SearchParams searchParam) {
//利用分页工具构建分页条件
//配置分页参数
PageHelper.startPage(Integer.valueOf(searchParam.getPageNum()),Integer.valueOf(searchParam.getPageSize()));
//根据条件到数据库中查询
Page<SysUser> page = sysUserMapper.pageQuery(searchParam);
Integer totalRows = (int) page.getTotal();
List<SysUser> rows = page.getResult();
int pageNum= page.getPageNum();
int pageSize = page.getPageSize();
int totalPages = page.getPages();
int size = page.getEndRow() - page.getStartRow() + 1;
PageResult<SysUser> pageResult1 = new PageResult<SysUser>(totalRows,totalPages,pageNum,pageSize,size,rows);
return R.ok(pageResult1);
}mapper层：
Page<SysUser> pageQuery(SearchParams searchParam);<select id="pageQuery" resultType="com.itheima.stock.pojo.entity.SysUser">
SELECT ssu.*,
create_su.username AS createUserName,
update_su.username AS updateUserName
FROM sys_user as ssu
LEFT JOIN sys_user create_su ON ssu.create_id = create_su.id
LEFT JOIN sys_user update_su ON ssu.update_id = update_su.id
<where>
<if test="username!= null and username!= ''">
AND ssu.username like concat('%',#{username},'%')
</if>
<if test="nickName!= null and nickName!= ''">
AND ssu.nick_name like concat('%',#{nickName},'%')
</if>
<if test="startTime!= null and startTime!= ''">
AND ssu.create_time >= STR_TO_DATE(#{startTime}, '%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!= null and endTime!= ''">
AND ssu.create_time <=STR_TO_DATE(#{endTime}, '%Y-%m-%d %H:%i:%s')
</if>
</where>
</select>