*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 --
{ { noformat} @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; }}
{{ } }}
} { { noformat } }}
#AgencyDetail.java --
{ { noformat} @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; }}
{{ } }}
} { { noformat } }}
#User.java
{noformat} @Entity
@Table(name = "USER", schema = "TEST")
public class User {
{noformat} 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;
}
} {noformat}
}
#Group.java
{ { noformat} @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; }}
{{ } }}
} { { noformat } }}
*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.
|
|
