Andrea Boriero ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *commented* on HHH-16469 ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTAyND... ) Re: Hibernate repeatedly issues identical SELECT queries to load an optional one-to-one association ( https://hibernate.atlassian.net/browse/HHH-16469?atlOrigin=eyJpIjoiZTAyND... ) Hi Puhong You ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) , I have created this test import java.util.HashSet; import java.util.List; import java.util.Set; import org.hibernate.annotations.Fetch; import org.hibernate.annotations.FetchMode; import org.hibernate.testing.TestForIssue; import org.hibernate.testing.jdbc.SQLStatementInspector; import org.hibernate.testing.orm.junit.DomainModel; import org.hibernate.testing.orm.junit.SessionFactory; import org.hibernate.testing.orm.junit.SessionFactoryScope; import org.junit.jupiter.api.BeforeAll; import org.junit.jupiter.api.Test; import jakarta.persistence.CascadeType; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.FetchType; import jakarta.persistence.Id; import jakarta.persistence.JoinColumn; import jakarta.persistence.JoinTable; import jakarta.persistence.ManyToMany; import jakarta.persistence.ManyToOne; import jakarta.persistence.OneToMany; import jakarta.persistence.OneToOne; import jakarta.persistence.PrimaryKeyJoinColumn; import jakarta.persistence.Table; import static org.assertj.core.api.AssertionsForClassTypes.assertThat; @DomainModel( annotatedClasses = { TestIt.Agency.class, TestIt.AgencyDetail.class, TestIt.User.class, TestIt.Group.class } ) @SessionFactory(useCollectingStatementInspector = true) @TestForIssue(jiraKey = "HHH_16469") public class TestIt { @BeforeAll public void setUp(SessionFactoryScope scope) { scope.inTransaction( session -> { Agency agency = new Agency( 1, "Test Agency" ); session.persist( agency ); Group group = new Group( 1, "Test Group 1" ); session.persist( group ); for ( int i = 1; i < 9; i++ ) { User user = new User( i, "User " + i ); group.addUser( user ); agency.addUser( user ); session.persist( user ); } } ); } @Test public void tesGetAgency(SessionFactoryScope scope) { SQLStatementInspector statementInspector = (SQLStatementInspector) scope.getStatementInspector(); statementInspector.clear(); scope.inTransaction( session -> { Agency agency = session.get( Agency.class, 1 ); List<String> executedQueries = statementInspector.getSqlQueries(); assertThat( executedQueries.size()).isEqualTo( 3 ); assertThat( executedQueries.get( 0 ) ).contains( " from AGENCY_TABLE " ); assertThat( executedQueries.get( 1 ) ).contains( "from USER_TABLE " ); assertThat( executedQueries.get( 2 ) ).contains( "from GROUP_TABLE " ); } ); } @Entity(name = "Agency") @Table(name = "AGENCY_TABLE") public static class Agency { private Integer agencyId; private String agencyName; private AgencyDetail agencyDetail; private Set<User> users; private Set<Group> groups; public Agency() { } public Agency(Integer agencyId, String agencyName) { this.agencyId = agencyId; this.agencyName = agencyName; } @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; } public void addUser(User user){ if(users == null){ users = new HashSet<>(); } users.add( user ); user.setAgency( this ); } @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; } } @Entity(name = "AgencyDetail") @Table(name = "AGENCY_DETAIL_TABLE") public static 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 setAgencyDetail(String agencyDetail) { agencyDetail = agencyDetail; } } @Entity(name = "User") @Table(name = "USER_TABLE") public static class User { private Integer userId; private String userName; private Agency agency; public User() { } public User(Integer userId, String userName) { this.userId = userId; this.userName = userName; } @Id @Column(name = "USER_ID") public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.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; } } @Entity(name = "Group") @Table(name = "GROUP_TABLE") public static class Group { private Integer groupId; private String name; private Agency agency; private Set<User> users; public Group() { } public Group(Integer groupId, String name) { this.groupId = groupId; this.name = name; } @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", 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; } public void addUser(User user) { if ( users == null ) { users = new HashSet<>(); } users.add( user ); } @ManyToOne @JoinColumn(name = "AGENCY_ID") public Agency getAgency() { return agency; } public void setAgency(Agency agency) { this.agency = agency; } } } and session.get( Agency.class, 1 ); executes only 3 queries List<String> executedQueries = statementInspector.getSqlQueries(); assertThat( executedQueries.size()).isEqualTo( 3 ); that are Hibernate: select a1_0.AGENCY_ID,a2_0.AGENCY_ID,a2_0.AGENCY_DETAIL,a1_0.AGENCY_TXT from AGENCY_TABLE a1_0 left join AGENCY_DETAIL_TABLE a2_0 on a2_0.AGENCY_ID=a1_0.AGENCY_ID where a1_0.AGENCY_ID=? [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:28 - binding parameter [1] as [INTEGER] - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:39 - extracted value ([2] : [INTEGER]) - [null] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:39 - extracted value ([3] : [VARCHAR]) - [null] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([4] : [VARCHAR]) - [Test Agency] Hibernate: select u1_0.AGENCY_ID,u1_0.USER_ID,u1_0.USER_NAME from USER_TABLE u1_0 where u1_0.AGENCY_ID=? [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:28 - binding parameter [1] as [INTEGER] - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [2] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 2] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [3] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 3] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [4] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 4] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [5] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 5] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [6] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 6] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [7] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 7] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([1] : [INTEGER]) - [1] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([2] : [INTEGER]) - [8] [subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:28 - extracted value ([3] : [VARCHAR]) - [User 8] and Hibernate: select g1_0.AGENCY_ID,g1_0.GROUP_ID,g1_0.GROUP_NAME from GROUP_TABLE g1_0 where g1_0.AGENCY_ID=? [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:28 - binding parameter [1] as [INTEGER] - [1] am I miissing something? ( https://hibernate.atlassian.net/browse/HHH-16469#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16469#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100222- sha1:cb3bdf0 )