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

2012-11-13

Hibernate多表联合查询解决办法[ZT]Hibernate是典型的OPM工具,它将每一个物理表格(Table)映射成为对象(Obj

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吧！

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;
    } 2 楼 zhoulei984623 2007-12-14   应该结合视图来做可以提高效率！ 3 楼 infante_yin 2010-08-10   srs.getString(0) 不报错吗？ 4 楼 zhoulei984623 2010-09-15   infante_yin 写道srs.getString(0) 不报错吗？
报啥错？

热点排行

软件架构设计

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