Which one of the following queries selects the customer whose order has the highest total price?
A.
CriteriaBuilder cb = …
Criteria Query <Customer> cq = cb.create Query (Customer.class);
Root<Customer> c = cq.from(Customer.class);
Join<Customer, Order> o = c.join(Customer__.orders);
cq.select(c).distinct(true);
Subquery<Double> sq = cq.subquery(Double.class);
Root<Order> subo = cq.correlate(o);
sq.select(cb.max(subo.get(Order_.totalPrice)));
cq.where(cb.equal(o.get(Order_.totalPrice), cb.all(sq)));
B.
CriteriaBuilder cb = …
CriteriaQuery<Customer> cq = cb.createquery(customer.class)
Root<Customer> c = cq.from(Customer.class);
Join<Customer, Order> o = c.join(Customer__.orders);
cq.select(c).distinct(true);
Subquery<Double> sq = cq.subquery(Double.class);
Root<Order> subo = cq.correlate(o);
sq.select(cb.max(subo.get(Order_.totalPrice)));
cq.where(cb.equal(o.get(Order_.totalPrice), cb.all(sq)));
C.
CriteriaBuilder cb = …
CriteriaQuery<Customer> cq = cb.cteateQuery(Customer.class);
Root<Customer> c = cq.from(Customer.class);
Join<Customer, Order> o = c.join(Customer__.orders);
cq.select(c).distinct(true);
Subquery<Double> sq = cq.subquery(Double.class);
Root<Order> subo = cq.correlate(o);
sq.select(cb.max(subo.get(Order_.totalPrice)));
cq.where(cb.equal(o.get(Order_.totalPrice), cb.all(sq)));
D.
CriteriaBuilder cb = …
CriteriaQuery<Customer> cq = cb.createQuery(Customer.class);
Root<Customer> c = cq.from(Customer.class);
Join<Customer, Order> o = c.join(Customer_.orders);
cq.select(c).distinct(true);
Subquery<Double> sq = cq.subquery(Double.class);
Root<Order> subo = sq.from(Order.class);
sq. select (ci: . max ( subo . get (Order_ . Total Price) ) ) ;
cq.where(sq.all(o.gei(Order_.totalPrice)));
D
A B and C are the same and are correct.
D is incorrect: Subquery doesn’t have method all, this is in CriteriaBuilder (sq.all -> cb.all). Among other errors (result of all not suitable for a where).
I think that none of the answers are correct.
A, B and C are not correct because of the following line:
Root{Order} subo = cq.correlate(o);
in this case “o” is the variable of type Join{Customer, Order} and according to the javax.persistence.criteria.Subquery{T} interface, return type of the correlate method that accepts Join{X,Y} is Join{X,Y} and in this case it is Root{Order}, what is wrong.
public interface Subquery{T} extends AbstractQuery{T}, Expression{T} {
…
{Y} Root{Y} correlate(Root{Y} parentRoot);
{X, Y} Join{X, Y} correlate(Join{X, Y} parentJoin);
…
}
I agree with your explanation for D (subquery doesn’t have method all).
I think that correct answer should look like this:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery {Customer} cq = cb.createQuery(Customer.class);
Root{Customer} c = cq.from(Customer.class);
Join{Customer, Order} o = c.join(Customer__.orders);
cq.select(c).distinct(true);
Subquery{Double} sq = cq.subquery(Double.class);
Root{Order} subo = cq.from(Order.class);
sq.select(cb.max(subo.get(Order_.totalPrice)));
cq.where(cb.equal(o.get(Order_.totalPrice), cb.all(sq)));
So if you just change following line:
Root subo = cq.correlate(o);
with this line:
Root{Order} subo = cq.from(Order.class);
P.s. I have deliberately used curly brackets instead of triangular brackets,due to the problems that I had on some posts earlier (triangular brackets are not visible when you post answers that includes them).
While formulating a Criteria API query, it is best to build a regular sql query on paper and then think about converting it to criteria API query. In this case, all you really need is:
SELECT c.* FROM Customer c, CustomerOrder co
WHERE c.ID=co.CUSTOMER_ID and
co.DISCOUNT = (select max(DISCOUNT) from CUSTOMERORDER)
1. SELECT c.* implies:
CriteriaQuery cq = cb.createQuery(Customer.class);
Root customer = cq.from(Customer.class);
cq.select(customer).distinct(true);
2. FROM Customer c, CustomerOrder co
WHERE c.ID=co.CUSTOMER_ID implies:
Join custOrder = customer.join(“orders”);
Note that customer.join(“orders”) is same as customer.join(Customer_.orders).
3. (select max(DISCOUNT) from CUSTOMERORDER) implies:
Subquery sq = cq.subquery(Double.class);
Root sqRoot = sq.from(CustomerOrder.class); sq.select(cb.max(sqRoot.get(CustomerOrder_.discount)));
4. co.DISCOUNT = implies:
cq.where( cb.equal(custOrder.get(CustomerOrder_.discount), sq) );