hibernate联合查询问题

最近在用hibernate做项目，遇到了联合查询的问题，以及联合查询中的N+1问题。

针对无外键关联的联合查询，我做了HQL和SQL的实验，希望能帮助到大家。（我使用的版本是hibernate3.3.2）

 

1 几个常识：

 （1）hql中的几种join查询，只有在外键关联、并且作了相应配置时才能使用。

 （2）hql的默认查询策略，在进行联合查询时，会产生N+1问题，即先查询一次得到主键列表，然后根据主键查询N次数据库。

 （3）hibernate有许多优化策略来避免N+1问题，但前提都是外键关联的情况下。

2 基本项目背景

不存在外键关联的几张表，具有各自的主键，需要进行联合查询。示例如下：

      table fare<!--StartFragment--> 

idint(11) NOT NULL PK,

desStnvarchar(255) NULL,

orgStnvarchar(255) NULL

 

      table route

idint(11) NOT NULL PK,

desCodevarchar(255) NULL,

oriCodevarchar(255) NULL

 

fare表和route表是不存在外键关联的，我想利用hibernate进行联合查询，怎么办？

 

3 实验如下

 我首先想到的是，利用HQL进行联合查询，因为可以避免写繁琐的getter和setter方法，如下：

@Test
 public void testHQLSelect() {
       session.beginTransaction();
       List<FareRoute>  list = session.createQuery("select new com.hibernate.FareRoute(f, r) from Fare f,     Route r where f.orgStn=r.oriCode and f.desStn=r.desCode").list();
       for (FareRoute fareRoute : list) {
            System.out.println(fareRoute);
        }
        session.getTransaction().commit();
 }

 

其中，Fare和Route分别是对应于fare表和route表的映射对象，FareRoute是自己定义的引用Fare和Route的类，用于前台显示。

 

这样子，是可以顺利的得到List<FareRoute>的，结果如下：

Hibernate: select fare0_.id as col_0_0_, route1_.routeId as col_1_0_ from Fare fare0_ cross join Route route1_ where fare0_.orgStn=route1_.oriCode and fare0_.desStn=route1_.desCode
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select route0_.routeId as routeId24_0_, route0_.desCode as desCode24_0_, route0_.oriCode as oriCode24_0_ from Route route0_ where route0_.routeId=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?
Hibernate: select fare0_.id as id23_0_, fare0_.desStn as desStn23_0_, fare0_.orgStn as orgStn23_0_ from Fare fare0_ where fare0_.id=?

1-SHA-BJS-1-SHA-BJS
2-BJS-SHA-2-BJS-SHA
3-BJS-PEK-4-BJS-PEK
5-PEK-SHA-5-PEK-SHA
5-PEK-SHA-6-PEK-SHA
5-PEK-SHA-7-PEK-SHA
7-BBB-AAA-3-BBB-AAA
8-PEK-SHA-5-PEK-SHA
8-PEK-SHA-6-PEK-SHA
8-PEK-SHA-7-PEK-SHA
9-PEK-SHA-5-PEK-SHA
9-PEK-SHA-6-PEK-SHA
9-PEK-SHA-7-PEK-SHA
10-PEK-SHA-5-PEK-SHA
10-PEK-SHA-6-PEK-SHA
10-PEK-SHA-7-PEK-SHA

 

有过项目经验的同学，肯定知道这就是N+1问题，我尝试用left join、设置fetch、修改batch-size等方式进行优化，却由于fare表和route表不存在外键关联，均以失败告终。做了另外一个实验，如下：

 

@Test
 public void testSQLSelect() {
     session.beginTransaction();
     List<Object[]>  list = session.createSQLQuery("select * from fare f, route r where f.orgStn=r.oriCode and f.desStn=r.desCode").addEntity("f", Fare.class).addEntity("r", Route.class).list();
    for (Object[] obj : list) {
        FareRoute fr = new FareRoute((Fare)obj[0], (Route)obj[1]);
        System.out.println(fr);
     }
     session.getTransaction().commit();
 }

执行结果如下：

Hibernate: select * from fare f, route r where f.orgStn=r.oriCode and f.desStn=r.desCode

1-SHA-BJS-1-SHA-BJS
2-BJS-SHA-2-BJS-SHA
3-BJS-PEK-4-BJS-PEK
5-PEK-SHA-5-PEK-SHA
5-PEK-SHA-6-PEK-SHA
5-PEK-SHA-7-PEK-SHA
7-BBB-AAA-3-BBB-AAA
8-PEK-SHA-5-PEK-SHA
8-PEK-SHA-6-PEK-SHA
8-PEK-SHA-7-PEK-SHA
9-PEK-SHA-5-PEK-SHA
9-PEK-SHA-6-PEK-SHA
9-PEK-SHA-7-PEK-SHA
10-PEK-SHA-5-PEK-SHA
10-PEK-SHA-6-PEK-SHA
10-PEK-SHA-7-PEK-SHA

 

使用sql进行联合查询时，只查询了一次，避免了N+1的问题，通过addEntity指定了返回的类型，返回的时候对象数组的列表， 通过使用

for (Object[] obj : list) {
      FareRoute fr = new FareRoute((Fare)obj[0], (Route)obj[1]);

}

得到了想要的FareRoute对象。

 

总结：使用SQL的方式，解决了没有外键关联的多表联合查询时的N+1问题，需要自己对得到的List<Object[]>进行一些处理，但是这个处理并不麻烦，我认为可以接受，这样算是在编码繁琐性和性能之间的一个折衷。

 

希望能帮助到大家，与大家共同进步。