hibernate criteria query with extra left outer join

Question

I have two objects, Program and Classification, with a one Program to many Classifications relationship. Here is my criteria query:

Criteria crit2 = sessionFactory.getCurrentSession()
 .createCriteria(Program.class)
 .add(Restrictions.eq("code", input.getCode()))
 .createAlias("classifications", "cl")
 .add(Restrictions.eq("cl.id", 3249023L));
 List<Program> programs2 = crit2.list();

and the SQL it generates has an additional left outer join which I cannot understand the purpose of?

Hibernate:
 select
 this_.id as id16_2_,
 this_.created as created16_2_,
 this_.modified as modified16_2_,
 this_.version as version16_2_,
 this_.active as active16_2_,
 this_.adminProgramId as adminPro6_16_2_,
 this_.code as code16_2_,
 this_.deletable as deletable16_2_,
 this_.desciption as desciption16_2_,
 this_.discontinued as discont10_16_2_,
 this_.effectiveDate as effecti11_16_2_,
 this_.expirationDate as expirat12_16_2_,
 this_.name as name16_2_,
 this_.programVersion as program14_16_2_,
 cl1_.id as id1_0_,
 cl1_.created as created1_0_,
 cl1_.modified as modified1_0_,
 cl1_.version as version1_0_,
 cl1_.causeOfLoss as causeOfL5_1_0_,
 cl1_.code as code1_0_,
 cl1_.description as descript7_1_0_,
 cl1_.lobCode as lobCode1_0_,
 cl1_.lobName as lobName1_0_,
 cl1_.premiumBaseCode as premium10_1_0_,
 cl1_.premiumBaseDesc as premium11_1_0_,
 cl1_.premiumBaseValue as premium12_1_0_,
 cl1_.program_id as program13_1_0_,
 program4_.id as id16_1_,
 program4_.created as created16_1_,
 program4_.modified as modified16_1_,
 program4_.version as version16_1_,
 program4_.active as active16_1_,
 program4_.adminProgramId as adminPro6_16_1_,
 program4_.code as code16_1_,
 program4_.deletable as deletable16_1_,
 program4_.desciption as desciption16_1_,
 program4_.discontinued as discont10_16_1_,
 program4_.effectiveDate as effecti11_16_1_,
 program4_.expirationDate as expirat12_16_1_,
 program4_.name as name16_1_,
 program4_.programVersion as program14_16_1_
 from
 Program this_
 inner join
 Classification cl1_
 on this_.id=cl1_.program_id
 left outer join
 Program program4_
 on cl1_.program_id=program4_.id
 where
 this_.code=?
 and cl1_.id=?

Does anyone know why this block is there:

left outer join
 Program program4_
 on cl1_.program_id=program4_.id

how can I get rid of it as additional joins will impact performance.

I am using hibernate 3.6.8

Here is Program class:

