Spring JPA如何生成自定义JSON响应：查询用户权限激活状态

核心是用**左连接（LEFT JOIN）**保留权限主表的所有记录，同时关联目标用户的权限关系表，以此判断每个权限是否对该用户激活。

SQL 查询示例（以MySQL为例） #

SELECT 
    am.authority_id,
    am.authority_name,
    -- 用CASE判断该用户是否拥有此权限
    CASE WHEN uar.user_id IS NOT NULL THEN 1 ELSE 0 END AS is_active
FROM 
    authority_master am
-- 左连接用户权限关联表，关联条件指定目标用户ID
LEFT JOIN 
    users_authority_relation uar 
    ON am.authority_id = uar.authority_id 
    AND uar.user_id = 'target_user_id' -- 替换为实际要查询的用户ID
ORDER BY 
    am.authority_id;

关键说明 #

• 必须使用LEFT JOIN：如果用INNER JOIN只会返回用户已拥有的权限，这就是你之前遇到的问题
• 用户ID要放在ON子句而非WHERE子句：如果放在WHERE里，会过滤掉未关联的权限记录，等同于INNER JOIN的效果
• CASE语句直接标记激活状态：关联表中存在记录则为1（激活），否则为0（未激活）

直接生成JSON格式（MySQL 8.0+支持） #

如果需要直接输出JSON结构，可使用JSON聚合函数：

SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'authority_id', am.authority_id,
            'authority_name', am.authority_name,
            'is_active', CASE WHEN uar.user_id IS NOT NULL THEN 1 ELSE 0 END
        )
    ) AS authority_list
FROM 
    authority_master am
LEFT JOIN 
    users_authority_relation uar 
    ON am.authority_id = uar.authority_id 
    AND uar.user_id = 'target_user_id';

返回的JSON示例：

[
    {"authority_id": "auth001", "authority_name": "用户管理", "is_active": 1},
    {"authority_id": "auth002", "authority_name": "角色管理", "is_active": 0},
    {"authority_id": "auth003", "authority_name": "日志查看", "is_active": 1}
]

应用层处理 #

如果你的业务逻辑在应用代码中（如Java、Python），只需执行第一个基础SQL，将查询结果映射为实体类后，序列化为JSON即可，结构和上面的示例一致。

内容的提问来源于stack exchange，提问作者user13030738