java - Hibernate 使用注释映射多到一个表
问题描述
当提供患者名字和姓氏字段时,我将如何创建 HQL 查询来选择患者的多个电子邮件地址。下面提供的表格和分类。
CREATE TABLE Patient (
Patient_Id INT NOT NULL AUTO_INCREMENT,
First_Name VARCHAR(55) NOT NULL,
Middle_Name VARCHAR(55),
Last_Name VARCHAR(55) NOT NULL,
Is_Male TINYINT NOT NULL,
Medical_Information VARCHAR(5000),
Date_Of_Birth DATE,
Row_Create DATETIME NOT NULL,
Row_LastUpdate DATETIME,
PRIMARY KEY ( Patient_id),
INDEX name(First_Name, Last_Name)
) ENGINE=INNODB;
CREATE TABLE Address (
PatientAddress_Id INT NOT NULL AUTO_INCREMENT,
Patient_Id INT NOT NULL,
House_Name VARCHAR(100),
House_Number VARCHAR(20),
Street VARCHAR(70) NOT NULL,
City VARCHAR(70) NOT NULL,
Postcode VARCHAR(8) NOT NULL,
County VARCHAR(50) NOT NULL,
Country VARCHAR(60) NOT NULL,
Row_Create DATETIME NOT NULL,
Row_LastUpdate DATETIME,
PRIMARY KEY ( PatientAddress_id),
FOREIGN KEY (Patient_Id)
REFERENCES Patient(Patient_Id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX HouseNum_Street(House_Number, Street),
INDEX PatientID_postcode(Patient_Id, Postcode)
)ENGINE=INNODB;
在这里,我创建了一个患者类并设置所有字段并注释关系 患者类
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import org.jetbrains.annotations.Contract;
import javax.persistence.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.Objects;
import java.util.Set;
@NamedQueries(
value = {
@NamedQuery(
name = "findPatientByFirstName",
query = "from Patient p where p.firstName = :name"
),
@NamedQuery(
name = "findPatientByLastName",
query = "from Patient p where p.lastName = :name"
),
@NamedQuery(
name = "findPatientByDOB",
query = "from Patient p where p.dob = :dob"
),
@NamedQuery(
name = "findPatientByFullName",
query = "from Patient p where p.firstName = :firstName AND p.lastName = :lastName"
)
}
)
@Entity
@Table(name = "patient")
public class Patient {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column (name = "Patient_id")
private Integer id;
@Column( name="Date_Of_Birth")
private LocalDate dob;
@Column( name="First_Name")
private String firstName;
@Column( name="Middle_Name")
private String middleName;
@Column( name="Last_Name")
private String lastName;
@Column( name="Medical_Information")
private String medicalInformation;
@Column( name="Is_Male")
private boolean isMale;
@Column (name = "Row_Create")
@CreationTimestamp
private LocalDateTime createDateTime;
@Column (name = "Row_LastUpdate")
@UpdateTimestamp
private LocalDateTime updateDateTime;
public Patient() {
}
public Patient(LocalDate dob, String firstName, String lastName, String medicalInformation, boolean isMale) {
this.dob = dob;
this.firstName = firstName;
this.lastName = lastName;
this.medicalInformation = medicalInformation;
this.isMale = isMale;
}
//Setters and getters
患者电子邮件类
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.UpdateTimestamp;
import org.jetbrains.annotations.Contract;
import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.Objects;
@NamedQueries(
value = {
@NamedQuery(
name = "findEmailByPatientsName",
query = "SELECT pE.patient from PatientEmailAddress pE
join pE.emailAddress e where pE.patient = :patient"
// select cus.mail from Customer cus join cus.mainOrder man WHERE man.id = 1
)
}
)
@Entity
@Table(name = "patientEmailAddress")
public class PatientEmailAddress {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "PatientEmailAddress_Id", unique = true)
private Integer id;
@Column(name = "Email_Address", unique = true)
private String emailAddress;
@ManyToOne(cascade = CascadeType.ALL)
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "Patient_id")
private Patient patient;
@Column (name = "Row_Create")
@CreationTimestamp
private LocalDateTime createDateTime;
@Column (name = "Row_LastUpdate")
@UpdateTimestamp
private LocalDateTime updateDateTime;
public PatientEmailAddress() {
}
public PatientEmailAddress(String emailAddress, Patient patient) {
this.emailAddress = emailAddress;
this.patient = patient;
}
//setters and getters
我正在努力寻找解决方案。
解决方案
由于您对与一位患者相关的 PatientEmailAddress 实例感兴趣,您可以使用以下内容查询这些实体:
select t from PatientEmailAddress t
join t.patient p
where p.firstName = :firstName and p.lastname = :lastname
请注意,这将返回 PatientEmailAddress 的实例而不是 Patient 的实例。
推荐阅读
- reactjs - 测试库 React - 期望多个元素通过指定条件
- node.js - 对于 React Native 应用程序,Express-useragent 返回 isMobile = false
- linux - 在嵌入式 linux 驱动程序编程中使用 printk 导致错误
- javascript - 显示投票数最多的数组
- pygame-surface - 如何在pygame中更改精灵的服装
- java - Eclipse 中的 Checkstyle 插件:Checkstyle Execution 由于内部错误而失败
- c# - Moq 抽象类属性在转换为接口时为 NULL
- c# - 使用非字符串 DataBinding 值的 MarkupExtension
- python - 如何根据检测到的食物从 FatSecret API 输出卡路里信息?
- android-studio - Gradle - 自动嵌套模块依赖