title: MySQL connection pooling with JERSEY
body: <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>