Setup Connection pooling in jsp/servlets application based on MVC?

Question

I want to use Connection pooling in my java web application with MySQL and JDBC, I found a very resource to learn at Apache Tomcat 6.0 (6.0.35) - JNDI Datasource HOW-TO, But this example uses JSTL code to explain how to retrieve connection from pool. I want to work similarly but with a MVC architecture from scratch consisting of Beans, DAOs, Servlets and JSPs. I got everything I want from a very good DAO tutorial by BalusC but I am confused in the last part of tutorial saying How about Connection Pooling?. Could anyone please elaborate on this connection pooling topic and the close() method?

EDIT:
Actually i should have add this thing earlier also:
As the tutorial I have linked above comes before JDK7, which now has try-wth-resource code that closes the Connection automatically, then how can we maintain a Connection pool and closing a Connection in pool here with the same DAO code (or with few changes) as in the tutorial?

mykey · Accepted Answer · 2012-04-11T15:55:08.197

if your application requires is to be used by several users one connection can be held by a connection pool so several of those users will reuse the existing connection instead of making new connection which will consume time. about the close()method: the connection pool stays active and if you do not close a connection to it after every access,connections will pile up and if the number increases the connection pool get jammed and no longer accepts other connections!

public class MyDao {

    private InitialContext context;
    private DataSource datasource;

    public MyDao() {

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");
            context = new InitialContext();
            datasource = (DataSource) context.lookup("datasource name");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

    }
 public MyBean getMyBean() throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet res = null;

        String sql = "some query";
        try {
            connection = datasource.getConnection();//pool connection
            statement = connection.prepareStatement(sql);
            res = statement.executeQuery();
            while (res.next()) {
               //return true

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

     finally {
            if (rs!= null) try { rs.close(); } catch (SQLException logOrIgnore) {}//result set if any
            if (stm!= null) try { stm.close(); } catch (SQLException logOrIgnore) {}//clase statement if any
            if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}//close connection
            }
            }


}//close MyDao

will u please suggest a code for the pseudo code provided in `close()` method in the tutorial? — Asif, Apr 11 '12 at 14:41
i have editted my answer hope it is helpful to you. in the tutorial [link](http://balusc.blogspot.in/2008/07/dao-tutorial-data-layer.html) by @BalusC, i am seeing the connections are closed in the **DAO utility class** — mykey, Apr 11 '12 at 14:51
the `DataSource` and `Connection#getConnection()` methods are already clear in my mind and i am also using them, but my problem is with the `close()` method, in the tutorial the `close()` methos is used to close the connection, ResultSet and Statement objects but I want to rewrite it on the basis of Connection Pooling`...What will be the the _updated_ `close()` for Connection Pooling? — Asif, Apr 11 '12 at 15:18
you don't close the pool itself but the pooled connections established in it if they are not in use... as far as i understand. it is very clear in the tutorial. — mykey, Apr 11 '12 at 15:26
yeah u et it right, it is the thing confusing me -- _close the pooled connections established in it if they are not in use_, this is the thing explained by pseudo code in the tutorial but I can't make it real code by myself. . . :( — Asif, Apr 11 '12 at 15:44
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9970/discussion-between-mykey-and-asif) — mykey, Apr 11 '12 at 15:56

Setup Connection pooling in jsp/servlets application based on MVC?

1 Answers1

Linked