i am trying to return pagin records. without using the
q.setFirstResult(startPosition);
q.setMaxResults(nItems );

this is mainly because i think hibernate retrieves all records and then returns only teh number of records requested.
this will slow down the process because this inneccessarily gets all records and then gives only teh subset.
if i am wrong please correct me.

to directtly access only teh required records i am trying the follwoing query in hibernate.

select *
from
( select a.*, rownum rnum
from
(select order_date_time, order_type, original_price, original_qty
FROM HSOrderHistory orderhistory, Trader trader
WHERE orderhistory.trader_ID = trader.trader_ID
AND orderhistory.action= 'RECEIVED'
ORDER BY order_date_time desc ) a
where rownum <= MAX_LIMIT_ROWS)
where rnum >= MIN_LIMIT_ROWS

please help!!

thanks
kampli


Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

using the view V$SQLTEXT i have verified that hibernate retrieves only bvalues
which are concerned with the page.

example query obtained from the server view :


select * from ( select row_.*, rownum rownum_ from ( select hsor
derhis0_.order_history_id as order1_51_0_, trader1_.trader_id as
trader1_50_1_, hsorderhis0_.base_order_id as base2_51_0_, hsord
erhis0_.trader_id as trader3_51_0_, hsorderhis0_.specie_id as sp
ecie4_51_0_, hsorderhis0_.position_account_id as position5_51_0_
, hsorderhis0_.actual_display_name as actual6_51_0_, hsorderhis0
_.name as name51_0_, hsorderhis0_.action as action51_0_, hsorder
his0_.order_date_time as order9_51_0_, hsorderhis0_.buy_sell as
buy10_51_0_, hsorderhis0_.order_type as order11_51_0_, hsorderhi
s0_.original_qty as original12_51_0_, hsorderhis0_.original_pric
e as original13_51_0_, hsorderhis0_.delta_date_time as delta14_5
1_0_, hsorderhis0_.business_date_disp as business15_51_0_, hsord
erhis0_.trade_qty as trade16_51_0_, hsorderhis0_.trade_price as
trade17_51_0_, hsorderhis0_.account_open_qty as account18_51_0_,
hsorderhis0_.source_data as source19_51_0_, hsorderhis0_.busine
ss_date as business20_51_0_, hsorderhis0_.source as source51_0_,
hsorderhis0_.delta_id as delta22_51_0_, hsorderhis0_.create_ts
as create23_51_0_, trader1_.user_name as user2_50_1_, trader1_.s
ettlement_account_id as settlement3_50_1_, trader1_.tax_id as ta
x4_50_1_, trader1_.create_ts as create5_50_1_, trader1_.update_t
s as update6_50_1_, trader1_.acct_nbr as acct7_50_1_, trader1_.d
isplay_name as display8_50_1_, trader1_.cftc_code as cftc9_50_1_
, trader1_.nfa_id as nfa10_50_1_, trader1_.approval_date as appr
oval11_50_1_, trader1_.registration_date as registr12_50_1_, tra
der1_.effective_date as effective13_50_1_, trader1_.contactinfo_
id as contact14_50_1_, trader1_.user_password as user15_50_1_, t
rader1_.last_login as last16_50_1_, trader1_.expire_date as expi
re17_50_1_, trader1_.invalid_password_count as invalid18_50_1_,
trader1_.grace_login_count as grace19_50_1_, trader1_.clue_key a
s clue20_50_1_, trader1_.user_status_code as user21_50_1_, trade
r1_.clue_value as clue22_50_1_, trader1_.perm_set_id as perm23_5
0_1_ from hsorderhistory hsorderhis0_, hs_trader_v trader1_ wher
e (hsorderhis0_.trader_id=trader1_.trader_id )AND(hsorderhis0_.a
ction='kampli00000000' ) order by hsorderhis0_.order_date_time
desc ) row_ ) where rownum_ <= :1 and rownum_ > :2


this is exactly the same query mentioned in the previously

this is auto generated and executed by hibernate for

q.setFirstResult(startPosition) start from the point mentioned.
i.e if we give 20 will return records from the 20th row.

q.setMaxResults(nItems); this limits the max numbers of rows from the
start position.

hence we dont need to worry about it and confidently use this feature