JPA findDistinctPropertyBy magic method doesn't work as expected when using spring-boot-starter-jpa

1.4k views Asked by At

So, here is the problem that I got.

I am building a database model structure by JPA which has a many to many relation, here is the class model

public class UserActivityRelation{
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id",nullable = false)
    private User participant;


    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "activity_id",nullable = false)
    private Activity activity;
}

here is the corresponding DAO code that I followed the spring-doc spring-boot-jpa

public List<UserActivityRelation> findDistinctParticipantByParticipantAndActivity(User user, Activity activity);

hoping to distinct rows with same user_id, but it came with an unexpected result, the sql sentence is as follows:

select distinct useractivi0_.id as id1_1_, useractivi0_.created_time 
as created_2_1_, useractivi0_.modified_time as modified3_1_,
useractivi0_.activity_id as activity5_1_, useractivi0_.user_id as
user_id6_1_, useractivi0_.status as status4_1_ from act_users 
useractivi0_ left outer join users user1_ on useractivi0_.user_id=user1_.id 
left outer join activities activity2_ on 
useractivi0_.activity_id=activity2_.id where (user1_.id is null) 
and (activity2_.id is null)

the sql code seems really complex, but the point is clear, findDistinct only has effect on the primary key id rather than the participant----user_id, and it uses a lot of left outer join...

At first I thought I write the method wrong, so I test it with another method name

findDistinctHEHEByParticipantAndActivity , as you can see it has HEHE in the middle which has nothing do to with model class, neither a property nor a method... and it just gave an identical sql sentence, which I was so confused to see...

So, is it just the way that JPA with hibernate works or did I do it wrong?

0

There are 0 answers