Best structure for connection pooling with Java EE

I am trying to figure out the best structure for connection pools so that I can access the connection pool from any servlet and establish a connection to my database. I have been following some tutorials in setting up and configuring the datasource and connection pool and they all have them initialized and accessed in classes that extend HttpServlet. So it looks something like this:

public class DatabaseConnector extends HttpServlet {
    private static final long serialVersionUID = 1L;

    private DataSource dataSource;
    private Connection connection;
    private Statement statement;

    public void init() throws ServletException {
        try {
            // Get DataSource
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup("jdbc/test");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    /**
     * @see HttpServlet#HttpServlet()
     */
    public DatabaseConnector() {
        super();
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        ResultSet resultSet = null;
        try {
            // Get Connection and Statement
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            String query = "SELECT * FROM STUDENT";
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + resultSet.getString(2) + resultSet.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}   
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

It looks like to me that this connection pool is only for this servlet which would only distribute connections when a get request is sent the this servlets URL. What if I want to have another servlet that needs to access the database. I was thinking of just removing the doGet and doPost methods in this servlet and leave the init so that the connection pool would be initialized at runtime, and then have a singleton reference to this servlet which could be used in other servlets. However, this doesn't seem like the right way to me. What is the best way to structure a connection pool that can be accessed from all servlets and listeners?

Thanks for your help!

回答1:

Completely wrong.

The correct way to access a connection is to use a JNDI connection pool.

Servlets are HTTP listeners. They shouldn't have anything to do with databases.

A properly layered Java EE solution will restrict data sources to the service tier. It will check connections in and out, know about units of work and transactions, and interact with data access objects.

Servlets should deal with services, not data sources.

回答2:

The logic to create a connection could be placed within a simple class.

public class ConnectionManager{
       public static Connection getConnection(){
        Connection connection = null;
        try {
            // Get DataSource
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup("jdbc/test");
            connection = dataSource.getConnection();
        } catch (NamingException e) {
            e.printStackTrace();
        }

       if(connection == null){
           throw new RuntimeException("Cannot connect");
       }

       return connection;
       }
}