Hibernate 多表关联查询示例

Hibernate HSQL多表关联查询示例：
TSettlementPaymentRec,TIsvSettlement,TC001CorpMessage三个java Bean映射于T_Isv_Settlement，T_Isv_Settlement_Attach , T_C001_Corp_Messag表

其中T_Isv_Settlement，T_Isv_Settlement_Attach , T_C001_Corp_Messag 已经有主外键关联关系，所以在where条件中用
‘ AND t.TIsvSettlement=t2 AND t2.TC001CorpMessage=t3’表示关联关系。

testHSQL(){
        String HSQL="  select t.voucherno, t2.memo, t3.name
          FROM TSettlementPaymentRec t , TIsvSettlement t2 , TC001CorpMessage t3 " +
        " WHERE t2.settlementid = 'S2011042000066' AND t.TIsvSettlement=t2 AND t2.TC001CorpMessage=t3";
        List ltest=this.findByHSQL(HSQL);
}
        /**
         * 根据HQL查询字符串来返回实例集合对象
         *
         * @param querySql HQL查询字符串
         *           
         * @return 返回object
         */
        public List findByHSQL(final String querySql) {
            try {
                List obj = getHibernateTemplate().find(querySql);

                if (obj != null) {
                    return obj;
                } else {
                    return null;
                }
            } catch (RuntimeException re) {
                throw re;
            }
        }
               
=========================================================================
各文件属性定义（部分）       
TSettlementPaymentRec.java:
public class TSettlementPaymentRec implements java.io.Serializable {

    // Fields

    private String paymentid;
    private TIsvSettlement TIsvSettlement;
    private Double settlementnum;
    private Double amount;
    private String voucherno;
    private String paymenttype;
    private Date paymenttime;
    private String userid;
    private String source;
   
}

TC001CorpMessage.java:
public class TC001CorpMessage implements java.io.Serializable {

    // Fields

    private String userid;
    private String name;
    private String password;
    private String email;
    private String type;
    private String corpname;
    private String commaddress;
    private String commcall;
    private Integer zipcode;
    private String orgcode;
    private String registcode;
    private String registaddress;
    private String legalname;
    private String fax;
    private String contact;
    private String username;
    private String identity;
    private String mobile;
    private String industry;
    private BigDecimal integrity;
    private BigDecimal credit;
    private BigDecimal settlespan;
    private Date createtime;
}

TIsvSettlement.java
public class TIsvSettlement implements java.io.Serializable {

    // Fields

    private String settlementid;
    private TC001CorpMessage TC001CorpMessage;
    private Double settlementnum;
    private Double sum;
    private Date settlementTime;
    private String sendmState;
    private Date sendmTime;
    private String sendmId;
    private String receivemState;
    private Date receivemTime;
    private String recrivemId;
    private String state;
    private String memo;
    private Set TIsvSettlementAttachs = new HashSet(0);

}


TIsvSettlementAttach.java
public class TIsvSettlementAttach implements java.io.Serializable {

    // Fields

    private String id;
    private TIsvSettlement TIsvSettlement;
    private String isvproductno;
    private String usernum;
    private String amount;
    private String settleSum;
    private String sum;
    private Date createtime;
    private String isvuserid;
    private String productid;
    private String unit;
    private Double price;
    private Long buytimes;
    private Double amount2;
    private Date begintime;
    private Date disabletime;
    private String userid;
    private String username;

===========================================================================================================================================================


例子2：
        String HSQL="  select t.voucherno, t2.settleSum, t3.name  FROM TSettlementPaymentRec t , TIsvSettlementAttach t2 , TC001CorpMessage t3 " +
        " WHERE t2.TIsvSettlement.settlementid = 'S2011042000066' AND t.TIsvSettlement=t2.TIsvSettlement AND t2.isvuserid=t3.userid";
        List ltest=this.findByHSQL(HSQL);

取结果：
ArrayList sList = (ArrayList) result;
Iterator iterator1 = sList.iterator();
while (iterator1.hasNext()) {
　Object[] o = (Object[]) iterator1.next();
　tBookInfo bookInfo = (tBookInfo) o[0];
　BookSelection bookSelect = (BookSelection) o[1];
　System.out.println("BookInfo-Title: " + bookInfo.getTitle());
　System.out.println("BookSelection-BookSelectionId: " + bookSelect.getId());
}

例子3：
        String HSQL="  select  t2.settleSum, t3.name,t4.name  FROM  TIsvSettlementAttach t2 , TC001CorpMessage t3,TCusProduct t4 " +
        " WHERE t2.id like '405' AND t2.isvuserid=t3.userid  AND t4.productid=t2.productid";
        List ltest=this.findByHSQL(HSQL);
       
