Hibernate HQL Query with outer join

问题:

I would like to ask how to change the OUTER from Informix to HQL query. Currently facing error with unexpected token:outer

SQL query:

select a.SC_OB_PROFILE_CODE, b.SC_ORIG_SF_GROUP_CODE, f.SC_ORIG_SAC, 
f.SC_ORIG_FAC,b.SC_PROD_CONT_GROUP_CODE, d.SC_PROD_CONT_CODE, e.GP_CD, a.SC_ORIG_COUNTRY,
a.SC_DEST_COUNTRY, a.SC_DEST_SAC, a.SC_DEST_FAC, a.SC_SEND_DOW,
a.SC_OB_SORT_CODE, a.ORIG_OB_SORT_CODE, a.TARGET_OB_SORT_CODE, a.SC_TIMESTAMP
from SC_OB_TEMP_AUDIT2 a, SC_OB_ALLOCATION b, outer SC_FAC_GROUP f,
outer (SC_OB_PROD_GROUP d, GBL_PRODUCT e)
where a.SC_ORIG_COUNTRY = 'MY'
and a.EXPORT_FLAG = 'N'
and a.SC_OB_PROFILE_CODE = b.SC_OB_PROFILE_CODE
and a.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
and f.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
and b.SC_ORIG_SF_GROUP_CODE = f.SC_FAC_GROUP_CODE
and d.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
and b.SC_PROD_CONT_GROUP_CODE = d.SC_PROD_GROUP_CODE
and e.GP_CNT_CD = d.SC_PROD_CONT_CODE
order by a.SC_TIMESTAMP, a.SC_OB_PROFILE_CODE, a.SC_DEST_COUNTRY

HQL query:

@SuppressWarnings("unchecked")
public List<LoginCountryEBean> getExportDetails(String country){
List<LoginCountryEBean> expDel = null;
Query q= em1.createQuery("select a.obProfileCode, b.sfGroupCode, f.orgSac," +
"f.sc_orig_fac,b.contGroupCode, d.prodContCode, e.gpCd, a.origCountry," +
"a.destCountry, a.destSac, a.destFac, a.sendDow, a.obSortCode, " +
"a.origObSortCode, a.targetObSortCode, a.timeStamp from AuditBean a, " +
"AllocationEBean b, **outer** FacGroupEBean f,**outer**(ProdGroupEBean d, glbProductEBean e)" +
"where a.origCountry :country and a.exportFlag = 'N' " +
"and a.obProfileCode = b.profileCode" +
"and a.origCountry = b.orgCountry" +
"and f.orgCountry = b.orgCountry" +
"and b.sfGroupCode = f.facGroupCode" +
"and d.orgCountry = b.orgCountry" +
"and b.contGroupCode = d.prodGroupCode" +
"and e.gpCntCd = d.prodContCode" +
"order by a.timeStamp, a.obProfileCode, a.destCountry");
q.setParameter("country", country);
expDel =  q.getResultList();    
return expDel; 

//
AllocationEBean
package com.dhl.gls.persistence.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="SC_OB_ALLOCATION")
public class AllocationEBean implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="sc_orig_country")
    private String orgCountry;

    @Id
    @Column(name="sc_orig_sf_group_code")
    private String sfGroupCode;

    @Id
    @Column(name="sc_prod_cont_group_code")
    private String contGroupCode;

    @Id
    @Column(name="sc_ob_profile_code")
    private String profileCode;

    public String getOrgCountry() {
        return orgCountry;
    }

    public void setOrgCountry(String orgCountry) {
        this.orgCountry = orgCountry;
    }

    public String getSfGroupCode() {
        return sfGroupCode;
    }

    public void setSfGroupCode(String sfGroupCode) {
        this.sfGroupCode = sfGroupCode;
    }

    public String getContGroupCode() {
        return contGroupCode;
    }

    public void setContGroupCode(String contGroupCode) {
        this.contGroupCode = contGroupCode;
    }

    public String getProfileCode() {
        return profileCode;
    }

    public void setProfileCode(String profileCode) {
        this.profileCode = profileCode;
    }


}

