Java - Singleton pattern for mysql connection pool - too many connections

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 Data source rejected establishment of connection, message from server: "Too many connections" after some time - is my implementation of the Singleton pattern wrong or the cause is somewhere else ?

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;
        }
    }
}

For completeness sake here are the contents of context.xml :

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/myapp">
    <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" />
</Context>

NB : 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 ds object I think is instantiated only once. I do close the connections +sets + statements in a finally block. Example use :

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;
    }

Where :

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();
        }
    }
}

Answers


I would have to say that the right way to write one is to not write one: Use an existing library like C3P0

This is not meant to be a "smarty pants" answer either. It's just a "don't reinvent the wheel" one, especially a thread-safe pool implementation, which is tricky to do properly and typically creates a minefield of thread-related, subtle and hard-to-fix bugs.


Your pool is pretty shallow - there's only one connection in it! How will that scale?

You've hard wired the JNDI name. Why not pass it in?

This won't be very good for a multi-threaded application. Connection's not thread safe.

The best advice you got was "don't do it". Use an existing pool. Or, better yet, the one that's built into your Java EE app server.


Need Your Help

How to gives a black border to TreeItem SWT?

eclipse-plugin swt eclipse-rcp jface draw2d

I have created Tree with more than 1 column using TreeColumn. Right now what I want to achieve is to create border cell for the cell item in Tree. Here is my implementation now:

How to iterate through all <select> field options in behat / mink

list option loops behat mink

I am testing a product search form. Product may be searched be different parameters (like status, material, wight etc.). When i want to search by status i do the following:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.