[ajug-members] JTables with large amounts of data

[Smith, Curt H.]

Parna,

Here's a purely SQL based way of paging through a large result set.  The SQL
given here is from a previous job.  

The general gist is a triple nested select where the inner select will do
order by etc to get the whole cursur ordered, then the next two selects will
grabe chunks of that whole cursor based on which page of data you're
wanting.

All you need to do is store pageNum in your client's context and pass it to
the select along with the pageSize which may be a constant.

---- example sql edit to suit your schema =----
SELECT * 
FROM (SELECT rn,
	BED_SEQ_NO, 
       BedLevels,
	OCCUPIED_BY_UNO, 
       BED_BUNK_POS_DESC, 
       BED_SECURITY_DESC, 
       BED_DESC,
	RECORD_VERSION
FROM (
SELECT rownum rn,
	BED_SEQ_NO, 
       BedLevels,
	OCCUPIED_BY_UNO, 
       BED_BUNK_POS_DESC, 
       BED_SECURITY_DESC, 
       BED_DESC,
	RECORD_VERSION
FROM (SELECT JmovBeds.BED_SEQ_NO, 
       JmovBeds.LEVEL1||'-'||JmovBeds.LEVEL2||'-'||
       JmovBeds.LEVEL3||'-'||JmovBeds.LEVEL4 BedLevels,
	JmovBeds.OCCUPIED_BY_UNO, 
       JmovBedBunkPosRef.BED_BUNK_POS_DESC, 
       JmovBedSecurityRef.BED_SECURITY_DESC, 
       JmovBedTypeRef.BED_DESC,
	JmovBeds.RECORD_VERSION
FROM JMOV_BEDS JmovBeds,
     JMOV_BED_BUNK_POS_REF    	  JmovBedBunkPosRef,
     JMOV_BED_SECURITY_REF  JmovBedSecurityRef,  
     JMOV_BED_TYPE_REF  JmovBedTypeRef	 
WHERE JmovBeds.OCCUPIED_BY_UNO IS NULL
AND    JmovBeds.SCRIBE_CODE = :1
AND   JmovBeds.BED_BUNK_POS_CD = JmovBedBunkPosRef.BED_BUNK_POS_CD
AND    JmovBeds.BED_SECURITY_CD =  JmovBedSecurityRef.BED_SECURITY_CD
AND JmovBeds.BED_TYPE_CD = JmovBedTypeRef.BED_TYPE_CD
AND JmovBeds.DISABLE_DT is NULL
ORDER BY BedLevels
)
WHERE rownum <= (:2 * :3)
)
WHERE rn between (((:4 - 1) * :5) + 1) and (:6 * :7)
)

Oracle supports numbered bind params, for no real useful reason besides
helping the coding of the setString(N being less error prone then counting ?
marks.

Param:  :1 is your application specific variables.  Add more of course but
will push down the following paging control parameters.

Parms:   :2, :4, :6 are the page number [1,2,3]  Pass this param as a hidden
variable on your JSP or HttpSession.

Parms:  :3, :5, :7  are the page size (IE 10 lines per screen)


Curt Smith
csmith at javadepot.com
(h) 404-294-6686
(w) 404-499-5242