• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Calling stored procedure in hibernate

 
Greenhorn
Posts: 3
Hibernate Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Arthur, where are your pants? Check under this tiny ad.
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic