File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Generically Reading A Resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Generically Reading A Resultset" Watch "Generically Reading A Resultset" New topic
Author

Generically Reading A Resultset

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1043
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.

============================================================

int numberOfColumns = metaData.getColumnCount();

// print out column names
for ( int i = 1; i <= numberOfColumns; i++ )
results.append( metaData.getColumnName( i ) + "\t" );

while ( result.next() )
{
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.println( result.getObject( i ) + "\t" );
}

============================================================

I appreciate anyone's help or advice in advance.

Thanks.

-- Mike
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60766
    
  65

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 ]

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1043
Hi Bear,

No SQL being done on a JSP page. It's being done by a factory class instantiated in the "controller servlet" (MVC).

Your suggestions are great, though you didn't say *why* not to send a ResultSet to a JSP page.

Thanks very much.

-- Mike
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60766
    
  65

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 ]
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1043
Thanks for the great info!!!

-- Mike
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
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.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
 
 
subject: Generically Reading A Resultset
 
Similar Threads
List question
vector problem,how to retrieve data stored in vector and display in jsp pag
CallableStatement resultset returns only columns and no data, where data exists.
JDBC returning values using Scientific Notation
Menu Component