        int i=0;
       
       
例子4,去重复记录：
        String HSQL="  select  distinct t2.settleSum, t3.name,t4.name  FROM  TIsvSettlementAttach t2 , TC001CorpMessage t3,TCusProduct t4 " +
        " WHERE  t2.isvuserid=t3.userid  AND t4.productid=t2.productid";
        List ltest=this.findByHSQL(HSQL);
       

       
例子5,LIKe查询：
            String HSQL="  select  t2.settleSum, t3.name,t4.name  FROM  TIsvSettlementAttach t2 , TC001CorpMessage t3,TCusProduct t4 " +
            " WHERE t3.name like '%三' AND t2.isvuserid=t3.userid  AND t4.productid=t2.productid";


==================================================================================================================


Hibernate 对左右连接查询的支持不完善，需要做配置XML文件和关联关系才行，比较复杂，对于任意表字段的外连接无法支持。


简单的 做法是用SQL语法把外连接翻译成内连接语法，
Oracle SQL1：
 select   t3.userid,t2.isvuserid,t2.settle_Sum, t3.name from T_Isv_Settlement_Attach t2 , T_C001_Corp_Message t3  where t2.isvuserid(+)=t3.userid;
 
等于SQL2：
select null as isvuserid,null as settle_Sum ,t3.userid, t3.name
from  T_C001_Corp_Message t3 where t3.userid not in (select  t2.isvuserid from T_Isv_Settlement_Attach t2 , T_C001_Corp_Message t3  where t2.isvuserid=t3.userid)
union all
select  t2.isvuserid,t2.settle_Sum, t3.userid,t3.name from T_Isv_Settlement_Attach t2 , T_C001_Corp_Message t3  where t2.isvuserid=t3.userid;


Hibernate 对 Union不支持。

所以SQL2还必须用两条SQL来做，然后把2个结果集用LIST对象合并list1.addAll(),结果集出来后，还会有排序问题，
就必须自己实现List类中的排序，可以参考这个函数的做法，实现排序类：


import java.util.*;

public class testClass2 {

    /**
     * @param args
     */
    public static void main(String[] args) {
        TIsvSettlementAttach tisv1 = new TIsvSettlementAttach();
        tisv1.setId("333");
        TIsvSettlementAttach tisv2 = new TIsvSettlementAttach();
        tisv2.setId("222");

        TIsvSettlementAttach tisv3 = new TIsvSettlementAttach();
        tisv3.setId("111");

        List<TIsvSettlementAttach> list = new ArrayList<TIsvSettlementAttach>();
        list.add(tisv1);
        list.add(tisv2);
        list.add(tisv3);

        System.out.println("排序前的list：");
        System.out.println(list);

       
        Collections.sort(list, new Comparator<TIsvSettlementAttach>() {

            public int compare(TIsvSettlementAttach o1, TIsvSettlementAttach o2) {
                // 取出操作时间
                int ret = 0;
                try {
                    ret = (o1.getId()).compareTo(
                            o2.getId());
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
                return ret;
            }

        });

        System.out.println("排序后的list：");
        System.out.println(list);
    }

}