Passing jdbc datasource v/s passing the Connection object - From servlet to java class

Question

I have a main Servlet that processes post/get requests.
I am using connection pooling (jdbc / mysql with glassfish v3) and my servlet code is:

public class Controller extends HttpServlet {
private DataSource datasource;
@Override
public void init() throws ServletException {
      super.init();
     try {
        //Database Connection pooling:
        InitialContext ctx = new InitialContext();
        datasource = (DataSource)ctx.lookup("jdbc/MySQLPool");
      }
      catch (Exception e) {
         e.printStackTrace();
       }
   }
private Connection getConnection() throws SQLException {
    return datasource.getConnection();
    }
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
    Connection connection=null;
    try {
            connection = datasource.getConnection();
            Object obj=  cmdFactory.getInstance().getCommand(Cmd).execute(connection);
        }

etc... and at the end of the servlet in a finally block i close the connection

So right now i am passing the "connection" object as a parameter in the last line, to be used by other (non servlet) java classes through lower layers of the application. Is this wrong? is it better rather to pass the datasource object (and then in the specific classes do datasource.getConnection())? or is there something similar to "getServletContext().getAttr(database)" that can be used in the other java classes to get this connection?

JB Nizet · Accepted Answer · 2012-01-21 23:13:38Z

3

A DataSource allows getting a JDBC connection (from a pool of connections, most of the time). In a servlet environment, if you ask a connection to a DataSource twice, you'll get two different connections.

So passing the DataSource doesn't make sense: you want all the objects in the chain of calls to use the same connection, and commit at the end.

And the connection must be closed by the method which got it from the DataSource, in a finally block, else the pool will leak connections, and you'll quickly run out of available connections.

answered Jan 21, 2012 at 23:13

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ccot Over a year ago

much appreciated :) and yeah concerning the leaking you are absolutely right, thanks for reminding me because i had forgotten about it!

ccot Over a year ago

A question: how do you approximate or calculate, what should be the connection pool size for a specific web app?is there like a reference to follow? it's the first time i am using connection pooling and i just used glassfish's default parameters

JB Nizet Over a year ago

I depends on the number of concurrent users, the number of connections that the database actually supports, the number of threads in the thread pool. You have to load-test your app with the expected number of concurrent users, and see what the best number is. And then you have to verify if the reality matches with your expectations.

Collectives™ on Stack Overflow

Passing jdbc datasource v/s passing the Connection object - From servlet to java class

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related