Call Oracle Pl Sql With Hibernate

Calling Oracle Stored Procedure from Hibernate

From http://blog.unidev.com/index.php/2009/03/03/calling-oracle-stored-procedure-from-hibernate/

While calling Oracle stored procedure from Hibernate there is couple of rules that we need to follow. There can be only one return value and this must be a reference cursor. This return reference cusor must be the first and only out value for the stored procedure. It is required that stored procedure must return a reference cursor to be used from Hibernate.

Here is my stored procedure which return book name and ISBN number for a given branch and for a given author

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
as
  bookName varchar2(8);
  ISBN     number;
begin
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;
end;

HIbernate Mapping for return class and Named Query:

<?xml version=”1.0encoding=”utf-8?>
<!DOCTYPE hibernate-mapping PUBLIC-//Hibernate/Hibernate Mapping DTD 3.0//EN”
“http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd>
<hibernate-mapping>
<class name=”com.org.lib.LibraryDetails>
<id name=”ISBNtype=”long/>
<property name=”bookNametype=”string/>
</class>
 <sql-query name=”LIB_SPcallable=”true>
 <return class=”com.org.lib.LibraryDetails>
   <return-property name=”ISBNcolumn=”isbn_nbr/>
   <return-property name=”bookNamecolumn=”book_name/>
 </return>
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }
 </sql-query>
</hibernate-mapping>

Make sure you have used the correct database field name value for the column attribute for the return property mapping. You will get the following error if you don’t map the correct databse field name
could not execute query; bad SQL grammar [{ call SP_LIB_DTL(? , ?) }];
nested exception is java.sql.SQLException: Invalid column name
Here is the DAO implementation for executing query and to set the bind parameter values.

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
              {
                  Query q = session.getNamedQuery(LIB_SP);
                  q.setLong(branchCD”, branchCode.longValue());
                  q.setLong(authorCD”, authorCode.longValue());
                  return q.list();
              }
          });
      }

Official Hibernate3 Guide - 16.2.2. Using stored procedures for querying

From http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query

Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

CREATE OR REPLACE FUNCTION selectAllEmployments
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT EMPLOYEE, EMPLOYER,
STARTDATE, ENDDATE,
REGIONCODE, EID, VALUE, CURRENCY
FROM EMPLOYMENT;
RETURN st_cursor;
END;
To use this query in Hibernate you need to map it via a named query.

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="Employment">
<return-property name="employee" column="EMPLOYEE"/>
<return-property name="employer" column="EMPLOYER"/>
<return-property name="startDate" column="STARTDATE"/>
<return-property name="endDate" column="ENDDATE"/>
<return-property name="regionCode" column="REGIONCODE"/>
<return-property name="id" column="EID"/>
<return-property name="salary">
<return-column name="VALUE"/>
<return-column name="CURRENCY"/>
</return-property>
</return>
{ ? = call selectAllEmployments() }
</sql-query>
Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.
For Sybase or MS SQL server the following rules apply:

The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.
If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Does Hibernate close the reference cursor automatically?

http://stackoverflow.com/questions/9035361/does-hibernate-close-the-reference-cursor-automatically

I have defined a PL/SQL stored procedure which returns a SYS_REFCURSOR

CREATE OR REPLACE PROCEDURE findByName
(  res OUT SYS_REFCURSOR, 
   vName IN emp.name%type  ) AS
BEGIN
  OPEN res FOR
    SELECT * FROM emp WHERE name = vName;
END findByName;

Then, I map the returned cursor to a Hibernate entity.

....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByName", query = "call findByName(?, :vName)", callable = true, resultClass = Employee.class)
@Table(name = "EMP", schema = "WEBUI")
public class Employee implements java.io.Serializable {
....

This is a DAO method that calls the stored procedure.

....
public Employee findByName(final String name) {
    Session session = factory.getSession();
    Query query = session.getNamedQuery("findByName");
    query.setString("vName", name);
    return (Employee) query.uniqueResult();      
}
....

This is what I would do if I were calling the stored procedure from PL/SQL code.

DECLARE
    emp_cursor  SYS_REFCURSOR;
    emp_rec emp_cursor%ROWTYPE;
BEGIN
    findByName 
    (  res => emp_cursor,
       vName => 'Timothy Jones');
    FETCH emp_cursor
        INTO emp_rec;
    DBMS_OUTPUT.PUT_LINE(emp_rec.name);
    CLOSE emp_cursor; -- close the cursor
END;

I would like to highlight the fact that it is necessary to close the emp_cursor, somehow. Otherwise, memory leak would have been caused.

My question is: How does Hibernate handle this? Does it automatically close the cursor after the Employee object has been retrieved or does the programmer have to close it manually?

Thanks in advance.

Answer

You have no way to close this cursor. Hibernate reference wants you to return a cursor when using a stored procedure for a query.

http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/querysql.html#sp_query

My guess is that hibernate translates the returned cursor into a ResultSet used to iterate over the query results. It would be a serious hibernate bug if the ResultSet remained opened. Anyway this ResultSet is in the worst case closed when the database connection pool recycles/closes the jdbc connection/statement.

session.doWork

From http://stackoverflow.com/questions/1703351/how-to-call-a-oracle-function-from-hibernate-with-return-parameter

Hibernate Session provides a doWork() method that gives you direct access to java.sql.Connection. You can then create and use java.sql.CallableStatement to execute your function:
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
call.setLong(2, id);
call.setLong(3, transId);
call.execute();
int result = call.getInt(1); // propagate this back to enclosing class
}
});

