手写多表模糊条件分页查询 VS MP实现多表分页查询
手写多表模糊条件分页查询与MP实现分页查询Demo
前言
若想作为一名合格CRUD程序员,SQL语句的编写是前提,尽管现在有许许多多持久层的框架以及提供的分页查询等插件,使得我们在日常做项目过程中,几乎很少去手写SQL语句了,但是大部分的插件提供的分页查询对于单表查询非常友好,但是一到多表连接查询就感到很吃力,如果再加上非常复杂的业务逻辑需求,就会显得无能为力了,此时就得我们去手动写SQL去实现了。
这里我以一个业务需求案例作为演示,主要是为了记录一下针对于需要多表联查、条件查询、嵌套对象查询
时得解决方案。
由于没有过于复杂的业务逻辑,仅涉及到两个表的连接查询,只是为了演示手写一个针对于涉及多表联查、条件查询、嵌套对象查询时需要分页的场景,手写一个实现该场景下的分页查询功能,所以也可以使用Mybatis-Plus提供的分页插件来实现,下面也进行了演示。
需求描述
数据库中有一张用户表,角色表且用户和角色之间是一对一关系,在用户表中有一个RoleID的字段用于关联其之间的关系。业务逻辑需求是这样的:通过前端请求进行模糊条件的查询并且实现分页返回的效果,并且并不返回用户的全部字段,而是部门字段,并将其封装到UserVO实体中。
前端请求
由前端发送GET请求,同时遵循RESTFul风格,即
- 将当前页码以及每页条数放到路径中
- 将查询条件以参数形式进行传递
请求路径示例:http://localhost/user/admin/users/1/5?id=&username=&isVip=&status=&roleId=
后端控制层接收到请求后,进行相应得处理。
手写分页实现
UserVO实体类
@Data
public class UserVO implements Serializable {
private Long id;
private String username;
private String nickname;
private Integer sex; // 性别 0男 1女 2保密
@JsonFormat(timezone = "GMT+8", pattern = "yyyy年MM月dd日")
private Date birth;
private String email;
private String avatar;
private String description;
private String phoneNumber;
private Integer answerNumber;
private Role role;
private Integer status; // 状态 0锁定 1有效
private Integer isVip; // 是否VIP 0否 1是
}
注意:这里UserVO里需要一个Role对象。
UserController
@GetMapping("/admin/users/{currentPage}/{pageSize}")
public Result<List<UserVO>> getAllusers(@PathVariable Integer currentPage,
@PathVariable Integer pageSize,
UserSerachDTO userSerachDTO){
Map<String, Object> allUsers = userService.getAllUsers(currentPage, pageSize, userSerachDTO);
if (allUsers != null){
List<UserVO> data = (List<UserVO>) allUsers.get("data");
String total = (String) allUsers.get("total");
return Result.success(data, total);
}
return Result.error(200, "用户信息查询失败~");
}
这里只需要先注意请求路径和请求参数即可。UserSerachDTO实体呢,就是由前端传递过来的模糊查询得条件,代码如下
UserSerachDTO.java
/**
* @Classname UserSerachDTO
* @Description 前端传递过来的用户搜索条件
* @Date 2022/5/5 22:16
* @Created by YJS
* @WebSite www.imyjs.cn
*/
@Data
public class UserSerachDTO {
private Integer id;
private String username;
private Integer isVip;
private Integer status;
private Integer roleId;
}
UserService
public interface UserService extends IService<User> {
Map<String, Object> getAllUsers(Integer currentPage, Integer pageSize, UserSerachDTO userSerachDTO);
}
// 实现类
/**
* 获取系统所有用户信息
* @return
*/
@Override
public Map<String, Object> getAllUsers(Integer currentPage, Integer pageSize, UserSerachDTO userSerachDTO) {
// 返回结果
Map<String, Object> resultMap = new HashMap<>();
// 获取符合条件的记录总条数
Integer total = userMapper.getAllUsersCounts(userSerachDTO);
// 获取记录数据
// 计算当前页的起始索引
Integer currentIndex = (currentPage-1) * pageSize;
List<UserVO> data = userMapper.getAllUsers(currentIndex, pageSize, userSerachDTO);
// 计算总页数
Integer totalPages = 0;
if (total != 0){ // 避免算数异常
if (total % pageSize != 0){
totalPages = (total / pageSize) + 1;
}else {
totalPages = total / pageSize;
}
// 如果当前页码值大于了总页码值,那么使用最大页码值作为当前页码值重新执行查询操作,
if (currentPage > totalPages) {
currentPage = (currentPage-2) * pageSize;
data = userMapper.getAllUsers(currentPage, pageSize, userSerachDTO);
}
resultMap.put("data", data);
resultMap.put("total", total.toString());
return resultMap;
}
return null;
}
分页查询处理核心代码逻辑就在这里了,首先是获取了符合条件数据的总记录数,并由其计算出总页数,从而解决 如果当前页码值大于了总页码值的问题,同时需要注意计算当前页的起始索引,是固定的公式:
Integer currentIndex = (currentPage-1) * pageSize;
最后由于前端需要数据以及总记录数,所以封装了一个Map结果集,由此返回到Controller。
UserMapper
@Mapper
public interface UserMapper extends BaseMapper<User> {
/**
* 根据搜索条件查询指定页码的用户信息
* @param currentPage 当前页码
* @param pageSize 每页查询数量
* @param userSerachDTO 搜索条件
* @return 用户信息
*/
List<UserVO> getAllUsers(@Param("currentPage") Integer currentPage, @Param("pageSize") Integer pageSize, @Param("user") UserSerachDTO userSerachDTO);
/**
* 根据搜索条件查询用户条数
* @param userSerachDTO 搜索条件
* @return 用户条数
*/
Integer getAllUsersCounts(@Param("user") UserSerachDTO userSerachDTO);
}
Mapper.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="cn.imyjs.api.system.mapper.UserMapper">
<!--使用association处理映射关系-->
<resultMap id="getAllUsersRusult" type="cn.imyjs.api.system.vo.UserVO">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="nickname" column="nickname"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<result property="birth" column="birth"/>
<result property="avatar" column="avatar"/>
<result property="description" column="description"/>
<result property="phoneNumber" column="phone_number"/>
<result property="answerNumber" column="answer_number"/>
<result property="status" column="status"/>
<result property="isVip" column="is_vip"/>
<!--collection:一对多
assocication:一对一、多对一
-->
<association property="role" javaType="cn.imyjs.common.pojo.Role">
<result column="rid" property="id" />
<result column="role_name" property="roleName" />
<result column="remark" property="remark" />
<result column="demand" property="demand" />
<!--<result column="status" property="status" />-->
</association>
</resultMap>
<select id="getAllUsers" resultMap="getAllUsersRusult">
select sys_user.*,sys_role.role_name,sys_role.remark,sys_role.demand,sys_role.id as rid
from sys_user left join sys_role on sys_user.roleid=sys_role.id
<where>
<if test="user.id != null">
and sys_user.id=#{user.id}
</if>
<if test="user.username != null and user.username != ''">
and `username` like "%"#{user.username}"%"
</if>
<if test="user.status != null and user.status == 0 or user.status == 1">
and sys_user.status=#{user.status}
</if>
<if test="user.isVip != null">
and is_vip=#{user.isVip}
</if>
<if test="user.roleId != null">
and roleid=#{user.roleId}
</if>
</where>
limit #{currentPage}, #{pageSize}
</select>
<select id="getAllUsersCounts" resultType="java.lang.Integer">
select count(*)
from sys_user left join sys_role on sys_user.roleid=sys_role.id
<where>
<if test="user.id != null">
and sys_user.id=#{user.id}
</if>
<if test="user.username != null and user.username != ''">
and `username` like "%"#{user.username}"%"
</if>
<if test="user.status != null">
and sys_user.status=#{user.status}
</if>
<if test="user.isVip != null">
and is_vip=#{user.isVip}
</if>
<if test="user.roleId != null">
and roleid=#{user.roleId}
</if>
</where>
</select>
</mapper>
进行SQL查询的核心。首先是自定义了一个resultMap,也就是返回结果集,使返回结果与UserVO实例相对应。针对于内嵌Role实体,由于用户和角色是一对一的关系,这里使用assocication
来定义,如果业务逻辑是一对多的关系,即一个用户可以拥有多个角色,可以使用collection
来定义。然后就是SQL语句联查,以及进行模糊条件的查询,这里使用了动态SQL。最后一定要注意字段名称要与数据库保持一致!还有if语句中的test条件的编写,如果是整型数据时只需user.id != null
进行判断。
Test
@Test
public void test(){
UserSerachDTO userSerachDTO = new UserSerachDTO();
userSerachDTO.setUsername("mi");
userSerachDTO.setStatus(1);
userSerachDTO.setId(1);
Map<String, Object> allUsers = userService.getAllUsers(1, 5, userSerachDTO);
List<UserVO> data = (List<UserVO>) allUsers.get("data");
data.forEach(System.out::println);
}
Mybatis-Plus分页查询
其实这里可以使用Mybatis-Plus提供的分页查询插件来实现,但是也是需要进行编写对应的SQL语句,只是省去了Service中手动进行处理分页功能实现的逻辑。具体操作如下。
UserController
@GetMapping("/admin/users/{currentPage}/{pageSize}")
public Result<List<UserVO>> getAllusersByMP(@PathVariable Integer currentPage,
@PathVariable Integer pageSize,
UserSerachDTO userSerachDTO){
Page<UserVO> iPage = new Page<UserVO>(currentPage, pageSize);
Page<UserVO> allUsersByMP = userService.getAllUsersByMP(iPage, userSerachDTO);
List<UserVO> users = allUsersByMP.getRecords();
long total = allUsersByMP.getTotal();
if (total > 0){
return Result.success(users, String.valueOf(total));
}else {
return Result.error(200, "用户信息查询失败~");
}
}
UserService
/**
* 获取系统所有用户信息 使用MP提供的分页插件
* @param iPage 设置分页参数
* @param userSerachDTO 查询条件
*/
@Override
public Page<UserVO> getAllUsersByMP(Page<UserVO> iPage, UserSerachDTO userSerachDTO) {
Page<UserVO> allUsersByMP = userMapper.getAllUsersByMP(iPage, userSerachDTO);
return allUsersByMP;
}
不再需要手写分页查询逻辑,是不是简单多了.....
UserMapper
/**
* 根据搜索条件查询指定页码的用户信息 手写分页
* @param iPage 分页对象
* @param userSerachDTO 模糊查询条件
* @return
*/
Page<UserVO> getAllUsersByMP(IPage<UserVO> iPage, @Param("user") UserSerachDTO userSerachDTO);
不再需要自己去写分页查询,只需传递IPage
对象
RoleMapper
@Mapper
public interface RoleMapper extends BaseMapper<Role> {
@Select("select * from sys_role where id=#{roleid}")
List<Role> selectByStudentId(Integer roleid);
}
由于UserVO实体中需要Role对象,而且实现分页,这里采用分步查询的方式。
UserMapper.xml
<resultMap id="getAllUsersByMP" type="cn.imyjs.api.system.vo.UserVO">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="nickname" column="nickname"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<result property="birth" column="birth"/>
<result property="avatar" column="avatar"/>
<result property="description" column="description"/>
<result property="phoneNumber" column="phone_number"/>
<result property="answerNumber" column="answer_number"/>
<result property="status" column="status"/>
<result property="isVip" column="is_vip"/>
<collection property="role" column="roleid"
select="cn.imyjs.api.system.mapper.RoleMapper.selectByStudentId"/>
</resultMap>
<select id="getAllUsersByMP" resultMap="getAllUsersByMP">
select * from sys_user
<where>
<if test="user.id != null">
and sys_user.id=#{user.id}
</if>
<if test="user.username != null and user.username != ''">
and `username` like "%"#{user.username}"%"
</if>
<if test="user.status != null and user.status == 0 or user.status == 1">
and sys_user.status=#{user.status}
</if>
<if test="user.isVip != null">
and is_vip=#{user.isVip}
</if>
<if test="user.roleId != null">
and roleid=#{user.roleId}
</if>
</where>
</select>
因为需要进行模糊条件查询,所以仍然需要自己手动编写动态SQL语句。
注意:这里的collection
中使用了select
属性指向了RoleMapper
中的方法。
Test
@Test
public void testMP(){
UserSerachDTO userSerachDTO = new UserSerachDTO();
// userSerachDTO.setUsername("mi");
// userSerachDTO.setStatus(1);
//userSerachDTO.setId(1);
Page<UserVO> iPage = new Page<UserVO>(1, 5);
Page<UserVO> allUsersByMP = userService.getAllUsersByMP(iPage, userSerachDTO);
System.out.println(allUsersByMP.getRecords());
}
到这里,就实现了刚开始的需求。
总结
针对于数据库查询看似是挺容易的一件事,但是如果业务逻辑过于复杂,或者当数据库表之间的关系设计不当时,在后端进行实现时就会有些吃力,此时如果使用框架提供的一些工具就可能会遇到一些问题,有些时候还是不如自己去手写SQL来的直接。可见,对于数据库的设计,比如字段设置、字段约束、表关系设计等是否恰当是至关重要的,直接会影响到后面业务逻辑的实现难易程度。
好了,以上就是针对这些问题的解决办法,感觉肯定不是最完美的,会有更简单的解决方案,这里只是为了记录这种解决办法,希望能帮助到你!
学习的技术是拿来用的,不是用来死记硬背的!灵活运用,才能记得牢,实践出真知!