JDBC too many connections

Question

Here are connection methods:

private static Connection connection = null;

/**
 * Gets the database connection.
 */
public static Connection getConnection() {
    return Storage.connection;
}

/**
 * Connects to the database.
 */
public static void connectToDatabase() {
    try {
        String userName = "root";
        String password = "mysql";
        String url = "jdbc:mysql://localhost/secondsemesterprojectdanishcrowncaos";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Storage.connection = DriverManager.getConnection(url, userName,
                password);
        Storage.connection
                .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        Storage.connection.setAutoCommit(false);
    } catch (Exception e) {
        System.err.println("Cannot connect to database server." + e);
    }
}

/**
 * Terminates the database connection.
 */
public static void disconnectDatabase() {
    if (Storage.connection != null) {
        try {
            Storage.connection.close();
        } catch (Exception e) {
        }
    }
}

And here is my connection and query: All of my database connections are almost the same as this one:

public void addLoadingDock(LoadingDock loadingDock) {
    loadingDocks.add(loadingDock);

    Storage.connectToDatabase();

    PreparedStatement s;
    try {
        String query = "INSERT INTO LoadingDocks(dock_no, active, product_type_id) VALUES (?, ?, ?);";
        s = Storage.connection.prepareStatement(query,
                Statement.RETURN_GENERATED_KEYS);
        s.setInt(1, loadingDock.getDockNo());
        s.setBoolean(2, loadingDock.getActive());
        s.setInt(3, loadingDock.getProductType().getId());
        int rows = s.executeUpdate();
        Storage.connection.commit();
        System.out.println(rows + " row(s) created in database");
        ResultSet generatedKeys;
        generatedKeys = s.getGeneratedKeys();
        if (generatedKeys.next()) {
            loadingDock.setId(generatedKeys.getInt(1));
        } else {
            throw new SQLException(
                    "Creating LoadingDock failed, no generated key obtained.");
        }
        generatedKeys.close();
        s.close();
    } catch (Exception e) {
        System.err.println("Error adding LoadingDock. Details: " + e);
        try {
            Storage.connection.rollback();
        } catch (SQLException e1) {
            System.err.println("Error on rollback. Details: " + e1);
        }
    } finally {
        Storage.disconnectDatabase();
    }
}

At one moment program runs into too many connection problem.

Please tell me, what is wrong with the connections. Where I don't close them correctly? Or any other problem.

Answer 1

Two things jump out at me...

1. You should avoid using static in this way ...
2. You never check to see if the Connection is null before you create it, so it's possible that you have rogue connections still running, for example...

If you have two pieces of code that create an instance of the connection and the second is created before the first is closed, only the second instance of the Connection is disposed

Storage.connectToDatabase();
//...
Storage.connectToDatabase();
//...
Storage.disconnectDatabase();

You could start by checking for null in the connectToDatabase, for example

public static void connectToDatabase() {
    if (Storage.connection == null) {
        try {
            String userName = "root";
            String password = "mysql";
            String url = "jdbc:mysql://localhost/secondsemesterprojectdanishcrowncaos";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Storage.connection = DriverManager.getConnection(url, userName,
                    password);
            Storage.connection
                    .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            Storage.connection.setAutoCommit(false);
        } catch (Exception e) {
            System.err.println("Cannot connect to database server." + e);
        }
    }
}

To overcome the use of static, you could use a singlton pattern, for example...

public enum Storage {

    INSTANCE;

    private static Connection connection = null;

    /**
     * Gets the database connection.
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * Connects to the database.
     */
    public void connectToDatabase() {
        if (connection == null) {
            try {
                String userName = "root";
                String password = "mysql";
                String url = "jdbc:mysql://localhost/secondsemesterprojectdanishcrowncaos";
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                connection = DriverManager.getConnection(url, userName,
                        password);
                connection
                        .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                connection.setAutoCommit(false);
            } catch (Exception e) {
                System.err.println("Cannot connect to database server." + e);
            }
        }
    }

    /**
     * Terminates the database connection.
     */
    public void disconnectDatabase() {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
            }
        }
    }
}

Which guarantees a single instance of the Storage and, if done carefully, better management of the Connection.

Another alternative is the use of some kind of connection pool, which would allow it to manage the connections for you...

Answer 2

Use a Connection Pool. There are a great many available, but c3p0 and bonecp are popular. A connection pool will allow you to more easily reuse your database connections so you don't exhaust the maximum number of "open" resources.