Menu
Can anybody give me some hints on how to put that kind of subquery in a CriteriaQuery? (I'm using JPA 2.0 - Hibernate 4.x)
SELECT a, b, c FROM tableA WHERE a = (SELECT d FROM tableB WHERE tableB.id = 3) - the second select will always get a single result or null.
359
In the first step, I instantiate a CriteriaQuery which returns Author entities. Then I call the subquery method on the CriteriaQuery to create a subquery that counts the Books written by the Author which is selected by the outer query. As you can see, I define the subquery in the same way as I create a CriteriaQuery.
A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Note that HQL subqueries can occur only in the select or where clauses.
In the query design grid, click the Criteria row of the field where you want to add the criterion.
Subqueries must be surrounded by parentheses. Subqueries may contain EXISTS, ALL, and ANY expressions. EXISTS expressions: The [NOT] EXISTS expression is used with a subquery and is true only if the result of the subquery consists of one or more values and is false otherwise.
Try something like the following example to create a subquery:
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root tableA = cq.from(TableA.class);
Subquery<String> sq = cq.subquery(TableB.class);
Root tableB = cq.from(TableB.class);
sq.select(tableB.get("d"));
sq.where(cb.equal(tableB.get("id"), 3));
cq.multiselect(
cb.get("a"),
cb.get("b"),
cb.get("c"));
cq.where(cb.equal(tableA.get("a"), sq));
List<Object[]> = em.createQuery(cq).getResultList();
Note the code has not been tested due to the lack of an IDE nearby.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
