1. We will begin by configuring our connection pool in GlassFish 5. Once it is up and running, go to this URL:

http://localhost:8080

2. Now click on the go to the Administration Console link or if you prefer, go straight to the URL at:

http://localhost:4848/

3. Then follow this path in the left menu:

Resources | JDBC | JDBC Connection Pools

4. Click on the New button. It will open the New JDBC Connection Pool page. Fill in the fields as described here:

• Pool Name: MysqlPool
• Resource Type: javax.sql.DataSource
• Database Driver Vendor: MySql

Of course, you can make your own custom choices, but then we will be following different paths!

5. Click on the Next button. It will open the second step for our pool creation process.

This new page has three sections: General Settings, Pool Settings, and Transaction and Additional Properties. For our recipe, we are only dealing with General Settings and Additional Properties.

6. In the General Settings section make sure that DataSource Classname has this value selected:

com.mysql.jdbc.jdbc2.optional.MysqlDatasource

7. Now let's move to the Additional Properties section. There might be a bunch of properties listed, but we will just fill in a few of them:

• DatabaseName: sys
• ServerName: localhost
• User: root
• Password: mysql
• PortNumber: 3306

8. Click on the Finish button and voilá! Your connection pool is ready... or almost.

You can't access it until you do one more configuration. In the same menu, on the left, following this path:

Resources | JDBC | JDBC Resources

9. Click on the New button and then fill in the fields like this:

• JNDI Name: jdbc/MysqlPool
• Pool Name: MysqlPool

Now you are good to go! Your connection pool is ready to be used. Let's build a simple application to try it:

1. First, we create a class to get a connection from the pool:

public class ConnectionPool {

    public static Connection getConnection() throws SQLException, 
    NamingException {
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/MysqlPool");

        return ds.getConnection();
    }
}

2. Then, a class that we will use as a representation of the sys_config table (MySQL's system table):

public class SysConfig {

    private final String variable;
    private final String value;

    public SysConfig(String variable, String value) {
        this.variable = variable;
        this.value = value;
    }

    public String getVariable() {
        return variable;
    }

    public String getValue() {
        return value;
    }
}

3. Here we create another class, to create a list based on the data returned from the database:

@Stateless
public class SysConfigBean {

    public String getSysConfig() throws SQLException, NamingException {
        String sql = "SELECT variable, value FROM sys_config";

        try (Connection conn = ConnectionPool.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()
                Jsonb jsonb = JsonbBuilder.create()) {

            List<SysConfig> list = new ArrayList<>();
            while (rs.next()) {
                list.add(new SysConfig(rs.getString("variable"), 
                rs.getString("value")));
            }

            Jsonb jsonb = JsonbBuilder.create();
            return jsonb.toJson(list);
        }
    }
}

4. And finally a servlet that will try them all:

@WebServlet(name = "PoolTestServlet", urlPatterns = {"/PoolTestServlet"})
public class PoolTestServlet extends HttpServlet {

    @EJB
    private SysConfigBean config;

    @Override
    protected void doGet(HttpServletRequest request, 
    HttpServletResponse response)
            throws ServletException, IOException {

        try (PrintWriter writer = response.getWriter()) {
            config = new SysConfigBean();
            writer.write(config.getSysConfig());
        } catch (SQLException | NamingException ex) {
            System.err.println(ex.getMessage());
        }
    }
}

To try it just open this URL in your browser:

http://localhost:8080/ch06-connectionpooling/PoolTestServlet