*Object models:

An Agency contains many users. Each user belongs to 0 or more groups. An Agency can optionally have detail information.

*Database tables:

AGENCY:
AGENCY_ID integer not null primary key,
AGENCY_NAME varchar(1024) not null

AGENCY_DETAIL:
AGENCY_ID integer not null primary key,
AGENCY_DETAIL varchar(1024) null

USER:
USER_ID integer not null primary key,
USER_NAME varchar(256) not null,
AGENCY_ID integer not null

GROUP:
GROUP_ID integer not null primary key,
GROUP_NAME varchar(256) not null,
AGENCY_ID integer not null

GROUP_USER:
GROUP_ID integer not null,
USER_ID integer not null,
composite key (GROUP_ID, USER_ID)

*Entity Beans

#Agency.java –

@Entity
@Table(name = "AGENCY", schema = "TEST")
public class Agency {

{{ private Integer agencyId;}}
{{ private String agencyName;}}

{{ private AgencyDetail agencyDetail;}}

{{ private Set<User> users;}}

{{ private Set<Group> groups;}}

{{ @Id}}
{{ @Column(name = "AGENCY_ID")}}
{{ public Integer getAgencyId() {}}
{{ return agencyId;}}
{{ }}}

{{ public void setAgencyId(Integer agencyId) {}}
{{ this.agencyId = agencyId;}}
{{ }}}

{{ @Column(name = "AGENCY_TXT")}}
{{ public String getAgencyName() {}}
{{ return agencyName;}}
{{ }}}

{{ public void setAgencyName(String agencyName) {}}
{{ this.agencyName = agencyName;}}
{{ }}}

{{ @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)}}
{{ @PrimaryKeyJoinColumn(name = "AGENCY_ID")}}
{{ public AgencyDetail getAgencyDetail() {}}
{{ return agencyDetail;}}
{{ }}}

{{ public void setAgencyDetail(AgencyDetail agencyDetail) {}}
{{ this.agencyDetail = agencyDetail;}}
{{ }}}

{{ @OneToMany(}}
{{ cascade = CascadeType.ALL,}}
{{ orphanRemoval = true,}}
{{ fetch = FetchType.EAGER,}}
{{ mappedBy = "agency")}}
{{ @Fetch(FetchMode.SELECT)}}
{{ public Set<User> getUsers() {}}
{{ return users;}}
{{ }}}

{{ public void setUsers(Set<User> users) {}}
{{ this.users = users;}}
{{ }}}

{{ @OneToMany(}}
{{ cascade = CascadeType.ALL,}}
{{ orphanRemoval = true,}}
{{ fetch = FetchType.EAGER,}}
{{ mappedBy = "agency")}}
{{ @Fetch(FetchMode.SELECT)}}
{{ public Set<Group> getGroups() {}}
{{ return groups;}}
{{ }}}

{{ public void setGroups(Set<Group> groups) {}}
{{ this.groups = groups;}}
{{ }}}
}

#AgencyDetail.java –

@Entity
@Table(name = "AGENCY_DETAIL", schema = "TEST")
public class AgencyDetail {

{{ private Integer agencyId = null;}}
{{ private String agencyDetail = null;}}

{{ @Id}}
{{ @Column(name = "AGENCY_ID")}}
{{ public Integer getAgencyId() {}}
{{ return agencyId;}}
{{ }}}

{{ public void setAgencyId(Integer agencyId) {}}
{{ this.agencyId = agencyId;}}
{{ }}}

{{ @Column(name = "AGENCY_DETAIL")}}
{{ public String getAgencyDetail() {}}
{{ return agencyDetail;}}
{{ }}}

{{ public void set AgencyDetail(String agencyDetail) {}}
{{ agencyDetail = agencyDetail;}}
{{ }}}
}

#User.java

@Entity
@Table(name = "USER", schema = "TEST")
public class User {

private Integer userId;
private String userName;

private Agency agency;

@Id
@Column(name = "USER_ID")
public Integer getUserId() {
  return userId;
}

public void setUserId(Integer userId) {
  userId = userId;
}

@Column(name = "USER_NAME")
public String getUserName() {
  return userName;
}

public void setUserName(String userName) {
  this.userName = userName;
}

@ManyToOne
@JoinColumn(name = "AGENCY_ID")
public Agency getAgency() {
  return agency;
}

public void setAgency(Agency agency) {
  this.agency = agency;
}

}

#Group.java

