Hibernate多表联合查询解决办法[ZT]

Hibernate
是典型的OPM工具,它将每一个物理表格(Table)映射成为对象(Object),这发挥了面向对象的优势,使设计和开发人员可以从面向对象的角度来进行对数据库
的管理。

     在设计到多表操作时，Hibernate提供了与数据库表关系相对应的对象映射关系，一对一、一对多和多对多在这里都可以通过Hibernate的对象映 射关系（Set等）来实现。这为一般情况下的数据库多表操作提供了便捷途径。关于这方面的介绍已经很多，在这里不再复述。

但是，在有些情况下的多表操作，比如一个统计顾客在2005年的消费总金额的SQL操作如下：

sql 代码
select
b.name
, 
count
(a.fee) mix(a.chargeBeginTime) 
max
(a.chargeEndTime)
from
charge a, customer b
where
a.idCustomer = b.idCustomer and
 a.chargeBeginTime >= '2005-01-01'
and
 a.chargeEndTime < '2005-12-31' gourp 
by
 a.idCustomer



customer表和charge结构如下：

customer表结构：

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| IdCustomer | varchar(32) |      | PRI |         |       |

| Name       | varchar(30) |      |     |         |       |

+------------+-------------+------+-----+---------+-------+

charge表结构：

+-----------------+-------------+------+-----+---------+-------+

| Field           | Type        | Null | Key | Default | Extra |

+-----------------+-------------+------+-----+---------+-------+

| IdCharge        | varchar(32) |      | PRI |         |       |

| Fee             | double      | YES  |     | NULL    |       |

| ChargeTimeBegin | datetime    | YES  |     | NULL    |       |

| ChargeTimeEnd   | datetime    | YES  |     | NULL    |       |

+-----------------+-------------+------+-----+---------+-------+

在Hibernate的自带文档中有类似下面的多表查询操作提示：

sql 代码
select
 new OjbectC(field1, field2,...) 
from
 ObjectA a, ObjectB b ...

分析一下可以看出这个操作有两个缺点：

    1）必须声明并创建类ObjectC，根据Hibernate的特点，需要写一个ObjectC.hbm.XML
的PO映射，在只用到创建查询结果的新对象的时候，这个映射可以是个虚的，即可以没有一个真正的数据库表和ObjectC对应，但是这样的一个虚设的逻辑显然已经违背了Hibernate的思想初衷；

    2）这个办法只能查询出但条结果记录并只能创建单个的ObjectC对象，这是很局限的，因此在某些情况下根本不能使用（比如本例）。

    所以，对于本例，上面的方法是行不通的。

其实，仔细看看Hibernate的API，就会发现这个问题很好解决。在net.sf.hibernate包中有下面三个对我们很有用的接口：

1、Interface ScrollableResults

    这个接口类似JDBC
中的ResultSet一样，提供了对返回结果集合的遍历和字段访问方法，如：

    public boolean next
()    游标后移

    public boolean previous
() 游标前移

    public boolean scroll
(int i) 游标移动到指定未知

    public void beforeFirst
() 游标在首记录前

    public void afterLast
() 游标在末记录后

    public Object

[] get
() 将当前记录的字段值以Object对象数组形式返回

    public Object

 get
(int i) 将当前记录的特定字段值以Object对象形式返回

    public Integer

 getInteger
(int col) 将当前记录的特定字段值以Integer对象返回

    public Long

 getLong
(int col) 将当前记录的特定字段值以Long对象返回

    public String

 getText
(int col) 将当前记录的特定字段值以Text对象返回

    public String

 getString
(int col) 将当前记录的特定字段值以String对象返回

    ...等等

2、Interface Query

    Query接口封装了对数据库的查询等操作，在这里，我们使用到它的原因是在于它的scroll()方法可以返回一个ScrollableResults实例：

    public ScrollableResults

 scroll
() 将查询结果以ScrollableResults实例返回，但需要注意的是查询返回的结果其实只是一些id，当需要的时候（比如我们使用 ScrollableResults.next()方法后移游标时）这条需要用到的记录才会被真正初始化（这种技术可以称作：延时初始化）

3、Interface Session

    Session是Hibernate的核心中的核心，通过Session的createQuery()方法，我们能生成一个Query实例：

    public Query

 createQuery
(String

 queryString) 用给出的HQL查询串创建一个Query实例