package com.sg.pds.domain.entity.ratedelivery;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.CollectionTable;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElementWrapper;
import javax.xml.bind.annotation.XmlTransient;
import org.hibernate.annotations.Index;
@XmlAccessorType(XmlAccessType.FIELD)
@Entity
public class Program extends BaseEntity {
 private String code;
 private String desciption;
 private String name;
 @XmlElement(name="classification") @XmlElementWrapper(name = "classifications")
 private Set<Classification> classifications = new HashSet<Classification>();
 @XmlElement(name="debitCredit") @XmlElementWrapper(name = "debitCredits")
 private Set<DebitCredit> debitCredits = new HashSet<DebitCredit>(); 
 @XmlElement(name="minPremium") @XmlElementWrapper(name = "minPremiums")
 private Set<DebitCredit> minPremiums = new HashSet<DebitCredit>(); 
 @XmlElement(name="deductible") @XmlElementWrapper(name = "deductibles")
 private Set<Deductible> deductibles = new HashSet<Deductible>(); 
 @XmlElement(name="optionalCoverage") @XmlElementWrapper(name = "optionalCoverages")
 private Set<OptionalCoverage> optionalCoverages = new HashSet<OptionalCoverage>();
 @XmlTransient
 private Date expirationDate;
 @XmlTransient
 private Date effectiveDate;
 @XmlTransient
 private String programVersion;
 @XmlTransient
 private Boolean discontinued;
 @XmlTransient
 private Boolean active;
 @XmlTransient
 private Boolean deletable;
 @XmlElement(name="company") @XmlElementWrapper(name = "companies")
 private Set<String> companies = new HashSet<String>();
 @XmlTransient
 private Set<Territory> territories = new HashSet<Territory>();
 @XmlTransient
 private String adminProgramId; // primary key from program table in admin db
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 public Set<DebitCredit> getMinPremiums() {
 return minPremiums;
 }
 public void setMinPremiums(Set<DebitCredit> minPremiums) {
 this.minPremiums = minPremiums;
 }
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 @OrderBy("lobCode ASC")
 public Set<OptionalCoverage> getOptionalCoverages() {
 return optionalCoverages;
 }
 public void setOptionalCoverages(Set<OptionalCoverage> optionalCoverages) {
 this.optionalCoverages = optionalCoverages;
 }
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 @OrderBy("lobCode ASC")
 public Set<Deductible> getDeductibles() {
 return deductibles;
 }
 public void setDeductibles(Set<Deductible> deductibles) {
 this.deductibles = deductibles;
 } 
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 @OrderBy("name ASC") 
 public Set<Territory> getTerritories() {
 return territories;
 }
 public void setTerritories(Set<Territory> territories) {
 this.territories = territories;
 }
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 @OrderBy("name ASC") 
 public Set<DebitCredit> getDebitCredits() {
 return debitCredits;
 }
 public void setDebitCredits(Set<DebitCredit> debitCredits) {
 this.debitCredits = debitCredits;
 }
 @ElementCollection
 @CollectionTable(name="Company", joinColumns=@JoinColumn(name="program_id"))
 @Column(name="name", length=255) 
 public Set<String> getCompanies() {
 return companies;
 }
 public void setCompanies(Set<String> companies) {
 this.companies = companies;
 }
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="program", fetch=FetchType.LAZY)
 @OrderBy("code ASC")
 public Set<Classification> getClassifications() {
 return classifications;
 }
 public void setClassifications(Set<Classification> classifications) {
 this.classifications = classifications;
 }
 public String getAdminProgramId() {
 return adminProgramId;
 }
 public void setAdminProgramId(String adminProgramId) {
 this.adminProgramId = adminProgramId;
 }
 @Column(length=255)
 public String getProgramVersion() {
 return programVersion;
 }
 public void setProgramVersion(String programVersion) {
 this.programVersion = programVersion;
 }
 public Boolean getDiscontinued() {
 return discontinued;
 }
 public void setDiscontinued(Boolean discontinued) {
 this.discontinued = discontinued;
 }
 public Boolean getActive() {
 return active;
 }
 public void setActive(Boolean active) {
 this.active = active;
 }
 public Boolean getDeletable() {
 return deletable;
 }
 public void setDeletable(Boolean deletable) {
 this.deletable = deletable;
 }
 @Column(nullable = false) 
 @Temporal(TemporalType.DATE)
 @Index(name="Program_EffectiveDate_idx")
 public Date getEffectiveDate() {
 return effectiveDate;
 }
 public void setEffectiveDate(Date effectiveDate) {
 this.effectiveDate = effectiveDate;
 }
 @Temporal(TemporalType.DATE)
 public Date getExpirationDate() {
 return expirationDate;
 }
 public void setExpirationDate(Date expirationDate) {
 this.expirationDate = expirationDate;
 } 
 @Column(nullable = false, length=255)
 @Index(name="Program_Code_idx")
 public String getCode() {
 return code;
 }
 public void setCode(String code) {
 this.code = code;
 }
 @Column(length=255)
 public String getDesciption() {
 return desciption;
 }
 public void setDesciption(String desciption) {
 this.desciption = desciption;
 }
 @Column(length=255)
 public String getName() {
 return name;
 }
 public void setName(String name) {
 this.name = name;
 }
 public boolean equals(Object obj) {
 if((obj != null) && (obj.getClass() == this.getClass())) {
 Long objId = ((Program)obj).getId();
 if( objId != null && objId.equals(this.getId()) ){
 return true;
 }
 } 
 return false;
 }
}

Here is classification:

