SSM项目开发——Mybatis中使用View视图+union 解决多表数据合并分页查询问题

背景：在博主做项目过程中遇到这样一个问题。

数据库建表模型：

如图可以看到，用户表泛化为住户与服务人员，泛化后根据自己的特性，生成独立的表。在建表过程中，用户表只用作登录处理，将与用户相关的属性等分别放置在住户表和服务人员表中，例如：真实姓名，电话号码等。

数据库建表如下：

用户表(登录表):

住户表：

服务人员表：

依据数据结构设计原则，将外键放置在住户表，服务人员表中=>(userId)。

需求要求后端界面要能够维护用户信息，其中要数据列表截图如下：

1.初期解决方案：

将住户数据与服务人员数据分别查询出来，然后再services层中合并成一个List集合返回给前端进行数据渲染。

创建VO类进行数据映射：

public class UserManagerVO {
    //用户id
    private Integer userId;
    //用户名
    private String  userName;
    //角色名称
    private String  roleName;
    //真实姓名
    private String  realName;
    //电话号码
    private String  telNum;
    //注册时间
    private Date    registerTime;
    //上次登录时间
    private Date    loginTime;
    //上次退出时间
    private Date    logoutTime;
    //用户状态
    private String  userState;
    private Integer delState;
    ....//get、set方法
}
           

sql语句如下：

-- 查询服务人员表中满足情况的数据
SELECT
	s.staffName realName,
	s.telNum telNum,
	u.userId userId,
	u.userName userName,
	u.registTime registerTime,
	u.loginTime loginTime,
	u.logoutTime logoutTime,
	u.userState userState,
	r.roleName roleName,
	u.deleteState deleteState 
FROM
	t_staff s
	JOIN t_user u ON s.userId = u.userId
	JOIN r_user_role ur ON u.userId = ur.userId
	JOIN t_role r ON r.roleId = ur.roleId
	WHERE
	u.deleteState = 1 
-- 查询住户表中满足情况的数据
SELECT
	i.inhabitantName realName,
	i.telNum telnum,
	u.userId userId,
	u.userName userName,
	u.registTime registerTime,
	u.loginTime loginTime,
	u.logoutTime logoutTime,
	u.userState userState,
	u.deleteState deleteState,
	r.roleName roleName 
FROM
	t_inhabitant i
	JOIN t_user u ON i.userId = u.userId
	JOIN r_user_role ur ON u.userId = ur.userId
	JOIN t_role r ON r.roleId = ur.roleId 
WHERE
	u.deleteState = 1 
           

在Java的UserManagerVO的services层实现类中，分别调用mybatis中的db查询语句，将查询到的结果合并到一个List中，最后将这个List集合返回给前端进行渲染。（模糊查询等也用类似方法映射）

遇到问题：在进行分页查询的时候，数据查询并正确响应困难。使用上述方法时，会通过分页条件，分别查询，若前端要显示5条数据，则后端会响应10条，且若一方不足5条(<5)前端总条数不能处理。

2.优化解决方案

问题思考：

1. 在登录表t_user中加入前端所需要显示的列，即将用户真实姓名以及电话号码从住户表和服务人员表中提取出来，放到登录表t_user中，即可解决问题。

2. 问题出现：

   a.提取字段出来放在登录表中，是否意味着前端要显示住户地址，数据库就得加两方的地址上去。

   b.在项目过程中进行改动数据库，会导致代码相关联的代码推翻重构。

   c.整个数据表的设计跌落，使得第三范式不再满足。

3. 问题：如何在不改动数据库的情况下，将需要的数据字段提取出来？（视图！）
4. 视图除了进行权限控制之外，还能将查询出来的字段独立生成一个表且不影响原有表的结构。刚好能满足当前需求。
5. 问题:视图只能进行一个select语句的结果集生成为一个视图，而我们上述的情况则需要将两条sql语句查询到的数据合并为一条数据。(Union)
6. 在sql语句中，能够用union将多条sql语句的查询结果合并为一个结果集，结合view和union就能将上述需求进行改进了！

方法改进：

创建视图：

CREATE VIEW view_userManager AS SELECT
u.userId userId,
u.userName userName,
u.registTime registerTime,
u.loginTime loginTime,
u.logoutTime logoutTime,
u.userState userState,
u.deleteState delState,
s.staffName realName,
s.telNum telNum,
r.roleName roleName 
FROM
	t_staff s
	JOIN t_user u ON s.userId = u.userId
	JOIN r_user_role ur ON u.userId = ur.userId
	JOIN t_role r ON r.roleId = ur.roleId UNION all
	
SELECT
	u.userId userId,
	u.userName userName,
	u.registTime registerTime,
	u.loginTime loginTime,
	u.logoutTime logoutTime,
	u.userState userState,
	u.deleteState delState,
	i.inhabitantName realName,
	i.telNum telNum ,
	r.roleName roleName
FROM
	t_inhabitant i
	JOIN t_user u ON i.userId = u.userId
	JOIN r_user_role ur ON u.userId = ur.userId
	JOIN t_role r ON r.roleId = ur.roleId
           

注：在取别名的时候，需要将冲突的列名字段取别名为其他的字段。例如：三个表当中都有deleteState字段，名称相同，需要将查询的u.deleteState 命名为delState。

在mybatis的mapper映射文件中，使用视图进行查询以及分页操作：

<select id="selectUserManagerByLike" resultMap="userManagerVOMap">
        select userId,userName,registerTime,loginTime
        ,logoutTime,userState,delState,realName,telNum
        ,roleName from view_userManager
        where delState=1
        <if test="telNum != null">
            and telNum like concat('%',#{telNum},'%')
        </if>
        <if test="realName != null">
            and realName like concat('%',#{realName},'%')
        </if>
        <if test="userState != null">
            and userState like concat('%',#{userState},'%')
        </if>
        <if test="roleName != null">
            and roleName like concat('%',#{roleName},'%')
        </if>
        <if test="pageIndex!=null and pageSize!=null">
            limit #{pageIndex} , #{pageSize}
        </if>
    </select>
           

特别注意：

若视图是可更新的，则可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。

但在下述几种情况下，视图是不可更新的：

• 视图中不包含基表中被定为非空的列；
• 在定义视图的SELECT语句后的字段列表中使用了数学表达式；
• 在定义视图的SELECT语句后的字段列表中使用了聚合函数COUNT、SUM、MAX、MIN、AVG；
• 在定义视图的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句。
• ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

另外，若视图是基于多个表使用联接操作而导出的，且不满足上述条件，那么对这个视图执行更新操作时，每次只能影响其中的一个表，否则，更新操作也不能执行。

至此，博主遇到的问题解决。大家有更好的解决方案，欢迎留言栏交流。