How to Write Left Outer Join

[dinesh thalis]

Below show my query, In here main table is "Account" , Joining table is "Customer". How can I write left outer join.

   

        Query query = em.createQuery("SELECT a.brCode.brName, a.pBNumber, a.cusId.cusID, a.owner, a.altCusId, "
        +"CONCAT(a.cusId.title,' ',a.cusId.initial,' ',a.cusId.lName), CONCAT(cu.title,' ',cu.initial,' ',cu.lName),"
        +"p.description, p.rate, a.trnAcc, b.brName, a.doIntEff, a.acrInt, a.balance, a.holdAmt, a.otherChg, "
        +"a.doLastTrn, a.status, a.trnChd, a.certificateNo, a.ledger, a.doOpen, a.intRate, a.rollOver, a.matDate, "
        +"a.taxCode FROM Account a, Customer cu , Product p, Branch b WHERE a.accNo = :accNo AND a.chd = :chd AND "
        +"p.productPK.appType = '2' AND p.productPK.proType = a.proType AND a.altCusId = cu.cusID AND"
        +"b.brCode = a.trnBrCode ");
 

Account Entity

   
public class Account implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "AccNo")
    private String accNo;
    @Basic(optional = false)
    @Column(name = "Chd")
    private char chd;
    @Basic(optional = false)
    @Column(name = "AppType")
    private char appType;
    @Basic(optional = false)
    @Column(name = "ProType")
    private String proType;
    @Basic(optional = false)
    @Column(name = "GlCode")
    private String glCode;//default value
    @Basic(optional = false)
    @Column(name = "GlOrig")
    private String glOrig;//default value
    @Column(name = "AltCusId")
    private String altCusId;
    @Basic(optional = false)
    @Column(name = "Owner")
    private char owner; // Single,AND/OR
    @Basic(optional = false)
    @Column(name = "AccName")
    private String accName;// defa
    @Basic(optional = false)
    @Column(name = "Status")
    private String status; // defa
    @Basic(optional = false)
    @Column(name = "DoStatus")
    @Temporal(TemporalType.DATE)
    private Date doStatus;
    @Basic(optional = false)
    @Column(name = "DoOpen")
    @Temporal(TemporalType.DATE)
    private Date doOpen;
    @Column(name = "PrevTrn")
    private String prevTrn;
    @Column(name = "DoLastTrn")
    @Temporal(TemporalType.DATE)
    private Date doLastTrn;
    @Column(name = "DoLastCustTrn")
    @Temporal(TemporalType.DATE)
    private Date doLastCustTrn;
    @Basic(optional = false)
    @Column(name = "Seq")
    private short seq;
    @Basic(optional = false)
    @Column(name = "Balance")
    private long balance;
    @Basic(optional = false)
    @Column(name = "HoldAmt")
    private long holdAmt;
    @Column(name = "MinBalQ1")
    private BigInteger minBalQ1;
    @Column(name = "MinBalQ2")
    private BigInteger minBalQ2;
    @Column(name = "MinBalQ3")
    private BigInteger minBalQ3;
    @Column(name = "MinBalQ4")
    private BigInteger minBalQ4;
    @Basic(optional = false)
    @Column(name = "IntRate")
    private int intRate;
    @Basic(optional = false)
    @Column(name = "MinBal")
    private long minBal;
    @Basic(optional = false)
    @Column(name = "AcrInt")
    private long acrInt;
    @Basic(optional = false)
    @Column(name = "DoIntEff")
    @Temporal(TemporalType.DATE)
    private Date doIntEff;
    @Basic(optional = false)
    @Column(name = "IntBal")
    private long intBal;
    @Basic(optional = false)
    @Column(name = "TaxCode")
    private String taxCode;
    @Column(name = "CertificateNo")
    private String certificateNo;
    @Basic(optional = false)
    @Column(name = "MatDate")
    @Temporal(TemporalType.DATE)
    private Date matDate;
    @Basic(optional = false)
    @Column(name = "MaxBal")
    private long maxBal;
    @Basic(optional = false)
    @Column(name = "AvgBal")
    private long avgBal;
    @Column(name = "RollOver")
    private String rollOver;
    @Column(name = "DoNextAct")
    @Temporal(TemporalType.DATE)
    private Date doNextAct;
    @Column(name = "TrnAcc")
    private String trnAcc;
    @Column(name = "TrnChd")
    private Character trnChd;
    @Column(name = "TrnBrCode")
    private String trnBrCode;
    @Basic(optional = false)
    @Column(name = "PBNumber")
    private String pBNumber;
    @Column(name = "PBBal")
    private BigInteger pBBal;
    @Column(name = "PBLine")
    private Short pBLine;
    @Column(name = "Ledger")
    private Short ledger;
    @Basic(optional = false)
    @Column(name = "OtherChg")
    private long otherChg;
    @Basic(optional = false)
    @Column(name = "ComDate")
    @Temporal(TemporalType.DATE)
    private Date comDate;
    @Basic(optional = false)
    @Column(name = "OtherInfo")
    private String otherInfo;
    @Basic(optional = false)
    @Column(name = "Hash")
    private String hash;
    @JoinColumn(name = "BrCode", referencedColumnName = "BrCode")
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    private Branch brCode;
    @JoinColumn(name = "CusId", referencedColumnName = "CusID")
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    private Customer cusId;
 

