如何在Skills实体中关联Worker名称且不影响其他查询？

方案1：用@Formula实现动态字段映射 #

不需要修改数据库，直接在Skills实体中给workerName字段添加@Formula注解，让Hibernate自动通过关联查询获取员工姓名，其他查询不受影响——这个字段是虚拟计算的，不存在数据库列依赖：

@Entity 
@Table(name = "skills") 
public class Skills {
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name = "id")
    private UUID id;
    
    @Column(name = "worker_id")
    private UUID workerId;
    
    // 通过Formula动态关联查询worker姓名，无需数据库存在该列
    @Formula("(SELECT w.name FROM worker w WHERE w.id = worker_id)")
    private String workerName;
    
    // 原有getter/setter方法
}

同时简化原生查询，无需再手动查询worker_name：

public interface SkillRepository extends JpaRepository<Skills, UUID> {
    @Query(nativeQuery = true, value = """
                select
                    s.*
                from
                    worker w
                    INNER JOIN skills s ON w.id = s.worker_id
                where
                    w.company_id = :companyId
            """)
    List<Skills> getSkillsAndWorkers(UUID companyId);
}

方案2：创建专用DTO投影（推荐，完全不改动原有实体） #

既然仅该查询需要worker_name，直接创建一个DTO类承载查询结果，完全不修改Skills实体，也不会影响其他业务查询：

首先定义DTO类（需包含与查询字段匹配的全参构造函数）：

public class SkillWithWorkerDto {
    private UUID id;
    private UUID workerId;
    private String workerName;
    // 可按需添加Skills表的其他字段，比如skillName等

    public SkillWithWorkerDto(UUID id, UUID workerId, String workerName) {
        this.id = id;
        this.workerId = workerId;
        this.workerName = workerName;
    }

    // getter方法
}

然后修改Repository的查询，返回该DTO：

public interface SkillRepository extends JpaRepository<Skills, UUID> {
    @Query(nativeQuery = true, value = """
                select
                    s.id,
                    s.worker_id,
                    w.name worker_name
                from
                    worker w
                    INNER JOIN skills s ON w.id = s.worker_id
                where
                    w.company_id = :companyId
            """)
    List<SkillWithWorkerDto> getSkillsAndWorkers(UUID companyId);
}

方案3：建立实体关联关系 #

在Skills实体中添加@ManyToOne关联Worker实体，使用懒加载模式，确保其他查询不需要worker信息时，不会自动查询worker表，避免性能问题和报错：

@Entity 
@Table(name = "skills") 
public class Skills {
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name = "id")
    private UUID id;
    
    // 懒加载关联Worker，insertable/updatable设为false避免修改worker_id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "worker_id", insertable = false, updatable = false)
    private Worker worker;
    
    // 新增getter方法直接返回员工姓名，方便前端调用
    public String getWorkerName() {
        return worker != null ? worker.getName() : null;
    }
    
    // 原有getter/setter方法
}

同时将查询改为更简洁的JPQL：

public interface SkillRepository extends JpaRepository<Skills, UUID> {
    @Query("SELECT s FROM Skills s JOIN s.worker w WHERE w.company.id = :companyId")
    List<Skills> getSkillsAndWorkers(UUID companyId);
}

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