Hibernate – JPQL correctly

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 in a flat table , Only need to match the rows in the 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 to use objects, for example

// suppose I have FlatEntity Object 
flatEntity.getUserToFlatEntity();

The above code will hit flat_ID = for each unit’s database? (4 times in this case), I think this is not very good performance.

So is there any way to make JPQL achieve the correct connection without affecting 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 userToFlatEntitySet;
 }

Exception
 Path expected for join!
 at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:369)

SELECT uc, MAX(ua.accessTs) FROM Flat Table uc LEFT JOIN uc.User Table ua

After searching, I found that there is no correct addition in JPQL. I see that there is another way to use JPA bidirectional Implement it (not connected correctly but using the pojo object) but I noticed one thing in the console that it will make multiple calls to the database, for example, see the following table.

< pre>Flat Table UserToFlat User
| Flat_ID | Flat No | | ID | Flat_ID | User_ID | | User_ID | Name |
| 1 | 101 | | 1 | 1 | 1 | | 1 | XYZ | < br />| 2 | 102 | | 2 | 2 | 2 | | 2 | PQR |
| 3 | 103 | | 3 | 3 | 3 | | 3 | ABC |
| 4 | 104 |

I want all the rows in the flat table, only need to match the rows in the 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 to use objects, for example

// suppose I have FlatEntity Object 
flatEntity.getUserToFlatEntity();

The above code will hit flat_ID = for each unit’s database? (4 times in this case), I think this is not very good performance.

So is there any way to make JPQL achieve the correct connection without affecting 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 userToFlatEntitySet;
 }

Exception
 Path expected for join!
 at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:369)

You should use the Flat table as the owner side of the relationship (this makes IMO more logical). Then you can use LEFT JOIN instead of 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:

< p>For more details, please visit: RIGHT JOIN in JPQL