英文:
Java - Criteria API - Unidirectional OneToMany
问题
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：```javafinal 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'm trying to get the license number from the person from the process table through criteria API.Basically from `Process -> Person -> 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”：```javaJoin<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));
英文:
Strictly speaking, you don't have to join the Process tables with license, you have to join Process with Person and Person with License as follows:
Join<Process, Person> personJoin = rootSelect.join("person",JoinType.INNER);Join<Person, License> licenseJoin = personJoin.join("licenses",JoinType.INNER);
It is recommended to use metamodels and entities, with them the Join would be as follows (the metamodel is represented by EntityName_):
Join<Process, Person> personJoin = rootSelect.join(Process_.person,JoinType.INNER);Join<Person, License> licenseJoin = personJoin.join(Person_.licences,JoinType.INNER);
For me the clearest benefit is that you can autocomplete the properties of the metamodel, putting a string increases the chances that we are wrong.
We add the filter condition
cq.where(cb.equal(licenceseJoin.get("licenseNumber"),LICENSE_NUMBER));//cq.where(cb.equal(licenceseJoin.get(License_.licenseNumber),LICENSE_NUMBER));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论