Hibernate通过函数执行sql
编程技术 / houtizong 发布于 3年前 70
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>
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
技术博客集 - 网站简介：
前后端技术：
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成
网站主要作用：
1.编程技术分享及讨论交流，内置聊天系统;
2.测试交流框架问题，比如：Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识，如有侵权请发邮件到站长邮箱，站长会尽快处理;
4.站长邮箱：[email protected];
文章归档
文章标签
友情链接