好了，了解了上面的三个接口，问题就能够很好的解决了。需要如下几个文件：

Customer.Java
                            PO对象

Charge.java                                PO对象

TotalCharge.java                        用于保存统计结果Bean

Customer.hbm.xml                      PO映射

Charge.hbm.xml                          PO映射

TotalChargeDao.java                  统计Dao定义

TotalChargeDaoImpl.java           统计Dao定义实现

DaoFactory.java                         Dao工厂

HibernateSessionFactory.java     Session工厂

因为这里主要讨论的重点是对Customer和Charge的联合查询，所以Customer.java、Charge.java、 Customer.hbm.xml、Charge.hbm.xml四个文件以及TotalChargeDao.java、 DaoFactory.java、HibernateSessionFactory.java的源代码在这里省略掉。

TotalCharge.

java 代码
package
 test.bean;
/**
 *作者：孙星
 **/
public
 
class
 TotalCharge {
  private
 String name;
  private
 Double fee;
  private
 java.util.Date chargeTimeBegin;
  private
 java.util.Date chargeTimeEnd;
  public
 TotalCharge() {
  }
  public
 String getName() {
    return
 name;
  }
  public
 TotalCharge(String name, Double fee, java.util.Date chargeTimeBegin,
                     java.util.Date chargeTimeEnd) {
    this
.name = name;
    this
.fee = fee;
    this
.chargeTimeBegin = chargeTimeBegin;
    this
.chargeTimeEnd = chargeTimeEnd;
  }
  public
 
void
 setName(String name) {
    this
.name = name;
  }
  public
 Double getFee() {
    return
 fee;
  }
  public
 
void
 setFee(Double fee) {
    this
.fee = fee;
  }
  public
 java.util.Date getChargeTimeBegin() {
    return
 chargeTimeBegin;
  }
  public
 
void
 setChargeTimeBegin(java.util.Date chargeTimeBegin) {
    this
.chargeTimeBegin = chargeTimeBegin;
  }
  public
 java.util.Date getChargeTimeEnd() {
    return
 chargeTimeEnd;
  }
  public
 
void
 setChargeTimeEnd(java.util.Date chargeTimeEnd) {
    this
.chargeTimeEnd = chargeTimeEnd;
  }
}



TotalChargeDaoImpl.java  代码

package
 test.dao.impl;
import
 java.util.*;
import
 test.bean.*;
import
 test.dao.*;
import
 net.sf.hibernate.*;
/**
 *作者：孙星
 **/
public
 
class
 TotalChargeDaoImple 
extends
 TotalChargeDao{
    //下面方法集成自TotalChargeDao
    public
 List statTotalCharge(Date statTimeBegin, Date statTimeEnd) 
throws
         DaoException{
        List res = new
 Vector();
//将用于存放保存的结果集合
        Session session = null
;
        ScrollableResults srs ＝ null
;
        try
{
            //得到一个Hibernate Session
            session = HibernateSessionFactory.openSession();

            //下面创建一个匿名Query实例并调用它的scroll()方法返回以
ScrollableResults形式组织的查询结果
            srs
= session.createQuery(“select b.name, count(a.fee)
mix(a.chargeBeginTime) max(a.chargeEndTime) from charge a, customer b
where a.idCustomer = b.idCustomer and a.chargeBeginTime >= ? and
a.chargeEndTime < ? gourp by a.idCustomer“).setDate(0
,
statTimeBegin).setDate(1
, statTimeEnd).scroll();

            //将查询结果放入List保存
            while
(srs.next()){
                res.add(new
 TotalCharge(srs.getString(
0
), srs,getDouble(
1
), srs.getDate(
2
), srs.getDate(
3
)));
            }
        }catch
(HibernateException he){
            ;//loging err.....
            if
(srs!=
null
){
                try
{
                    srs.close();
                }catch
(Exception e){
                    ;
                }
            }
        }finally
{
            try
{
                session.close();
            }catch
(Exception e){
                ;
            }
        }
        return
 res;
    }
}

 

看，现在问题解决了。坐下来，喝杯Java吧！

评论

4 楼 zhoulei984623 2010-09-15  

infante_yin 写道

srs.getString(0) 不报错吗？

报啥错？

3 楼 infante_yin 2010-08-10  

srs.getString(0) 不报错吗？

2 楼 zhoulei984623 2007-12-14  

