A user entity is in a one-to-many relationship with a Book entity. In other words, a developer reach the collection of books that a user instance myUser has by using the path expression-myuser -books”.
A developer wants to write a Java Persistence query that returns all users that have only two books.
Which two are valid queries that return this information? (Choose two.)
A.
SELECT u FROM User U WHERE SIZE (u.books) = 2
B.
SELECT u FROM User WHERE COUNT (u.books) = 2
C.
SELECT u FROM User u (WHERE COUNT (b) FROM u.books b) = 2
D.
SELECT u FROM user u WHERE (SELECT SIZE (b) FROM u.books b) = 2
A, D
B: Hibernate/Mysql says: MySQLSyntaxErrorException: You have an error in your SQL syntax;’
C: Hibernate says: unexpected token: ( near line 1, column 38
A,D
I think D should be
SELECT u FROM user u WHERE (SELECT COUNT(b) FROM u.books b) = 2
i.e replace SIZE with count and it should work fine, cuz
\\The COUNT function is only allowed in the SELECT, GROUP BY, ORDER BY and HAVING clauses.\\
Tested with eclipse Link and worked in the given form.
agree
check a collection size in JPA2 :
SELECT u FROM User U WHERE SIZE (u.books) = 2
D.SELECT u FROM user u WHERE (SELECT SIZE (b) FROM u.books b) = 2
(is not valid)
The definition for size given in section 4.6.17.2.2 says, “SIZE(collection_valued_path_expression)”.
So select SIZE (u.books) would be valid
but select size(b) from u.books b is not valid.
Correct answers are A and D.
B. java.lang.IllegalArgumentException will occur with the following description: Exception Description: Syntax error parsing [SELECT u FROM User WHERE COUNT (u.books) = 2] The COUNT function is only allowed in the SELECT, GROUP BY, ORDER BY and HAVING clauses.
C. java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing [SELECT u FROM User u (WHERE COUNT (b) FROM u.books b) = 2].
[29, 29] The encapsulated expression is missing.
[35, 59] The expression is not a valid conditional expression.
[59, 64] The query contains a malformed ending.
D. java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing [SELECT u FROM user u WHERE (SELECT SIZE (b) FROM u.books b) = 2].
[47, 48] The encapsulated expression is not a valid expression.
D would be valid if we put COUNT instead of the SIZE function, as an example:
SELECT u FROM User u WHERE (SELECT COUNT(b) FROM u.books b) = 2
CONCLUSION:
I think that was exactly the point of this question, to realize that SIZE(collection) = (number) will be actually translated to the following subquery (SELECT COUNT(c) FROM ownerObject.collection c) = (number).
Here is an explanation from the JPA 2 book:
The SIZE function requires special attention because it is shorthand notation for an aggregate subquery. For example, consider the following query that returns all departments with only two employees:
SELECT d
FROM Department d
WHERE SIZE(d.employees) = 2
Like the collection expressions IS EMPTY and MEMBER OF, the SIZE function will be translated to SQL using a subquery. The equivalent form of the previous example using a subquery is as follows:
SELECT d
FROM Department d
WHERE (SELECT COUNT(e)
FROM d.employees e) = 2