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>