@Entity
@Table(name = "GROUP", schema = "TEST")
public class Group {

{{ private Integer groupId;}}
{{ private String name;}}
{{ private Agency agency;}}
{{ private Set<User> users;}}

{{ @Id}}
{{ @Column(name = "GROUP_ID")}}
{{ public Integer getGroupId() {}}
{{ return groupId;}}
{{ }}}

{{ public void setGroupId(Integer groupId) {}}
{{ this.groupId = groupId;}}
{{ }}}

{{ @Column(name = "GROUP_NAME", nullable = false, unique = false)}}
{{ public String getName() {}}
{{ return name;}}
{{ }}}

{{ public void setName(String name) {}}
{{ this.name = name;}}
{{ }}}

{{ @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)}}
{{ @JoinTable(}}
{{ name = "GROUP_USER",}}
{{ schema = "CRISOP",}}
{{ joinColumns = @JoinColumn(name = "GROUP_ID"),}}
{{ inverseJoinColumns = @JoinColumn(name = "USER_ID"))}}
{{ @Fetch(FetchMode.SUBSELECT)}}
{{ public Set<User> getUsers() {}}
{{ return users;}}
{{ }}}

{{ public void setUsers(Set<User> users) {}}
{{ this.users = users;}}
{{ }}}

{{ @ManyToOne}}
{{ @JoinColumn(name = "AGENCY_ID")}}
{{ public Agency getAgency() {}}
{{ return agency;}}
{{ }}}

{{ public void setAgency(Agency agency) {}}
{{ this.agency = agency;}}
{{ }}}
}

*Database table contents

INSERT INTO TEST.AGENCY VALUES(1, ‘TEST AGENCY’);

INSERT INTO TEST.USER VALUES (1, 'TEST USER 1);
INSERT INTO TEST.USER VALUES (2, 'TEST USER 2);
INSERT INTO TEST.USER VALUES (3, 'TEST USER 3);
INSERT INTO TEST.USER VALUES (4, 'TEST USER 4);
INSERT INTO TEST.USER VALUES (5, 'TEST USER 5);
INSERT INTO TEST.USER VALUES (6, 'TEST USER 6);
INSERT INTO TEST.USER VALUES (7, 'TEST USER 7);
INSERT INTO TEST.USER VALUES (8, 'TEST USER 8);

INSERT INTO TEST.GROUP VALUES(1, ‘TEST GROUP 1’);

INSERT INTO TEST.GROUP_USER VALUES (1,1);
INSERT INTO TEST.GROUP_USER VALUES (1,2);
INSERT INTO TEST.GROUP_USER VALUES (1,3);
INSERT INTO TEST.GROUP_USER VALUES (1,4);

*Test code

Agency agency = session.get(Agency.class, 1);

• Problems

load the Agency object from DB using the above test code generates 4 identical queries –

_{[04/13/23 14:53:43.425] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?
[04/13/23 14:53:43.425] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]
[04/13/23 14:53:43.514] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?
[04/13/23 14:53:43.515] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]
[04/13/23 14:53:43.601] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?
[04/13/23 14:53:43.601] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]
[04/13/23 14:53:43.692] DEBUG : main : SQL - select t1_0.AGENCY_ID,t1_0.AGENCY_DETAIL from TEST.AGENCY_DETAIL t1_0 where t1_0.AGENCY_ID=?
[04/13/23 14:53:43.692] TRACE : main : bind - binding parameter [1] as [INTEGER] - [1]}

If I add four more rows into the GROUP_USER table:

INSERT INTO TEST.GROUP_USER VALUES (1,5);
INSERT INTO TEST.GROUP_USER VALUES (1,6);
INSERT INTO TEST.GROUP_USER VALUES (1,7);
INSERT INTO TEST.GROUP_USER VALUES (1,8);

then loading the Agency object from DB using the test code generates 8 identical queries to SELECT from the AGENCY_DETAIL table.

In our project we have an agency with 5000+ users and 100+ groups, and thousands of rows in the GROUP_USER table. It took minutes to load the agency because the same SELECT from the AGENCY_DETAIL table query was issued thousands of times.

If I insert a row into the AGENCY_DETAIL table for agency with id 1 –

INSERT INTO TEST.AGENCY_DETAIL VALUES (1, ‘DETAIL’);

then loading the Agency object using the test code finished quickly, and there is no repeatedly identical SELECT from AGENCY_DETAIL query.