1. 如何使用 HQL 语句
HQL 语句一般写在 XML 配置文件中， Web 系统启动时，引导初始化配置文件，同时也就把 HQL 语句加载了。这么用的好处是，给程序员维护项目留有灵活的手段。缺点也有，开发的时候不太好调试。
Hibernate + Spring + Struts 框架群的用法如下：
(1) 包结构
(2) web.xml 配置文件
<? xml version = "1.0" encoding = "UTF-8" ?>
< web-app version = "2.5"
xmlns = "http://java.sun.com/xml/ns/j2ee"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation = "http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_5.xsd" >
<!-- log4j configuration listener -->
< context-param >
< param-name > webAppRootKey </ param-name >
< param-value > hydropubilc.root </ param-value >
</ context-param >
< context-param >
< param-name > log4jConfigLocation </ param-name >
< param-value > /WEB-INF/log4j.properties </ param-value >
</ context-param >
< context-param >
< param-name > log4jRefreshInterval </ param-name >
< param-value > 60000 </ param-value >
</ context-param >
< listener >
< listener-class > org.springframework.web.util.Log4jConfigListener </ listener-class >
</ listener >
<!-- spring context loader listener -->
< context-param >
< param-name > contextConfigLocation </ param-name >
< param-value >
/WEB-INF/spring/app-DB-Context.xml,
/WEB-INF/spring/app-Beans-Context.xml,
/WEB-INF/spring/app-EndPoint-Context.xml
</ param-value >
</ context-param >
< listener >
< listener-class > org.springframework.web.context.ContextLoaderListener </ listener-class >
</ listener >
<!-- CXF servlet -->
< servlet >
< servlet-name > CXFServlet </ servlet-name >
< servlet-class > org.apache.cxf.transport.servlet.CXFServlet </ servlet-class >
< load-on-startup > 1 </ load-on-startup >
</ servlet >
< servlet-mapping >
< servlet-name > CXFServlet </ servlet-name >
< url-pattern > /CXF/* </ url-pattern >
</ servlet-mapping >
<!-- struts2 filter -->
< filter >
< filter-name > struts2 </ filter-name >
< filter-class > org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter </ filter-class >
</ filter >
< filter-mapping >
< filter-name > struts2 </ filter-name >
< url-pattern > /view/* </ url-pattern >
</ filter-mapping >
< welcome-file-list >
< welcome-file > index.jsp </ welcome-file >
</ welcome-file-list >
</ web-app >
说明：
- “ CXF Servlet ”配的是 Apache CXF Web Service ，不需要可以不配。
- Spring 配置文件被分割成三部分，分别是：
app-DB-Context.xml ，管理数据库连接和 Hibernate 配置文件。
app-Beans-Context.xml ，管理 Spring 的 Bean 。
app-EndPoint-Context.xml ，管理 CXF Web Service 。
(3) Spring 配置文件
这里只讲讲 app-DB-Context.xml 配置文件，另外两个配置文件就不讲了，它们与 HQL 没有关系。
app-DB-Context.xml 内容如下：
< bean id = "dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method = "close" >
< property name = "driverClass" value = "oracle.jdbc.driver.OracleDriver" />
< property name = "jdbcUrl" value = " 。。。 " />
< property name = "user" value = " 。。。 " />
< property name = "password" value = " 。。。 " />
< property name = "maxPoolSize" value = "20" />
< property name = "minPoolSize" value = "1" />
< property name = "initialPoolSize" value = "1" />
< property name = "maxIdleTime" value = "20" />
</ bean >
< bean id = "sessionFactory" class = "org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
< property name = "dataSource" ref = "dataSource" />
< property name = "mappingResources" >
< list >
< value > /com/gw/hbm/ImportClasses.hbm.xml </ value >
< value > /com/gw/hbm/oracle/ 各种表映射文件 .hbm.xml </ value >
< value > 。。。 </ value >
< value > /com/gw/hbm/oracle/HQLOracle.hbm.xml </ value >
</ list >
</ property >
< property name = "hibernateProperties" >
< props >
< prop key = "hibernate.dialect" > org.hibernate.dialect.Oracle9Dialect </ prop >
<!-- 正式运行时 hibernate.show_sql 设置为 false -->
< prop key = "hibernate.show_sql" > true </ prop >
<!-- 正式运行时 hibernate.hbm2ddl.auto 这行要注释掉 -->
<!-- <prop key="hibernate.hbm2ddl.auto">validate</prop>-->
< prop key = "hibernate.jdbc.batch_size" > 20 </ prop >
< prop key = "hibernate.query.factory_class" > org.hibernate.hql.classic.ClassicQueryTranslatorFactory </ prop >
</ props >
</ property >
</ bean >
< bean id = "daoTemplate" abstract = "true" >
< property name = "sessionFactory" ref = "sessionFactory" />
</ bean >
ImportClasses.hbm.xml 配置文件主要用来导入 HQL 语句中用到的实体类。
ImportClasses.hbm.xml 内容如下：
<? xml version = "1.0" encoding = "utf-8" ?>
<! DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<!--
自定义对象的导入声明
-->
< hibernate-mapping >
< import class = "com.gw.model.entity.EnAlarm" />
< import class = "com.gw.model.entity.EnDYP" />
< import class = "com.gw.model.entity.EnRealInfoPptn" />
< import class = "com.gw.model.entity.EnRealInfoRiver" />
< import class = "com.gw.model.entity.EnRealInfoRsvr" />
</ hibernate-mapping >
HQLOracle.hbm.xml 配置文件主要用来保存 HQL 语句。所保存的语句都是 For Oracle 版的，如果要针对 SQL Server 或 MySQL 数据库，就再分别另写一套。
HQLOracle.hbm.xml 内容如下：
<? xml version = "1.0" encoding = "UTF-8" ?>
<! DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<!--
User custom query
-->
< hibernate-mapping >
< query name = " 查询名称 1" >
<![CDATA[
HQL 语句 1
]]>
</ query >
< query name = " 查询名称 2" >
<![CDATA[
HQL 语句 2
]]>
</ query >
</hibernate-mapping>
(4) 程序中的调用
package com.gw.dao.impl;
import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.gw.dao.IAlarmDAO;
import com.gw.model.entity.EnAlarm;
@SuppressWarnings ( "unchecked" )
public class AlarmDAOImpl extends HibernateDaoSupport implements IAlarmDAO {
public List<EnAlarm> findRiverWrz(String startDate, String endDate, String abs) throws Exception {
String[] names = new String[]{ "startDate" , "endDate" , "abs" };
Object[] values = new Object[]{startDate, endDate, Double.parseDouble (abs)};
return this .getHibernateTemplate().findByNamedQueryAndNamedParam( " 查询名称 1 " , names, values);
}
}
说明：“查询名称 1 ” 要和 HQLOracle.hbm.xml 文件中的“查询名称 1 ” 相对应。
2. 如何适应不同类型数据库
实际项目中会碰到同时连接多个数据库，特别是多个不同类型的数据库的情况。只要调整 Sping 的配置文件 app-DB-Context.xml 就可以。
app-DB-Context.xml 内容如下：
< bean id = "dataSource1" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method = "close" >
<property name = "driverClass" value = "oracle.jdbc.driver.OracleDriver" />
。。。
</ bean >
< bean id = "sessionFactory1" class = "org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
< property name = "dataSource" ref = "dataSource1" />
。。。
</ bean >
< bean id = "daoTemplate" abstract = "true" >
< property name = "sessionFactory" ref = "sessionFactory1" />
</ bean >
< bean id = "dataSource2" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method = "close" >
< property name = "driverClass" value = "com.microsoft.sqlserver.jdbc.SQLServerDriver" />
。。。
</ bean >
< bean id = "sessionFactory2" class = "org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
< property name = "dataSource" ref = "dataSource2" />
。。。
</ bean >
< bean id = "daoTemplate2" abstract = "true" >
< property name = "sessionFactory" ref = "sessionFactory2" />
</ bean >
3. Select 子查询的使用
< query name = "queryGetRsvrFsr" >
<![CDATA[
select distinct new EnRealInfoRsvr(a.id.stcd, c.stnm, a.id.tm, a.rz, a.w, a.inq, a.otq, b.fsltdz, round(a.rz-b.fsltdz, 2))
from StRsvrR as a, StRsvrfsrB as b, StStbprpB as c
where a.id.stcd = b.id.stcd and a.id.stcd = c.stcd
and a.rz - b.fsltdz > 0
and a.id.stcd <= '99999999'
and (a.id.stcd, a.id.tm) in
(
select a.id.stcd, max(a.id.tm)
from StRsvrR as a
where a.id.tm between to_date(:startDate, 'yyyy-mm-dd hh24:mi:ss') and to_date(:endDate, 'yyyy-mm-dd hh24:mi:ss')
and a.rz is not null
group by a.id.stcd
)
and to_char(a.id.tm, 'mmdd') between b.id.bgmd and b.edmd
order by a.id.stcd
]]>
</ query >
4. like 操作符传值问题
程序里这样设置查询条件：
public List<EnRealInfoPptn> getPptnByLocality(String tm, String locality) {
String[] names = new String[]{ "tm" , "locality" };
Object[] values = new Object[]{tm, "%" + locality + "%" };
return this .getHibernateTemplate().findByNamedQueryAndNamedParam( "queryGetPptnByLocality" , names, values);
}
HQL 语句这样写：
< query name = "queryGetPptnByLocality" >
<![CDATA[
select new EnRealInfoPptn(a.id.stcd, b.locality, b.rvnm, b.stnm, a.id.tm, a.dyp, a.wth)
from StPptnR as a, StStbprpB as b
where a.id.stcd = b.stcd and
a.dyp is not null and
a.id.tm = to_date(:tm, 'yyyy-mm-dd hh24:mi:ss') and
a.id.stcd <= '99999999' and
b.locality like :locality
order by a.id.stcd
]]>
</ query >
5. in 操作符传值问题
HQL 支持 Where 子句中的 in 操作符，不过怎样把 in 括号内的值传入呢？
比如说，我们要执行这样一句 SQL 语句：
select stpptnr0_.STCD as col_0_0_, ststbprpb1_.STNM as col_1_0_, stpptnr0_.TM as col_2_0_, stpptnr0_.DRP as col_3_0_, stpptnr0_.INTV as col_4_0_
from cwRWDB.dbo.ST_PPTN_R stpptnr0_, cwRWDB.dbo.ST_STBPRP_B ststbprpb1_
where (stpptnr0_.STCD=ststbprpb1_.STCD )and(stpptnr0_.DRP is not null )
and(stpptnr0_.STCD in ('61535000','61333250','61127500','61804110','60109100','60802310'))
and(stpptnr0_.TM>='2010-08-01 00:00:00' )and(stpptnr0_.TM<='2010-08-05 20:00:00' )
order by stpptnr0_.STCD , stpptnr0_.TM
如何把“ '61535000','61333250','61127500','61804110','60109100','60802310' ”作为查询条件传给配置文件中的 HQL 语句呢？答案：利用数组。
程序里这样设置查询条件：
public List<EnRainfall> findPptnOfTime(String stcds, String startDate, String endDate) throws Exception {
//stcds = '61535000','61333250','61127500','61804110','60109100','60802310'
String[] arrStcds = stcds.replaceAll( "'" , "" ).split( "," );
if (arrStcds. length > 0) {
String[] names = new String[]{ "stcds" , "startDate" , "endDate" };
Object[] values = new Object[]{arrStcds, startDate, endDate};
return this .getHibernateTemplate().findByNamedQueryAndNamedParam( "queryFindPptnOfTime" , names, values);
} else {
return null ;
}
}
HQL 语句这样写：
< query name = "queryFindPptnOfTime" >
<![CDATA[
select new EnRainfall(a.id.stcd, b.stnm, a.id.tm, a.drp, a.intv)
from StPptnR as a, StStbprpB as b
where a.id.stcd = b.stcd
and a.drp is not null
and a.id.stcd in (:stcds)
and a.id.tm >= :startDate and a.id.tm <= :endDate
order by a.id.stcd, a.id.tm
]]>
</ query >
6. dialect 方言的处理
Hibernate 方言对 Oracle 支持的比较好，基本 Oracle 的数据库内置函数都可以调用。但是对 MS SQL Server 数据库就支持的差一点了，很多 SQL Server 数据库内置函数都不能用。为什么会这样？不知道 - -!
可是项目里用 SQL Server 库，又想使用 SQL Server 内置函数写 sql 语句，怎么办？
改造 For SQL Server 的 dialect 方言。
创建个新类，如下：
package org.hibernate.dialect;
import org.hibernate.Hibernate;
import org.hibernate.dialect.function.SQLFunctionTemplate;
public class GWSQLServerDialect extends SQLServerDialect {
public GWSQLServerDialect() {
super ();
registerFunction( "varchar" , new SQLFunctionTemplate(Hibernate. STRING , "varchar(?1)" ));
registerFunction( "convert" , new SQLFunctionTemplate(Hibernate. STRING , "convert(?1,?2,?3)" ));
}
}
Spring 配置文件 app-DB-Context.xml 中这样创建 sessionFactory bean ：
< bean id = "sessionFactoryForecast" class = "org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >
< property name = "dataSource" ref = "dataSourceForecast" />
< property name = "mappingResources" >
< list >
< value > /com/gw/hbm/ImportClasses.hbm.xml </ value >
< value > /com/gw/hbm/sql/FForecast.hbm.xml </ value >
< value > /com/gw/hbm/sql/FForecastC.hbm.xml </ value >
< value > /com/gw/hbm/sql/HQLSqlServer.hbm.xml </ value >
</ list >
</ property >
< property name = "hibernateProperties" >
< props >
< prop key = "hibernate.dialect" > org.hibernate.dialect.GWSQLServerDialect </ prop >
<!-- 正式运行时 hibernate.show_sql 设置为 false -->
< prop key = "hibernate.show_sql" > true </ prop >
<!-- 正式运行时 hibernate.hbm2ddl.auto 这行要注释掉 -->
<!-- <prop key="hibernate.hbm2ddl.auto">validate</prop>-->
< prop key = "hibernate.jdbc.batch_size" > 20 </ prop >
< prop key = "hibernate.query.factory_class" > org.hibernate.hql.classic.ClassicQueryTranslatorFactory </ prop >
</ props >
</ property >
</ bean >
说明：“ hibernateProperties ”属性中的“ hibernate.dialect ”设置为新创建的方言类。
362
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
