Get database connection from a connection pool

Question

I am refactoring others code. The one thing I notice is that of the manner on how the system is getting a connection from the connection pool.

Sample is like this. On every call of the service method, the system is making a context lookup on the JNDI for the datasource.

public class CheckinServlet extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        try {
            //Obtain Connection
            InitialContext initialContext = new InitialContext();
            javax.sql.DataSource ds = (javax.sql.DataSource) initialContext
                    .lookup("jdbc/mysqldb");
            java.sql.Connection conn = ds.getConnection();
            //business logic
            //redirect
        } finally {
            conn.close();
        }
    }
}

I do think that there is a performance hit on doing this every time. I am thinking of another way around these on how to retrieve a connection from a connection pool.

I am thinking about using the servlet's init() method but I think that is not optimal.

Answer 1

Do it once in a ServletContextListener instead of everytime in init() of many servlets. The contextInitialized() method is executed only once during webapp's startup.

public class Config implements ServletContextListener {
    private static final String ATTRIBUTE_NAME = "config";
    private DataSource dataSource;

    @Override
    public void contextInitialized(ServletContextEvent event) {
        ServletContext servletContext = event.getServletContext();
        String databaseName = servletContext.getInitParameter("database.name");
        try {
            dataSource = (DataSource) new InitialContext().lookup(databaseName);
        } catch (NamingException e) {
            throw new RuntimeException("Config failed: datasource not found", e);
        }
        servletContext.setAttribute(ATTRIBUTE_NAME, this);
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        // NOOP.
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public static Config getInstance(ServletContext servletContext) {
        return (Config) servletContext.getAttribute(ATTRIBUTE_NAME);
    }
}

Configure it as follows in web.xml:

<context-param>
    <param-name>database.name</param-name>
    <param-value>jdbc/mysqldb</param-value>
</context-param>
<listener>
    <listener-class>com.example.Config</listener-class>
</listener>

You can obtain it in your servlet as follows (init() or doXXX() method, you choose):

DataSource dataSource = Config.getInstance(getServletContext()).getDataSource();

I'd however refactor it a step further, JDBC code should preferably be placed in its own classes, not in servlets. Lookup the DAO pattern.

Answer 2

The method I have used in the past is to create a singleton class that holds the datasource

E.g.

public class DatabaseConnectionManager {

    DataSource ds;

    public void init() {
        InitialContext initialContext = new InitialContext();
        ds = (javax.sql.DataSource)initialContext.lookup("jdbc/mysqldb");
    }

    public Connection getConnection() {
        if(ds == null) init();

        return ds.getConnection();
    }
}

This means that you have a shared reference to your datasource, taking away the jndi lookup overhead.