How to Use Multiple Join on Hibernate?

Question

I have these following Classes:

class Person(){
   @OneToMany(mappedBy="person")
   private List<PersonRoles> roles;
}

class PersonRoles(){
   @ManyToOne
   @JoinColumn(name = "person_id", nullable = false)
   private Person person;

   @ManyToOne
   @JoinColumn(name = "request_id")
   private Request request;
}

class Request(){
   @OneToMany(mappedBy="request")
   private List<PersonRoles> roles;
}

Now I am going to fetch all person based on a given request id and his roles by using hibernate and inner join but my log is telling me that my table doesn't exist. This is my query so far:

sql = "SELECT p.* FROM person AS p INNER JOIN p.roles ON p.roles.personId = p.id 
INNER JOIN request AS r ON p.roles.requestId = r.id AND p.roles.role like :item 
AND r.id = :id";
query = session.createSQLQuery(sql);
query.addEntity(Person.class);
query.setParameter("item", "Members");
query.setParameter("id", id);
person = (Person) query.uniqueResult();

and this is what i received on the log:

Table 'p.roles' doesn't exist

Did i forget some hibernate annotation? or My query has something wrong?

You would have to use entities instead of table names, here is an example: https://www.baeldung.com/hibernate-named-query — Andronicus, Apr 07 '19 at 11:24
I tried something like this but it gives me a *null*: ```sql = "from Person as p inner join p.roles as roles inner join roles.request as request where roles.role like :item and request.id = :id";``` — Anirudh Lou, Apr 07 '19 at 11:50

score 0 · Answer 1 · answered Apr 07 '19 at 13:56

0

Brief reason

your syntax of SQL is wrong

Detailed explanation

here is the syntax of inner join example

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

for multiple inner join

SELECT * 
FROM table1 
INNER JOIN table2
      ON table1.primaryKey=table2.table1Id
INNER JOIN table3
      ON table1.primaryKey=table3.table1Id

but you have used INNER JOIN p.roles there should be a table name after the INNER JOIN, not a column name.

that's why you got an error, moreover, use HQL instead of SQL in hibernate it is a good practice.

happy coding!

answered Apr 07 '19 at 13:56

majurageerthan

2,169
3
17
30

I tried to use HQL but i got null. ```sql = "from Person as person inner join person.roles as roles inner join roles.request as request where roles.role like :item and request.id = :id"``` – Anirudh Lou Apr 07 '19 at 14:05
your syntax is wrong friend, "inner join person.roles" this is wrong – majurageerthan Apr 07 '19 at 14:07
you can not join a column, try to join another table – majurageerthan Apr 07 '19 at 14:07
even in HQL? I already used the Class not the table. Person class has many association with class Roles. – Anirudh Lou Apr 07 '19 at 14:16
yes, hql is not a magic, it change the code to sql so you should follow the SQL rules and requlations – majurageerthan Apr 07 '19 at 14:18
use a class and post the error details, then i can suggest a solution – majurageerthan Apr 07 '19 at 14:19
use "query = session.createQuery(hql); " for hql – majurageerthan Apr 07 '19 at 14:21
```sql = "from Person as person inner join person.roles as roles inner join roles.request as request where roles.role like :item and request.id = :id"; Query q = session.createQuery(sql); q.setParameter("item", "Members"); q.setParameter("id", id); person = (Person) q.uniqueResult();``` – Anirudh Lou Apr 07 '19 at 14:24
I already used ```query = session.createQuery(sql)``` but i got null. – Anirudh Lou Apr 07 '19 at 14:25
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191398/discussion-between-anirudh-lou-and-majuran). – Anirudh Lou Apr 07 '19 at 14:28

How to Use Multiple Join on Hibernate?

1 Answers1