Proper usage of JDBC Connection Pool (Glassfish)

Question

I need a database connection in Java Web service implemented as a session bean, and I'm not sure if I do it right.

I created a class

public final class SQLUtils   {  
    //.....  
    private static DataSource  m_ds=null;    

    static  
    {  
        try
        {
            InitialContext ic = new InitialContext();
            m_ds = (DataSource) ic.lookup(dbName); //Connection pool and jdbc resource previously created in Glassfish  , dbName contains the proper JNDI resource name 

        }
        catch (Exception e)
        {
            e.printStackTrace();
            m_ds = null;
        }

    }

    public static Connection getSQLConnection() throws SQLException  
    {  
        return m_ds.getConnection();             
    }
}

Whenever I need a connection I do

 Connection cn = null;  
 try  
 {
     cn = SQLUtils.getSQLConnection();
     // use connection
 }
 finally 
 {
     if (null != cn)
     {
         try
         {
             cn.close();
         }
         catch (SQLException e)
         {

         }
     }
 }

Is it ok to use it this way, or I DataSource must be a member of the bean ?

  @Stateless  
  @WebService  
  public class TestBean  {  
   private @Resource(name=dbName) DataSource m_ds;   
  }

I'm sorry if it is a nube question, but I'm pretty new to Java. Thanks in advance.

Answer 1

Apart from the C-style formatting, a few unnecessary lines and a bit poor exception handling, you can just do so.

Here's how I'd do it:

public final class SQLUtil {
    private static DataSource dataSource;
    // ..

    static {
        try {
            dataSource = (DataSource) new InitialContext().lookup(name);
        } catch (NamingException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws SQLException {  
        return dataSource.getConnection();             
    }
}

I throw here ExceptionInInitializerError so that the application will immediately stop so that you don't need to face "unexplainable" NullPointerException when trying to obtain a connection.

Answer 2

In the ancient J2EE world, the traditional way to manage this was to use a ServiceLocator. Below, a sample implementation (non optimized, the DataSource could be cached):

public class ServiceLocator {
    private Context initalContext;

    private static ServiceLocator ourInstance = new ServiceLocator();

    public static ServiceLocator getInstance() {
        return ourInstance;
    }

    private ServiceLocator() {
        try {
            this.initalContext = new InitialContext();
        } catch (NamingException ex) {
            throw new ServiceLocatorException(...);
        }
    }

    public DataSource getDataSource(String dataSourceName) {
        DataSource datasource = null;

        try {
            Context ctx = (Context) initalContext.lookup("java:comp/env");
            datasource = (DataSource) ctx.lookup(dataSourceName);
        } catch (NamingException ex) {
            throw new ServiceLocatorException(...);
        }

        return datasource;
    }
}

To use it, simply call it like this:

DataSource ds = ServiceLocator.getInstance().getDataSource("jdbc/mydatabase");

But this was prior to the EJB3 and Dependency Injection era. Now, when using EJB3, if you have setup your DataSource in your EJB container, all you have to do to automatically inject the DataSource in your Stateless Bean is to write (where mydatabase is the name of the datasource):

@Resource
private DataSource mydatabase;

Use the name attribute if you want to explicitly, well, set the name:

@Resource(name="jdbc/mydatabase")
private DataSource dataSource;

EJB3 actually make the ServiceLocator pattern obsolete and you should really prefer injection when working with them.