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

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

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!

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!

I think you might not quite have gotten the hang of code tags yet ;)