7

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)
philipxy
  • 14,867
  • 6
  • 39
  • 83
pise
  • 849
  • 6
  • 24
  • 51
  • There is right join in JPQL. – Dragan Bozanovic Feb 04 '16 at 11:28
  • @DraganBozanovic Can you please give me some example because I am getting error. I am posting my mapping configuration. – pise Feb 06 '16 at 18:27
  • I don't see any `from` clauses in your queries. I suggest you go through some Hibernate/JPA tutorials first. – Dragan Bozanovic Feb 06 '16 at 19:49
  • @DraganBozanovic it was my typing error not having from clause – pise Feb 10 '16 at 19:00
  • Ok, could you then please post the errors you get with right join? – Dragan Bozanovic Feb 10 '16 at 19:01
  • @DraganBozanovic JPQL query `select f from UserToFlatEntity uf right join uf.flatEntity ` error I am getting `No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode \-[IDENT] IdentNode: 'f' {originalText=f}` – pise Feb 10 '16 at 19:03
  • @DraganBozanovic I was f alias to flatEntity in query and because of that it was throughing error. – pise Feb 10 '16 at 19:09
  • @DraganBozanovic is there any Jpql eclipse tool where I can directly run JPQL query. – pise Feb 10 '16 at 19:10
  • [What topics can I ask about here?](http://stackoverflow.com/help/on-topic) _"4. Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam."_ – Dragan Bozanovic Feb 10 '16 at 19:14

3 Answers3

5

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

enter image description here

Community
  • 1
  • 1
Harshad
  • 1,344
  • 1
  • 10
  • 25
4

RRIGHT JOINS aka RRIGHT OUTER JOINS is the same as

SLEFT JOINR aka SLEFT OUTER JOINR.

philipxy
  • 14,867
  • 6
  • 39
  • 83
0

You won't find any (working) examples of a right join in JPQL. As mentioned in this EclipseLink forum post, the JPA specification does not include a right join:

The JPA spec only defines a Left Outer Join. Feel free to file an EclipseLink enhancement or request its inclusion in the JPA specification.

Maybe you can resort to a native query?

blubb
  • 9,510
  • 3
  • 40
  • 82