为基于oneTomany关系的查询编写queryDSL谓词查询

Question

我正在使用spring-data、QueryDSL和MySQL。

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

返回所有包含姓名的员工（在first_name和last_name中），并从该结果中获得的证书在2014年12月22日至2015年12月22日之间

我尝试了它，但无法获得如何以QueryDSL方式迭代每个员工的每个证书并返回员工列表。

您的回应将非常感谢！！

@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;
}

Answer 1

最简单的做法是反转它并查询证书，这很简单，然后可以从返回的证书中获取员工。

QCertificate certificate = QCertificate.certificate;
BooleanExpression a = certificate.date.between(d1, d2);
BooleanExpression b = certificate.employee.forename.eq("name").
      or(certificate.employee.surname.eq("name"));

certificateRepository.findAll(a.and(b));

如果要查询员工，请尝试以下针对QueryDSL Version4.1.3的操作。

    QEmployee employee = QEmployee.employee;
    QCertificate certificate = QCertificate.certificate;

    BooleanExpression a = employee.forename.eq("name").or(employee.surname.eq("name"));

    BooleanExpression b = employee.certificates.contains(
        JPAExpressions.selectFrom(certificate).
          where(certificate.employee.eq(employee).
           and(certificate.date.between(d1, d2))));

    userRepository.findAll(a.and(b));