//
AuditEBean
package com.dhl.gls.persistence.bean;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="sc_ob_temp_audit2")
public class AuditBean implements Serializable
{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="audit_Id")
    private long auditId;

    @Column(name="sc_ob_profile_code")
    private String obProfileCode = null;


    @Column(name="sc_orig_country")
    private String origCountry = null;


    @Column(name="sc_dest_country")
    private String destCountry = null;


    @Column(name="sc_dest_sac")
    private String destSac = null;


    @Column(name="sc_dest_fac")
    private String destFac = null;


    @Column(name="sc_send_dow")
    private String sendDow = null;


    @Column(name="sc_ob_sort_code")
    private String obSortCode = null;

    @Column(name="orig_ob_sort_code")
    private String origObSortCode = null;

    @Column(name="target_ob_sort_code")
    private String targetObSortCode = null;

    @Column(name="usr_id")
    private String userId = null;

    @Column(name="sc_timestamp")
    private Date timeStamp = null;

    @Column(name="export_flag")
    private String exportFlag = null;

    public String getObProfileCode() {
        return obProfileCode;
    }

    public String getOrigCountry() {
        return origCountry;
    }

    public String getDestCountry() {
        return destCountry;
    }

    public String getDestSac() {
        return destSac;
    }

    public String getDestFac() {
        return destFac;
    }

    public String getSendDow() {
        return sendDow;
    }

    public String getObSortCode() {
        return obSortCode;
    }

    public String getOrigObSortCode() {
        return origObSortCode;
    }

    public String getTargetObSortCode() {
        return targetObSortCode;
    }

    public Date getTimeStamp() {
        return timeStamp;
    }

    public String getExportFlag() {
        return exportFlag;
    }

    public void setObProfileCode(String obProfileCode) {
        this.obProfileCode = obProfileCode;
    }

    public void setOrigCountry(String origCountry) {
        this.origCountry = origCountry;
    }

    public void setDestCountry(String destCountry) {
        this.destCountry = destCountry;
    }

    public void setDestSac(String destSac) {
        this.destSac = destSac;
    }

    public void setDestFac(String destFac) {
        this.destFac = destFac;
    }

    public void setSendDow(String sendDow) {
        this.sendDow = sendDow;
    }

    public void setObSortCode(String obSortCode) {
        this.obSortCode = obSortCode;
    }

    public void setOrigObSortCode(String origObSortCode) {
        this.origObSortCode = origObSortCode;
    }

    public void setTargetObSortCode(String targetObSortCode) {
        this.targetObSortCode = targetObSortCode;
    }

    public void setTimeStamp(Date timeStamp) {
        this.timeStamp = timeStamp;
    }

    public void setExportFlag(String exportFlag) {
        this.exportFlag = exportFlag;
    }

    public long getAuditId() {
        return auditId;
    }

    public void setAuditId(long auditId) {
        this.auditId = auditId;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }
}

//
prodGroupEBean
package com.dhl.gls.persistence.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="SC_OB_PROD_GROUP")
public class ProdGroupEBean implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="sc_orig_country")
    private String orgCountry;

    @Id
    @Column(name="sc_prod_cont_code")
    private String prodContCode;

    @Id
    @Column(name="sc_prod_group_code")
    private String prodGroupCode;

    public String getOrgCountry() {
        return orgCountry;
    }

    public void setOrgCountry(String orgCountry) {
        this.orgCountry = orgCountry;
    }

    public String getProdContCode() {
        return prodContCode;
    }

    public void setProdContCode(String prodContCode) {
        this.prodContCode = prodContCode;
    }

    public String getProdGroupCode() {
        return prodGroupCode;
    }

    public void setProdGroupCode(String prodGroupCode) {
        this.prodGroupCode = prodGroupCode;
    }


}

//
FacGroupEBean
package com.dhl.gls.persistence.bean;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="SC_FAC_GROUP")
public class FacGroupEBean implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="sc_orig_country")
    private String orgCountry;

    @Id
    @Column(name="sc_orig_sac")
    private String orgSac;

    @Id
    @Column(name="sc_orig_fac")
    private String orgFac;

    @Id
    @Column(name="sc_fac_group_code")
    private String facGroupCode;

    public String getOrgCountry() {
        return orgCountry;
    }

    public void setOrgCountry(String orgCountry) {
        this.orgCountry = orgCountry;
    }

    public String getOrgSac() {
        return orgSac;
    }

    public void setOrgSac(String orgSac) {
        this.orgSac = orgSac;
    }

    public String getOrgFac() {
        return orgFac;
    }

    public void setOrgFac(String orgFac) {
        this.orgFac = orgFac;
    }

    public String getFacGroupCode() {
        return facGroupCode;
    }

    public void setFacGroupCode(String facGroupCode) {
        this.facGroupCode = facGroupCode;
    }   
}

