Java SpringBoot JPA多表关联实现组织权限数据组装求助

针对你的需求，以下是直接可落地的实现步骤，用新手易理解的方式完成数据组装，避开复杂的JPA高阶用法：

1. 调整实体类（添加临时映射字段） #

在Organization实体中添加非数据库映射的managers字段，用@Transient标注避免JPA将其视为数据库字段：

@Entity
@Table(name = "organizations")
public class Organization {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;

    @Transient // 该字段不与数据库表字段关联
    private Map<Long, List<Pair<Long, String>>> managers;

    // 生成getter、setter、无参/全参构造函数
}

2. 定义返回结果DTO #

创建与目标JSON结构完全匹配的OrganizationDTO，用于接口返回：

public class OrganizationDTO {
    private Integer id;
    private String name;
    private Map<Long, List<Pair<Long, String>>> managers;

    // 生成getter、setter、构造函数
}

如果没有现成的Pair类，可以自定义一个简单的元组类：

public class Pair<L, R> {
    private L left;
    private R right;

    public Pair(L left, R right) {
        this.left = left;
        this.right = right;
    }

    // 生成getter、setter
}

3. 编写关联查询Repository #

在OrganizationRepository中定义JPQL查询，直接关联查询组织、用户、权限的核心数据：

@Repository
public interface OrganizationRepository extends JpaRepository<Organization, Integer> {

    @Query("SELECT o.id as orgId, o.name as orgName, up.idUser as userId, a.id as abilityId, a.key as abilityKey " +
           "FROM Organization o " +
           "JOIN OrgPermission up ON o.id = up.idOrganization " +
           "JOIN OrgAbility a ON up.idAbility = a.id")
    List<Object[]> findOrgUserPermissions();
}

4. 业务层组装数据 #

在Service层处理查询结果，将原始的数组结果组装成目标格式：

@Service
public class OrganizationService {

    private final OrganizationRepository organizationRepository;

    // 构造注入（替代@Autowired，更符合Spring规范）
    public OrganizationService(OrganizationRepository organizationRepository) {
        this.organizationRepository = organizationRepository;
    }

    public List<OrganizationDTO> getOrganizationsWithManagers() {
        List<Object[]> rawResults = organizationRepository.findOrgUserPermissions();
        Map<Integer, OrganizationDTO> orgTempMap = new HashMap<>();

        for (Object[] result : rawResults) {
            // 解析查询结果
            Integer orgId = (Integer) result[0];
            String orgName = (String) result[1];
            Long userId = ((Integer) result[2]).longValue();
            Long abilityId = ((Integer) result[3]).longValue();
            String abilityKey = (String) result[4];

            // 初始化组织DTO
            OrganizationDTO dto = orgTempMap.getOrDefault(orgId, new OrganizationDTO());
            if (dto.getId() == null) {
                dto.setId(orgId);
                dto.setName(orgName);
                dto.setManagers(new HashMap<>());
                orgTempMap.put(orgId, dto);
            }

            // 组装用户权限列表
            List<Pair<Long, String>> permissions = dto.getManagers().getOrDefault(userId, new ArrayList<>());
            permissions.add(new Pair<>(abilityId, abilityKey));
            dto.getManagers().put(userId, permissions);
        }

        return new ArrayList<>(orgTempMap.values());
    }
}

5. 接口层返回结果 #

编写Controller接口对外提供数据：

@RestController
@RequestMapping("/api/organizations")
public class OrganizationController {

    private final OrganizationService organizationService;

    public OrganizationController(OrganizationService organizationService) {
        this.organizationService = organizationService;
    }

    @GetMapping
    public ResponseEntity<List<OrganizationDTO>> getOrganizationList() {
        List<OrganizationDTO> result = organizationService.getOrganizationsWithManagers();
        return ResponseEntity.ok(result);
    }
}

补充说明 #

• 该方案用直观的循环组装方式替代复杂的JPA映射规则，新手易调试和维护
• 如果需要包含无任何用户权限的空组织，可以先查询所有组织，再将权限数据填充进去
• 若使用Lombok插件，可通过@Data注解简化实体类和DTO的getter/setter编写

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