Hibernate的多表查询

    经常看到有人发帖寻求Hibernate多表联合查询的问题，又是inner join，又是left,又是right。我比较死板，看到这些头很大。我觉得，又了Hibernate的映射文件，根本就没必要搞联合查询，至少不用写那么累赘的SQL语句。无可否认，SQL语句已经在我们的脑海中根深蒂固。

    现在做的项目，经常要用到多表查询，而且用到的表，一般不是三个，有的时候需要更多，但是，用了映射文件的OneToMany，其实查询也可以很简单！

例子一：

TABLE 1：IMPLANTBT，记录病人植管信息

TABLE 2：FOLLOWUP_PERFORM_BT，记录病人随访结果

TABLE 3: HER_MT，记录病人的电子病历信息

他们的映射文件片段分别如下：

/* ImPlantBt.hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class
         name ="ImplantBt"
        table ="IMPLANT_BT"
     >
         < id
             name ="id"
            type ="integer"
            column ="IMPLANT_ID"
         >
             < generator  class ="sequence" >< param  name ="sequence" > S_implant_bt </ param ></ generator >
         </ id >

         < property
             name ="implantDate"
            column ="IMPLANT_DATE"
            type ="date"
            not-null ="false"
            length ="7"
         />
         < many-to-one
             name ="followupPerform"
            column ="FOLLOWUP_PERFORM_ID"
            class ="FollowupPerfInfoBt"
            not-null ="false"
         >
         </ many-to-one >


     </ class >     
</ hibernate-mapping >

/*FollowupPerformBt.hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class  name ="FollowupPerfInfoBt"  table ="FOLLOWUP_PERF_INFO_BT" >
         < id  name ="id"  type ="integer"  column ="FOLLOWUP_PERFORM_ID" >
             < generator  class ="sequence" >
                 < param  name ="sequence" > S_followup_perf_info_bt </ param >
             </ generator >
         </ id >

         < many-to-one  name ="ehr"  column ="EHR_ID"  class ="EhrMt"  not-null ="false" ></ many-to-one >


         < set  inverse ="true"  lazy ="true"  name ="implant" >
             < key  column ="FOLLOWUP_PERFORM_ID"   />
             < one-to-many  class ="ImplantBt"   />
         </ set >

     </ class >
</ hibernate-mapping >

 

/*EhrMt.hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class  name ="EhrMt"  table ="EHR_MT"  dynamic-update ="true"  dynamic-insert ="true" >
         < id  name ="id"  type ="integer"  column ="EHR_ID" >
             < generator  class ="sequence" >
                 < param  name ="sequence" > S_ehr_mt </ param >
             </ generator >
         </ id >

        
    
     </ class >
</ hibernate-mapping >

[任务]根据电子病历ID查询植管记录中的植管时间ImplantBt .implantDate

ImplantBt映射文件中有followupPerform

他实际上对应于一条随访结果记录，因此，用Implant.followupPerform就可以得到相应的随访结果记录，同理，Implant.followupPerform.ehr.id则得到了与Implant中记录对应的电子病历的ID号，因此，查询代码可以这样写：

try   ... {
            Query q = s
                    .createQuery("select a.implantDate from  ImplantBt  a where a.followupPerform.ehr.id=:ehrid");
            q.setInteger("ehrid",ehrid);
            d=(Date)q.uniqueResult();
            System.err.println("    d=   "+d);
            return d;
        }   catch  (Exception e)  ... {
            e.printStackTrace();
        }   finally   ... {
            DAOHelper.closeSession(s);
        }

 

再看下面例子！

VIEW 1：VEMBER

TABLE 1：EHR_MT

TABLE 2: EMP_INFO1_ST

对应的映射文件片段如下：

/* VMember,hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class  name ="VMember"  table ="V_MEMBER" >
         < id  name ="id"  type ="integer"  column ="MEMBER_INFO1_ID" >
             < generator  class ="sequence" >
                 < param  name ="sequence" > S_member_info1_st </ param >
             </ generator >
         </ id >
         < many-to-one  name ="primaryNurse"  column ="PRIMARY_NURSE_ID"  class ="EmpInfo1St"  not-null ="false"  fetch ="join" ></ many-to-one >
         < many-to-one  name ="ehr"  column ="EHR_ID"  class ="EhrMt"  not-null ="false"  fetch ="join" ></ many-to-one >

     </ class >
</ hibernate-mapping >

/* EmpInfo1St.hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class  name ="EmpInfo1St"  table ="EMP_INFO1_ST" >
         < id  name ="id"  type ="integer"  column ="EMP_INFO1_ID" >
             < generator  class ="sequence" >
                 < param  name ="sequence" > S_emp_info1_st </ param >
             </ generator >
         </ id >
         < property  name ="name"  column ="NAME"  type ="string"  not-null ="true"  length ="10"   />
        

         < set  name ="memberInfo1StsByprimaryNurse"  inverse ="true"  lazy ="true" >
             < key  column ="EMP_INFO1_ID"   />
             < one-to-many  class ="MemberInfo1St"   />
         </ set >
     </ class >
</ hibernate-mapping >

 

/* EhrMt.hbm.xml */
< hibernate-mapping  package ="com.cdms.dataaccess.model" >
     < class  name ="EhrMt"  table ="EHR_MT"  dynamic-update ="true"  dynamic-insert ="true" >
         < id  name ="id"  type ="integer"  column ="EHR_ID" >
             < generator  class ="sequence" >
                 < param  name ="sequence" > S_ehr_mt </ param >
             </ generator >
         </ id >
     </ class >
</ hibernate-mapping >

  [任务]根据电子病历ID查询病人的责任护士名字。

首先，责任护士名字在表EMP_INFO1_ST中，电子病历ID在表EHR_MT中。用视图VMEMBER把他们联系起来！

查询代码如下：

try   ... {
            System.err.println("ehrid="+ehrid);
            Query q = s
            .createQuery("select a.primaryNurse.name  from  VMember  a where a.ehr.id=:ehrid");
    q.setInteger("ehrid",ehrid);
            d=(String)q.uniqueResult();
            System.err.println("    d=   "+d);
            return d;
        }   catch  (Exception e)  ... {
            e.printStackTrace();
        }   finally   ... {
            DAOHelper.closeSession(s);
        }