java调用代码
public BcsResponse getEstateByCustomer(int customerId, Date startDate, Date endDate, int queryType)
{
BCSSession session = null;
BcsResponse retVal = new BcsResponse();
String query_name = "getEndpointVirtualrooms_v2";
if(queryType==1)
query_name="getEndpoints_v2";
if(queryType==2)
query_name="getVirtualrooms_v2";
try
{
session = ProvisionCoreLogic.getInstance().getPersistenceManager().getNewSession();
log.info("ProvisionCoreLogic.getInstance().getPersistenceManager().getNewSession(): "+session);
BCSQuery query = session.getNamedQuery(query_name);
query.setParameter(0,customerId); //Integer.parseInt(String.valueOf(customerId)));
query.setParameter(1, startDate);
query.setParameter(2, endDate);
List resultList = query.getListResult();
retVal.setSuccessful(true);
retVal.setReturnedObject(resultList);
}catch(Exception e){
retVal.setSuccessful(false);
retVal.setErrorStr("getEstateByCustomer Failed");
retVal.setOriginalRequest(null);
retVal.setReturnedException(null);
retVal.setReturnedObject(null);
return retVal;
}
return retVal;}
Hibernate配置文件
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Apr 6, 2009 10:03:38 PM by Hibernate Tools 3.2.2.GA -->
<hibernate-mapping>
<sql-query name="getEndpoints" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
select * from (
select rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,history_guid,name,datatype ,is_primary_tp
from bcs_e164_history_map where
deleted is null and name is not null and (datatype='ep')
and customer_id in (select * from customer_tree(?,0)) and secterr='-1342177274'
and history_action_time < ?
) x
where
x.id_rank=1 and (x.is_primary_tp is null or x.is_primary_tp ='Yes')
order by datatype, name
]]>
</sql-query>
<sql-query name="getVirtualrooms" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
select * from (
select rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,history_guid,name,datatype ,is_primary_tp
from bcs_e164_history_map where
deleted is null and name is not null and (datatype='vr')
and customer_id in (select * from customer_tree(?,0)) and secterr='-1342177274'
and history_action_time < ?
) x
where
x.id_rank=1 and (x.is_primary_tp is null or x.is_primary_tp ='Yes')
order by datatype, name
]]>
</sql-query>
<sql-query name="getEndpointVirtualrooms" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
select * from (
select rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,history_guid,name,datatype ,is_primary_tp
from bcs_e164_history_map where
deleted is null and name is not null and (datatype='ep' or datatype='vr')
and customer_id in (select * from customer_tree(?,0)) and secterr='-1342177274'
and history_action_time < ?
) x
where
x.id_rank=1 and (x.is_primary_tp is null or x.is_primary_tp ='Yes')
order by datatype, name
]]>
</sql-query>
<sql-query name="getEndpoints_v2" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
DECLARE @customerId int;
DECLARE @start_time datetime;
DECLARE @end_time datetime;
SET @customerId = ?;
SET @start_time = DATEADD(day, -1, ?);
SET @end_time = DATEADD(day, 2, ?);
WITH
STEP1 AS
(
SELECT
rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='ep' )
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274' and status like 'Active%'
and @start_time <= history_action_time and history_action_time <= @end_time
UNION
SELECT
10000 + rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='ep')
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274'
and history_action_time < @start_time
),
STEP_2 AS
(
SELECT
(rank() over (partition by STEP1.bcs_e164_map_id order by STEP1.id_rank)) as id_rank2,
STEP1.id_rank as id_rank,
STEP1.bcs_e164_map_id as bcs_e164_map_id,
STEP1.history_guid as history_guid,
STEP1.name as name,
STEP1.datatype as datatype,
STEP1.is_primary_tp as is_primary_tp,
STEP1.[status] as [status],
STEP1.history_action_time as history_action_time,
STEP1.history_change_number as history_change_number
FROM
STEP1
WHERE
(STEP1.id_rank in (1, 10001))
and (STEP1.is_primary_tp is null or STEP1.is_primary_tp ='Yes')
and STEP1.[status] like 'Active%'
)
SELECT
STEP_2.id_rank,
STEP_2.bcs_e164_map_id,
STEP_2.history_guid,
STEP_2.name,
STEP_2.datatype,
STEP_2.is_primary_tp
FROM STEP_2
WHERE STEP_2.id_rank2 = 1
ORDER BY STEP_2.datatype, STEP_2.name;
]]>
</sql-query>
<sql-query name="getVirtualrooms_v2" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
DECLARE @customerId int;
DECLARE @start_time datetime;
DECLARE @end_time datetime;
SET @customerId = ?;
SET @start_time = DATEADD(day, -1, ?);
SET @end_time = DATEADD(day, 2, ?);
WITH
STEP1 AS
(
SELECT
rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='vr')
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274' and status like 'Active%'
and @start_time <= history_action_time and history_action_time <= @end_time
UNION
SELECT
10000 + rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='vr')
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274'
and history_action_time < @start_time
),
STEP_2 AS
(
SELECT
(rank() over (partition by STEP1.bcs_e164_map_id order by STEP1.id_rank)) as id_rank2,
STEP1.id_rank as id_rank,
STEP1.bcs_e164_map_id as bcs_e164_map_id,
STEP1.history_guid as history_guid,
STEP1.name as name,
STEP1.datatype as datatype,
STEP1.is_primary_tp as is_primary_tp,
STEP1.[status] as [status],
STEP1.history_action_time as history_action_time,
STEP1.history_change_number as history_change_number
FROM
STEP1
WHERE
(STEP1.id_rank in (1, 10001))
and (STEP1.is_primary_tp is null or STEP1.is_primary_tp ='Yes')
and STEP1.[status] like 'Active%'
)
SELECT
STEP_2.id_rank,
STEP_2.bcs_e164_map_id,
STEP_2.history_guid,
STEP_2.name,
STEP_2.datatype,
STEP_2.is_primary_tp
FROM STEP_2
WHERE STEP_2.id_rank2 = 1
ORDER BY STEP_2.datatype, STEP_2.name;
]]>
</sql-query>
<sql-query name="getEndpointVirtualrooms_v2" >
<return alias="BcsE164MapNew" class="com.bcsglobal.provision.entities.BcsE164MapNew"/>
<![CDATA[
DECLARE @customerId int;
DECLARE @start_time datetime;
DECLARE @end_time datetime;
SET @customerId = ?;
SET @start_time = DATEADD(day, -1, ?);
SET @end_time = DATEADD(day, 2, ?);
WITH
STEP1 AS
(
SELECT
rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='vr' or datatype='ep' )
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274' and status like 'Active%'
and @start_time <= history_action_time and history_action_time <= @end_time
UNION
SELECT
10000 + rank() over (partition by bcs_e164_map_id order by history_change_number desc) as id_rank,
bcs_e164_map_id,
history_guid,
name,
datatype ,
is_primary_tp,
[status],
history_action_time,
history_change_number
FROM
BCS.dbo.bcs_e164_history_map
WHERE
deleted is null and name is not null and (datatype='vr' or datatype='ep')
and customer_id in (SELECT * FROM BCS.dbo.customer_tree(@customerId,0)) and secterr='-1342177274'
and history_action_time < @start_time
),
STEP_2 AS
(
SELECT
(rank() over (partition by STEP1.bcs_e164_map_id order by STEP1.id_rank)) as id_rank2,
STEP1.id_rank as id_rank,
STEP1.bcs_e164_map_id as bcs_e164_map_id,
STEP1.history_guid as history_guid,
STEP1.name as name,
STEP1.datatype as datatype,
STEP1.is_primary_tp as is_primary_tp,
STEP1.[status] as [status],
STEP1.history_action_time as history_action_time,
STEP1.history_change_number as history_change_number
FROM
STEP1
WHERE
(STEP1.id_rank in (1, 10001))
and (STEP1.is_primary_tp is null or STEP1.is_primary_tp='Yes')
and STEP1.[status] like 'Active%'
)
SELECT
STEP_2.id_rank,
STEP_2.bcs_e164_map_id,
STEP_2.history_guid,
STEP_2.name,
STEP_2.datatype,
STEP_2.is_primary_tp
FROM STEP_2
WHERE STEP_2.id_rank2 = 1
ORDER BY STEP_2.datatype, STEP_2.name;
]]>
</sql-query>
</hibernate-mapping>
相关推荐
Hibernate 函数 ,子查询 和原生SQL查询。Hibernate 函数 ,子查询 和原生SQL查询
本示例演示Hibernate 3.2呼叫Oracle的存贮过程和函数,以及通过Hibernate的Query接口简化JDBC的开发步骤--两个步骤得到List集合。使用这种方式开发可以要求开发人员不需要太了解Hibernate框架,但是需要开发人员非常...
上传的资料都是非常经典的,这也是我学习过程中的心得,希望大家能给我指点,也希望大家多上传资料共同学习。千万不要传空文件夹。。。那些人很可恶啊
3.8. Hibernate SQL方言 (hibernate.dialect) 3.9. Hibernate日志类别 3.10. JTA TransactionManagers 9.1. 继承映射特性(Features of inheritance mappings) 16.1. 别名注射(alias injection names) 19.1. ...
8.2.2 整合存储过程和函数 8.3 改进Schema DDL 8.3.1 定制SQL名称和数据类型 8.3.2 确保数据一致性 8.3.3 添加领域约束和列约束 8.3.4 表级约束 8.3.5 数据库约束 8.3.6 创建索引 ...
第一、使用Hibernate 的sql 查询函数,将查询结果对象转为Entity对象。 第二、使用Hibernate Session的getConnection 获得JDBC Connection,然后进行纯JDBC API操作; 第三、选择把Spring的JDBCTemplate作为一种很...
3.8. Hibernate SQL方言 (hibernate.dialect) 3.9. Hibernate日志类别 3.10. JTA TransactionManagers 9.1. 继承映射特性(Features of inheritance mappings) 16.1. 别名注射(alias injection names) 19.1. ...
3.8. Hibernate SQL方言 (hibernate.dialect) 3.9. Hibernate日志类别 3.10. JTA TransactionManagers 9.1. 继承映射特性(Features of inheritance mappings) 16.1. 别名注射(alias injection names) 19.1. ...
3.5 通过Hibernate API操纵数据库 3.5.1 Hibernate的初始化 3.5.2 访问Hibernate的Session接口 3.6 运行helloapp应用 3.6.1 创建运行本书范例的系统环境 3.6.2 创建helloapp应用的目录结构 3.6.3 把...
统计函数(Aggregate functions) 11.6. 多态(polymorphism)查询 11.7. where子句 11.8. 表达式(Expressions) 11.9. order by 子句 11.10. group by 子句 11.11. 子查询 11.12. HQL示例 ...
Hibernate 使用 SQL Union 查询来实现这种策略。 这种策略支持双向的一对多关联,但不支持 IDENTIFY 生成器策略,因为ID必须在多个表间共享。一旦使用就不能使用AUTO和IDENTIFY生成器。 每个类层次结构一张表 @...
10.4.1. 执行查询 10.4.2. 过滤集合 10.4.3. 条件查询(Criteria queries) 10.4.4. 使用原生SQL的查询 10.5. 修改持久对象 10.6. 修改脱管(Detached)对象 10.7. 自动状态检测 10.8. 删除持久对象 10.9. 在两...
10.4.1. 执行查询 10.4.1.1. 迭代式获取结果(Iterating results) 10.4.1.2. 返回元组(tuples)的查询 10.4.1.3. 标量(Scalar)结果 10.4.1.4. 绑定参数 10.4.1.5. 分页 10.4.1.6. 可滚动遍历(Scrollable iteration) ...
10.4.1. 执行查询 10.4.1.1. 迭代式获取结果(Iterating results) 10.4.1.2. 返回元组(tuples)的查询 10.4.1.3. 标量(Scalar)结果 10.4.1.4. 绑定参数 10.4.1.5. 分页 10.4.1.6. 可滚动遍历(Scrollable iteration) ...
* 11.guid 采用数据库底层的guid算法机制,对应MySQL的uuid()函数,SQL Server的newid()函数,ORCALE的rawtohex(sys_guid())函数等 * 例:@GeneratedValue(generator = "paymentableGenerator") * @...
Hibernate的SessionFactory,是单个数据库映射关系编译后的内存镜像,是Hibernate执行持久化访问的基础。 Spring通过ApplicationContext管理SessionFactory,可以不使用Hibernate应用必需的hibernate.cfg.xml。 ...
11.4.1. 执行查询 11.4.1.1. 迭代式获取结果(Iterating results) 11.4.1.2. 返回元组(tuples)的查询 11.4.1.3. 标量(Scalar)结果 11.4.1.4. 绑定参数 11.4.1.5. 分页 11.4.1.6. 可滚动遍历(Scrollable ...
执行查询 10.4.1.1. 迭代式获取结果(Iterating results) 10.4.1.2. 返回元组(tuples)的查询 10.4.1.3. 标量(Scalar)结果 10.4.1.4. 绑定参数 10.4.1.5. 分页 10.4.1.6. 可滚动遍历(Scrollable iteration) ...
3.8. Hibernate SQL方言 (hibernate.dialect) 3.9. Hibernate日志类别 3.10. JTA TransactionManagers 9.1. 继承映射特性(Features of inheritance mappings) 16.1. 别名注射(alias injection names) 19.1. ...