Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

closing Connection before returning ResultSet

 
Aparna Ram
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I have a JAVA program which makes a connection with a MySQL dB. This connection is done by using a separate class. It contains one of the following methods-


Here, this method returns the ResultSet, and so I cannot close the connection after the return statement, or before the return statement b'cos it has not yet returned the ResultSet.
I would like to know - where and how can I close the connection, statement and resultset? I call this method in my main class - should I close the connection in the main class? Kindly guide me , as I am facing OutOfMemoryError too.

Thanks in advance
Aparna
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your approach is wrong. Do you only need to connect to the database once? I don't suppose so. Reconnecting every time you need to interact is likely to be slow. Also, a ResultSet becomes unusable when the statement that generated it is closed, and a statement becomes unusable when the connection is closed. Here are two basic suggestions.

1. When retrieving data, copy it out of the ResultSet. If you are retrieving a list of product information, copy it into an ArrayList of Product objects, for example.

2. In a simple application, create a Connection object once and hold onto it. The Singleton pattern may be useful here.

I'm also hinting here that PreparedStatement is a good idea!
 
Aparna Ram
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jeff,

In the following method in my main class, I call the selectDB method of Database.java(follows later)

From Main class -->

---------------------------
Database.java



With the above, I am not able to follow how to implement your suggestions. Can you guide me more?

Thanks in advance
Aparna
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you are missing Jeff's point. This is not an effective way of organizing the responsibilities within your code. "Help" in this case would be "don't work this way".

A more typical way of organizing code is to have a method (in the same class or in a different class) that simply provides you with the connection. That way you can hide how you want to deal with connection management. You could create a single connection and cache it as Jeff suggested, but copy-and-paste blocks of driver loading/connection creation logic is just pointless code bloat.

After that, look for symmetry in your use of the JDBC resources. If you want to be able to close a statement in some method, that same method should have been the one to cause the creation of the statement. Ditto for the result set. Usually people do something like:



Obviously exception handling is needed there, closes should be in finally blocks, etc.

As a rule of thumb, asymmetric responsibilities in code (creates with destroys, destroys without creates, adds without removes, connects without disconnects, etc.) are the starting point for future headaches.
 
Aparna Ram
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is the following then okay? I guess I should now make a DB connection in every method of the main class wherever I need it, in the following manner, or am I completely lost..?



Also, when I run the above, the 'Database connection terminated' never gets printed out-so the connection doesnt close. When will the variable con be null, or how do I force the connection to close?
Thanks again.
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you need to access the database anywhere else?
 
Aparna Ram
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, in two other methods
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Reid and I have both mentioned, do not copy the code to load the driver and make the connection. In a simple client application you should create one connection and hold onto it. You should not connect over and over again, unless there are significant gaps in time between these connections.
 
Aparna Ram
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In that case, how can I close the connection? Since, the methods which need a db connection might be called on a random basis, where do I close the connection?
Also, I receive a java.lang.OutOfMemoryError - I believe it could be because I have not closed the connection.

In the main class:


DatabaseConn.java
-------------------


This returns a NullPointerException. Can you point where I am going wrong?
Thanks.
Aparna
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
> In that case, how can I close the connection?

In a simple client application, if you need to maintain the connection until the end of the
application's execution, you can either do nothing, and let the database notice eventually
that the connection is no longer there on the client end, or you can code a shutdown hook --
see java.lang.Runtime's addShutdownHook method.

> Also, I receive a java.lang.OutOfMemoryError - I believe it could be because I have not closed the connection.

I doubt an OutOfMemoryError exception could be caused solely because a Connection was not closed.

> Connection con = null;
> databaseConn.makeConnection();
> Statement stmt = con.createStatement();

It doesn't matter what makeConnection does (as long as it returns!) that third
line will raise a NullPointerException because con is a local variable, and is still null!
Why not have makeConnection *return* the connection? But again note that you should
only call it once.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic