在JPA条件API中使用子查询 [英] using subqueries in jpa criteria api

问题描述

我正在研究JPA条件api，并且我的数据库包含Employee表. 我正在寻找所有薪水第二高的员工.我能够成功编写JPQL，如下所示.

I'm studying JPA criteria api and my database contains Employee table. I am trying to find all the employees who are paid second highest salary. I was able to write JPQL successfully as follows.

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并尝试了以下操作.

but now I am trying to convert it to criteria api and have tried following.

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方法不兼容的错误.我不明白如何获得此查询.我的方法正确吗?

I get the error that parameters are not compatible with lessThan method. I do not understand how can I get this query worked out. Is my approach right?

-在Mikko的答案之后更新问题.

EDIT :- Updating the question after Mikko's answer.

上面提供的jpql提供以下结果，即薪水第二高的员工.

The jpql provided above provides following results, which are the employees with second highest salary.

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

，但更新的条件查询如下:

but the updated criteria query as below,

        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());
        }

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

This provides the employee with highest salary. The result is as below.

Pranil Gildasalary5555555.0

请告诉我我错了.对此深表感谢.

Please tell me where I am being wrong. An explanation is deeply appreciated.

推荐答案

经过反复尝试后，我可以编写查询以选择第二高薪的员工.我建议您应该首先编写JPQL查询，并相应地编写条件api.这就是我从JPQL分析的内容.

After some more trial and error, I could write the query to select employees with second maximum salary. I would like to suggest that you should write a JPQL query first and write the criteria api accordingly. This is what I analyzed from 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中转换此查询.

Now let us convert this query in criteria api.

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

First write CriteriaQuery that corresponds to outermost query i.e. 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 =并转到子查询

Let us leave the WHERE e.salary = for now and go for the subquery

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

Now this should have a subquery that selects the maximum salary of employees i.e. SELECT MAX(emp.salary) FROM Employee emp WHERE emp.salary < again let us leave the WHERE emp.salary < for now.

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

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

repeating this for subquery of above subquery,

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

现在我们已经编写了子查询，但是还需要应用WHERE条件.因此，现在标准api中与WHERE emp.salary < (SELECT MAX(employee.salary) FROM Employee employee)相对应的where条件如下.

Now we have written subqueries but WHERE conditions need to be applied yet. So now the where condition in criteria api corresponding to WHERE emp.salary < (SELECT MAX(employee.salary) FROM Employee employee) will be as below.

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

类似地，与WHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp对应的WHERE条件如下.

Similarly, WHERE condition corresponding to WHERE e.salary = (SELECT MAX(emp.salary) FROM Employee emp will be as below.

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

因此，可以使用以下标准api编写给出薪水第二高的员工的完整查询.

So the complete query which gives the employees with second highest salary can be written in criteria api as below.

        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());
    }

这篇关于在JPA条件API中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！