Connection pooling with SQLServerXADataSource

Question

The documentation of the SQLServerXADataSource interface, says that it provides connection pooling and whenever a client is done with the connection, should call the close() method; in this case the connection won't be closed but returned to the pool. I'm trying this, but next time I'm trying to get a connection it says the connection is already closed. How can I implement the connection pooling using this interface?

The class that provides the connection is:

public class ConnectionPool {
   SQLServerXADataSource ds;
   Decoder dec = Base64.getDecoder();
   Connection connection = null;

   public ConnectionPool(InputStream inputStream) throws Exception {
    ds = new SQLServerXADataSource();
    boolean res = Config.load(inputStream);
    if (res) {
        ds.setServerName(Config.SQL_SERVER());
        ds.setDatabaseName(Config.SQL_DB_NAME());
        ds.setIntegratedSecurity(Config.SQL_INTEGRATED_SECURITY());
        ds.setPortNumber(Config.SQL_PORT());
        if (!Config.SQL_INTEGRATED_SECURITY()) {
            ds.setUser(Config.SQL_USERNAME());
            ds.setPassword(new String(dec.decode(Config.SQL_USER_PASSWORD())));
           }
       } else {
           throw new Exception("Failed to load parameters.");
       }
       connection = ds.getConnection();
   }

   public Connection getConnection() throws SQLServerException {
       return this.connection;
   }
}

After creating the object of ConnectionPool (when the user is logged in), I save it as a session attribute so that the servlets can get it when they need it.

Can you please provide an example of taking advantage of this interface to implement connection pooling in a web application? I know I'm not doing it right, but cannot figure out where the problem is.

Mark Rotteveel · Accepted Answer · 2016-02-09 13:57:17Z

You are creating a connection once, in your constructor. If you close this connection, then this connection is closed (even if the physical handle is returned to the connection pool). Instead your getConnection method should do something like:

public Connection getConnection() throws SQLServerException {
    return ds.getConnection();
}

Or better yet: just give access to the data source itself instead of introducing an unnecessary wrapper.

However as far as I know the SQLServerXADataSource is not a connection pool. It is a factory for XA connections (which you aren't using in your code example), and also extends SQLServerConnectionPoolDataSource (which is a factory for connections to be used by a connection pool, not a connection pool itself).

The Java EE way is to define a JNDI datasource (which could use the SQLServerXADataSource or SQLServerConnectionPoolDataSource as its factory), and then inject (or get) that data source from JNDI.

Roman C · Accepted Answer · 2024-01-04 10:35:16Z

0

A database might close the connection, and this is not a correct implementation of connection pooling. If you want more information about connection pooling you might read commons-dbcp project, or c3p0. Here the example of datasource that implements connection pooling.

edited Jan 4, 2024 at 10:35

answered Feb 9, 2016 at 11:20

Roman C

1

Collectives™ on Stack Overflow

Connection pooling with SQLServerXADataSource

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related