CODEWITHSUNDEEP
javamysqlsqlhibernatejpa
Mercer
Mercer

Reputation: 9986

How to define JPA Repository Query with a Join?

I would like to make a Join query by Jpa repository by annotation @Query I have three tables.

The native query is:

select application.APP_ID 
from user, customer, application 
where user.USE_CUSTOMER_ID = customer.CUS_ID 
and application.APP_CUSTOMER_ID = customer.CUS_ID 
and user.USE_ID=1;

Now I have Table Hibernate entity, so I tried in ApplicationRepository

@Query(SELECT  application FROM  Application a
  INNER JOIN customer c ON c.customer.id = a.customer.id 
  INNER JOIN user u ON u.customer.id = c.customer.id
  INNER JOIN application a ON a.user.id = u.id
  WHERE
  u.id = :user.id)
List<Application> findApplicationsByUser(@Param("User") User user);

The log says

unexpected token

Any ideas, please?

My table Entity

Application.java:

@Entity
@Table
public class Application extends BaseSimpleEntity {
...
    @ManyToOne(optional = false)
    private Customer customer;
...
}

Customer.java:

@Entity
@Table
public class Customer extends BaseSimpleEntity {
...
    @OneToMany(mappedBy = "customer")
    private List<User> users;
    @OneToMany(mappedBy = "customer")
    private List<Application> applications;
...
}

User.java: 

@Entity
@Table
public class User extends BaseSimpleEntity {
...
    @ManyToOne(optional = false)
    private Customer customer;
...
}

Upvotes: 7

Views: 24185

Answers (1)

JB Nizet
JB Nizet

Reputation: 692131

You don't need ON clauses in JPA, because the JPA already know how entities are associated thanks to the mapping annotations.

Moreover, you're selecting application, which is not an alias defined in your query.

And your joins make no sense.

The query should simply be

select application FROM Application a
join a.customer c 
join c.users u
where u.id = :userId

Read the Hibernate documentation to understand how HQL and joins work.

Upvotes: 13

Related Questions