After search I came to know that there is no Right Join in JPQL. I saw there is another way to achieve it using JPA bidirectional (not right join but using pojo object ) but one thing I noticed in console that it make many calls to database for eg see below table.
Flat Table UserToFlat User
| Flat_ID | Flat No | | ID | Flat_ID | User_ID | | User_ID | Name |
| 1 | 101 | | 1 | 1 | 1 | | 1 | XYZ |
| 2 | 102 | | 2 | 2 | 2 | | 2 | PQR |
| 3 | 103 | | 3 | 3 | 3 | | 3 | ABC |
| 4 | 104 |
I want all rows from flat table and only matching rows from User table
// below query do not work as flat is having primary key and usertoflat having foreign key
select f, u from FlatEntity f left join f.userToFlatEntity uf join uf.user u;
// now if I try right join then jpql throws exception
select f from UserToFlatEntity uf right join uf.flatEntity f;
Now if I use jpql birectional and fetch using object for eg
// suppose I have FlatEntity Object
flatEntity.getUserToFlatEntity();
above code will hits database for every flat no with where condition flat_ID = ? (4 times in this case ) and I think this not good performance wise.
So is there any way I can JPQL achieve right join with out effecting performance.
Entity Configuration
public class FlatEntity {
@OneToOne(mappedBy = "flatEntity")
private UserToFlatEntity userToFlatEntity;
// getter setter
}
public class UserToFlatEntity {
@ManyToOne
@JoinColumn(name = "flatId", insertable = false, updatable = false)
private FlatEntity flatEntity;
}
public class UserEntity {
@OneToMany(mappedBy = "userEntity")
private Set<UserToFlatEntity> userToFlatEntitySet;
}
Exception
Path expected for join!
at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:369)
You should make the Flat table the owner side of the relationship (which make more logical sense IMO). Then you can use a LEFT JOIN instead of a RIGHT JOIN.
SELECT uc, MAX(ua.accessTs) FROM Flat Table uc LEFT JOIN uc.User Table ua
Here's why left join on UserTable works:
for more detail visit here : RIGHT JOIN in JPQL