Proper coding of database connections in websphere

Good evening,

I am a new J2EE developer.  I was assigned to work on this enourmous application at my work.  
At least once a week (sometimes more), our system crashes because it runs out of connections in the connection pool.

While doing some research, I came across the following function (i've changed the sql statement and took out the loggin parts ) .    It doesn't look right to me, why would they commit and then call rollback and then committ again.. What happens if the rs (resultset) is not closed?  

People have told me that "since it was committed, rollback wll not have any effect", but won't the system start a rollback procedure anyways?  Couldn't that cause more problems (btw, our database is DB2)?

Could this be causing connection pool problems or stale connections?  Any help will be appreciated.  Also, what are best practies for handeling connections in websphere?  thanks!


public static void delete(String itemToDelete) {

    Statement stmt = null;
    ResultSet rs = null;
    Connection con = null;
    StringBuffer query = new StringBuffer();
          
    query.append(" DELETE * from EVERRYTHING");
          
    try {
    con = Utility.openConnection(null);  //this will get connection from cnnection pool, autocommit = false
    stmt = con.createStatement();
    int ret = stmt.executeUpdate(query.toString());
    con.commit();
    }catch(Exception e) {
      //log stuff but doesnt do anything else..
          }
      finally {
          try{
      if(stmt != null)
      stmt.close();
          }catch(Exception e) {};
          if(con != null)
          Anotherfile.closeConnection(con);
          }
                   
      }

---The Following is the code in Anotherfile:

public static void closeConnection( Connection connection ) {
  commitConnection(connection);
  getConnectionPool().close( connection ); // this has the .close which closes the connection
}

   public static void commitConnection(Connection con) {
     try
     {
        con.rollback();
       con.commit();
     }
     catch (SQLException e)
     {
       //log stuff.. but doesnt do anything else
     }
   }
Virge57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Virge57Author Commented:
Wow, i can't beleive i paid $99 for this, how do i get my money refunded?
damonfCommented:
This particular forum is not super active.  Mostly WebSphere specific stuff here.  Your question is general enough that you could ask it in the JSP forum for example.  That one is quite busy.  But I'll take a crack at your Q:

Recommended way:  ALWAYS close connection when finished with it.  In cleanup process, always catch any exceptions so code can reach the connection cleanup point.

The .rollback() should not have any affect if the conn is already committed, but why would they could it this way?

It looks like the commitConnection() method could throw an exception on either the commit or rollback ... that means the close code is never reached.  You should catch and log any exception it throws.

Also, why would they do getConnectionPool().close( connection );  ... normally it's just conn.close()  ... The connection pool is provided by the container.  Or is this app implementing its own connection pool?  If so that's another can of worms...

Have you tuned your connection pool settings?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Virge57Author Commented:
wow, so if public static void commitConnection has an error, it will never return to the calling function?  What do you think would be the best way to close the connection, something like this: (?)

public static void closeConnection( Connection connection ) {
 
 try{ getConnectionPool().close( connection ); // this has the .close which closes the connection}
catch (Exception e) {}
finally{
if(connection !=null)
connection.close();

}
damonfCommented:
Okay, it was end of the day Friday ... I take back what I said.  When I look at the code again, if an exception is thrown in the commit/rollback, it will be caught so execution can continue to the close().  What about my other questions?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java App Servers

From novice to tech pro — start learning today.