wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Calling stored procedure in hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Calling stored procedure in hibernate" Watch "Calling stored procedure in hibernate" New topic
Author

Calling stored procedure in hibernate

ranjani ra
Greenhorn

Joined: Feb 13, 2013
Posts: 3

Hi all,

I am new to hibernate. I need to call a stored procedure from hibernate. But I am unable to call the stored proc. Given below is the code.

Table: CREATE TABLE DSL_MEMBER(ID INT NOT NULL, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20), STATUS VARCHAR2(2), SALARY INT); ID is the primary key.

Given below is the POJO Dsl_member with annotated table and column names
package com;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import org.hibernate.annotations.NamedNativeQuery;

@Entity
@Table(name="DSL_MEMBER")
public class Dsl_member implements Serializable{

@Id
@Column(name="ID")
private int id;

@Column(name="FIRST_NAME")
private String firstName;

@Column(name="LAST_NAME")
private String lastName;

@Column(name="STATUS")
private String status;

@Column(name="SALARY")
private int salary;
//getters and setters, cosntructors

My hibernate-cfg.xml:
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
<property name="connection.driver_class">oracle.jdbc.OracleDriver</property>
<!-- url, userid and pwd mappings for my db -->
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="current_session_context_class">thread</property>
<property name="hibernate.show_sql">false</property>
<mapping class="com.Dsl_member"></mapping>
</session-factory>
</hibernate-configuration>

Stored proc that I have to access:
create or replace procedure DSL_SELECTALL_MEMBER( p_cursor out SYS_REFCURSOR) as
begin
open p_cursor for select mem.first_name, mem.last_name, mem.salary from dsl_member mem;
end;

Test class:
In JDBC, I know that I can call this proc as below:
CallableStatement cs = conn.prepareCall("{call dsl_selectall_member(?)}");
cs.registerOutParameter(1,OracleTypes.CURSOR);
rs = cs.executeQuery();

But in hibernate, is there an equivalent of cs.registeroutparamater??

When I try to call the above proc as
Query query = session.createSQLQuery("call dsl_selectall_member(?)").addEntity(Dsl_member.class).setParameter("?",OracleTypes.CURSOR);
List result = query.list();

When I try calling like above, I get the below exception:
could not locate named parameter [?]
org.hibernate.QueryParameterException: could not locate named parameter [?]

Is there any other way to call a stored proc from hibernate?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling stored procedure in hibernate