要给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
注意红色部分。
如果我有两个表一个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
注意红色部分。