I put this code together to gather data for a "task" in a task list. It has a number of attributes which are all foreign keys to other database tables. Hence, it produces a number of drop down lists which correspond to these.
What would you do differently to improve this?
Close the database connection explicitly? Close the resultSets explicitly?
[ October 13, 2006: Message edited by: Bear Bibeault ]
The database queries need to be run in a bean/servlet. You should open the resultset, run through it and save the results to a List/Map (or whatever datastructure you prefer)
You should then set this as an attribute in appropriate scope. Most often I use request scope, but that means it needs to be requeried on each request.
In this case, these dropdowns seem to be global and unchanging no matter who is logged into the application. Storing them in the application scope might make sense. If it is user-specific data that doesn't change while the user is logged in, then session scope would be applicable. Then rather than iterating over a result set in the jsp, you iterate over a list/map that is in scope.
The other suggestion is one more focused on the HTML rather than the JSP per se. Use css styles for your formatting, rather than hardcoding in colours, font sizes, bold, alignments etc etc. It will make the page both simpler to read, and easier to change. As a quick example:
The other change I would recommend is to use connection pooling on your server rather than open a connection to the DB with each and every JSP/Servlet that runs.
I don't know the structure of your DB so I can't say for sure but some kind of join statement is probably in order here. At the very least, a select statement from multiple tables would be a good idea too. Putting all those selects into one DB call will save a LOT, as it would be only one DB call instead of 4 and when you're reading the data, you could do it in one loop not 4, and you'd only have to create one resultset, not 4. Sure it might be "cleaner" to keep them all separate, but in a production environment that'd be a LOT slower. Think about it, 1000 requests all generating 4 DB calls and looping through 4 resultsets is a LOT more intensive than 1 per.