jayendra bhatt jayendra bhatt - 3 years ago 83
Java Question

Unable to call Stored procedure from hibernate

Please try to answer the problem in the below code for calling a stored procedure in ORACLE from hibernate . i am using a named query in hbm file to be called from the testclient.java .

Hibernate3.2.1 core file is being used.
My stored procedure:

create or replace procedure get_empdetails2(mycursor out sys_refcursor, cond in varchar2)

as
begin
open mycursor for

select eb.first_name,eb.last_name from employee3 eb where eb.email like cond ;

end;


Employee.java:

public class Employee{

public Employee()
{
}
private int eid;
private String fname,lname,email;
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getFname() {
return fname;
}
public void setFname(String fname) {
this.fname = fname;
}
public String getLname() {
return lname;
}
public void setLname(String lname) {
this.lname = lname;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}

}


table employee3:

Column Name Data Type Nullable Default Primary Key
EID NUMBER Yes - -
FIRST_NAME VARCHAR2(20) Yes - -
LAST_NAME VARCHAR2(20) Yes - -
EMAIL VARCHAR2(20) Yes - -


hibernate.cfg:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>

<session-factory>
<property name="hbm2ddl.auto">update</property>
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name='connection.url'>jdbc:oracle:thin:@localhost:1521/xe</property>
<property name='connection.username'>system</property>
<property name='connection.password'>oracle123</property>
<property name='connection.driver_class'>oracle.jdbc.driver.OracleDriver</property>
<property name="show_sql">true</property>
<mapping resource="employee3.hbm.xml"/>
</session-factory>

</hibernate-configuration>


Testclient.java

import java.util.*;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.Criteria;
public class TestClient
{
private static SessionFactory factory;
public static void main(String[] args)
{

System.out.println("--------------done-----------");
Configuration cfg= new Configuration();
System.out.println("--------------got cfg object-----------");
cfg = cfg.configure("hibernate.cfg.xml");
System.out.println("--------------hbm loaded into cfg-----------");
SessionFactory sf= cfg.buildSessionFactory();

System.out.println("--------------done-----------");


System.out.println("--------------getting session-----------");
Session ses =sf.openSession();

Transaction tx = ses.beginTransaction();
System.out.println("--------------got tx object-----------");
Employee eb =null;

SQLQuery q1=(SQLQuery)ses.getNamedQuery("test1");

//q1.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);


System.out.println("--------------q1 processing-----------");
//q1.setInteger(0,0);
q1.setString(0,"v%");

//q1.setParameter(0, "%a%");
System.out.println("--------------q1 processing-----------");
List res=q1.list();
System.out.println(res);

tx.commit();
ses.close();

System.out.println("--------------closing connection-----------");
sf.close();
System.out.println("--------------connection closed-----------");



}}


employee3.hbm:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">


<hibernate-mapping>
<class name="Employee" table="employee3">
<id name="eid" column="eid"/>
<property name="fname" column="first_name"/>
<property name="lname" column="last_name"/>
<property name="email" column="email"/>
</class>


<sql-query name="test1" callable="true">
<return alias ="test1" class ="Employee">
<return-property name="fname" column="FIRST_NAME"/>
<return-property name="lname" column="LAST_NAME"/>
</return>

{call get_empdetails2(?,?)}

</sql-query>

</hibernate-mapping>


In the call get_empdetails(?,?) , the first ? is used for output mycursor which as per the hibernate doc must be the first parameter and second ? is the input parameter for the cond variable in procedure.
My output error is :

Oct 10, 2014 2:38:54 PM org.hibernate.tool.hbm2ddl.TableMetadata <init>
INFO: indexes: [] Oct 10, 2014 2:38:54 PM

org.hibernate.tool.hbm2ddl.SchemaUpdateexecuteINFO:schema update complete
--------------done-----------
--------------getting session-----------
--------------got tx object-----------
--------------q1 processing-----------
--------------q1 processing-----------
Hibernate: {call get_empdetails2(?,?)}
Oct 10, 2014 2:38:54 PM org.hibernate.type.NullableType nullSafeGet
INFO: **could not read column value from result set: eid0_0_; Invalid column name**
Oct 10, 2014 2:38:54 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 17006, SQLState: null
Oct 10, 2014 2:38:54 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Invalid column name Exception in thread "main"

org.hibernate.exception.GenericJDBCException: could not execute query at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at TestClient.main(TestClient.java:44)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)


As present in the output , i am getting this - could not read column . whats wrong in the configuration?

Thanks

Answer Source

Hibernate is trying to map whole table from return result of Stored Procedure. You are returning eb.first_name,eb.last_name from Stored Procedure.

Possible Solution 1:

Select all field in Stored Procedure SELECT query like select * from employee3 eb where eb.email like cond ;

If solution 1 works for you then use it but if you want to return only two fields from stored procedure I'd suggest you second solution. As I don't know how we can execute named query which returns only selected fields.

Possible Solution 2:

Use SQLQuery. I used it with MySQL You can try for Oracle. Hope it will work.

Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "test");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
List customResult = callStoredProcedure_MYSQL.list();
if (customResult != null && !customResult.isEmpty()) {
 Object[] obj = customResult.get(0);
 System.out.println(obj[0]);
 System.out.println(obj[1]);            
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download