Customer Entity

  public class Customer implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "CusID")
    private String cusID;
    @Basic(optional = false)
    @Column(name = "CusType")
    private char cusType;
    @Basic(optional = false)
    @Column(name = "Title")
    private String title;
    @Basic(optional = false)
    @Column(name = "Initial")
    private String initial;
    @Basic(optional = false)
    @Column(name = "LName")
    private String lName;
    @Basic(optional = false)
    @Column(name = "OName")
    private String oName;
    @Column(name = "NIC")
    private String nic;
    @Column(name = "AltIDType")
    private Character altIDType;
    @Column(name = "AltID")
    private String altID;
    @Basic(optional = false)
    @Column(name = "DOB")
    @Temporal(TemporalType.DATE)
    private Date dob;
    @Basic(optional = false)
    @Column(name = "Sex")
    private char sex;
    @Basic(optional = false)
    @Column(name = "AddressFlag")
    private char addressFlag;
    @Basic(optional = false)
    @Column(name = "Address1")
    private String address1;
    @Basic(optional = false)
    @Column(name = "Address2")
    private String address2;
    @Basic(optional = false)
    @Column(name = "City")
    private String city;
    @Column(name = "TelHome")
    private String telHome;
    @Column(name = "Mobile")
    private String mobile;
    @Column(name = "OfficeNo")
    private String officeNo;
    @Column(name = "FaxNo")
    private String faxNo;
    @Column(name = "Email")
    private String email;
    @Column(name = "TaxCode")
    private String taxCode;
    @Basic(optional = false)
    @Column(name = "DOpen")
    @Temporal(TemporalType.DATE)
    private Date dOpen;
    @Basic(optional = false)
    @Column(name = "Status")
    private char status;
    @Column(name = "OldCusID")
    private String oldCusID;
    @Basic(optional = false)
    @Column(name = "Hash")
    private String hash;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "cusId", fetch = FetchType.LAZY)
    private List<Account> accountList;
    @OneToOne(cascade = CascadeType.ALL, mappedBy = "customer", fetch = FetchType.EAGER)
    private CustomerDetail customerDetail;
    @JoinColumn(name = "BrCode", referencedColumnName = "BrCode")
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    private Branch brCode;
  

Please How can I resolve it.
Thank You.

[Mark Spritzler]

Well there are two issues. 1) you have a projection query. which means you get an Object[] back or a List<Object[]> depending on the number of rows.

2) In your From clause you use the JOIN clause. so

FROM Customer c JOIN c.address a

To join a Customer with an Address. By default it will use a Left Outer Join.

Mark

[dinesh thalis]

Thanks for reply, so I want to create outer join but that table(secondary) haven't relation with main table, In that case how can I create it.

[Mark Spritzler]

Do you guys have a standard where you have to put all those annotations in your class, because for all those properties of your class that are the same name as the corresponding database field name doesn't need any Annotation. The smart defaults are all set to match what you have as those Annotations.

So you don't have an assocation mapping between those two class, but still want a left outer join. I would try two things

First, try just adding a "(+)" on the optional side, just like you would in SQL.

If that doesn't work, use SQL instead and use the "(+)" on the optional side.

Mark