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?