title: Java - Singleton pattern for mysql connection pool - too many connections
body: <p>I wrote the code below - I want the DataSource to be a singleton and use the enum idiom for the singleton. I am getting a lot of <code>Data source rejected establishment of connection,  message from server: "Too many connections"</code> after some time - is my implementation of the Singleton pattern wrong or the cause is somewhere else ?</p>
  
  <pre><code>import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import javax.sql.DataSource;
  
  class DBConnectionPool {
  
      private DataSource ds = null;
  
      private DBConnectionPool() {
          try {
              Context context = new InitialContext();
              Context envctx = (Context) context.lookup("java:comp/env");
              ds = (DataSource) envctx.lookup("jdbc/TestDB");
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      private static enum PoolSingleton {
          POOL_INSTANCE;
  
          private static final DBConnectionPool singleton = new DBConnectionPool();
  
          private DBConnectionPool getSingleton() {
              return singleton;
          }
      }
  
      private static DBConnectionPool getDBConnectionPoolInstance() {
          return PoolSingleton.POOL_INSTANCE.getSingleton();
      }
  
      static Connection getConnection() {
          try {
              return getDBConnectionPoolInstance().ds.getConnection();
          } catch (SQLException e) {
              e.printStackTrace();
              return null;
          }
      }
  }
  </code></pre>
  
  <p>For completeness sake here are the contents of context.xml :</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;Context antiJARLocking="true" path="/myapp"&gt;
      &lt;Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" username="root"
          password="root" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/mydb"
          removeAbandoned="true" removeAbandonedTimeout="60" /&gt;
  &lt;/Context&gt;
  </code></pre>
  
  <p><strong>NB :</strong> I am not implementing a connection Pool ! I use the tomcat factory. What I do is wrapping the pool in a Class. The pool is the <code>ds</code> object I think is instantiated only once. I do close the connections +sets + statements in a finally block. Example use :</p>
  
  <pre><code>public User findByUsername(String username) throws DBExFailure {
          Connection conn = DBConnectionPool.getConnection();
          PreparedStatement statement = null;
          ResultSet set = null;
          final String query = "SELECT * FROM users WHERE username=?";
          if (conn != null) {
              try {
                  statement = conn.prepareStatement(query);
                  statement.setString(1, username);
                  set = statement.executeQuery();
                  while (set.next()) {
                      User user = new User();
                      // user.setId(set.getInt("ID"));
                      user.setUsername(set.getString("username"));
                      user.setName(set.getString("name"));
                      user.setSurname(set.getString("surname"));
                      user.setPassword(set.getString("password"));
                      user.setEmail(set.getString("email"));
                      user.setRole(RolesENUM.values()[set.getInt("role")]);
                      return user;
                  }
              } catch (SQLException ex) {
                  ex.printStackTrace();
                  throw new DBExFailure();
              } finally {
                  DBConnectionPool.closeResources(set, statement, conn);
              }
          }
          return null;
      }
  </code></pre>
  
  <p>Where :</p>
  
  <pre><code>static void closeResources(ResultSet set, Statement statement,
          Connection conn) {
      if (set != null) {
          try {
              set.close();
          } catch (SQLException ex) {
              ex.printStackTrace();
          }
      }
      if (statement != null) {
          try {
              statement.close();
          } catch (SQLException ex) {
              ex.printStackTrace();
          }
      }
      if (conn != null) {
          try {
              conn.close();
          } catch (SQLException ex) {
              ex.printStackTrace();
          }
      }
  }
  </code></pre>