问题

I have translated the provided content into Chinese as you requested:

我正试图通过Criteria API从流程表中获取人员的许可证号码。基本上是从`Process -> Person -> License`。

然而，Person表与License表之间有一个单向的OneToMany关系。

所以我有以下的实体类：

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

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "person_id")
Person person;

}

@Entity
@Table(name = "Person")
public class Person {
@Id
@Column(name = "id", columnDefinition="INTEGER")
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "person")
List<License> licenses;

}

@Entity
@Table(name = "License")
public class License {
@Id
@Column(name = "id", columnDefinition="INTEGER")
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;

@JoinColumn(name = "person_id")
@ManyToOne(fetch = FetchType.LAZY)
Person person;

String licenseNumber;

}

在一个原生查询中，我想要实现的结果如下：

select lic.license_number, * from Process process
left join Person p on process.person_id = p.id
left join License lic on lic.person_id = p.id;

我尝试过使用Join：

```java
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Process> criteria = builder.createQuery(Process.class);
final Root<Process> rootSelect = criteria.from(Process.class);

//我真的不知道如何连接rootSelect（Process表）与License表...中间有Person表..
Join<Process, Person> personJoin = rootSelect.join("person");
Join<License, Person> licenseJoin = rootSelect.join("person");

我还考虑使用子查询：

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Process> criteria = builder.createQuery(Process.class);
final Root<Process> rootSelect = criteria.from(Process.class);

Subquery sub = criteria.subquery(String.class);
Root subRoot = sub.from(License.class);
//如何仅选择下面的字段'license_number'？
sub.select(subRoot);
sub.where(builder.equal(rootSelect.get("person").get("id"), subRoot.get("person")));

我将需要许可证号码来进行最后的筛选（where）。

在考虑我的根表是Process的情况下，最好的方法是什么？

<details>
<summary>英文:</summary>

I&#39;m trying to get the license number from the person from the process table through criteria API.
Basically from `Process -&gt; Person -&gt; License`

However, Person table has a unidirectional OneToMany relationship with table License.

So I have the following entities:

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

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "person_id")
Person person;

}

@Entity
@Table(name = "Person")
public class Person {
@Id
@Column(name = "id",columnDefinition="INTEGER")
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "person")
List<License> licenses;

}

@Entity
@Table(name = "License")
public class License {
@Id
@Column(name = "id",columnDefinition="INTEGER")
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;

@JoinColumn(name = "person_id")
@ManyToOne(fetch = FetchType.LAZY)
Person person;

String licenseNumber;

}

In a native query, the result I would want to accomplish would be:

select lic.license_number, * from Process process
left join Person p on process.person_id = p.id
left join License lic on lic.person_id = p.id;

I have tried with Join:

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Process> criteria = builder.createQuery(Process.class);
final Root<Process> rootSelect = criteria.from(Process.class);

//I don't really know how to join rootSelect (Process table), with License table... having the table Person in middle..
Join<Process, Person> personJoin = rootSelect.join("person");
Join<License, Person> licenseJoin = rootSelect.join("person");

and also considered using subquery:

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Process> criteria = builder.createQuery(Process.class);
final Root<Process> rootSelect = criteria.from(Process.class);

Subquery sub = criteria.subquery(String.class);
Root subRoot = sub.from(License.class);
//How to select just the field 'license_number' below?
sub.select(subRoot);
sub.where(builder.equal(rootSelect.get("person").get("id"), subRoot.get("person")));

I will need the license_number for a filter (where) at the end.

What would be the best way to do such a filter, considering my root table is Process?

</details>


# 答案1
**得分**: 1

严格来说，您不必将“Process”表与“license”表连接，而是必须按照以下方式连接“Process”与“Person”以及“Person”与“License”：

```java
Join<Process, Person> personJoin = rootSelect.join("person", JoinType.INNER);
Join<Person, License> licenseJoin = personJoin.join("licenses", JoinType.INNER);

建议使用元模型和实体，使用它们的话，连接将如下所示（元模型由EntityName_表示）：

Join<Process, Person> personJoin = rootSelect.join(Process_.person, JoinType.INNER);
Join<Person, License> licenseJoin = personJoin.join(Person_.licenses, JoinType.INNER);

对我来说，最明显的好处是您可以自动完成元模型的属性，而使用字符串则增加了出错的机会。

我们添加了筛选条件：

cq.where(cb.equal(licenseJoin.get("licenseNumber"), LICENSE_NUMBER));
// cq.where(cb.equal(licenseJoin.get(License_.licenseNumber), LICENSE_NUMBER));

Join<Process, Person> personJoin = rootSelect.join("person",JoinType.INNER);
Join<Person, License> licenseJoin = personJoin.join("licenses",JoinType.INNER);

Join<Process, Person> personJoin = rootSelect.join(Process_.person,JoinType.INNER);
Join<Person, License> licenseJoin = personJoin.join(Person_.licences,JoinType.INNER);

cq.where(cb.equal(licenceseJoin.get("licenseNumber"),LICENSE_NUMBER));
//cq.where(cb.equal(licenceseJoin.get(License_.licenseNumber),LICENSE_NUMBER));