• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

reusing the Statement object

 
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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();
dbcon.connect();
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.
e.g.
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?
regards
maulin
 
Maulin Vasavada
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
regards
maulin
 
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
HTH
 
Maulin Vasavada
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
so...........
'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...
regards
maulin
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic