【2020-09-03】day03：Mybatis分页联表查询基于SQL语句与collection映射嵌入的简单性能优化

个人知识碎片的体系化理解与整理，缓慢更新，理解不到位与错漏之处望见谅与指正。

day03摘要思想：mybatis的mapper工具类提供了一整套的简单增删改查逻辑解决方案，在代码层直接使用可满足大部分需求，但若涉及联表而采用了for循环反复调用mybatis的现成方法，则将造成一定的效率浪费问题；可以采用一种在mapper.xml中定义实体映射规则并直接嵌入SQL语句的方式避免这种效率浪费

开发环境：Intellij IDEA 2017.2.6

1.用户-用户角色-角色实体类定义

相关实体类定义如下，表定义略：

package com.dream.summer.air.entity; 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 com.fasterxml.jackson.annotation.JsonFormat; import lombok.Data; import lombok.NoArgsConstructor; import lombok.experimental.Accessors; import java.util.Date; import java.util.List; /** * 用户(User)表实体类 * * @author makejava * @since 2020-08-10 11:47:03 */ @TableName("app_user") @Data @Accessors(chain = true) @NoArgsConstructor public class User { //主键ID @TableId(type = IdType.AUTO) private Integer id; //账号 @TableField(exist = false) private String accountCode; //密码 private String password; //删除标志 private Boolean isDeleted; //创建日期 @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") private Date createDate; @TableField(exist = false) private List<Role> userRole; } package com.dream.summer.air.entity; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import com.fasterxml.jackson.annotation.JsonFormat; import lombok.Data; import lombok.NoArgsConstructor; import lombok.experimental.Accessors; import java.util.Date; /** * 用户与角色关系(UserRole)表实体类 * * @author makejava * @since 2020-08-10 11:48:40 */ @TableName("app_user_role") @Data @Accessors(chain = true) @NoArgsConstructor public class UserRole { @TableId(type = IdType.AUTO) private Integer id; //用户ID private Integer userId; //角色ID private Integer roleId; //优先级，数值越大越高级 private Integer priority; //是否禁用 private Boolean disable; //创建时间 @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") private Date createDate; } package com.dream.summer.air.entity; 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 com.fasterxml.jackson.annotation.JsonFormat; import lombok.Data; import lombok.NoArgsConstructor; import lombok.experimental.Accessors; import java.io.Serializable; import java.util.Date; import java.util.List; /** * 角色(Role)表实体类 * * @author makejava * @since 2020-08-10 11:45:48 */ @TableName("app_role") @Data @Accessors(chain = true) @NoArgsConstructor public class Role { //PK @TableId(type = IdType.AUTO) private Integer id; //角色代码 private String code; //角色名称 private String name; //角色类型 private Short defaultRole; private String userType; //创建时间 @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") private Date createDate; }

 

2.不涉及SQL基于代码层直接解决联表查询问题的方式

此处基础场景为分页查询，目的为查询该页用户信息以及这些用户所对应的角色信息；前端可指定当前页码与每页最大条数；实现层核心代码如下：

@Override public PageUtils findPage(Map<String, Object> queryMap) { //使用mybatis默认方法的正常联表查询 Query<?> query = new Query<>(queryMap); Page<User> page = new Page<>(query.getCurrPage(), query.getLimit()); User entity = JSONObject.parseObject(JSONObject.toJSONString(queryMap), User.class); QueryWrapper<User> eWrapper = new QueryWrapper<>(entity); Page<User> record = this.userMapper.selectPage(page, eWrapper); //在已获取结果中循环使用所有id作为参数获取其角色信息 for(User user : record.getRecords()) { //第一步：获取用户角色关联表信息 UserRole role = new UserRole().setUserId(user.getId()); QueryWrapper<UserRole> roleQueryWrapper = new QueryWrapper<>(role); List<UserRole> userRoleList = this.userRoleMapper.selectList(roleQueryWrapper); //第二步：获取角色信息 List<Role> roleList = new ArrayList<>(); for(UserRole userRole : userRoleList) { Role point = roleMapper.selectById(userRole.getRoleId()); roleList.add(point); } user.setUserRole(roleList); } return new PageUtils(record); }

其中PageUtils为简单封装的分页工具类，数据内核为mybatis的Page类；该方式属于较简单的mybatis api直接应用

 

3.下沉至mapper.xml层使用SQL语句实现的一次性联表查询映射方法

实现层不解决实际联表工作，仅将前端所指定当前页码与每页最大条数取出后传递给Mapper接口中的用户自定义方法，由mapper.xml中使用select标签的id属性关联之，则可实现在mapper.xml的select标签中直接嵌入SQL语句联表查询的方式；

实现层代码如下：

@Override public PageUtils selectUserList(@RequestParam Map<String, Object> queryMap) { Object numObj = queryMap.get("pageNum"); Object sizeObj = queryMap.get("pageSize"); int pageNum = 1; int pageSize = 10; if(numObj != null) { pageNum = Integer.parseInt(numObj.toString()); } if(sizeObj != null) { pageSize = Integer.parseInt(sizeObj.toString()); } Map<String, Object> data = new HashMap<>(); data.put("currIndex", (pageNum-1)*pageSize); data.put("pageSize", pageSize); return new PageUtils(userMapper.selectUserList(data),userMapper.getUserCount(),pageSize,pageNum); }

Mapper接口声明：

List<User> selectUserList(Map<String, Object> queryMap); Integer getUserCount();

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="com.dream.summer.air.app.mapper.UserMapper"> <resultMap id="baseResultMap" type="com.dream.summer.air.app.entity.User" > <id column="id" property="id" jdbcType="INTEGER"/> <result column="password" property="password" jdbcType="INTEGER"/> <result column="is_deleted" property="isDeleted" jdbcType="BIT"/> <result column="create_date" property="createDate" jdbcType="DATE"/> <collection property="userRole" ofType="com.dream.summer.air.app.entity.Role"> <id column="user_role_id" property="id" jdbcType="INTEGER" /> <result column="code" property="code" jdbcType="VARCHAR" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="default_role" property="defaultRole" jdbcType="TINYINT" /> <result column="user_type" property="userType" jdbcType="VARCHAR" /> <result column="user_role_create_date" property="createDate" jdbcType="DATE" /> </collection> </resultMap> <select id="selectUserList" resultMap="baseResultMap" parameterType="Map"> SELECT query_app_user.id , query_app_user.password , query_app_user.is_deleted , query_app_user.create_date , app_role.id AS user_role_id , app_role.code , app_role.name , app_role.default_role , app_role.user_type , app_role.create_date AS user_role_create_date FROM (SELECT * FROM app_user LIMIT #{currIndex} , #{pageSize}) AS query_app_user LEFT JOIN app_user_role ON query_app_user.id = app_user_role.user_id LEFT JOIN app_role ON app_user_role.role_id = app_role.id </select> <select id="getUserCount" resultType="java.lang.Integer"> SELECT COUNT(id) FROM app_user </select> </mapper>

SQL语句拆分如下：

①使用LIMIT关键字查询app_user表对应需要分页查询位置的数据，其中currIndex与pageSize均由实现层指定，currIndex表示查询起点，pageSize表示查询条数；查询后的结果集取别名query_app_user；

②该结果集与另外两个关联表直接进行LEFT JOIN；

③对于已获取联表结果，在最外层SELECT关键字中明确指定其需要获取的字段名称及其别名，所有字段名称与别名需与resultMap中所指定column字段一致；

④resultMap所对应实体中涉及集合字段需要使用collection标签，其集合字段的合并逻辑为resultMap中的id标签所指定的字段相同，在结果集中默认按数据排列顺序进行合并，因此LEFT JOIN后的结果集必须是有序的，经测试，使用这种方式暂未出现结果集无序的情况

 

4.两种方式的效率对比

启动服务后分别使用postman发送请求进行测试：

分别使用代码层方式与SQL联表查询方式获取200条数据，耗时612ms与25ms：

分别使用代码层方式与SQL联表查询方式获取500条数据，耗时1315ms与38ms：

实际耗时每次调用有一定波动，但后者效率已明显高于前者；代码层的调用因在for循环中反复调用mapper工具类的方法，相当于多次进行了SQL语句的查询，而联表查询中只需进行一次查询，以及一次前端分页需要额外进行的COUNT查询即可，避免了多次访问数据库的效率浪费问题

未完待续...