I want to be able to return a resultset to a JSP page and then, without the JSP page knowing the types of the columns in the ResultSet, I want to be able to create a table of all the columns in the ResultSet.
My current thinking about how to best do this is below.
Is there a better way to process a ResultSet without the receiving JSP page knowing about the data types? I'm trying to keep all the "data aware" code in one place and *Not* in the JSP pages so the SQL can change, in the Servlet or wherever, without the JSP pages needing to change. IOW, the JSP always displays whatever's in the ResultSet passed to it without code changes when the SQL changes.
Never ever ever ever ever send a result set to a JSP page.
Capture the results of the resultset in something like an Object and send it to the page (you aren't doing the SQL on-page, are you?).
On the JSP iterate over the rows and over the columns for display.
Be aware that since you are treating all objects as generic, your output will consist of whatever the toString() method of the actual objects returns.
I do stuff like this all the time (except I use JSTL and EL to perform the iteration on scriptless JSP pages, and have some custom tags that allow me to specifying formatting to the columns as appropriate). [ September 08, 2004: Message edited by: Bear Bibeault ]
Best practices are to extract the data from the result set in the model layer and close all db resources (result set and statement, and connection if not pooling) as quickly as possible. The collection of data is sent to the JSP where it's a lot easier to deal with than a resultset which has a very limited interface (and is not very JSTL and EL friendly).
Architecturally, exposing the resultset in the JSP is putting the "M" (in MVC) squarely in the middle of the "V". Not a good separation. Your view (and controller, in fact) should be completely divorced from the details of the data persistance. A resultset, where the data is dealt with as database columns hardly quaifies.
In my own code, the API exposed by the model layer completely hides any such details and provides the data as simple collections (List, Map, arrays, as appropriate). No database details are exposed. As far as the "VC" components are concerned, the data could have come from a database, an XML file, the ozone layer, or Mars, and not care. [ September 09, 2004: Message edited by: Bear Bibeault ]
I have a class within my FormattedDataSet API that converts a ResultSet to a Object and the ResultSet's MetaData column names into a String.
It works as follows:
The FormattedDataSet class itself can be used to generate text (i.e html, xml, csv, form letters,...) from any tabuldar data (Object, ResultSets,...). Note the clean separation of data and formatting. Both can be reused independently. Continuing from the above code
One other thing. Because I use arrays so much in my code I have added a limited SQL style syntax for manipulating the. Note the execute() method below returns an Object and not a ResultSet. However it is operating on the array that was generated from ResultSet (i.e. the ResultSetConverter).
More than you asked for I know, but I got carried away. To see a live demo and sample code check out http://www.fdsapi.com .The jar that does all of this is about 130K.