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
 
Similar Threads
Double Insert Oracle 11g / JPA / Swing
Facing the problem while reterving the resultsets in java from named query function in hibernate
Working Example of Hibernate Call to an Oracle Stored Proc.
bidirectional one to many mapping issues
org.hibernate.InvalidMappingException