I have problem with JPA Repository.I have entity:
@Getter
@Setter
@Entity
@NamedEntityGraph(name = "user-graph", includeAllAttributes = true,
attributeNodes = {
@NamedAttributeNode("recruiter")
})
@Table(name = "users")
public class User extends BaseEntity implements Serializable, UserDetails {
private static final long serialVersionUID = 1L;
private String name;
private String lastName;
@Column(nullable = false, unique = true)
private String username;
private String dateOfBirth;
private String phone;
private String facebook;
private String twitter;
private String instagram;
private String description;
private Long experience;
@Column(columnDefinition = "boolean default false")
private boolean possibleRelocation;
@Column(columnDefinition = "boolean default false")
private boolean remote;
private boolean emailVisible;
@Column(nullable = false)
private String password;
private boolean enabled;
private boolean isExpired;
private boolean isLocked;
private boolean isCredentialsExpired;
private boolean isSuperAdmin;
@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<RecruiterFavouriteUsers> recruiterFavouriteUsers = new HashSet<>();
@Transient
@JsonIgnore
@Column(nullable = false)
private String passwordConfirm;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "role_id", referencedColumnName = "id", nullable = false)
private Role role;
@OneToOne(mappedBy = "user")
private VerificationToken verificationToken;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "company_id")
private Company company;
@OneToOne
@JoinColumn(name = "recruiter_id")
private Recruiter recruiter;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "address_id")
private Address address;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "avatar_id")
private Avatar avatar;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "background_id")
private Background background;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "position_level_id", referencedColumnName = "id")
private PositionLevelDict positionLevel;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "workplace_id", referencedColumnName = "id")
private WorkplaceDict workplace;
@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<Skill> skill;
@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<Language> language;
@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<Work> work;
@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<School> school;
@OneToMany(mappedBy = "userTo", fetch = FetchType.LAZY)
private Set<MessageGroup> messageGroupsTo;
@OneToMany(mappedBy = "userFrom", fetch = FetchType.LAZY)
private Set<MessageGroup> messageGroupsFrom;
...
}
And Repository:
@Repository
@Transactional
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
@EntityGraph("user-graph")
Optional<User> findById(Long id);
@EntityGraph("user-graph")
User findByUsername(String username);
@EntityGraph("user-graph")
User findByUsernameAndEnabledTrue(String username);
@EntityGraph("user-graph")
User findByUsernameAndEnabledFalse(String username);
}
When I'm trying use findById method, I'm getting n+1 problem:
Hibernate: select user0_.id as id1_32_0_, user0_.create_date as create_d2_32_0_, user0_.last_update as last_upd3_32_0_, user0_.address_id as address23_32_0_, user0_.avatar_id as avatar_24_32_0_, user0_.background_id as backgro25_32_0_, user0_.company_id as company26_32_0_, user0_.date_of_birth as date_of_4_32_0_, user0_.description as descript5_32_0_, user0_.email_visible as email_vi6_32_0_, user0_.enabled as enabled7_32_0_, user0_.experience as experien8_32_0_, user0_.facebook as facebook9_32_0_, user0_.instagram as instagr10_32_0_, user0_.is_credentials_expired as is_cred11_32_0_, user0_.is_expired as is_expi12_32_0_, user0_.is_locked as is_lock13_32_0_, user0_.is_super_admin as is_supe14_32_0_, user0_.last_name as last_na15_32_0_, user0_.name as name16_32_0_, user0_.password as passwor17_32_0_, user0_.phone as phone18_32_0_, user0_.position_level_id as positio27_32_0_, user0_.possible_relocation as possibl19_32_0_, user0_.recruiter_id as recruit28_32_0_, user0_.remote as remote20_32_0_, user0_.role_id as role_id29_32_0_, user0_.twitter as twitter21_32_0_, user0_.username as usernam22_32_0_, user0_.workplace_id as workpla30_32_0_, address1_.id as id1_0_1_, address1_.create_date as create_d2_0_1_, address1_.last_update as last_upd3_0_1_, address1_.city as city4_0_1_, address1_.country as country5_0_1_, address1_.flat_number as flat_num6_0_1_, address1_.house_number as house_nu7_0_1_, address1_.post_code as post_cod8_0_1_, address1_.street as street9_0_1_, avatar2_.id as id1_1_2_, avatar2_.create_date as create_d2_1_2_, avatar2_.last_update as last_upd3_1_2_, avatar2_.image as image4_1_2_, avatar2_.mimetype as mimetype5_1_2_, avatar2_.name as name6_1_2_, background3_.id as id1_2_3_, background3_.create_date as create_d2_2_3_, background3_.last_update as last_upd3_2_3_, background3_.image as image4_2_3_, background3_.mimetype as mimetype5_2_3_, background3_.name as name6_2_3_, company4_.id as id1_3_4_, company4_.create_date as create_d2_3_4_, company4_.last_update as last_upd3_3_4_, company4_.company_creation_date as company_4_3_4_, company4_.company_size_id as company_7_3_4_, company4_.description as descript5_3_4_, company4_.name as name6_3_4_, language5_.user_id as user_id7_23_5_, language5_.id as id1_23_5_, language5_.id as id1_23_6_, language5_.create_date as create_d2_23_6_, language5_.last_update as last_upd3_23_6_, language5_.company_id as company_6_23_6_, language5_.level as level4_23_6_, language5_.name as name5_23_6_, language5_.user_id as user_id7_23_6_, messagegro6_.user_from_id as user_fro6_25_7_, messagegro6_.id as id1_25_7_, messagegro6_.id as id1_25_8_, messagegro6_.create_date as create_d2_25_8_, messagegro6_.last_update as last_upd3_25_8_, messagegro6_.deleted_user_id as deleted_4_25_8_, messagegro6_.last_message as last_mes5_25_8_, messagegro6_.user_from_id as user_fro6_25_8_, messagegro6_.user_to_id as user_to_7_25_8_, messagegro7_.user_to_id as user_to_7_25_9_, messagegro7_.id as id1_25_9_, messagegro7_.id as id1_25_10_, messagegro7_.create_date as create_d2_25_10_, messagegro7_.last_update as last_upd3_25_10_, messagegro7_.deleted_user_id as deleted_4_25_10_, messagegro7_.last_message as last_mes5_25_10_, messagegro7_.user_from_id as user_fro6_25_10_, messagegro7_.user_to_id as user_to_7_25_10_, positionle8_.id as id1_14_11_, positionle8_.create_date as create_d2_14_11_, positionle8_.last_update as last_upd3_14_11_, positionle8_.name as name4_14_11_, recruiter9_.id as id1_27_12_, recruiter9_.create_date as create_d2_27_12_, recruiter9_.last_update as last_upd3_27_12_, recruiter9_.payment_id as payment_4_27_12_, recruiter9_.recruiter_type_id as recruite5_27_12_, recruiterf10_.user_id as user_id5_28_13_, recruiterf10_.id as id1_28_13_, recruiterf10_.id as id1_28_14_, recruiterf10_.create_date as create_d2_28_14_, recruiterf10_.last_update as last_upd3_28_14_, recruiterf10_.recruiter_id as recruite4_28_14_, recruiterf10_.user_id as user_id5_28_14_, role11_.id as id1_29_15_, role11_.create_date as create_d2_29_15_, role11_.last_update as last_upd3_29_15_, role11_.name as name4_29_15_, school12_.user_id as user_id9_30_16_, school12_.id as id1_30_16_, school12_.id as id1_30_17_, school12_.create_date as create_d2_30_17_, school12_.last_update as last_upd3_30_17_, school12_.end_year as end_year4_30_17_, school12_.field_of_study as field_of5_30_17_, school12_.name as name6_30_17_, school12_.start_year as start_ye7_30_17_, school12_.title as title8_30_17_, school12_.user_id as user_id9_30_17_, skill13_.user_id as user_id7_31_18_, skill13_.id as id1_31_18_, skill13_.id as id1_31_19_, skill13_.create_date as create_d2_31_19_, skill13_.last_update as last_upd3_31_19_, skill13_.company_id as company_5_31_19_, skill13_.level as level4_31_19_, skill13_.technology_id as technolo6_31_19_, skill13_.user_id as user_id7_31_19_, work14_.user_id as user_id14_34_20_, work14_.id as id1_34_20_, work14_.id as id1_34_21_, work14_.create_date as create_d2_34_21_, work14_.last_update as last_upd3_34_21_, work14_.city as city4_34_21_, work14_.company_name as company_5_34_21_, work14_.country as country6_34_21_, work14_.description as descript7_34_21_, work14_.end_month as end_mont8_34_21_, work14_.end_year as end_year9_34_21_, work14_.start_month as start_m10_34_21_, work14_.start_year as start_y11_34_21_, work14_.still_working as still_w12_34_21_, work14_.user_id as user_id14_34_21_, work14_.workplace as workpla13_34_21_, workplaced15_.id as id1_17_22_, workplaced15_.create_date as create_d2_17_22_, workplaced15_.last_update as last_upd3_17_22_, workplaced15_.name as name4_17_22_, verificati16_.id as id1_33_23_, verificati16_.create_date as create_d2_33_23_, verificati16_.last_update as last_upd3_33_23_, verificati16_.enabled as enabled4_33_23_, verificati16_.expiry_date as expiry_d5_33_23_, verificati16_.token as token6_33_23_, verificati16_.user_id as user_id8_33_23_, verificati16_.verification_token as verifica7_33_23_ from users user0_ left outer join address address1_ on user0_.address_id=address1_.id left outer join avatar avatar2_ on user0_.avatar_id=avatar2_.id left outer join background background3_ on user0_.background_id=background3_.id left outer join company company4_ on user0_.company_id=company4_.id left outer join language language5_ on user0_.id=language5_.user_id left outer join message_group messagegro6_ on user0_.id=messagegro6_.user_from_id left outer join message_group messagegro7_ on user0_.id=messagegro7_.user_to_id left outer join d_position_level positionle8_ on user0_.position_level_id=positionle8_.id left outer join recruiter recruiter9_ on user0_.recruiter_id=recruiter9_.id left outer join recruiter_favourite_users recruiterf10_ on user0_.id=recruiterf10_.user_id inner join role role11_ on user0_.role_id=role11_.id left outer join school school12_ on user0_.id=school12_.user_id left outer join skill skill13_ on user0_.id=skill13_.user_id left outer join work work14_ on user0_.id=work14_.user_id left outer join d_workplace workplaced15_ on user0_.workplace_id=workplaced15_.id left outer join verification_token verificati16_ on user0_.id=verificati16_.user_id where user0_.id=? Hibernate: select recruiter0_.id as id1_27_0_, recruiter0_.create_date as create_d2_27_0_, recruiter0_.last_update as last_upd3_27_0_, recruiter0_.payment_id as payment_4_27_0_, recruiter0_.recruiter_type_id as recruite5_27_0_, user1_.id as id1_32_1_, user1_.create_date as create_d2_32_1_, user1_.last_update as last_upd3_32_1_, user1_.address_id as address23_32_1_, user1_.avatar_id as avatar_24_32_1_, user1_.background_id as backgro25_32_1_, user1_.company_id as company26_32_1_, user1_.date_of_birth as date_of_4_32_1_, user1_.description as descript5_32_1_, user1_.email_visible as email_vi6_32_1_, user1_.enabled as enabled7_32_1_, user1_.experience as experien8_32_1_, user1_.facebook as facebook9_32_1_, user1_.instagram as instagr10_32_1_, user1_.is_credentials_expired as is_cred11_32_1_, user1_.is_expired as is_expi12_32_1_, user1_.is_locked as is_lock13_32_1_, user1_.is_super_admin as is_supe14_32_1_, user1_.last_name as last_na15_32_1_, user1_.name as name16_32_1_, user1_.password as passwor17_32_1_, user1_.phone as phone18_32_1_, user1_.position_level_id as positio27_32_1_, user1_.possible_relocation as possibl19_32_1_, user1_.recruiter_id as recruit28_32_1_, user1_.remote as remote20_32_1_, user1_.role_id as role_id29_32_1_, user1_.twitter as twitter21_32_1_, user1_.username as usernam22_32_1_, user1_.workplace_id as workpla30_32_1_, verificati2_.id as id1_33_2_, verificati2_.create_date as create_d2_33_2_, verificati2_.last_update as last_upd3_33_2_, verificati2_.enabled as enabled4_33_2_, verificati2_.expiry_date as expiry_d5_33_2_, verificati2_.token as token6_33_2_, verificati2_.user_id as user_id8_33_2_, verificati2_.verification_token as verifica7_33_2_ from recruiter recruiter0_ left outer join users user1_ on recruiter0_.id=user1_.recruiter_id left outer join verification_token verificati2_ on user1_.id=verificati2_.user_id where recruiter0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=? Hibernate: select technology0_.id as id1_16_0_, technology0_.create_date as create_d2_16_0_, technology0_.last_update as last_upd3_16_0_, technology0_.name as name4_16_0_ from d_technology technology0_ where technology0_.id=?
How to reduce all of this selects to one?
Another entities:
@Data
@Entity
public class VerificationToken extends BaseEntity {
private String token;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private User user;
private boolean verificationToken;
private boolean enabled;
private Date expiryDate;
@Getter
@Setter
@Entity
@Table(name = "recruiter")
public class Recruiter extends BaseEntity {
@OneToOne(mappedBy = "recruiter")
private User user;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "recruiter_type_id", referencedColumnName = "id")
private RecruiterTypeDict recruiterType;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "payment_id")
private Payment payment;
@OneToMany(mappedBy = "recruiter")
private Set<RecruiterFavouriteUsers> recruiterFavouriteUsers = new HashSet<>();
}
@Entity
@Getter
@Setter
@Table(name = "skill")
public class Skill extends BaseEntity {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "technology_id", nullable = false)
private TechnologyDict technology;
@Column(nullable = false)
private Long level;
@ManyToOne
@JoinColumn(name = "user_id", referencedColumnName = "id")
private User user;
@ManyToOne
@JoinColumn(name = "company_id", referencedColumnName = "id")
private Company company;
}
