JPQL does not work properly

Question

Parent class:

@Table(name = "users")
class User {
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
List<Gender> genders = new ArrayList<>();

Child class:

@Table(name = "gender")
class Gender {
@ManyToOne(cascade = {CascadeType.ALL})
@JoinColumn(name = "userId", nullable = false)
User user;
String sex;

my goal in MySQL:

SELECT * FROM users a LEFT JOIN genders b ON a.id=b.id WHERE b.sex='dasd'

Returns 1 ROW It works good. I got only one record that meets this condition.

but same code in JPQL:

SELECT a FROM Users a LEFT JOIN a.genders b WHERE b.sex ='dasd'

Returns: one user – correctly and ALL genders in table Gender, but I dont want to ALL I want only when sex ='dasd' and only one record that meets this condition.btw JPQL generateN+1 issue and for every subquery ignoring condtion sex ='dasd'

How to write subquery satisfying the condition? Can I force subquery to return only Gender matching the JPQL query?

Could you provide Gender entity code? – Piotr Sołtysiak Dec 05 '16 at 16:57 — Piotr Sołtysiak, Dec 05 '16 at 16:57
I included also Gender.java(child) – Kamil Nękanowicz Dec 05 '16 at 17:53 — Kamil Nękanowicz, Dec 05 '16 at 17:53

chalailama · Accepted Answer · 2016-12-05T23:16:51.313

3

In JPQL you select from entity not table,

SELECT a FROM User a LEFT JOIN a.genders b WHERE b.sex ='dasd'

Since you configured the a.genders list as EAGER, every time you select an User, it will fetch all genders the user is asociated to. Even if they don't match the JPQL query.

If you setup the a.genders list as LAZY, it wont fetch any Gender, since you selected only from User

That query is equivalent to SQL

SELECT a.* FROM users a LEFT JOIN genders b ON a.id=b.id WHERE b.sex='dasd'

If you want to select only one Gender

SELECT b FROM Gender b WHERE b.user = :user AND b.sex = 'dasd'

edited Dec 05 '16 at 23:16

answered Dec 05 '16 at 20:07

chalailama

66
5

Every time you select an User, it will fetch all genders the user is asociated to. Even if they don't match the JPQL query. Yes, and this is problem. Can I force it to return only Gender matching the JPQL query? – Kamil Nękanowicz Dec 05 '16 at 20:23
The problem is that you are not selecting a Gender, you are selecting an User. If you use EAGER, the genders collection gets initialized with the complete collection. If you use LAZY, the genders collection will be initialized with a proxy that will fetch the entire collection if needed (if you do `a.getGenders()` ). I have edited the answer on how to select one Gender. – chalailama Dec 05 '16 at 23:20
Not with criteria. The problem is you are querying for an User. If you want to select only one Gender, use the select from gender query. – chalailama Dec 06 '16 at 18:40
I want to querying for an User and Gender. not individually. In raw SQL it works. I will try do it wit mybatis. – Kamil Nękanowicz Dec 19 '16 at 06:30

JPQL does not work properly

1 Answers1