ORACLE PlSQL、Jdbc调用存储过程
From http://hi.baidu.com/ykscar/blog/item/ea07dc05a4e4136d020881ff.html

1 存储过程任务
用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块,可以永久的保存在数据库中以供其他程序调用。

2 存储过程的参数模式

存储过程的参数特性:
IN类型的参数 OUT类型的参数 IN-OUT类型的参数
值被 传递给子程序 返回给调用环境 传递给子程序
返回给调用环境
参数形式 常量 未初始化的变量 初始化的变量
使用时 默认类型 必须明确指定 必须明确指定

3 无参数存储过程的使用:
CREATE OR REPLACE PROCEDURE 过程名 [(parameter,…)]
IS
定义变量
Begin
Plsql程序
End;

例:创建一个存储过程,用于向数据库中插入一条记录。

第一步:创建
CREATE OR REPLACE PROCEDURE pro_1
IS
Begin
insert into person values (11,'aa','aav');
End;

第二步:在sql*plus中执行该过程
exec pro_1;

第三步:通过JDBC使用存储过程。
private Connection conn = null;
private ResultSet rs = null;
private CallableStatement state = null;
//调用一个无参数的存储过程
public void testPro()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_1}");
state.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

4 带有IN类型参数的存储过程的使用。
例:创建一个存储过程,用于向数据库中插入一条记录。
第一步:创建
CREATE OR REPLACE PROCEDURE pro_2(id number,name varchar2,email varchar2)
IS
Begin
insert into person values (id,name,email);
End;
第二步:在sql*plus中执行该过程
exec pro_2(12,'aaa','aaa');

第三步:通过JDBC使用存储过程。
//使用一个带有 IN 类型参数的存储过程
public void testPro_in(int id,String name,String email)
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_2(?,?,?)}");
state.setLong(1, id);
state.setString(2, name);
state.setString(3, email);
state.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

5 带有out类型参数的存储过程的使用。
例:创建一个存储过程,用于返回数据库中的Person表的总行数。

第一步:创建
CREATE OR REPLACE PROCEDURE pro_3(num out number)
IS
mynum number;
Begin
select count(*) into mynum from person;
num := mynum;
End;
或者
CREATE OR REPLACE PROCEDURE pro_3(num out number)
IS
Begin
select count(*) into num from person;
End;

第二步:在sql*plus中执行该过程
declare
a number;
begin
pro_3(a);
dbms_output.put_line(a);
end;

第三步:通过JDBC使用存储过程。
public void testPro_out()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_3(?)}");
state.registerOutParameter(1, Types.NUMERIC);
state.execute();
int num = state.getInt(1);
System.out.println(num);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

6 带有in-out类型参数的存储过程的使用。
创建:
CREATE OR REPLACE PROCEDURE pro_4(num in out number)
IS
a number := 100;
Begin
num := a*num;
End;

在sql*plus中执行该过程
declare
a number := 12;
begin
pro_4(a);
dbms_output.put_line(a);
end;

7 Data Source

javax.sql.DataSource ds;
Connection dbConnection = null;
String statement=null;
… <some other stuff>
try {
ds=txManager.getDataSource();
dbConnection =ds.getConnection();
dbConnection.setAutoCommit(false);
log.info("Connection to database is established");
}
catch (Exception ex) {
ex.printStackTrace();
Thread.yield();
}
try {
statement = "{ ? = call my_function(";
for (int istmt=0; istmt<2; ++istmt)
statement = statement + "?,";
statement=statement+"?) }";
log.info("Statement to be executed:");
log.info(statement);
log.debug("creating CallableStatement");
CallableStatement proc = dbConnection.prepareCall(statement);
log.debug("Creating return value");
proc.registerOutParameter(1,OracleTypes.CURSOR);
log.debug("Loading the first function parameter");
proc.setInt(2, 1);
log.debug("Loading the second and third function parameter");
proc.setString(3, "Days");
proc.setString(4, "DESC");
proc.execute();
log.info("Query completed");
ResultSet rs=(ResultSet)proc.getObject(1);
}