Static class to get connections from connection pool

Question

I want to understand the concepts of connection pool.For example I have the following xml file in META-INF with my DB settings

 <Resource name="jdbc/appname"
              auth="Container"
              type="javax.sql.DataSource"
              maxActive="100" 
              maxIdle="30"
              maxWait="10000"
              minIdle="10"
              username="postgres" 
              password="123"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost:5432/Lab4"/>

To use connection pool I use the folliwng class

    public class DataBaseConnection {

        private static DataSource dataSource;

        static {
            try {
                InitialContext initContext = new InitialContext();
                dataSource = (DataSource) initContext.lookup("java:comp/env/jdbc/appname");
            } catch (NamingException ex) {
                throw new RuntimeException(ex);
            }
        }

        public static Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }

This is the usage

public class UserQueries{ 
 public User selectUserByLoginAndPassword(final String login, final String password) {
        try(Connection connection=DataBaseConnection.getConnection())
        try (PreparedStatement st = connection.prepareStatement(SELECT_QUERY)) {
            st.setString(1, login);
            st.setString(2, password);
            ResultSet result = st.executeQuery();
            while (result.next()) {
                final User user = User.newBuilder()
                        .setId(result.getInt("id"))
                        .setAge(result.getInt("age"))
                        .setName(result.getString("name"))
                        .setPassword(result.getString("password"))
                        .setLogin(login)
                        .build();
                return user;

            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        throw new NullPointerException("Nu such user in db");
    }

   }

The question is, if i modify DataBaseConnection in the following way,

public class DataBaseConnection {

        private  DataSource dataSource;

        public DataBaseConnection()
            try {
                InitialContext initContext = new InitialContext();
                dataSource = (DataSource) initContext.lookup("java:comp/env/jdbc/appname");
            } catch (NamingException ex) {
                throw new RuntimeException(ex);                
        }

        public  Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }

And i will create new DataBaseConnection object in each class that use db conections, does it mean that each class(for example UserQueries) will create separates conection pool to use?

Example

public class UserQueries{ 
  private DataBaseConnection dbCon = new DataBaseConnection();
 public User selectUserByLoginAndPassword(final String login, final String password) {
        try(Connection connection = dbCon.getConnection())
        try (PreparedStatement st = connection.prepareStatement(SELECT_QUERY)) {
            st.setString(1, login);
            st.setString(2, password);
            ResultSet result = st.executeQuery();
            while (result.next()) {
                final User user = User.newBuilder()
                        .setId(result.getInt("id"))
                        .setAge(result.getInt("age"))
                        .setName(result.getString("name"))
                        .setPassword(result.getString("password"))
                        .setLogin(login)
                        .build();
                return user;

            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        throw new NullPointerException("Nu such user in db");
    }

   }

Answer 1

Each JNDI lookup returns a new instance of DataSource. Each DataSource instance maintains it's own pool of connections.

See tomcat implementation of Datasource.getConnection() below:,

public Connection getConnection() throws SQLException {
    if (pool == null)
        return createPool().getConnection();
    return pool.getConnection();
}

And this creates the connectionPool:

private synchronized ConnectionPool pCreatePool() throws SQLException {
    if (pool != null) {
        return pool;
    } else {
        pool = new ConnectionPool(poolProperties);
        return pool;
    }
}

In summary,

• New DataSource instance is returned for every JNDI lookup.
• New connection pool will be created for every instance of DataSource