Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem using hibernate and stored procedures for reading portion of data from database

 
poornima komanduri
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi...
I am trying to read one column of the table using hibernate and stored procedures.But I got the following error

Hibernate: {call sample(?,?)}
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:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
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:152)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
at TestDAO.main(TestDAO.java:20)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:4383)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:667)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1374)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 8 more



My hbm file is...


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="Building_DetailsBean" table="building_details" lazy="false">
<id name="building_ID" type="string" column="buildingID" >
</id>

<property column="bandWidth" name="bandWidth" type="int"/>
</class>

<sql-query name="sample" callable="true">
<return alias="buildingDetails" class="Building_DetailsBean">

<return-property name="bandWidth" column="bandWidth"/>
</return>
{call sample(?,:buildingID)}
</sql-query>
</hibernate-mapping>



Stored procedure is...

CREATE OR REPLACE PROCEDURE sample(bdetails_cur OUT sys_refcursor,bid IN varchar2)
AS
BEGIN
OPEN bdetails_cur FOR
SELECT bandwidth FROM building_details WHERE buildingID=bid;
END;


java code is......

Integer i=(Integer)session.getNamedQuery("sample").setParameter("buildingID","LONWTS01").uniqueResult();

I have been struggling for this....

Could anyone please help me out to find the soulution for this...

Thanks in advance....

 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't cast a Ref cursor to an Integer.

In your mapping it looks like you are mapping the ref cursor to a Building_DetailsBean object. So if that is correct mapping, then you would have to cast your ref cursor to a Building_DetailsBean object in Java.

Hope that helps

Mark
 
poornima komanduri
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply....

I have tried as you told even then its throwing the same exception

But I think that cannot be done because I am reading only one value i.e bandwidth into the cursor.Then how can I convert the cursor to Building_DetailsBean ?


Its working if I donot use stored procedure i.e. with the following code I could retrieve bandwidth

Integer i=(Integer)session.createQuery("select bd.bandWidth from Building_DetailsBean as bd where bd.building_ID='xyz01'").list().get(0);

But I donot know why I couldnot do it using stored procedures...
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah, the refcursor has to be a full row that is mapped to your class. You can't just select one field and try a projection, it isn't allowed. The big reason is that before 10G, Oracle didn't fully implement the JDBC spec, which cause Hibernate to have to have these conditions.

Mark
 
poornima komanduri
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh... that means I cannot read one field value using cursor and stored procedures in hibernate....

I have tried it using out parameter instead of cursor But I could not find a way to register out parameter.Is there any way to register an out parameter in hibernate??

will the performance of hibernate be less than JDBC if our requirement is to read only one field value??
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
poornima komanduri wrote:Oh... that means I cannot read one field value using cursor and stored procedures in hibernate....

I have tried it using out parameter instead of cursor But I could not find a way to register out parameter.Is there any way to register an out parameter in hibernate??

will the performance of hibernate be less than JDBC if our requirement is to read only one field value??


No you cannot.

The rules are

1) Only one out parameter and it must be a refcursor of the table
2) The out parameter must be the first parameter

The problem is because Oracle's drivers for version 9 and below did not fully implement the JDBC specification.

Mark
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic