Hibernate 多表联查

要给WEB短信群发系统加一个根据时间统计短信发送条数的功能。以前的系统持久层用的Hibernate。对Hibernate也不是很熟悉。分析后一看要多表联查，郁闷！没有玩过Hibernate的多表联查。上网查了一下，通过试验完成任务。趁热总结一下:
如果我有两个表一个User，一个MtLog。关系是User<1-----------*->MtLog。

Hibernate 3.2.5
middlegen 1.3.3
mysql 5.0.17

在数据库中建好表之后用middlegen 自动生成***.hbm.xml文件，有两种方法其中一种是不建立两个表的关系联合查询，另外一种是建立两个表的关系联合查询。首先是不建立两个表的关系联合查询:

User.hbm.xml文件

[code]<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.2

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class name="org.apache.eason.Test.User" table="user"
lazy="false">

<id name="id" type="java.lang.String" column="ID">

<generator class="assigned" />
</id>
......
<property name="fullname" type="java.lang.String"
column="FULLNAME" not-null="true" length="50" />
......
</class>
</hibernate-mapping>
[/code]


MtLog.hbm.xml文件

[code]<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.2

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class name="org.apache.eason.Test.MtLog" table="mt_log"
lazy="false">

<id name="id" type="java.lang.String" column="ID">

<generator class="assigned" />
</id>

<property name="userId" column="USER_ID" type="java.lang.String"
length="32" />

</class>
</hibernate-mapping>
[/code]

对应的bean文件就不贴了。有三个bean，User.java、MtLog.java、StatMtLog.java :D
HibernateTest.java

[code]
public class HibernateTest
{
public static void mian(String [] args)
{
.....
ScrollableResults srs = session
.createQuery(
"select u.fullname ,sum(ml.count) from User u , MtLog ml where ml.userId = u.id and ml.mtTime > :minRecordTime and ml.mtTime < :maxRecordTime GROUP BY ml.userId")
.setString("minRecordTime", minRecordTime + "%").setString("maxRecordTime", maxRecordTime + "%")
.scroll();
List<StatMtLog> totalList = new ArrayList<StatMtLog>() ;
while(srs.next())
{
totalList.add(new StatMtLog(srs.getString(0),srs.getInteger(1)));
}
....

}


}

[/code]

下面是建立关系后两表联查的做法

User.hbm.xml

[code]
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.2

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class name="org.apache.eason.Test.User" table="user"
lazy="false">

<id name="id" type="java.lang.String" column="ID">

<generator class="assigned" />
</id>
......
<property name="fullname" type="java.lang.String"
column="FULLNAME" not-null="true" length="50" />
......
/* user-1--*->mtlog:一对多的关系 */
<set name="mtLogs" lazy="true" inverse="true" cascade="all">
<key>
<column name="USER_ID" />
</key>
<one-to-many class="org.apache.eason.Test.MtLog" />
</set>
</class>
</hibernate-mapping>
[/code]

MtLog.hbm.xml

[code]
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.2

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class name="org.apache.eason.Test.MtLog" table="mt_log"
lazy="false">

<id name="id" type="java.lang.String" column="ID">

<generator class="assigned" />
</id>

......
/* mtlog-*--1->user:多对一的关系 */
<many-to-one name="user" class="org.apache.eason.Test.User"
not-null="true">
<column name="USER_ID" />
</many-to-one>

</class>
</hibernate-mapping>


[/code]

这样建立关联关系后，要修改一下查询语句

select u.fullname ,sum([color=red]u.mtLogs.count[/color]) from User u GROUP BY u.fullname
注意红色部分。