时间戳

首页 > 解决方案 > 如何编写一个基于一行中的布尔值返回多个结果的选择查询（用于弹簧安全机构查询）？

mysql - 如何编写一个基于一行中的布尔值返回多个结果的选择查询（用于弹簧安全机构查询）？

问题描述

我有一个使用 Spring Security 5 的 Java Web 应用程序。

我使用 MySQL 作为数据库。

包含用户的数据库表如下所示：

USERS TABLE
ID | USERNAME | PASSWORD | ADMIN_ROLE | USER_ROLE | READONLY_ROLE
-----------------------------------------------------------------
1  | Andy     | abc123   | 1          | 1         | 0
2  | Bob      | xyz789   | 0          | 1         | 1
3  | Chuck    | qrs567   | 1          | 0         | 1

Spring Security 要求我给它两个查询……一个是获取用户，一个是获取用户的角色。

通常，查询如下所示：

users-by-username-query="SELECT USERNAME, PASSWORD FROM USERS WHERE USERNAME=?;"

authorities-by-username-query="SELECT USERNAME, ROLENAME FROM TBL_USER_ROLE WHERE USERNAME=?;"

但是，我的用户和角色在一张表中。

那么，对于我的“权限”查询，有没有办法在我的 USERS 表（上图）上编写一个 SQL 查询，它会给我一个记录，每个“角色”值是 1（真）？结果是这样的：

USERNAME | ROLE_NAME
---------------
Andy     | ADMIN_ROLE
Andy     | USER_ROLE
Bob      | USER_ROLE
Bob      | READONLY_ROLE
Chuck    | ADMIN_ROLE
Chuck    | READONLY_ROLE

标签： mysqlsqlspring-security

解决方案

Select main.ID,
       main.USERNAME, 
CASE WHEN main.ADMIN_ROLE = 1 
     THEN 'ADMIN_ROLE'
     ELSE NULL END AS ROLE
FROM users main
WHERE ADMIN_ROLE = 1
UNION
SELECT u.ID,
       u.USERNAME,
      CASE WHEN u.user_role = 1 
        THEN 'USER_ROLE'
        ELSE NULL END AS ROLE
FROM USERS u
WHERE USER_ROLE = 1
UNION
SELECT r.ID,
       r.USERNAME,
     CASE WHEN r.READONLY_ROLE = 1 
          THEN 'READONLY_ROLE'
          ELSE NULL END AS ROLE
FROM users r
WHERE READONLY_ROLE = 1


ID  USERNAME    ROLE
 1  Andy      ADMIN_ROLE
 1  Andy      USER_ROLE
 2  Bob       READONLY_ROLE
 2  Bob       USER_ROLE
 3  Chuck     ADMIN_ROLE
 3  Chuck     READONLY_ROLE

推荐阅读