Scope of Connection Object for a Website using Connection Pooling (Local or Instance)

Question

For a web application with connection polling enabled, is it better to work with a locally scoped connection object or instance scoped connection object. I know there is probably not a big performance improvement between the two (because of the pooling) but would you say that one follows a better pattern than the other. Thanks ;)

public class MyServlet extends HttpServlet {
    DataSource ds;

    public void init() throws ServletException {
        ds = (DataSource) getServletContext().getAttribute("DBCPool");
    }

    protected void doGet(HttpServletRequest arg0, HttpServletResponse arg1) throws ServletException, IOException {
        SomeWork("SELECT * FROM A");
        SomeWork("SELECT * FROM B");
    }

    void SomeWork(String sql) {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            // execute some sql
            .....
        } finally {
            if(conn != null) {
                conn.close(); // return to pool
            }
        }
    }
}

Or

public class MyServlet extends HttpServlet {
    DataSource ds;
    Connection conn;*

    public void init() throws ServletException {
        ds = (DataSource) getServletContext().getAttribute("DBCPool");
    }

    protected void doGet(HttpServletRequest arg0, HttpServletResponse arg1) throws ServletException, IOException {
        try {
            conn = ds.getConnection();
            SomeWork("SELECT * FROM A");
            SomeWork("SELECT * FROM B");
        } finally {
            if(conn != null) {
                conn.close(); // return to pool
            }
        }
    }

    void SomeWork(String sql) {
        // execute some sql
        .....
    }
}

Answer 1

You should take this approach:

1. Take a connection from the pool
2. Conduct a complete transaction, moving the database from one valid state to the next
3. Commit
4. Return the connection to the pool
5. Return the HTTP response

Your first approach is not a good idea because if one of the units of work fails you may leave the database in an inconsistant state.

Perhaps you will complete the first unit of work and then be unable to take the second pool connection (some other thread got in first). Maybe under heavy load, this will happen to multiple requests and nothing will get done properly.