aspose file tools*
The moose likes Object Relational Mapping and the fly likes problem using hibernate and stored procedures for reading portion of data from database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "problem using hibernate and stored procedures for reading portion of data from database" Watch "problem using hibernate and stored procedures for reading portion of data from database" New topic
Author

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

poornima komanduri
Greenhorn

Joined: Dec 01, 2009
Posts: 3
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

Joined: Feb 05, 2001
Posts: 17260
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
poornima komanduri
Greenhorn

Joined: Dec 01, 2009
Posts: 3
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

Joined: Feb 05, 2001
Posts: 17260
    
    6

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

Joined: Dec 01, 2009
Posts: 3
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

Joined: Feb 05, 2001
Posts: 17260
    
    6

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
 
Don't get me started about those stupid light bulbs.
 
subject: problem using hibernate and stored procedures for reading portion of data from database