File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
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
 
jQuery in Action, 2nd edition
 
subject: Executing HQL queries along with SQL Queries