如果我在JPA实体之间存在多对多关系,如下所示,如何检索特定公司员工的Person
列表(我对person属性感兴趣)?Person
和Company
之间的关系是多对多的。关系表Employee
具有FK到Person
和Company
,以及start_date和end_date,以指示雇佣开始和结束的时间。
@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "address")
private String address;
}
@Entity
public class Company {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "address")
private String address;
}
@Entity
public class CompanyEmployee {
//note this is to model a relationship table. Am I doing this wrong?
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "start_date", nullable = false)
private LocalDate startDate;
@Column(name = "end_date", nullable = false)
private LocalDate endDate;
@ManyToOne
private Company company;
@ManyToOne
private Person person;
}
我可以在
@Query
上使用CompanyEmployeeJPARepository
吗?我该如何解决?public interface CompanyEmployeeRepository extends JpaRepository<CompanyEmployee,Long> {
//
}
最佳答案
巴勃罗
我们公司正在将现有的 Spring / MyBatis 代码转换为 Spring Data JPA ,因此,我已经学习了 Spring Data JPA 几周了。我显然不是专家,但我提出了一个与您相似的示例,可能会对您有所帮助。
我有与您的类相似的Person
和Company
类,但是(如Jens所述),您需要带有OneToMany
批注的列表。我使用了一个单独的联接表(名为company_person),该表仅具有companyId和personId列,以维护多对多关系。请参见下面的代码。
我没有看到将开始/结束日期放在company_person连接表中的方法,因此我为此做了一个单独的(第4个表)。我用Java类实体EmploymentRecord
称它为“employment_record”。它具有组合主键(companyId,personId)和开始/结束日期。
您需要用于“个人”,“公司”和“就业记录”的存储库。我扩展了CrudRepository而不是JpaRepository。但是,您不需要连接表(company_record)的实体或存储库。
我做了一个Spring Boot Application类来测试它。我在CascadeType.ALL
的Person
上使用了OneToMany
。在我的应用程序测试中,我测试了我可以更改分配给一个人的公司,并且Spring Data将所有需要的更改传播到Company
实体和联接表。
但是,我必须通过其存储库手动更新EmploymentRecord
实体。例如,每次我向某人添加公司时,我都必须添加一个start_date。然后,在我从该人中删除该公司时添加一个end_date。可能有一些方法可以自动执行此操作。 Spring/JPA审核功能是可能的,因此请检查一下。
您问题的答案:
您只需使用companyRepository的findOne(Long id)方法,然后使用getPersonList()方法即可。
Application.java中的代码段:
PersonRepository pRep = context.getBean(PersonRepository.class);
CompanyRepository cRep = context.getBean(CompanyRepository.class);
EmploymentRecordRepository emplRep = context.getBean(EmploymentRecordRepository.class);
...
// fetch a Company by Id and get its list of employees
Company comp = cRep.findOne(5L);
System.out.println("Found a company using findOne(5L), company= " + comp.getName());
System.out.println("People who work at " + comp.getName());
for (Person p : comp.getPersonList()) {
System.out.println(p);
}
以下是一些我发现有用的引用:
Spring Data JPA tutorial
Join Table example
Person.java:
@Entity
public class Person {
// no-arg constructor
Person() { }
// normal use constructor
public Person(String name, String address) {
this.name = name;
this.address = address;
}
@Id
@GeneratedValue
private Long id;
@Column(name = "name")
private String name;
@Column(name = "address")
private String address;
@Version
private int versionId;
@OneToMany(cascade=CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name="company_person",
joinColumns={@JoinColumn(name="person_id", referencedColumnName="id")},
inverseJoinColumns={@JoinColumn(name="company_id", referencedColumnName="id")})
private List<Company> companyList;
// Getters / setters
}
Company.java:
@Entity
public class Company {
// no-arg constructor
Company() { }
// normal use constructor
public Company(String name, String address) {
this.name = name;
this.address = address;
}
@Id
@GeneratedValue
private Long id;
@Column(name = "name")
private String name;
@Column(name = "address")
private String address;
@Version
private int versionId;
//@OneToMany(cascade=CascadeType.ALL)
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name="company_person",
joinColumns={@JoinColumn(name="company_id", referencedColumnName="id")},
inverseJoinColumns={@JoinColumn(name="person_id", referencedColumnName="id")})
private List<Person> personList;
// Getters / Setters
}
EmploymentRecord.java:
@Entity
@IdClass(EmploymentRecordKey.class)
public class EmploymentRecord {
// no-arg constructor
EmploymentRecord() { }
// normal use constructor
public EmploymentRecord(Long personId, Long companyId, Date startDate, Date endDate) {
this.startDate = startDate;
this.endDate = endDate;
this.companyId = companyId;
this.personId = personId;
}
// composite key
@Id
@Column(name = "company_id", nullable = false)
private Long companyId;
@Id
@Column(name = "person_id", nullable = false)
private Long personId;
@Column(name = "start_date")
private Date startDate;
@Column(name = "end_date")
private Date endDate;
@Version
private int versionId;
@Override
public String toString() {
return
" companyId=" + companyId +
" personId=" + personId +
" startDate=" + startDate +
" endDate=" + endDate +
" versionId=" + versionId;
}
// Getters/Setters
}
// Class to wrap the composite key
class EmploymentRecordKey implements Serializable {
private long companyId;
private long personId;
// no arg constructor
EmploymentRecordKey() { }
@Override
public int hashCode() {
return (int) ((int) companyId + personId);
}
@Override
public boolean equals(Object obj) {
if (obj == null) return false;
if (obj == this) return true;
if (!(obj instanceof EmploymentRecordKey)) return false;
EmploymentRecordKey pk = (EmploymentRecordKey) obj;
return pk.companyId == companyId && pk.personId == personId;
}
// Getters/Setters
}
MySql脚本createTables.sql:
DROP TABLE IF EXISTS `test`.`company_person`;
DROP TABLE IF EXISTS `test`.`employment_record`;
DROP TABLE IF EXISTS `test`.`company`;
DROP TABLE IF EXISTS `test`.`person`;
CREATE TABLE `company` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`address` varchar(500) DEFAULT '',
`version_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `person` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`address` varchar(500) DEFAULT '',
`version_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* Join table */
CREATE TABLE `company_person` (
`company_id` int NOT NULL,
`person_id` int NOT NULL,
PRIMARY KEY (`person_id`,`company_id`),
KEY `company_idx` (`company_id`),
KEY `person_idx` (`person_id`),
CONSTRAINT `fk_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* Employment records */
CREATE TABLE `employment_record` (
`company_id` int NOT NULL,
`person_id` int NOT NULL,
`start_date` datetime,
`end_date` datetime,
`version_id` int NOT NULL,
PRIMARY KEY (`person_id`,`company_id`),
KEY `empl_company_idx` (`company_id`),
KEY `empl_person_idx` (`person_id`),
CONSTRAINT `fk_empl_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_empl_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;