MySQL connection pooling with JERSEY
<p>I'm developping a RESTful API with Jersey and MySQL.</p>

<p>I'm actually using the JDBC driver to connect to the database and I create a new connection everytime I want to acess it. As it clearly is a memory leakage, I started to implement the <code>ServletContextClass</code>class but I don't know how to call the method when I need to get the result of a SQL query.</p>

<p>Here is how I did it wrong:</p>

<p><strong>DbConnection.java</strong>
    public class DbConnection {</p>

<pre><code>    public Connection getConnection() throws Exception {
        try {
            String connectionURL = "jdbc:mysql://root:port/path";
            Connection connection = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "password");
            return connection;
        }

        catch (SQLException e) {
            throw e;
        }
    }
}
</code></pre>

<p><strong>DbData.java</strong></p>

<pre><code>public ArrayList&lt;Product&gt; getAllProducts(Connection connection) throws Exception {
    ArrayList&lt;Product&gt; productList = new ArrayList&lt;Product&gt;();
    try {
        PreparedStatement ps = connection.prepareStatement("SELECT id, name FROM product");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Product product = new Product();
            product.setId(rs.getInt("id"));
            product.setName(rs.getString("name"));
            productList.add(product);
        }
        return productList;
    } catch (Exception e) {
            throw e;
    }
}
</code></pre>

<p><strong>Resource.java</strong></p>

<pre><code>@GET
@Path("task/{taskId}")
@Consumes(MediaType.APPLICATION_JSON)
public Response getInfos(@PathParam("taskId") int taskId) throws Exception {
    try {
        DbConnection database= new DbConnection();
        Connection connection = database.getConnection();
        Task task = new Task();
        DbData dbData = new DbData();
        task = dbData.getTask(connection, taskId);

        return Response.status(200).entity(task).build();
    } catch (Exception e) {
        throw e;
    }
}
</code></pre>

<p>Here is where I ended up trying to implement the new class:</p>

<p><strong>ServletContextClass.java</strong></p>

<pre><code>public class ServletContextClass implements ServletContextListener {

    public Connection getConnection() throws Exception {
        try {
            String connectionURL = "jdbc:mysql://root:port/path";
            Connection connection = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "password");
            return connection;
        } catch (SQLException e) {
            throw e;
        }
    }

    public void contextInitialized(ServletContextEvent arg0) {
        System.out.println("ServletContextListener started");
        DbConnection database = new DbConnection();
        try {
            Connection connection = database.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("ServletContextListener destroyed");
        //con.close ();       
    }

}
</code></pre>

<p>But problem is, I don't know what to do next. Any help? Thanks</p>