Hibernate多表查询实践总结

 

Hibernate多表查询实践总结（2006-11-24）

实在无聊，前段时间做了个对单表的ZCGC(增删改查)，练了练手，虽然做过，但还是碰到了不少问题，让自己对struts+hibernate模式开发更加熟练了点。今天，我做多表的复习的时候，又碰到问题了，现在将关键点记录下来，以免日后再有相同问题出现，以便查阅。

 

我做的两张表：一张department表，一张employee表，建表语句如下：

drop table employee

create table EMPLOYEE

(

 ID       NUMBER(10) not null,

  parentID   number(10),

 NAME     VARCHAR2(20),

 AGE      NUMBER(3),

 PASSWARD VARCHAR2(12),

 CSRQ     DATE,

 PICTURE varchar(12),

 primary key(id),

 foreign key(parentID) references department(id)

)

 

drop table department

create table department

(

 id number(10) not null,

 dep_id varchar2(20),

 dep_mc varchar2(20),

 primary key(id)

)

其中，employee表中的parentID是department表的外键，department和employee表是一对多的关系，反过来是多对一的关系。怎么叫都可以。

Department.hbm.xml代码如下：

<hibernate-mapping>

    <class name="com.dudeng.employee.hibernate.vo.Department"

       table="DEPARTMENT" schema="DUDENG">

       <id name="id" type="java.lang.Long">

           <column name="ID" precision="10" scale="0" />

           <generator class="sequence">

              <param name="sequence">seq_department</param>

           </generator>

       </id>

       <property name="dep_id" type="java.lang.String">

           <column name="DEP_ID" length="20" not-null="false" />

       </property>

       <property name="dep_mc" type="java.lang.String">

           <column name="DEP_MC" length="20" not-null="false" />

       </property>

       <set name="employees" inverse="true">        

<key>

              <column name="parentID" precision="5" scale="0"

                  not-null="true" />

           </key>

           <one-to-many

              class="com.dudeng.employee.hibernate.vo.Employee" />

       </set>

    </class>

</hibernate-mapping>

 

Employee.hbm.xml的代码如下：

<hibernate-mapping>

    <class name="com.dudeng.employee.hibernate.vo.Employee"

       table="EMPLOYEE" schema="DUDENG">

       <id name="id" type="java.lang.Long">

           <column name="ID" precision="10" scale="0" />

           <generator class="sequence">

              <param name="sequence">seq_employee</param>

           </generator>

       </id>

<many-to-one name="department" class="com.dudeng.employee.hibernate.vo.Department">

                <column name="parentID" precision="5" scale="0" not-null="true" />

        </many-to-one>

       <property name="name" type="java.lang.String">

           <column name="NAME" length="20" not-null="false" />

        </property>

       <property name="age" type="java.lang.Long">

         <column name="AGE" precision="3" scale="0" not-null="false" />

       </property>

       <property name="passward" type="java.lang.String">

           <column name="PASSWARD" length="12" not-null="false" />

        </property>

       <property name="csrq" type="java.util.Date">

           <column name="CSRQ" length="7" not-null="false" />

       </property>

       <property name="picture" type="java.lang.String">

           <column name="PICTURE" length="20" not-null="false"/>

       </property>

    </class>

</hibernate-mapping>

 

 

查询结果集合：

List list = null;

 

String QueryStr = "from Employee emp,Department dep where emp.department = dep.id";

 

EmployeeBiz empbiz = EmployeeBiz.getInstance();

 

list = empbiz.find(QueryStr);

 

for (int i = 0; i < list.size(); i++)

{

    Object[] obj = (Object[]) list.get(i);

    for (int j = 0; j < obj.length; j++)

    {

       if (obj[j] instanceof Employee)

       {

           Employee emp = (Employee) obj[j];

           System.out.print(emp.getName());

       }

 elseif (obj[j] instanceof Department)

       {

           Department dep = (Department) obj[j];

           System.out.print(dep.getDep_mc());

       }

    }

    System.out.println();

}

 

以上特别注意的地方：emp.department不能写成emp.parentID，我上网查了半天才找出原因的。开始写成emp.parentID了，后来改成emp.department程序运行正常了。

 

 

 

最后在页面上显示：

    <logic:present name="employees" scope="request">

          <logic:iterate id="objs" name="employees" indexId="number">

                  <tr>

                     <td>

                         &nbsp;

                         ${objs[0].name}

                     </td>

                     <td>

                         &nbsp;

                         ${objs[1].dep_mc}

                     </td>     

                  </tr>

              </logic:iterate>

           </logic:present>