Category Archives: Database

How to use DataSources in a Web Application on Oracle OAS10g

There are many how-to documents on using DataSources in web applications. I’m adding yet another, mainly because I want one in a location that I can remember when I need to point someone at it. Additionally, there aren’t a ton of documents on how to set all this up for OAS10g application server.

This document will outline the proper way to reference a DataSource on OAS and how to set up the application-specific configuration file with a default JNDI mapping for the logical DataSource.
Continue reading How to use DataSources in a Web Application on Oracle OAS10g

Can you find the connection leak?

I did a short code review with a developer who couldn’t understand why some code was leaking connections. As far as the developer could tell, he was closing out all of the database objects correctly.
Consider the following code and see if you can find the connection leak:

public static String getNiDNAString(String lookup, String value)
    throws SQLException {
    try
    {
        // Create the initial context. No JNDI properties are to be supplied
        InitialContext initialContext = new InitialContext();
        PreparedStatement psSelect = null;
        ResultSet rsSelect = null;
        DataSource dataSource = (DataSource) initialContext.lookup("DS-NameHere");

    // Execute the query and get the results back from the handler
        String result="";
        try
        {
            psSelect = dataSource.getConnection().prepareStatement(
                "SELECT 42 from dual where 1=?"; // Query was changed
                psSelect.setString(1, "1");
            if((rsSelect = psSelect.executeQuery()).next())
            {
                result=(rsSelect.getString(1));
            }
        }
        catch(SQLException sqlexception)
        {
            throw sqlexception;
        }
        finally
        {
            closeObjects(rsSelect, psSelect, dataSource.getConnection());
        }
        return result;
        }
    }

public static void closeObjects(ResultSet rs, Statement s, Connection c)

{
    // Assume this implementation correctly closes all objects
}

The problem, of course lies in this call:

closeObjects(rsSelect, psSelect, dataSource.getConnection());

As you can see, the developer never gets a handle to the actual connection object and then tries to close the connection by passing dataSource.getConnection() as an argument to the closeObjects method.

The net effect is that the developer passes in a new connection into the closeObjects() method. The new connection is closed, leaving the original connection opened, thus a connection leak.

The corrected code follows: Continue reading Can you find the connection leak?

Managing JDBC Database Connectivity

The single largest source of bugs in my shop lies around managing database connectivity in applications.

Now, my shop is still in the dark ages in that we typically don’t use any kind of persistence framework. Instead, we code our SQL by hand into our classes.

When working with a database in this way, you’ll need to manage several different objects, making sure to properly close each of them in turn or you’ll end up with resource leaks. This is especially true if you are using a connection pool to manage your database connections.

The objects that you need to manage are:

  • Connection
  • Statement
  • ResultSet

You’ll open each of these in turn, and each must be closed out when you are finished with it. Each of these objects has a close() method on it for just this purpose.

It’s common for me to come across the following code during code reviews:

DataSource ds = (Some DataSource here)
try{
   Connection c = ds.getConnection();
   Statement s = c.createStatement();
   ResultSet r = s.executeQuery("SELECT * FROM DUAL");

//Code to process the query results

   r.close();
   s.close();
   c.close();
}
catch(SQLException e)
{
   // Log the error
}

At first glance, this may look correct. However, we have to think about what happens if an exception is thrown before one or all of the database objects are closed.

A better solution is to close out the database objects in a finally block which is guaranteed to run:

DataSource ds = (Some DataSource here)
try{
   Connection c = ds.getConnection();
   Statement s = c.createStatement();
   ResultSet r = s.executeQuery("SELECT * FROM DUAL");

//Code to process the query results
}
catch(SQLException e)
{
   // Log the error
}
finally
{
   r.close();
   s.close();
   c.close();
}

Of course, this is still problematic as any of the close methods can also throw a SQLException. We can get a little crazy and nest some additional try / catch blocks inside our finally block to be certain that we close out everything:

...
finally
{
   try
   {
      r.close();
   }
   catch(SQLException e)
   {
      // Log Exception
   }
   //use a finally here in case some other RunTime exception
   //is thrown in the previous close statement
   finally   {
      try
      {
         s.close();
      }
      catch(SQLException e)
      {
         // Log Exception
      }
      //use a finally here in case some other RunTime exception
      //is thrown in the previous close statement
      finally
      {
         try
         {
            c.close();
         }
         catch(SQLException e)
         {
            // Log Exception
         }
      }
   }
}

The above will ensure that all database objects are closed.

Until my shop moves to embrace a persistence framework or ORDB mapping solution such as TopLink or Hibernate, I am stuck using the above method.

One alternative that I’ve begun to endorse is to use the Commons-DBUtils library from the Jakarta Commons project. This API allows you to pass in a DataSource, a Query, and a Query Processor. You never touch connections, statements, or result sets. As such, the API can ensure that all objects are closed correctly. This removes some of the tedium of doing direct JDBC calls from within your code and helps to avoid resource leaks by ensuring that all DB resources are properly managed.