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文件

<?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>

MtLog.hbm.xml文件

<?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>

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

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))); 
			}
....

    }


}

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

User.hbm.xml

<?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>

MtLog.hbm.xml

<?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>

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

select u.fullname ,sum(u.mtLogs.count) from User u GROUP BY u.fullname
注意红色部分。

评论

2 楼 liucl_tiger 2008-10-28  

你那红色部分是什么？

1 楼 yuan_2004 2008-10-25  

感谢你的红色部分．．．