This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Executing HQL queries along with SQL Queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Executing HQL queries along with SQL Queries" Watch "Executing HQL queries along with SQL Queries" New topic
Author

Executing HQL queries along with SQL Queries

Ayyappan Selvaraj
Ranch Hand

Joined: Sep 20, 2004
Posts: 63
Friends, I have a requirement in my project like: I have a Stored Procedue which will return a set of tables and considering the result set as a table , I need to execute HQL query on it. How can this be done.

Can be better understood with the query which works fine in the SQL IDEs.
select * from (
select distinct PARTY_ID, ACCT_NO_1 , ROW_NUMBER() OVER (ORDER BY PARTY_ID) R from table(pax.p2a_services_pkg.get_by_cust_loc_func('G','327888475','AML'))
)where R between 10 and 18

pax.p2a_services_pkg.get_by_cust_loc_func('G','327888475','AML')) This is the stored procedure call and the remaining is the SQL query on the results it gives.

I want to know how this query can be executed using HQL and named sql query. Advice please...
saranga rao
Ranch Hand

Joined: Apr 24, 2007
Posts: 49
Hi,

Please Go through the getNamedQuery() for this.....

which is very familiar for the stored procedure ...........only


regards
saranga
saranga rao
Ranch Hand

Joined: Apr 24, 2007
Posts: 49
Hi

feel free to ask the code and say about the query you need ..........man



regards
saranga
Ayyappan Selvaraj
Ranch Hand

Joined: Sep 20, 2004
Posts: 63
Hi thanks for replying.. I have got the code for executing simple named query from the javaranach forum only... now I require that an HQL query shud be run on the result sets that would be returned from the stored procedure..
saranga rao
Ranch Hand

Joined: Apr 24, 2007
Posts: 49
Hi,

check the link below.............


http://swik.net/Hibernate/Using+SQL+From+Within+Hibernate


or else give some more details so that I can refer to this...

Regards
saranga
Ayyappan Selvaraj
Ranch Hand

Joined: Sep 20, 2004
Posts: 63
Hey this is for running simple SQL queries. I want code for running Stored procedure..
saranga rao
Ranch Hand

Joined: Apr 24, 2007
Posts: 49
Hi,

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html here
search for getNamedQuery() You will find the details.

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="Employment">
<return-property name="employee" column="EMPLOYEE"/>
<return-property name="employer" column="EMPLOYER"/>
<return-property name="startDate" column="STARTDATE"/>
<return-property name="endDate" column="ENDDATE"/>
<return-property name="regionCode" column="REGIONCODE"/>
<return-property name="id" column="EID"/>
<return-property name="salary">
<return-column name="VALUE"/>
<return-column name="CURRENCY"/>
</return-property>
</return>
{ ? = call selectAllEmployments() }
</sql-query>



the named written like :

List people = sess.getNamedQuery("selectAllEmployees")
.setString("endDate", endDate)
.setMaxResults(50)
.list();


This will return the list you values
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Executing HQL queries along with SQL Queries
 
Similar Threads
Stored Procedure inside HQL query?
native SQL update in Hibernate
Help with Resultset
questions about Hibernate
caching for SQL queries via Hibernate not HQL