Is it a good idea to use a resultset as a return from a EJB? I have a session EJB with a method that selects different rows and sends the result to a servlet. I'm concerned about when to close the connection in my EJB. I don't want to leave connections open that I'm not using. If I return a resultset I have to keep the connection open until I'm done with the resultset in the servlet and then close the connection. Is this a good idea (from the design point of view) or should I store the resultset data in a collection class?

This is a bad idea ! u should store the results to an object (such as vector etc) and pass it back to the client.
In addition, u can use connection pooling to improve performance.

alex

Hector-

You can use the ResultSet as a return from an EJB as long as it is Serializable (the ResultSet interface is not) and it doesn't require a connection to the database (Most do).
You can however package up the results into a JDBC 2.0 RowSet class which will cache all the data and not require a database connection.
NEVER, NEVER, NEVER, leave a connection open while the data is used in the servlet layer. It uses up resources and will bring you database to a screeching halt.
Take a look at http://java.sun.com/products/jdbc/download.html#spec for the Optional Spec which includes a CachedRowSet implementation that takes a regular ResultSet in its constructor, and may be used outside of a transaction.
Please note that this should only be used for read-only queries and that you should use value objects in cases where you need to update data.

Good Luck!
Clay Roach
croach@middleware-company.com

1) Please see my Pattern on passing IDL types rather then serialized types. A Vector of Vectors is a miserable way to return a result set.

2) We have implemented a pure IDL version of the ResultSet which includes all the metadata, as well as static methods to convert from the java.sql.ResultSet to the IDL type, and an interator to iterate over it.

Advantages:

o Purely portable to any container using RMI/IIOP or pure RMI
o No overhead for Serialization
o Includes all metadata
o Fully CORBA compliant as well

Ill be releasing this as OpenSource over the next few weeks

Dave Wolf
Internet Applications Division
Sybase

Thank you very much for your help and tips.

Hi !
I use Stateful Session bean and BMP entity bean for my system. I have 60 columns in a table which I have to retrieve (from the table) and update (to the table) with the user entered value for all the columns. I use JSP as my client. My system architecture is JSP client calls session bean which inturn calls entity bean for getting and setting the data. I've created a wrapper class with 60 attributes (corresponds to 60 columns of the table) and a constructor with 60 parameters, instead of having 60 setters and getters.
I have some questions regarding this :

1. Initially when the user logs in, he gets the value of the 60 columns data populated in the textfield. To do this, I've planned to call a method in a session bean, which inturn calls entity bean business method. In entity bean, I execute a query and instantiate the wrapper class with 60 column values of the retrieved record (query retrieves only one record) and finally returning the object itself as a whole. Is it a right approach, or if any better approach, pl let me know asap.
2. For setting the values of the attributes (and updating to the table finally) with user entered values, how to do it.

Thanx
AJP