JPA条件查询根据id从3个表中选择

Question

如何实现以下作为JPA条件查询的查询：sql

SELECT 
tab1.ID,
tab1.PROJECT_ID,
tab1.EMP_ID,
tab1.MONTH_ID,
tab1.MONTH,
tab2.projectName as ProjName,
tab3.empName as EmpName,
tab1.Table1
FROM 
Table1 tab1,Table2 tab2,Table3 tab3
WHERE 
Tab1.EMP_ID=tab3.ID AND tab1.PROJECT_ID=tab2.ID
I'm trying to make criteria query but I am unable to apply filters on 
that according to my requirement



CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Object[]> criteriaQuery = 

builder.createQuery(Object[].class);

Root<Table3> tab3 = criteriaQuery.from(Table3.class);

Root<Table1> tab1 = criteriaQuery.from(Table1.class);

Root<Table2> tab2 = criteriaQuery.from(Table2.class);

Path<Object> empPath = empRoot.get("id");

criteriaQuery.where(builder.equal(projectAllocationRoot.get("empId"),
  empRoot.get("id")));

criteriaQuery.multiselect(projectAllocationRoot);
        criteriaQuery.

Entity: JPA criteria query select from 3 tables based on id

 Table 1 
 long id ;
 @OneToMany
 Tanle2 projectId ; 
 @OneToMany   
 Table3 empId; 

JPA标准查询基于id从3个表中选择Table 2 long id； long bpId； String project Name；

JPA条件查询根据id从3个表中选择表3长id；字符串empName；字符串contactNum；

Answer 1

您可以使用FluentJPA实现任何SQL，例如：

java prettyprint-override">FluentQuery query = FluentJPA.SQL((Table1 t1,
                                   Table2 t2,
                                   Table3 t3) -> {
    SELECT(t1.getId(), t1.getProjectId(), t1.getEmpId(),
           alias(t2.getProjectName(), MultiTab::getProjectName), t3.getEmpName());

    FROM(t1, t2, t3);
    WHERE(t1.getEmpId() == t3.getId() && t1.getProjectId() == t2.getId());
});

query.createQuery(em, MultiTab.class).getResultList();

生成以下SQL：

SELECT t0.id, t0.project_id, t0.emp_id, t1.projectName AS projName, t2.empName 
FROM tab1 t0, tab2 t1, tab3 t2 
WHERE ((t0.emp_id = t2.id) AND (t0.project_id = t1.id))

声明的投影类：

@Tuple
@Data // lombok
public class MultiTab {
    long id;
    long projectId;
    long empId;
    @Column(name = "projName")
    String projectName;
    @Column(name = "empName")
    String empName;
}