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(使用临时表总会使视图成为不可更新的)。

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


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

相关推荐
©️2020 CSDN 皮肤主题: 数字50 设计师:CSDN官方博客 返回首页