java - 在 Hibernate 中将 3 个实体连接在一起

Question

我有以下实体:

Person.java

@Table(name = persons)
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "UserID", nullable = false) 
    private Long userId;

    @Column(name = "Employeenumber", nullable = false) private String employeeNumber;
    @Column(name = "Firstname", nullable = false) private String firstName;
    @Column(name = "Lastname", nullable = false) private String lastName;

    public User() { }

    public User(String employeeNumber, String firstName, String lastName) {
        super();

        this.employeeNumber = employeeNumber;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    /*
        getter and setters

        ...
    */

}

Personhistory.java

@Entity
@Table(name = personhistory)
public class Personhistory {

    @Id
    @Column(name = "UserID", nullable = false) 
    private Long userId;

    @Column(name = "Fromdate", nullable = false) private Date fromDate;
    @Column(name = "Todate", nullable = false) private Date toDate;
    @Column(name = "TeamID", nullable = false) private Integer teamId;


    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "UnikId", nullable = false) 
    private Integer unikId;

    public Userhistory() {

    }


    public Userhistory(Long userId, Date fromDate, Date toDate, int teamId) {
        super();

        this.userId = userId;
        this.fromDate = fromDate;
        this.toDate = toDate;
        this.teamId = teamId;
    }

    /*
        Getters and setters

        ...
    */

}

Team.java

@Entity
@Table(name = "team")
public class Team {
    @Id
    @Column(name = "TeamID") 
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int teamId;

    @Column(name = "TeamNumber") private String teamNumber;

    public Team() {}

    public Team(String teamNumber) {
        super();
        this.teamNumber = teamNumber;
    }

    /*
        Getters and setters

        ...
    */

}

我想进行这样的 API 调用:

localhost:8080/users/{employee}

并返回一个包含此人的对象(他的员工编号、名字和姓氏)、他在团队中的时间以及团队所属的团队。

如果我在 MSSQL 中编写此查询，它将如下所示:

select * from persons p

join personhistory ph on ph.UserID = p.UserID
    and ph.Fromdate <= cast(getdate() as date)
    and ph.Todate >= cast(getdate() as date)
join team t on t.TeamID = ph.TeamID

where u.Employeenumber = '999'

我已经搜索了不同的解决方案，例如 HQL、JPQL、Criteria 等，但我无法使其工作。

任何帮助将不胜感激。

Answer 1

据我所知 Hibernate 5.1 提供了更通用的连接，但在之前的版本中，您要么必须使用交叉连接并在 where 子句中添加条件，要么提供实体之间的真实关系并对该关系进行连接(使用“with"关键字用于附加连接条件)。

示例(请注意，为了简单起见，我省略了许多注释):

class Person {      
  @OneToMany( mappedBy = "user" )
  Collection<Personhistory> history;

  ...
}

class Personhistory {
  @ManyToOne
  Person user;

  @ManyToOne
  Team team;

  ...
}

那么查询可能会变成

select p, ph, t from Person p 
  join p.history ph with ph.fromdate <= :date and ph.toDate >= :date
  join ph.team t
  where p.employeeNumber = :number