具有额外左外连接的 hibernate 条件查询

Question

我有两个对象，程序和分类，具有一个程序与多个分类的关系。这是我的条件查询:

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();

它生成的 SQL 有一个额外的左外连接，我无法理解其目的？

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=?

有谁知道为什么这个 block 在那里:

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

我怎样才能摆脱它，因为额外的连接会影响性能。

我使用的是hibernate 3.6.8

这是程序类:

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;
    }

}

分类如下:

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;
    }   

}

这是我的 session 工厂配置:

<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>

Answer 1

这是因为 JPA 默认为 ManyToOne 关联预先加载。将 fetch 设置为惰性应该会删除额外的连接。