//
glsproductionEBean
package com.dhl.gls.persistence.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="GBL_PRODUCT")
public class glbProductEBean implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="gp_cd")
    private String gpCd;

    @Column(name="gp_cnt_cd")
    private String gpCntCd;

    @Column(name="gp_sort_cd")
    private String gpSortCd;

    @Column(name="gp_short_nm")
    private String gp_short_nm;

    @Column(name="hf_lk")
    private Long hfkl;

    @Column(name="pasdt_cd")
    private String pasdtCd;

    @Column(name="psd_cd")
    private Short psdCd;

    @Column(name="gp_geo_markt")
    private String gpGeoMarkt;

    @Column(name="gp_doc_ndoc_ind")
    private String gpDocNdocInd;

    public String getGpCd() {
        return gpCd;
    }

    public void setGpCd(String gpCd) {
        this.gpCd = gpCd;
    }

    public String getGpCntCd() {
        return gpCntCd;
    }

    public void setGpCntCd(String gpCntCd) {
        this.gpCntCd = gpCntCd;
    }

    public String getGpSortCd() {
        return gpSortCd;
    }

    public void setGpSortCd(String gpSortCd) {
        this.gpSortCd = gpSortCd;
    }

    public String getGp_short_nm() {
        return gp_short_nm;
    }

    public void setGp_short_nm(String gp_short_nm) {
        this.gp_short_nm = gp_short_nm;
    }

    public Long getHfkl() {
        return hfkl;
    }

    public void setHfkl(Long hfkl) {
        this.hfkl = hfkl;
    }

    public String getPasdtCd() {
        return pasdtCd;
    }

    public void setPasdtCd(String pasdtCd) {
        this.pasdtCd = pasdtCd;
    }

    public Short getPsdCd() {
        return psdCd;
    }

    public void setPsdCd(Short psdCd) {
        this.psdCd = psdCd;
    }

    public String getGpGeoMarkt() {
        return gpGeoMarkt;
    }

    public void setGpGeoMarkt(String gpGeoMarkt) {
        this.gpGeoMarkt = gpGeoMarkt;
    }

    public String getGpDocNdocInd() {
        return gpDocNdocInd;
    }

    public void setGpDocNdocInd(String gpDocNdocInd) {
        this.gpDocNdocInd = gpDocNdocInd;
    }

}

回答1:

The syntax to make a join is

select ... from Entity1 e1
[inner | left [outer]] join e1.entity2s e2
[inner | left [outer]] join e2.entity3s e3
...

Read the documentation

回答2:

The trouble you're running into is that the (obsolescent) old-style Informix OUTER join notation is not understood by other systems (such as HQL). Here's a slightly simplified version of the original Informix query:

select a.*, b.*, d.*, e.*, f.*
  from SC_OB_TEMP_AUDIT2 a,
       SC_OB_ALLOCATION b,
       outer SC_FAC_GROUP f,
       outer (SC_OB_PROD_GROUP d, GBL_PRODUCT e)
 where a.SC_ORIG_COUNTRY = 'MY'
   and a.EXPORT_FLAG = 'N'
   and a.SC_OB_PROFILE_CODE = b.SC_OB_PROFILE_CODE
   and a.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
   and f.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
   and b.SC_ORIG_SF_GROUP_CODE = f.SC_FAC_GROUP_CODE
   and d.SC_ORIG_COUNTRY = b.SC_ORIG_COUNTRY
   and b.SC_PROD_CONT_GROUP_CODE = d.SC_PROD_GROUP_CODE
   and e.GP_CNT_CD = d.SC_PROD_CONT_CODE
 order by a.SC_TIMESTAMP, a.SC_OB_PROFILE_CODE, a.SC_DEST_COUNTRY;

Primarily, I've simplified the select-list by using a.* notation for selecting the columns from each aliased table name, and I've exposed the FROM clause contents a bit more clearly.

The tables (with aliases — but I'm going to pretend that the aliases are the table names) a and b are inner joined. That is outer joined to f; it is also independently outer joined to the result of d outer joined to e. To rewrite this so that HQL has a chance of understanding it, we're going to have to write something close to this:

SELECT a.*, b.*, d.*, ef.*
  FROM sc_ob_temp_audit2 AS a
  JOIN sc_ob_allocation  AS b
    ON a.sc_ob_profile_code = b.sc_ob_profile_code
   AND a.sc_orig_country    = b.sc_ob_orig_country
 OUTER JOIN sc_fac_group AS f
    ON b.sc_orig_country       = f.sc_orig_country
   AND b.sc_orig_sf_group_code = f.sc_fac_group_code
 OUTER JOIN (SELECT e.*, f.*
               FROM sc_ob_prod_group AS d
              OUTER JOIN gbl_produce AS e
                 ON d.sc_prod_cont_code = e. gp_cnt_cd
            ) AS ef.*
    ON b.sc_orig_country         = ef.sc_orig_country
   AND b.sc_prod_cont_group_code = ef.sc_prod_group_code
 WHERE a.sc_orig_country = 'MY'
   AND a.export_flag     = 'N'
 ORDER BY a.sc_timestamp, a.sc_ob_profile_code, a.sc_dest_country;

Apart from inverting the case of keywords vs database objects, this is a fairly direct translation of the original query. You will note that the outer joined sub-query is given the alias ef in this revision. This is necessary; the SQL standard mandates that such sub-queries are given an alias. You therefore may run into ambiguous column errors. You can probably fix those by by replacing the * notation in the sub-query with the exact set of column names you want to select. And, of course, you'll need to replace the * notation in the select-list with the set of columns that you want once more.