Ok guys I'm giving you all a gift.... I had the same problem understanding
how PetStore does it and after reading much documentation on the design
pattern and various other people's implementation of said pattern here is
my interpretation of the Page-by-Page Iterator pattern...
This is actually still being used in production for a project developed
from three years ago so I know it works... Don't give me a hard time about
using a Vector though... If you want to use an ArrayList instead by all
means feel free to change it!
Debug is a class I wrote for outputting values while developing/unit
testing so implement your own such as Log4J or whatever. I'ved also removed
the bulk of the SQL statement as it is somewhat proprietary but I think you
will get the drift...
/**
* This method retrieves the TransactionList data for a client given
the investment number,
* fund number list, start date and end date for the reporting period.
* It uses the Page-by-Page Iterator pattern to display the result set
in manageable chunks
* as we could have many years of historical transaction data stored in
the database.
* This method will initially be used in XXXXXXX Phase 2 and Phase 3
for XXXXXXXX
* XML Account Inquiry system.<BR><BR>
*
* NOTE: Some parameters are optional in this method however there are
some guidelines to follow:<BR>
* 1) p_strESGFundNumList can be an emtpy string but not null<BR>
* 2) p_strLang can be "E", "F", "e", "f" or an empty string but not
null<BR>
* 3) p_strSort can be an empty string but not null
*
* @param long p_lInvNum - investment number to return transactions for
* @param String p_strESGFundNumList - ESG fund number list in the
format
* "esg_fund_num1',
'esg_fund_num2', 'esg_fund_num3', 'etc"
* @param String p_strStartDate - date string in the format YYYY/MM/DD
* @param String p_strEndDate - date string in the format YYYY/MM/DD
* @param String p_strLang - language code (values E, F)
* @param String p_strSort - prefered sort specified by the user
* @param int p_nStartRow - start row of the result set, greater than
or equal to 1
* @param int p_nMaxRows - maximum number of rows to return, greater
than or equal to 1
* @return Vector transaction list
*/
public Vector retrieveAll(long p_lInvNum, String p_strESGFundNumList,
String p_strStartDate, String p_strEndDate,
String p_strLang, String p_strSort,
int p_nStartRow, int p_nMaxRows)
throws SQLException, Exception {
String strLangCd;
String sql;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int nNumRows = 0;
// Create our transaction list vector object
Vector vData = new Vector();
Debug.println("in TransactionListDB retrieveAll(long p_lInvNum,
String p_strESGFundNumList, " +
"String
p_strStartDate, String p_strEndDate, " +
"String p_strLang,
String p_strSort, " +
"int p_nStartRow,
int p_nMaxRows)");
Debug.println("TransactionListDB: p_nStartRow -> " + p_nStartRow);
Debug.println("TransactionListDB: p_nMaxRows -> " + p_nMaxRows);
// Get the language code
if (p_strLang.equalsIgnoreCase("F")) {
strLangCd = "FRE";
} else { // default to English
strLangCd = "ENG";
}
Debug.println("TransactionListDB: strLangCd is " + strLangCd);
try {
// Get a java.sql.Connection object (conn) from the method call
to DBAccess.newConnection()
conn = newConnection();
Debug.println("TransactionListDB: after getting the oracle
connection");
stmt = conn.createStatement();
// GHB - 2001/08/30 - OK for explain plan
sql = "SELECT <some fields> " +
"FROM <some tables> " +
"WHERE FO.INV_NUM = " + p_lInvNum +
" AND ((FO.FO_SETTLEMENT_DATE >= TO_DATE('" +
p_strStartDate + "', 'yyyy/mm/dd') " +
"AND FO.FO_SETTLEMENT_DATE <= TO_DATE('" +
p_strEndDate + "', 'yyyy/mm/dd')) " +
"OR (FO.FO_SETTLEMENT_DATE IS NULL " +
"AND FO.FO_PROCESS_STATUS_CD != 'CAN'))";
if (!p_strESGFundNumList.equals("")) {
sql += "AND fn.esg_fund_num IN ('" + p_strESGFundNumList +
"') ";
}
// Are we sorting the records based on what column the user has
clicked on?
if (!p_strSort.equals("")) {
// Find out the prefered sort specified by the user
if (p_strSort.equals("tradedate")) {
sql += "ORDER BY 2 ";
} else if (p_strSort.equals("fundname")) {
sql += "ORDER BY 7 ";
} else if (p_strSort.equals("activity")) {
sql += "ORDER BY 8 ";
} else if (p_strSort.equals("status")) {
sql += "ORDER BY 4 ";
}
// Are we sorting by ascending or descending order this
time?
if (m_bSortAsc == true) {
sql += "ASC";
m_bSortAsc = false;
} else {
sql += "DESC";
m_bSortAsc = true;
}
} else { // default sort
sql += "ORDER BY xxxxxxxxxxxx DESC";
}
Debug.println("["+sql+"]");
long nStart = 0;
if ( (Debug.DEBUG_BUILD == true) ||
(Debug.FUNDCOM_TRACE == true) ) {
// Capture our start time
nStart = System.currentTimeMillis();
}
rs = stmt.executeQuery(sql);
if ( (Debug.DEBUG_BUILD == true) ||
(Debug.FUNDCOM_TRACE == true) ) {
long nFinish = System.currentTimeMillis();
// Log the query execution time to the the server output
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd
hh:mm:ss a");
Date dteStart = new Date(nStart);
Date dteFinish = new Date(nFinish);
System.err.println();
System.err.println("Transaction Summary Start Time -> " +
sdf.format(dteStart) + ", " + nStart + " milliseconds");
System.err.println("Transaction Summary Finish Time -> " +
sdf.format(dteFinish) + ", " + nFinish + " milliseconds");
System.err.println("Transaction Summary Elasped Time -> " +
(nFinish - nStart)/1000.0000 + " seconds");
System.err.println();
}
Debug.println("TransactionListDB: after executing the query
from XXXXXXX");
if (rs == null) {
System.err.println("rs is null");
} else {
// Iterate through the result set
while (rs.next()) {
nNumRows++;
if ( (nNumRows >= p_nStartRow) && (nNumRows <
p_nStartRow + p_nMaxRows) ) {
// Populate our transaction list objects
TransactionList transactionlist = new
TransactionList(rs.getString(3), rs.getString(5),
rs.getInt(6), rs.getString(7),
rs.getString(8),
rs.getBigDecimal(9, 2), rs.getBigDecimal(10, 2),
rs.getBigDecimal(11, 2), rs.getBigDecimal(12, 4),
rs.getBigDecimal(13, 4), rs.getBigDecimal(14, 4),
rs.getString(15), rs.getString(17),
rs.getString(22),
rs.getLong(1), rs.getString(19),
rs.getString(23), rs.getString(24),
rs.getString(25));
vData.addElement(transactionlist);
}
}
}
m_nNumOfRows = nNumRows;
Debug.println("TransactionListDB: vData.size() -> " +
vData.size());
} catch (SQLException e) {
m_nNumOfRows = 0;
System.err.println("SQLException caught: " + e.getMessage());
throw e;
} catch (Exception e) {
m_nNumOfRows = 0;
System.err.println("Exception: " + e.getMessage());
throw e;
} finally {
try {
if (rs != null) rs.close();
} catch (Exception e) {
System.err.println("Exception caught while closing
ResultSet: " + e.getMessage());
}
try {
if (stmt != null) stmt.close();
} catch (Exception e) {
System.err.println("Exception caught while closing
Statement: " + e.getMessage());
}
try {
if (conn != null) conn.close();
} catch (Exception e) {
System.err.println("Exception caught while closing
Connection: " + e.getMessage());
}
}
return vData;
}
HTH,
Greg.
"Conway. Fintan (IT Solutions)" <[EMAIL PROTECTED]> on 2002/11/21
10:37:51 AM
Please respond to "JDJList" <[EMAIL PROTECTED]>
To: "JDJList" <[EMAIL PROTECTED]>
cc: 'Lydia P' <[EMAIL PROTECTED]>
Subject: [jdjlist] RE: Performance Issues
Hi Lydia,
Unfortunately, I have not received any replies on this.
As far as I understand the design pattern (Page-by-Page iterator)
does not specify how to split up the data you receive from the database
into
ListChunks.
Does anybody have any concrete strategies for this? (Whether used
with the page-by-page iterator or not)
Thanks for your help,
Fintan
PS I am using Oracle DB if DB-specific solution is required
-----Original Message-----
From: Lydia P [mailto:[EMAIL PROTECTED]]
Sent: 20 November 2002 19:02
To: [EMAIL PROTECTED]
Subject: [jdjlist] RE: Performance Issues
Hi there, did you ever get an answer to this question?
I have the same question. I am having a really hard time finding a way
to get 'chunks' at a time of my list to display - I saw the ListChunk as
well but have no idea what to do with it (can you tell im pretty new at
this?)
I have a DAO, VO/TO, ServiceLocator and an Action class and I have a jsp
that uses struts/tiles and actually does return a very long list of
data, so I really do need to break it up into pages.
Any help you can give me would be greatly appreciated!
Thanks,
Lydia P
> Can anybody explain how the Page-by-Page iterator works.
>
> I am not using EJB's and the Sun PetStore example boils down a ListChunk
> class. This class retrieves the data from the database in Chunks, but
how
> this is done is not explained anywhere that I can find.
>
> Can anyone enlighten me on this issue?
>
> Many thanks,
>
> Fintan
* ** *** ** * ** *** ** * ** *** ** *
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed.
Any views or opinions presented are solely those of the author, and do not
necessarily
represent those of ESB.
If you have received this email in error please notify the sender.
Although ESB scans e-mail and attachments for viruses, it does not
guarantee
that either are virus-free and accepts no liability for any damage
sustained
as a result of viruses.
* ** *** ** * ** *** ** * ** *** ** *
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
******************************************************************************
This email message is confidential to the intended recipient and may be
legally privileged. It is intended solely for the addressee. Access to this
email by anyone else is unauthorized. Any unauthorised use, dissemination
of the information, or copying of this message is prohibited. If you are
not the intended addressee, kindly notify the sender by return e-mail
and delete this message.
******************************************************************************
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
