Query by foreign key value instead of left join

user_8130632 0
Viewed 328

I have a relation 1-n between two tables (let's call them User and Roles):

User(ID, Name, LastName, ...)
Role(ID, User_ID, Label, ...) where User_ID is a foreign key to reference the User

In my Role repository, I have a method like this:

public List<Role> findByUser(User user);

The generated SQL is of the following form:

select ... from Role r left outer join User u on u.ID = r.User_ID where u.ID = ?

This is a performance botleneck. How to make spring-data generate a simpler query, like:

select ... from Role r where r.User_ID = ?

(get rid of the useless join)

spring-data jpa oracle java
1 Answers

Can't check it now but you can try the following:

public List<Role> findByUser_ID(Long id);

or

public List<Role> findBy_User_ID(Long id);

It may also depend on JPA implementation and mapping. ID should be properly mapped as primary keys and User_ID as a foreign key. Lazy loading for Role.user may also affect.

user_11676900
Related