手写多表模糊条件分页查询 VS MP实现多表分页查询

理想 • 2022-05-06 • Java, 教程记录, 编程代码 • 393 阅读

手写多表模糊条件分页查询与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来的直接。可见，对于数据库的设计，比如字段设置、字段约束、表关系设计等是否恰当是至关重要的，直接会影响到后面业务逻辑的实现难易程度。

好了，以上就是针对这些问题的解决办法，感觉肯定不是最完美的，会有更简单的解决方案，这里只是为了记录这种解决办法，希望能帮助到你！

学习的技术是拿来用的，不是用来死记硬背的！灵活运用，才能记得牢，实践出真知！

微信关注

编程那点事儿

打赏

本站为非盈利性站点，所有资源、文章等仅供学习参考，并不贩卖软件且不存在任何商业目的及用途，如果您访问和下载某文件，表示您同意只将此文件用于参考、学习而非其他用途。
本站所发布的一切软件资源、文章内容、页面内容可能整理来自于互联网，在此郑重声明本站仅限用于学习和研究目的；并告知用户不得将上述内容用于商业或者非法用途，否则一切后果请用户自负。
如果本站相关内容有侵犯到您的合法权益，请仔细阅读本站公布的投诉指引页相关内容联系我，依法依规进行处理！
作者：理想
链接：https://www.imyjs.cn/archives/772

THE END

二维码

手写多表模糊条件分页查询 VS MP实现多表分页查询

手写多表模糊条件分页查询与MP实现分页查询Demo 前……

RBAC-基于角色的访问控制（附经典五张数据表结构）

<<上一篇

SpringBoot整合JSP和Shiro避坑！

下一篇>>

文章目录

关闭

搜索内容

手写多表模糊条件分页查询 VS MP实现多表分页查询

手写多表模糊条件分页查询与MP实现分页查询Demo

前言

需求描述

前端请求

手写分页实现

UserVO实体类

UserController

UserService

UserMapper

Mapper.xml

Test

Mybatis-Plus分页查询

UserController

UserService

UserMapper

RoleMapper

UserMapper.xml

Test

总结

微信关注

取消回复

共有 0 条评论

关于作者

近期文章

热门文章

目录