应该结合视图来做可以提高效率！

1 楼 zhoulei984623 2007-11-16  

自己的例子：
DAO部分：
public List<VehicleDetail> getVehicleDetailList(String ownerId) {
      
        ArrayList<VehicleDetail> vechileDetailList = new ArrayList<VehicleDetail>();
        ScrollableResults srs = null;
      
        StringBuffer buf = new StringBuffer(100);
        buf.append("select ownerVehicleReg.vehicleID,vehicleType.name,fuelType.name,model.name,manufacture.name,vehicle.registrationNO,vehicle.assetCode,vehicle.capacity,tonnageType.name ");
        buf.append("from ownerVehicleReg in class com.bsmart.bmc.bo.admin.OwnerVehicleReg,");
        buf.append("vehicle in class com.bsmart.bmc.bo.admin.Vehicle,");
        buf.append("model in class com.bsmart.bmc.bo.admin.Model,");
        buf.append("vehicleType in class com.bsmart.bmc.bo.admin.VehicleType,");
        buf.append("manufacture in class com.bsmart.bmc.bo.admin.Manufacture,");
        buf.append("fuelType in class com.bsmart.bmc.bo.admin.FuelType,");
        buf.append("tonnageType in class com.bsmart.bmc.bo.admin.TonnageType ");
        buf.append("where ownerVehicleReg.ownerID = ");
        buf.append(ownerId );
        buf.append("and ownerVehicleReg.vehicleID = vehicle.vehicleID ");
        buf.append("and vehicle.modelId = model.modelID ");
        buf.append("and vehicle.manufactureID = manufacture.manufactureID ");
        buf.append("and vehicle.vehicleTypeID = vehicleType.vehicleTypeID ");
        buf.append("and vehicle.fuelTypeID = fuelType.FuelTypeID ");
        buf.append("and vehicle.tonnageTypeID = tonnageType.tonnageTypeID");

        srs = getSession().createQuery(buf.toString()).scroll();
      
        while(srs.next()) {
            vechileDetailList.add(new VehicleDetail(srs.getString(0),srs.getString(1),srs.getString(2),srs.getString(3),srs.getString(4),srs.getString(5),srs.getString(6),srs.getString(7),srs.getString(8)));
        }
        return vechileDetailList;
    }

VehicleDetail类:
package com.bsmart.bmc.admin.bean;

/**
*
* @author developer
*
*/
public class VehicleDetail{
  
    private String vehicleID;
    private String vehicleType;
    private String fuelType;
    private String model;
    private String manufacture;
    private String registrationNO;
    private String assetCode;
    private String capacity;
    private String tonnageType;
  
    public VehicleDetail() {
      
    }
  
    public VehicleDetail(String vehicleID,String vehicleType,String fuelType,String model,String manufacture,String registrationNO
            ,String assetCode,String capacity,String tonnageType) {
        this.vehicleID = vehicleID;
        this.vehicleType = vehicleType;
        this.fuelType = fuelType;
        this.model = model;
        this.manufacture = manufacture;
        this.registrationNO = registrationNO;
        this.assetCode = assetCode;
        this.capacity = capacity;
        this.tonnageType = tonnageType;
    }
  
    public String getAssetCode() {
        return assetCode;
    }
    public void setAssetCode(String assetCode) {
        this.assetCode = assetCode;
    }
    public String getCapacity() {
        return capacity;
    }
    public void setCapacity(String capacity) {
        this.capacity = capacity;
    }
    public String getFuelType() {
        return fuelType;
    }
    public void setFuelType(String fuelType) {
        this.fuelType = fuelType;
    }
    public String getManufacture() {
        return manufacture;
    }
    public void setManufacture(String manufacture) {
        this.manufacture = manufacture;
    }
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    public String getRegistrationNO() {
        return registrationNO;
    }
    public void setRegistrationNO(String registrationNO) {
        this.registrationNO = registrationNO;
    }
    public String getTonnageType() {
        return tonnageType;
    }
    public void setTonnageType(String tonnageType) {
        this.tonnageType = tonnageType;
    }
    public String getVehicleID() {
        return vehicleID;
    }
    public void setVehicleID(String vehicleID) {
        this.vehicleID = vehicleID;
    }
    public String getVehicleType() {
        return vehicleType;
    }
    public void setVehicleType(String vehicleType) {
        this.vehicleType = vehicleType;
    }