Its better to return the arraylist instead of ResultSet. Because resultset depend upon the Connection object and connection object should remain open while you are using resultset and it is quite difficult and bad programming design.
You might want to convert the result set into a list of maps, and then return that list of maps. Take a look at the second post I made in the "Need a disconnected resultset" thread over in the JDBC forum. I'm moving this thread to the JDBC forum...
I'm not sure why the 'list of maps' solution is popular at the moment (I've seen multiple threads refer to it recently), but it's not a solution I'm fond of. Personally: get a decent object-to-relation mapping tooll and forget that you have a database in the back end. Castor and Hibernate are popular. I'm not a fan of the array-of-maps solution since it loses all of the type-safety (and meaning) associated with Java classes and all the data gets shovelled in data buckets. In truth the situation in which we used it had other associated problems (like namespace clashes and pretending 'Maps' are immutable), but it all stemmed from an architecture that was based on moving database information around in Maps. Oh yeah, it loses ordering too. Just my 2 cents. (Someone is making a penny!) Dave
My 2 cents. I'm not a fan of object/relational layers. In my shop, a developer should know oracle pl/sql and java. There are lots of those types around for future hire. Throw in an O/R tool. Now you hide them from sql, but hey, there's always a new variant of sql called something else. Plus, now you have to redefine your database tables and relationships in yet another way. Less people know the particular tool you've chosen. One more thing for new hires to learn. Also, it complicates the app, more layers, harder to debug (what sql is being ran? hmmm, turn on some type of logging to see), is harder to tune. We've tried for years to be database independent, but all that gets you is slow performance on all the databases, and developers who don't know much about any of them. Pick your database, use it to the fullest, don't hide it, simplify your apps. Use stored procedures and call them from your java. Usage of the database will determine the scalability of your application in most cases. (Note: I realize that vendors selling applications for multiple databases have to consider usage of multiple databases.) I like to think of SQL Server's T-SQL and Oracle's PL/SQL as the layer you'd swap out.