File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes reusing the Statement object Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "reusing the Statement object" Watch "reusing the Statement object" New topic

reusing the Statement object

Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
hi all,
here is what i have,
1. i have a class called DBConnection. where i have put Connection, Statement and ResultSet objects as members.
2. i have a method executeQuery() in that class which takes the query as parameter
now, the problem is i have the executeQuery() as follows,

so, here the issue is i can't use the executeQuery() on the DBConnection object twice while I have the ResultSet from first query because for example if we have,
DBConnection dbcon = new DBConnection();
ResultSet rs1 = dbcon.executeQuery("query1");
then in the DBConnection it created the Statement object stored as stmt.
now, until we are done with ResultSet rs1 we cant do,
ResultSet rs2 = dbcon.executeQuery("query2");
as stmt won't get re-created.
even if i re-create it everytime (basically, removing stmt == null from the code) then also it won't work as we would be "re-assigning" the stmt object and we would essentially make the older object available for GC.
now, one solution i think, to this problem is-
instead of having stmt and resultset objects stored in the DBConnection object, we use annoymous objects.
i do,

the only problem i see in the above approach is-
the "annoymous" Statement Objects will roam around for undetermined time...i'm not sure if we call close() on the Connection object, the associated statement objects also becomes eligible for GC. (tho i believe it won't be as they are separate objects then the connection)...
can anybody help me here?
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
no replies?
was i unclear OR the question is stupid?
please let me know so i can try provide more input on what i want to do here...
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
Have you tried closing the first ResultSet before attempting to re-use the original Statement ?
On a wider issue you seem to be attempting to write a DB Connection Pool, there are plenty of these out there already without needing to add an extra layer. My biggest concern would be to ensure that DBConnections, Statements and ResultSets are closed properly (returned to the pool of available connections in the case of a Connection) when they have served their purpose. As a part of best practise I ALWAYS put the retrieval of the Connection and the creation and use of Statements and ResultSets into a try{..}catch.. finally{..} structure so that I can ensure that I do the necessary housework in the finally block.
The non-release of DB Connections upon an error is one of the most common reasons for system failure that I have seen in Java applications.

Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Maulin Vasavada
Ranch Hand

Joined: Nov 04, 2001
Posts: 1873
hi Andy
thanks a lot for a response.
yes, i am aware that there what i am seeming to do is DBConnection Pooling but i really didn't mean it to be.
i just wanted to encapsulate the db connection parameters and things in a class so i don't have to write dbconnection code everywhere but somehow it turned out to be look like db connection pool. i really dont want to write a db connection pool. rather i want to just share the connection and have same functionality to create multiple statements etc on that connection.
i should have just made getConnection() method available and make the using program do createStatement() as i realize it now. this would get me out of the problem i face here.
i have put a close() method in the DBConnection class (i omitted it here as i found it unnecessary) which closes statement, resultset and connection in try {} catch{} finally {} block as you suggested. so that can be taken care of.
'm reflecting......
i should change my code just to have getConnection method and remove those executeQuery() and things may be which causes the code look like DBConnection Pooling......
i donno why i had those executeQuery() and all in there...OR i will try the option i suggested in the first post about reusing the statement and see how it works...
I agree. Here's the link:
subject: reusing the Statement object
It's not a secret anymore!