GRUMBLE ... The JSP mailing list does not stick a "Reply To" header in that causes replies to go to the list, so my reply went only to the original poster. Thought it might be of general interest, so I'm forwarding it to the list as well. Craig ============== Forwarded Message ============== "Craig R. McClanahan" wrote: > Mark Hughes wrote: > > > Hi, > > > > We're looking at implementing some sites based on the JSP / Bean model. > > Whilst single properties and arrays are straight forward enough, > > one common requirement of most of these pages is to represent the > > resultset from a SQL query. > > > > We're keen to avoid Java code in the HTML template - how have people > > gone about implemeting this with the bean model? > > > > Any ideas / comments more than welcome :-) > > > > Thanks, > > Mark. > > > > I haven't implemented this yet (but also plan to). Here's a few approaches I > am considering. > > APPROACH 1 -- SMALL/MEDIUM AMOUNT OF RESULT DATA > > Scenario: You want to return a set of rows (formatted in tables or > something), but there are more rows than will comfortably fit on a single > page. The total result set is small enough to be stored in memory in a > session variable. > > Approach: Provide a storage JavaBean that contains the results of your > database query, perhaps as a Vector of business objects that represent the > results. (You won't want to use the java.sql.ResultSet, because that would be > tying up database connection / statement / result set resources until you > close it). For the purposes of this example, assume that you have defined > another JavaBean called "Result" that contains the values from a single row, > available as read-only properties. The storage bean has the following > functionality: > > void setRowStart(int) -- accepts the value of a parameter (using > SETFROMREQUEST) that defines the starting index in the results to be > displayed. > > void setRowCount(int) -- accepts the value of a parameter > (using SETFROMREQUEST) that defines the number of rows to be displayed. This > could also be defaulted if you did not want to let the user change it. > > Result[] getResults() -- synthesizes and returns an array of just the desired > records. There will be rowCount rows in this result set (assuming you haven't > fallen off the end of the actual results). The element at the zero subscript > in the array will be the row from the original results at the value indicated > by rowStart, and ascending from there. > > Now, in your JSP page, you can use the normal LOOP tags (assuming 0.92 > compatibilty) to display the subset of data that as returned. You can also, > for example, synthesize a NEXT button that passes a new row start for the next > page that is higher than the value used on the current page (by the number of > rows you just displayed). Same idea for the PREVIOUS button for the previous > page. You can also get fancier and provide hyperlinks to all the available > pages, because you know how many results there were. > > APPROACH 2 -- LARGE AMOUNT OF DATA > > Scenario: Same as approach 1, except that the number of rows in the result > set is too large to fit in memory in between requests. > > Approach: Again, use a storage bean, with the same rowStart and rowCount > properties. But this time, the bean just stores the SQL query to be used to > select the results, instead of the results themselves. > > void setRowStart(int) -- Same as above > > void setRowCount(int) -- Same as above > > Result[] getResults() -- Execute the SQL query, skip the number of rows > specified by rowStart, and create an array containing the number of rows > specified by rowCount. If you have some knowledge of the underlying data, you > might be able to modify the SQL query instead of havfing to skip rows every > time. > > Potential Drawback: Because you are doing several queries, the snapshots of > data being shown are not necessarily consistent with each other if people are > changing the data from other apps. > > APPROACH 3 -- LARGE AMOUNT OF DATA > > Scenario: Same as approach 2, but we want a "consistent" snapshot of the > entire result set, thus avoiding the potential drawback of approach 2. > > Approach: The very first time, perform the entire select and store the > results somehow in a temporary disk file (whose pathname is stored as a > property of your bean). Then, for each request to display rows, you can treat > this file as a sequential vector of records, and skip the number you need when > you are synthesizing the array. > > COOL FEATURE: No matter which approach you take to coding your bean, the > display logic in the JSP page is exactly the same! That way, you can change > the underlying implementation of the storage bean as your needs change (lets > say you started out selecting only 50 rows and used approach 1, but now you > need to handle 5000 and want to use approach 2 instead), without affecting the > presentation in your JSP pages. > > COOL FEATURE: No Java logic in the JSP page -- just access to single-valued > and multi-valued bean properties. > > COOL FEATURE: No HTML logic in the storage JavaBean -- just the business > logic needed to retrieve what you want, store it as a collection of Result > objects, and synthesize the array for the getResults() method based on the > request parameters. > > This whole concept seems like a reasonable design pattern for the problem > you've posed. What do you think? > > Craig McClanahan =========================================================================== To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff JSP-INTEREST". For general help, send email to [EMAIL PROTECTED] and include in the body of the message "help".
