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（使用臨時表總會使視圖成為不可更新的）。

另外，若視圖是基于多個表使用聯接操作而導出的，且不滿足上述條件，那麼對這個視圖執行更新操作時，每次隻能影響其中的一個表，否則，更新操作也不能執行。

至此，部落客遇到的問題解決。大家有更好的解決方案，歡迎留言欄交流。