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>
相关推荐
在实际开发中,我们可能需要执行一些复杂的 SQL 查询,或者使用数据库特有的函数和语句,而这些操作在 HQL 中不支持。此时,我们需要使用原生 SQL 语句来实现这些操作。 使用 Hibernate 执行原生 SQL 语句 ...
虽然`Criteria API`主要是为了使用Hibernate的ORM功能,但也可以通过`Projections.sqlProjection()`执行原生SQL投影,从而创建自定义的查询。 ```java Criteria criteria = session.createCriteria(User.class); ...
当我们需要在Java应用中调用这些函数时,Hibernate提供了一种方式来封装和执行它们。以下是一些关键步骤: 1. **创建Oracle函数**:在Oracle数据库中,我们首先需要创建一个自定义函数。例如,我们可以创建一个计算...
Native SQL查询允许我们直接使用SQL语句来与数据库交互,不受HQL的限制,可以充分利用SQL的功能,如存储过程、自定义函数、复杂的联接和子查询等。这种方式尤其适用于那些对数据库性能有极高要求或者对数据库结构有...
在Java开发中,Hibernate是一个非常重要的对象关系映射(ORM)框架,它允许开发者通过面向对象的方式操作数据库,而无需直接编写SQL语句。本文将深入探讨如何模仿Hibernate生成SQL语句,以及如何利用Java反射技术来...
总结来说,Hibernate的Criteria API为Java开发人员提供了一种强大的、灵活的查询机制,使得我们可以以面向对象的方式来构建和执行SQL查询,极大地提升了代码的可读性和可维护性。结合源码理解和版本控制工具,可以...
**Hibernate函数** Hibernate 是一个流行的Java ORM(对象关系映射)框架,它允许开发者使用面向对象的方式来操作数据库。在Hibernate中,你可以使用内置的函数来处理数据,这些函数包括但不限于: 1. **Hibernate...
这通常涉及到在HQL中嵌入SQL片段,或者使用`createSQLQuery`方法直接执行SQL查询。同时,别忘了映射查询结果到对应的Java对象。 4. **max分页**: 提到的`max分页`可能是指在某些场景下,我们需要知道总共有多少页...
Java-Hibernate 框架中调用 MySQL 数据库中的过程和函数是通过 CallableStatement 对象来实现的。下面将详细介绍如何调用 MySQL 中的过程和函数。 调用函数 在 Java 中调用 MySQL 函数可以使用 CallableStatement ...
在使用Hibernate进行数据库操作时,虽然它提供了便捷的ORM(对象关系映射)功能,但同时也需要关注SQL注入的安全问题。SQL注入是一种常见的攻击手段,攻击者可以通过输入恶意的SQL语句来篡改数据库信息,严重威胁...
首先,让我们了解一下Hibernate中的HQL(Hibernate Query Language),它是Hibernate提供的面向对象的查询语言,类似于SQL,但更贴近于Java。在HQL中,我们可以方便地使用聚合函数进行数据处理。例如,如果你想要...
本示例演示Hibernate 3.2呼叫Oracle的存贮过程和函数,以及通过Hibernate的Query接口简化JDBC的开发步骤--两个步骤得到List集合。使用这种方式开发可以要求开发人员不需要太了解Hibernate框架,但是需要开发人员非常...
在Hibernate中执行原生SQL查询时,可以通过`addScalar`方法指定返回值的类型,从而改变Hibernate对char类型字段的处理方式。具体操作如下: ```java Session session = this.getSession(); SQLQuery query = ...
在配置Hibernate时,我们需要指定正确的方言类,这样Hibernate才能生成正确格式的SQL语句进行执行。例如,对于MySQL,我们应该使用`org.hibernate.dialect.MySQLDialect`;对于SQL Server,应选择`org.hibernate....
2. **查看Hibernate生成的SQL**:在`applicationConfig.xml`中设置`hibernate.show_sql`为`true`,即可在控制台查看执行的SQL。但要注意,这可能会影响性能优化。 3. **Hibernate缓存策略**: - **只读(Read-only...
2. **SQL语句的准备和执行**:一旦连接建立,就可以通过Statement或PreparedStatement对象来执行SQL。Statement适用于静态SQL,而PreparedStatement则支持参数化查询,更安全,防止SQL注入。例如: ```java ...
当Hibernate需要执行SQL操作时,它会根据所配置的方言,将ORM层的通用方法转化为具体数据库能理解的SQL语句。 "支持Access"表示Hibernate方言也涵盖了Microsoft Access数据库。尽管Access通常不被用作大型企业级...
Hibernate通过JDBC与数据库进行通信,执行SQL语句。在Hibernate中,JDBC驱动程序是必要的,以便应用程序能够连接到特定的数据库。例如,"mysql-connector-java-3.1.13-bin.jar"就是MySQL的JDBC驱动,用于连接到MySQL...
2. **提高性能**:预编译的SQL语句可以在数据库中缓存,多次执行相同结构但不同参数的查询时,可以显著提高执行效率。数据库只需解析一次SQL模板,然后用新参数替换问号即可。 3. **减少错误**:动态构建SQL字符串...
15. **Native SQL**: 当HQL无法满足需求时,Hibernate还支持执行原生的SQL查询,通过SQLQuery接口实现。 这些知识点构成了Hibernate 3.0 API的基础,通过熟练掌握和应用这些功能,开发者能够高效地进行数据库操作,...