困于如何在spring data app中查找QueryDSL谓词

Question

我使用Spring数据、QueryDSL和MySQL。

我想在queryDSL方法中这样查询。一个具体的例子只是一个简单的例子来给出一个想法。

例如，有两个表Employee和Certificate。两个之间的关系是一个（员工）对多个（证书）

以下是表

Employee (id, first_name, last_name);
Certificate (id, name, date, fk_emp);

以下是这些实体,

@Entity
@Table(name = "EMPLOYEE")
class Employee {
  @Id
  @Column(name = "ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "FIRST_NAME")
  private String firstName;

  @Column(name = "LAST_NAME")
  private String lastName;

  @OneToMany(mappedBy = "employee", cascade = CascadeType.ALL)
  private List<Certificate> certificates = new ArrayList<>();
}

@Entity
@Table(name = "CERTIFICATE")
class Certificate {
  @Id
  @Column(name = "ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "CERTIFICATE_NAME")
  private String certificateName;

  @Column(name = "DATE")
  private Date date;

  @ManyToOne
  @JoinColumn(name = "REF_EMPLOYEE")
  private Employee employee;
}

+----+--------------------+---------------------+--------+
| id | name               | date                | fk_emp |
+----+--------------------+---------------------+--------+
|  1 | FirstName LastName | 2014-02-01 00:00:00 |  11111 |
+----+--------------------+---------------------+--------+
|  2 | FirstName LastName | 2014-03-01 00:00:00 |  null  |
+----+--------------------+---------------------+--------+
|  3 | FirstName LastName | 2014-04-01 00:00:00 |        |
+----+--------------------+---------------------+--------+
|  5 | FirstName LastName | 2014-05-01 00:00:00 |        |
+----+--------------------+---------------------+--------+
| 12 | FirstName LastName | 2014-06-01 00:00:00 |  25555 |
+----+--------------------+---------------------+--------+
| 13 | FirstName LastName | 2014-07-01 00:00:00 |  78888 |
+----+--------------------+---------------------+--------+
| 16 | FirstName LastName | 2014-08-01 00:00:00 |  null  |
+----+--------------------+---------------------+--------+

谢谢你

Answer 1

本机JPQL查询：

SELECT * FROM EMPLOYEE A
LEFT JOIN CERTIFICATE B ON A.ID = B.REF_EMPLOYEE AND DATE BETWEEN CAST('2014-12-22' AS DATE) AND CAST('2015-12-22' AS DATE);

Querydsl联接变体：

query.from(employee)
    .leftJoin(employee.certificates, certificate)
    .on(certificate.date.between('from_date', 'to_date'))
    .list(employee);

注：此处日期格式可以不同，请根据您的需要进行更改