java Spring JPA Repository 通过关系表查询过滤器

Question

提问by Pablo

If I have a many-to-many relationship between JPA entities as below, how can I retrieve a list of Person(I am interested in the person attributes) that are employees of a specific company?

如果我在 JPA 实体之间存在多对多关系，如下所示，如何检索Person特定公司员工的列表（我对人员属性感兴趣）？

The relationship between Personand Companyis many-to-many. The relationship table Employeehas the FK to Personand Company, and a start_date and end_date to indicate when the employment started and finished.

Person和之间的关系Company是多对多的。关系表Employee有 FK toPerson和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;
}

Do I use a @Queryon the CompanyEmployeeJPARepository? How should I tackle it?

我在@Query上使用 aCompanyEmployeeJPARepository吗？我该如何应对？

public interface CompanyEmployeeRepository extends JpaRepository<CompanyEmployee,Long> {
//
}

Answer 1

采纳答案by Darren Parker

Pablo,
Our company is in the process of converting our existing Spring/ MyBatiscode to Spring Data JPA, so I have been learning Spring Data JPAfor a few weeks. I'm clearly not an expert, but I worked out an example similar to yours which may help you.

Pablo，
我们公司正在将我们现有的Spring/ MyBatis代码转换为Spring Data JPA，所以我已经学习Spring Data JPA几个星期了。我显然不是专家，但我想出了一个类似于你的例子，它可能对你有帮助。

I have Personand Companyclasses that are similar to yours, but (as Jens mentioned), you need lists with OneToManyannotations. I used a separate join table (named company_person) which only has companyId, personIdcolumns to maintain the many-to-manyrelationship. See the code below.

我有Person和Company你相似的类，但是（正如 Jens 提到的），你需要带有OneToMany注释的列表。我使用了一个单独的连接表（名为 company_person），其中只有companyId和personId列来维护多对多关系。请参阅下面的代码。

I did not see a way to put the start/end dates in the company_person join table, so I made a separate (4th table) for that. I called it employment_record with Java class entity EmploymentRecord. It has the combo primary key (companyId, personId) and the start/end dates.

我没有看到将开始/结束日期放在 company_person 连接表中的方法，因此我为此创建了一个单独的（第 4 个表）。我用 Java 类 entity 将其称为 job_record EmploymentRecord。它具有组合主键（companyId、personId）和开始/结束日期。

You need repositories for Person, Company, and EmploymentRecord. I extended CrudRepository instead of JpaRepository. But, you don't need an entity or repository for the join table (company_record).

您需要 Person、Company 和 EmploymentRecord 的存储库。我扩展了 CrudRepository 而不是 JpaRepository。但是，您不需要连接表 (company_record) 的实体或存储库。

I made a Spring Boot Application class to test it out. I used CascadeType.ALLon Person's OneToMany. In my Application test, I tested that I can change the companies assigned to a person and Spring Data propagates all the changes needed to the Companyentities and join table.

我制作了一个 Spring Boot Application 类来测试它。我曾经CascadeType.ALL对Person的OneToMany。在我的应用程序测试中，我测试了我可以更改分配给一个人的公司，并且 Spring Data 将所有需要的更改传播到Company实体和连接表。

However, I had to manually update the EmploymentRecordentities, via its repository. For example, I had to add a start_date each time I added a company to a person. Then, add an end_date when I removed that company from that person. There is probably some way to automate this. The Spring / JPA audit feature is a possibility, so check that out.

但是，我必须EmploymentRecord通过其存储库手动更新实体。例如，每次向某人添加公司时，我都必须添加一个 start_date。然后，当我从那个人中删除该公司时添加一个结束日期。可能有一些方法可以自动执行此操作。Spring / JPA 审计功能是可能的，所以检查一下。

The answer to your question:

你的问题的答案：

how can I retrieve a list of Person (I am interested in the person attributes) that are employees of a specific company?

如何检索特定公司员工的人员列表（我对人员属性感兴趣）？

You simply use companyRepository's findOne(Long id) method followed by getPersonList() method.

您只需使用 companyRepository 的 findOne(Long id) 方法，然后使用 getPersonList() 方法。

snippet from Application.java:

来自 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);
}

Here are some references that I found to be useful:

以下是我发现有用的一些参考资料：

Spring Data JPA tutorial
Join Table example

Spring Data JPA 教程
 Join Table 示例

Person.java:

人.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:

公司.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:

就业记录.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 script, createTables.sql:

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;

Answer 2

回答by Maverik

I have previous experience in hibernate JPA but not spring JPA. From that knowledge following query might be useful:

我以前有过 Hibernate JPA 的经验，但没有 Spring JPA 的经验。根据这些知识，以下查询可能有用：

select cp.person from CompanyEmployee cp where cp.company.id = ?

Answer 3

回答by AA_PV

You shouldn't need to make a separate entity for the relationship table.

您不需要为关系表创建单独的实体。

The relationship can be maintained within the two entities,

可以在两个实体内保持关系，

so if A and B are in a many-to-many relationship,

所以如果 A 和 B 是多对多的关系，

@Entity
class A {

@Id
Long id;
...

@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name="a_b",
            joinColumns={@JoinColumn(name="id_a", referencedColumnName="id")},
            inverseJoinColumns={@JoinColumn(name="id_b", referencedColumnName="id")})
List<B> bList;

...

}


@Entity
class B {

@Id
Long id;
...

@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name="a_b",
            joinColumns={@JoinColumn(name="id_b", referencedColumnName="id")},
            inverseJoinColumns={@JoinColumn(name="id_a", referencedColumnName="id")})
List<A> aList;

...

}

You can now use the repository queries on either of the entity repositories or if you have a query with params on both, you can create a custom query in the repository of one.

您现在可以在任一实体存储库上使用存储库查询，或者如果您在两个实体存储库上都有一个带有 params 的查询，则可以在其中一个的存储库中创建自定义查询。

java Spring JPA Repository 通过关系表查询过滤器

提问by Pablo

采纳答案by Darren Parker

回答by Maverik

回答by AA_PV

相关推荐

最近更新

标签

java Spring JPA Repository 通过关系表查询过滤器

提问by Pablo

采纳答案by Darren Parker

回答by Maverik

回答by AA_PV

相关推荐

java Android webview 输入类型文件

当套接字接收数据时，Java Socket 中是否有事件？

java Objects.isNull(...) / Objects.nonNull(...) 的用途

java 我应该为 POST HTTP 请求的 Content-Length 使用什么值？

相关推荐

最近更新

标签