Connection Pooling example appears to create more than one pool

Ranch Hand

Posts: 150

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,

This is not really a Connection or jdbc question, more of a basic question (I think) from a tutorial I'm following.

The following DataAccessPool is a servlet where the init() method shown below instantiates a new ConnectionPool.

The ConnectionPool class beneath DataAccessPool creates and manages the connection pool.

Why in this example would only one ConnectionPool object be created for and shared by all servlets?

It seems to me that each time a new ConnectionPool object is instantiated by method DatabaseAccessPool.init() a new ConnectionPool object is created, eventually filling up the virtual machine with ConnectionPoolObjects.

DatabaseAccessPool

package exercises.database;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;

public class DatabaseAccessPool extends HttpServlet {
    private static final String CONTENT_TYPE = "text/html";
    private ConnectionPool conPool;
    private static final String DB_URL = "jdbc:mysql://localhost/database";
    
    // Get connection.
    public void init(ServletConfig config) throws ServletException {
        try {
            conPool = new ConnectionPool("org.gjt.mm.mysql.Driver", 
                "jdbc:mysql://localhost/eMotherEarth", "user", "pasword", 3);
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();   
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();   
        }
    }
    
    // Process servlet request.
    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType(CONTENT_TYPE);
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("<head><title>DataAccessPool</title></head>");
        out.println("<body>");
        Connection con = null;
        try {
            con = conPool.getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select * from users");
            out.println("<h1>Users</h1>");
            out.println("<ul>");
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();   
                int numcols = rsmd.getColumnCount();
                out.println("<li>");
                for (int i = 1; i <= numcols; i++) {
                    out.println(rs.getString(i) + " ");   
                }
                out.println("</li>");
            }
            out.println("</ul");
            out.println("><br><br>");
            rs = stmt.executeQuery("select * from users");
            ResultSetMetaData rsmd = rs.getMetaData();
            out.println("<table border='1'>");
             int cols = rsmd.getColumnCount();
             out.println("<tr>");
             for (int col = 1; col <= cols; col++) {
                 out.print("<th >" + rsmd.getColumnLabel(col) + "</th>");
             }
             out.println("</tr>");
             while (rs.next()) {
                 int numcols = rsmd.getColumnCount();
                 out.println("<tr>");
                 for (int i = 1; i <= numcols; i++) {
                     out.print("<td >" + ((rs.getString(i).trim().length() > 0) ? rs.getString(i) : " ") + "</td>");
                 }
                 out.println("</tr>");
             }
             out.println("</table>");
            out.println("</body></html>");
        } catch (SQLException e) {
            out.println("</body></html>");
            out.println(e.getMessage());
        } finally {
            if (con != null) {
                conPool.returnConnection(con);   
            }
        }
    }
        
        
    // Clean up resources.
    public void destroy() {
    }
}

ConnectionPool

package exercises.database;

import java.util.*;
import java.sql.*;

public class ConnectionPool {
    private Hashtable<Connection,Boolean> connections = new Hashtable<Connection,Boolean>();
    private Properties props;
 
    public ConnectionPool(String driverClassName, String dbURL, 
    String user, String password, int initialConnections) 
    throws SQLException, ClassNotFoundException {
        props = new Properties();
        props.put("connection.driver", driverClassName);
        props.put("connection.url", dbURL);
        props.put("user", user);
        props.put("password", password);
        initializePool(props, initialConnections);
    }
    
    public Connection getConnection() throws SQLException {
        Connection con = null;
        Enumeration cons = connections.keys();
        synchronized (connections) {
            while (cons.hasMoreElements()) {
                con = (Connection) cons.nextElement();
                Boolean b = connections.get(con);
                
                if (b == Boolean.FALSE) {
                    try {
                        con.setAutoCommit(con.getAutoCommit());   
                    } catch (SQLException sqle) {
                        connections.remove(con);
                        con = getNewConnection();
                    }
                    connections.put(con, Boolean.TRUE);
                    return con;
                }
            }
        }
        con = getNewConnection();
        connections.put(con, Boolean.TRUE);
        return con;
    }
    
    public void returnConnection(Connection returned) {
        if (connections.containsKey(returned)) {
            connections.put(returned, Boolean.FALSE);       
        }
    }
    
    private void initializePool(Properties props, int initialConnections)
    throws SQLException, ClassNotFoundException {
        Class.forName(props.getProperty("connection.driver"));
        
        for (int i = 0; i < initialConnections; i++) {
            Connection con = getNewConnection();
            connections.put(con, Boolean.FALSE);
        }
    }
    
    private Connection getNewConnection() throws SQLException {
        return DriverManager.getConnection(props.getProperty("connection.url"), props);   
    }
}

Brace yourself while corporate america tries to sell us its things. Some day they will chill and use tiny ads.

We need your help - Coderanch server fundraiser

https://coderanch.com/wiki/782867/Coderanch-server-fundraiser