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

[garima mishra]

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]

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]

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]

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