jpa-2.0 - 在 jpa criteria api 中使用子查询

Question

我正在研究 JPA criteria api，我的数据库包含 Employee 表。我试图找到所有薪水第二高的员工。我能够如下成功编写 JPQL。

SELECT e FROM Employee e WHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp WHERE emp.salary < (SELECT MAX(employee.salary) FROM Employee employee) )

但现在我正在尝试将其转换为标准 api 并尝试遵循。

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e1 = c.from(Employee.class);
    c.select(e1);

    Subquery<Number> sq = c.subquery(Number.class);
    Root<Employee> e2 = sq.from(Employee.class);
    sq.select(cb.max(e2.<Number> get("salary")));

    Subquery<Number> sq1 = sq.subquery(Number.class);
    Root<Employee> e3 = sq1.from(Employee.class);
    sq1.select(cb.max(e3.<Number> get("salary")));

    c.where(cb.lessThan(e2.<Number>get("salary"), e3.<Number>get("salary")));// error here
    c.where(cb.equal(e1.get("salary"), sq));

我收到参数与 lessThan 方法不兼容的错误。我不明白如何才能解决此查询。我的做法对吗？

编辑:- 在 Mikko 回答后更新问题。

上面提供的 jpql 提供了以下结果，这些结果是薪水第二高的员工。

Harish Taware salary 4000000.0
Nilesh Deshmukh salary 4000000.0
Deodatta Chousalkar salary 4000000.0
Deodatta Chousalkar salary 4000000.0

但更新后的标准查询如下，

        CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
        Root<Employee> e1 = c.from(Employee.class);
        c.select(e1);

        Subquery<Long> sq = c.subquery(Long.class);
        Root<Employee> e2 = sq.from(Employee.class);
        sq.select(cb.max(e2.<Long> get("salary")));

        Subquery<Long> sq1 = sq.subquery(Long.class);
        Root<Employee> e3 = sq1.from(Employee.class);
        sq1.select(cb.max(e3.<Long> get("salary")));

        c.where(cb.lessThan(e2.<Long> get("salary"), e3.<Long> get("salary")));
        c.where(cb.equal(e1.get("salary"), sq));

        employees = em.createQuery(c).getResultList();

        for (Employee employee : employees) {
            System.out.println(employee.getName() + "salary"
                    + employee.getSalary());
        }

这为员工提供了最高薪水。结果如下。

Pranil Gildasalary5555555.0

请告诉我哪里错了。非常感谢您的解释。

Answer 1

经过反复试验，我可以编写查询来选择薪水第二高的员工。我想建议你应该先写一个JPQL查询并相应地编写条件api。这是我从JPQL分析出来的。

SELECT e FROM Employee e 
WHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp 
WHERE emp.salary < (SELECT MAX(employee.salary) FROM Employee employee) )

现在我们可以看到

• 有2个子查询，即主查询的子查询包含另一个子查询
• 标识变量e、emp、employee分别对应主查询、主查询的子查询、子查询的子查询。
• 现在在比较子查询的结果时，即最高工资与外部查询的员工工资相比，使用外部查询的标识变量。例如WHERE emp.salary = (SELECT MAX(emp.salary) FROM Employee emp)

现在让我们在条件 api 中转换此查询。

首先编写对应于最外层查询的 CriteriaQuery，即 SELECT e FROM Employee e WHERE e.salary =

CriteriaQuery<Employee> c1 = cb.createQuery(Employee.class);
Root<Employee> e3 = c1.from(Employee.class);
c1.select(e3);

让我们离开WHERE e.salary =现在去子查询

现在这应该有一个子查询来选择员工的最高工资，即 SELECT MAX(emp.salary) FROM Employee emp
WHERE emp.salary <再次让我们离开WHERE emp.salary <现在。

Subquery<Long> sq1 = c1.subquery(Long.class);
Root<Employee> e4 = sq1.from(Employee.class);
sq1.select(cb.max(e4.<Long> get("salary")));

对上述子查询的子查询重复此操作，

Subquery<Long> sq2 = sq1.subquery(Long.class);
Root<Employee> e5 = sq2.from(Employee.class);
sq2.select(cb.max(e5.<Long> get("salary")));

现在我们已经编写了子查询但是WHERE还需要应用条件。所以现在criteria api中的where条件对应WHERE emp.salary < (SELECT MAX(employee.salary) FROM Employee employee)将如下所示。

sq1.where(cb.lessThan(e4.<Long> get("salary"), sq2));

同理，WHERE条件对应WHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp将如下所示。

c1.where(cb.equal(e3.<Long> get("salary"), sq1));

所以给出第二高薪水的员工的完整查询可以写在 criteria api 中，如下所示。

        CriteriaQuery<Employee> c1 = cb.createQuery(Employee.class);
    Root<Employee> e3 = c1.from(Employee.class);
    c1.select(e3);

    Subquery<Long> sq1 = c1.subquery(Long.class);
    Root<Employee> e4 = sq1.from(Employee.class);
    sq1.select(cb.max(e4.<Long> get("salary")));

    Subquery<Long> sq2 = sq1.subquery(Long.class);
    Root<Employee> e5 = sq2.from(Employee.class);
    sq2.select(cb.max(e5.<Long> get("salary")));

    sq1.where(cb.lessThan(e4.<Long> get("salary"), sq2));
    c1.where(cb.equal(e3.<Long> get("salary"), sq1));

    employees = em.createQuery(c1).getResultList();

    for (Employee employee : employees) {
        System.out.println(employee.getName() + " " + employee.getSalary());
    }