I am using a customized jdbc api, which allows only one resultset and one statement object per connection object.I have a page(with two frames) where i accept some search criteria from the user(on the top frame) , do the search in the database and display the list of records found in bottom frame.But the problerm occurs when the user hits the search button multiple times , as in that case i receive an exception because my api allows only one resultset object open and a resultset object from my previous request was already open.I am making the database call in an beans and returning resultset object to the JSP. But i am not able to figure out that when a user hits the search(submit) button multiple times(say twice) how much my first request had progressed before getting terminated. Can i find this in any way , so that i can close my JDBC objetcs before the next request starts executing.
Originally posted by Yogesh Kapoor: I am using a customized jdbc api, which allows only one resultset and one statement object per connection object.
Reading between the lines, I would say that it is quite normal behaviour for a JDBC connection to choke on your attempts at multi-threaded access. It sounds as if you are retrieving a JDBC connection when you create your bean, and use that connection to fire the query when the JSP calls getResultSet() or something similar. There are a couple of problems with this. Your immediate problem seems to be that the JDBC connection is not threadsafe. If a second thread needs to access the database, it should either get its own Connection or wait until the first thread has completely finished using the Connection and all its Statement and ResultSet objects. Remember that the servlet engine will give each request its own thread! A connection pool is your friend here. A separate issue is that ResultSet objects encapsulate a live connection with the database - you don't pass them around without much thought for safety. What happens if there is an exception in your JSP? Do you have a finally clause closing the ResultSet and the Statement? If not, you have a potential resource leak. Then there are design issues: the division of responsibilities, and the fitness of your chosen tools for the purpose. To start with the latter, JSPs are meant for presentation. The less Java you find in a JSP, the better it is; that's why server-side JavaBeans and tag libraries exist. A JSP is not the best tool to handle a raw ResultSet. The problem with the division of responsibilities is that it is blurred. You initiate the database access in your bean, but by passing the ResultSet back to the JSP, the latter is just as tightly coupled to the database as the bean itself. It would preferable to let the database interface be handled purely by the bean. The JSP would get at the data by accessing bean properties, for example:
If you would use a taglib, you could even get rid of the while loop. Whatever you do, do not forget to use a try... finally construct to ensure your statement is closed. One way to keep all responsibility for this inside your bean is to load all the data the first time bean.nextItem() is called:
This is not necessarily the best way to code it, and certainly not the only way. But it illustrates how you can move responsibility for the database query, including cleanup, completely into the bean. Note that this bean is not threadsafe! It needs to be page- or request-scoped. If you insist on making it session-scoped and allocating a Connection when your bean is created, you could make loadItems() synchronized to protect it from simultaneous requests, and move the itemIterator into the JSP itself:
Or move them into a page-scoped bean which interfaces with your session bean. - Peter
[This message has been edited by Peter den Haan (edited May 02, 2001).]
Joined: May 01, 2001
Hi Peter, Thanks a lot, your suggestions have helped me a lot. Regards, Yogesh