[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Closing connection in finally block

Posted on 2004-11-12
6
Medium Priority
?
2,749 Views
Last Modified: 2007-12-19
Hi,

Is it a recommend practice to close db connection in finally block?

Here's my code.

public class ChargeService {

      private Connection conn = null;
      private PreparedStatement statement = null;
      private ResultSet rs = null;
      private String sqlStr = "";


      public boolean isCreditEnough(int groupId, String phoneNumber) {

            boolean isEnough = false;
            int creditBalance = 0;
            int creditToCharge = 0;

            try {
                  DbUtils.loadDriver(DbConfig.dbDriver);
                  conn = DriverManager.getConnection(DbConfig.dbUrl);

                  sqlStr = "SELECT totalCredit, credit" + " FROM"
                              + " chat_users, chat_credits" + " WHERE" + " groupId="
                              + groupId + " AND" + " phoneNumber='" + phoneNumber + "'";
                  statement = conn.prepareStatement(sqlStr);
                  rs = statement.executeQuery();

                  while (rs.next()) {
                        creditBalance = rs.getInt("totalCredit");
                        creditToCharge = rs.getInt("credit");
                  }

                  if (creditBalance - creditToCharge >= 0) {
                        isEnough = true;

                  } else {
                        isEnough = false;

                  }

            } catch (SQLException e) {
                  throw new ChatException(getClass().toString(), e);

            } finally {
                  DbUtils.closeQuietly(rs);
                  DbUtils.closeQuietly(statement);
                  DbUtils.closeQuietly(conn);

            }

            return isEnough;

      }
}

BTW, i use DBUtils from Jakarta Common project. So, my problem is,

-I'm got problems with closing my db connections, based on my connection threads i see in my mysqladmin it keeps getting more and more.

My question is,
-Is this a problem of DbUtils.closeQuietly(conn) not executing or my code problem?
-what is the recommend approach to close connection?

Melv

0
Comment
Question by:mwhc
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:tomgallo
ID: 12572493
I'll use this code instead:

try{
      Connection conn=null;
}catch(SQLException SQLE){
      throw new Exception(SQLE.getMessage());
}finally{
      try{
            if(conn!=null)
                  conn.close();
      }catch(SQLException SQLE){}
}

The same try/catch for a ResultSet or a CallableStatement/PreparedStatement
0
 
LVL 6

Expert Comment

by:abdullah716
ID: 12572903
I think you should explicitly call the Dispose method of the connection object to close unmanaged resources. Don't know about these Jakarta utils, but check if they call the Dispose method.
0
 
LVL 2

Expert Comment

by:tomgallo
ID: 12821591
Venabili,

The code that I posted is working and answers the question. What type of recomendation do you need?
Help me to understand please.

-tom
0
 
LVL 6

Accepted Solution

by:
abdullah716 earned 500 total points
ID: 12822694
Closing a connection is not enough to clear unmanaged resources. These are later handled by the GC. The dispose method of the connection object is required to free resource immediately. I feel I deserve at least some points.
0
 
LVL 2

Expert Comment

by:tomgallo
ID: 12822726
I agree abdullah
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question