This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

Generic Querying Design Questions

Jun 3rd, 2005, 08:23 AM

Hi ,

I'm building a reporting application that creates html report output based on running an SQL query or executing a stored proc. Most of the results are to be displayed in tabular format. In this situation is it wise to build a generic query object that would run the query/stored proc passed in and create a matrix data structure to represent the resultset. Something like an Arraylist of ArrayList would be sufficient to hold the resultset. All items in the matrix would be String values as this is how result page will be created. I know this does not represent the domain objects as they should but it would reduce the amount of additional code that needs to be written. In terms on adding new reports it would be easier to use this approach as less POJO classes, Interface and DAO classes would have to be created. Instead a generic query object would be used to handle the request and return the results. The other reason is sometimes it is unknown what the result is going to be in which case would you not have to create a POJO class for each possible resultset!
At the moment everytime I need to add a new stored proc I have to create several Classes that extend the StoredProcedure class each with its own RowMapper. I was just wandering if there was a more generic and extensible way of doing this.

The only caveat for stored procedures is they can't have output parameters. Only input parameters and a result set. And you would have to put the complete sql syntax, not like the StoredProcedure class which figures it out for you given just the name of the procedure.

Comment

Since your sql and procedure name might vary, you are better off using the JdbcTemplate.call instead of StoredProcedure. For mapping column values to a generic form you could use ColumnMapRowMapper which is in the jdbc.core package.

Here is an example of what it could look like (this is SqlServer/Sybase style):

My reasoning for this is because you don't have to declare any parameters when using the "query" method as opposed to using the "call" method. Also the ColumnMapRowMapper keeps the data types of the column in tact instead of converting all values to Strings which may be useful is different formatting is required in the presentation layer. Which leads to my next question. I am using displayTag to display the tabular output and the moment this component requires a List of java beans. Is there any way of making displaytag work with a list of Map values as returned by ColumnMapRowMapper. I would hate to have to convert the resultset into Pojo classes just so that displaytag works. I guess my own custom tag would be another option but didn't really want to go down this route. I would imagine these are common problems and was just wandering what the best practices are in such situations.