posted 12 years ago
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?