package com.sg.pds.domain.entity.ratedelivery;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.CollectionTable;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.JoinColumn;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlElementWrapper;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
import javax.xml.bind.annotation.XmlType;
import org.hibernate.annotations.Index;
@XmlAccessorType(XmlAccessType.FIELD)
@Entity
public class Classification extends BaseEntity{
 //@XmlElement(name="code")
 private String code;
 private String description;
 @XmlElement(name="liabilityLimit") @XmlElementWrapper(name = "liabilityLimits")
 private Set<LiabilityLimit> liabilityLimits = new HashSet<LiabilityLimit>();
 @XmlTransient
 private Program program;
 private String lobName;
 private String lobCode;
 private String premiumBaseValue;
 private String premiumBaseDesc;
 private String premiumBaseCode;
 private String causeOfLoss;
 @XmlElement(name="coverage") @XmlElementWrapper(name = "coverages")
 private Set<String> coverages = new HashSet<String>();
 //@XmlTransient
 //private Set<Rate> rates = new HashSet<Rate>();
 //@XmlTransient
 //private Set<Criterion> criteria = new HashSet<Criterion>();
 @XmlElement(name="territory") @XmlElementWrapper(name = "territories")
 private Set<Territory> territories = new HashSet<Territory>();
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="classification")
 public Set<Territory> getTerritories() {
 return territories;
 }
 public void setTerritories(Set<Territory> territories) {
 this.territories = territories;
 }
 //@OneToMany(mappedBy="classification")
 //public Set<Criterion> getCriteria() {
 // return criteria;
 //}
 //public void setCriteria(Set<Criterion> criteria) {
 // this.criteria = criteria;
 //}
 //@OneToMany(mappedBy="classification")
 //public Set<Rate> getRates() {
 // return rates;
 //}
 //public void setRates(Set<Rate> rates) {
 // this.rates = rates;
 //}
 @Column(length=255)
 public String getPremiumBaseValue() {
 return premiumBaseValue;
 }
 public void setPremiumBaseValue(String premiumBaseValue) {
 this.premiumBaseValue = premiumBaseValue;
 }
 @Column(length=255)
 public String getPremiumBaseDesc() {
 return premiumBaseDesc;
 }
 public void setPremiumBaseDesc(String premiumBaseDesc) {
 this.premiumBaseDesc = premiumBaseDesc;
 }
 @Column(length=255)
 public String getPremiumBaseCode() {
 return premiumBaseCode;
 }
 public void setPremiumBaseCode(String premiumBaseCode) {
 this.premiumBaseCode = premiumBaseCode;
 }
 @Column(length=255)
 public String getCauseOfLoss() {
 return causeOfLoss;
 }
 public void setCauseOfLoss(String causeOfLoss) {
 this.causeOfLoss = causeOfLoss;
 }
 @ElementCollection
 @CollectionTable(name="Coverage", joinColumns=@JoinColumn(name="classification_id"))
 @Column(name="name", length=255)
 public Set<String> getCoverages() {
 return coverages;
 }
 public void setCoverages(Set<String> coverages) {
 this.coverages = coverages;
 }
 @ManyToOne
 public Program getProgram() {
 return program;
 }
 public void setProgram(Program program) {
 this.program = program;
 }
 @OneToMany(cascade = {CascadeType.ALL}, mappedBy="classification")
 @OrderBy("type ASC")
 public Set<LiabilityLimit> getLiabilityLimits() {
 return liabilityLimits;
 }
 public void setLiabilityLimits(Set<LiabilityLimit> liabilityLimits) {
 this.liabilityLimits = liabilityLimits;
 }
 @Column(length=255)
 @Index(name="Classification_Code_idx")
 public String getCode() {
 return code;
 }
 public void setCode(String code) {
 this.code = code;
 }
 @Column(length=255)
 public String getDescription() {
 return description;
 }
 public void setDescription(String description) {
 this.description = description;
 }
 public String getLobName() {
 return lobName;
 }
 public void setLobName(String lobName) {
 this.lobName = lobName;
 }
 public String getLobCode() {
 return lobCode;
 }
 public void setLobCode(String lobCode) {
 this.lobCode = lobCode;
 }
 public boolean equals(Object obj) {
 if((obj != null) && (obj.getClass() == this.getClass())) {
 Long objId = ((Classification)obj).getId();
 if( objId != null && objId.equals(this.getId()) ){
 return true;
 }
 } 
 return false;
 } 
}

Here is my session factory config:

<bean id="sessionFactoryTester" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
 <property name="dataSource" ref="dataSourceLoader" />
 <property name="packagesToScan" value="com.sg.pds.domain.entity" />
 <property name="hibernateProperties">
 <props>
 <prop key="hibernate.dialect">${hibernate.dialect}</prop>
 <prop key="hibernate.show_sql">true</prop>
 <prop key="hibernate.format_sql">true</prop>
 <prop key="hibernate.jdbc.fetch_size">0</prop>
 <!-- Enable Hibernate's automatic session context management -->
 <prop key="current_session_context_class">jta</prop>
 <!-- 
 <prop key="hibernate.hbm2ddl.auto">validate</prop> 
 -->
 </props>
 </property>
 </bean>

• JB Nizet: Which version of Hibernate are you using, and what's the code and mapping of those two entities?

Answer 1

This is because JPA defaults to eager loading for a ManyToOne association. Setting fetch to lazy should remove the extra join.

Answer 2

Perhaps this is due to this bug. You may be able to work around the bug with custom SQL for loading, or some other solution.