aspose file tools*
The moose likes Object Relational Mapping and the fly likes Facing the problem while reterving the resultsets in java from named query function in hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Facing the problem while reterving the resultsets in java from named query function in hibernate" Watch "Facing the problem while reterving the resultsets in java from named query function in hibernate" New topic
Author

Facing the problem while reterving the resultsets in java from named query function in hibernate

garima mishra
Greenhorn

Joined: May 17, 2010
Posts: 2
Hi,

I am facing the problem while executing the calling of funtion through named query.

My java bean class file like this


package org.com.test;

public class Contact {
private String firstName;
private String lastName;
private String email;
private long contact_id;

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public long getContact_id() {
return contact_id;
}

public void setContact_id(long contact_id) {
this.contact_id = contact_id;
}






}


and hbm file is like this

<?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="org.com.test.Contact" table="TEST_CONTACT">
<id name="contact_id" type="long" column="CONTACT_ID">
<generator class="assigned" />
</id>

<property name="firstName">
<column name="FIRST_NAME" />
</property>
<property name="lastName">
<column name="LAST_NAME" />
</property>
<property name="email">
<column name="EMAIL_ID" />
</property>
</class>

<query name="HQLtest"><![CDATA[ select contact.firstName from Contact contact ]]>
</query>

<sql-query name="HQLtestFunction" callable="true">
<return class="org.com.test.Contact" alias="contact">
<return-property name="contact.firstName" column="First_name" />
</return>
{ ?=call Fn_ViewContact() }
</sql-query>


</hibernate-mapping>

i am using the oracle database my function is as follows:


CREATE OR REPLACE FUNCTION Fn_ViewContact

RETURN SYS_REFCURSOR

AS

st_cursor SYS_REFCURSOR;

BEGIN

OPEN st_cursor FOR


SELECT First_name from test_contact ;

RETURN st_cursor;

END;


i am calling this named query as in java like this

Query queryFunc = session.getNamedQuery("HQLtestFunction");
List FunctionResult = queryFunc.list();
displayObjectList(FunctionResult);

My problem is that when i am ruuning the queryFun.list();

I am getting the error

Hibernate: { ?=call Fn_ViewContact() }
43281 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 17006, SQLState: null
43281 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Invalid column name
could not execute query


please help me regarding this urgently.

Many many thanks in advance.

Best Regards
Garima
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

Please UseCodeTags when posting code or configuration. Unformatted code and configuration is unnecessarily difficult to read. You can edit your post by using the button.

And welcome to JavaRanch!
garima mishra
Greenhorn

Joined: May 17, 2010
Posts: 2
Hi David,

when i am seeting the return parameters all corresponding the Contact Class members
and in procedure selecting all the columns of the table.

I ma getting the result But when i am trying to get only selected member of Contact class I am getting the "Invalid Column error."

Is there any limitation in hibernate that we can not fetch the selective columns from the database.
My oracle procedure is like this :


CREATE OR REPLACE FUNCTION selectAllEmployments

RETURN SYS_REFCURSOR

AS

st_cursor SYS_REFCURSOR;

BEGIN

OPEN st_cursor FOR

SELECT CONTACT_ID, First_name,

LAST_NAME, EMAIL_ID

FROM test_contact;

RETURN st_cursor;

END;

My contact.hbm.xml file is below

<?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="org.com.test.Contact" table="TEST_CONTACT">
<id name="contact_id" type="long" column="CONTACT_ID">
<generator class="assigned" />
</id>

<property name="firstName">
<column name="FIRST_NAME" />
</property>
<property name="lastName">
<column name="LAST_NAME" />
</property>
<property name="email">
<column name="EMAIL_ID" />
</property>
</class>

<query name="HQLtest"><![CDATA[ select contact.firstName from Contact contact ]]>
</query>

<sql-query name="HQLtestFunction" callable="true">
<return class="org.com.test.Contact" alias="contact">
<return-property name="contact_id" column="CONTACT_ID" />
<return-property name="firstName" column="First_name" />
<return-property name="lastName" column="LAST_NAME" />
<return-property name="email" column="EMAIL_ID" />
</return>
{ ?=call selectAllEmployments() }
</sql-query>


</hibernate-mapping>

please let me know is this hibernate limitation while calling the procedure or function or any other way is possible.
Please help me regarding this.

Manny many thanks in advanace!
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

I think you might not quite have gotten the hang of code tags yet ;)
 
Don't get me started about those stupid light bulbs.
 
subject: Facing the problem while reterving the resultsets in java from named query function in hibernate