Will the performance of my web app decrease if i try this code?

Question

I create "DataSourceConnection" class follow Singleton Pattern, but I want to connect to multi-database, so I write "getDataSource(String driver,String url, String username,String password)" function that will return BasicDataSource variable. I save this variable to ServletContext's attribute, and when I need connect to which database I take it from ServletContext. Should I try to use this solution?

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;

public class DataSourceConnection implements Cloneable, Serializable {

    private static final long serialVersionUID = 1L;

    private BasicDataSource dataSource;

    private static class DataSourceConnectionHolder {
        private static DataSourceConnection INSTANCE = new CustomerDataSourceConnection();
    }

    protected Object clone() throws CloneNotSupportedException {
        return new CloneNotSupportedException();
    }

    private DataSourceConnection() {
    }

    public static DataSourceConnection getInstance() {
        return DataSourceConnectionHolder.INSTANCE;
    }

    public DataSource getDataSource(String driver, String url, String username, String password) {
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

Answer 1

The purpose of the singleton pattern in this context would be to only create a single connection to the database (which is a slow operation), and reuse that connection in your code.

Since you want to connect to multiple databases, you'll need a "singleton" for each database. You shouldn't implement multiple classes for this, just a single class with multiple static "shared" connections.

It is the getConnection() that's slow, unless the DataSource maintains a connection pool.

Updated with example

public final class DataSourceConnection {
    // This defines an instance of the class
    private BasicDataSource dataSource;
    private DataSourceConnection(String driver, String url, String username, String password) {
        this.dataSource = new BasicDataSource();
        this.dataSource.setDriverClassName(driver);
        this.dataSource.setUrl(url);
        this.dataSource.setUsername(username);
        this.dataSource.setPassword(password);
    }
    public Connection getConnection() throws SQLException {
        return this.dataSource.getConnection();
    }
    // This defines the "shared"/"multiple singleton" instances
    public static final DataSourceConnection DATABASE1 = new DataSourceConnection("...", "...", "...", "...");
    public static final DataSourceConnection DATABASE2 = new DataSourceConnection("...", "...", "...", "...");
    public static final DataSourceConnection DATABASE3 = new DataSourceConnection("...", "...", "...", "...");
}

And you would use it like this:

try (Connection conn = DataSourceConnection.DATABASE2.getConnection()) {
    // Access database #2 here
}

Of course, in reality, you'd want the initialization of your 3 Data Sources to be based on some configuration file, not to be hardcoded like shown.