C3P0 one server multiple databases

Question

Good morning,

As the title says I want to create a solution to use connection pools to connect to different databases on the same Mysql server. I have followed the steps indicated in the following post:

https://stackoverflow.com/questions/26785842/multiple-data-sources-for-c3p0

My solution is this:

public class DatabaseUtility
{
        public static ComboPooledDataSource getDataSource(String db, String user, String pass) throws PropertyVetoException
        {
                ComboPooledDataSource cpds = new ComboPooledDataSource();
                cpds.setJdbcUrl("jdbc:mysql://X.X.X.X:3306/"+db);
                cpds.setUser(user);
                cpds.setPassword(pass);

                // Optional Settings
                cpds.setInitialPoolSize(5);
                cpds.setMinPoolSize(5);
                cpds.setAcquireIncrement(5);
                cpds.setMaxPoolSize(20);
                cpds.setMaxStatements(100);

                return cpds;
        }

        public static void main(String[] args) throws SQLException
        {
                Connection connection = null;
                PreparedStatement pstmt = null;
                ResultSet resultSet = null;
                try
                {
                        //QUERY to DATABASE 1
                        ComboPooledDataSource dataSource = DatabaseUtility.getDataSource("bd1", "user1", "pass1");
                        connection = dataSource.getConnection();
                        pstmt = connection.prepareStatement("SELECT * FROM municipio");

                        System.out.println("The Connection Object is of Class: " + connection.getClass());

                        resultSet = pstmt.executeQuery();
                        while (resultSet.next())
                        {
                                System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
                        }


                        //QUERY to DATABASE 2
                        dataSource = DatabaseUtility.getDataSource("bd2", "user2", "pass2");
                        connection = dataSource.getConnection();
                        pstmt = connection.prepareStatement("SELECT * FROM alojamiento");

                        System.out.println("The Connection Object is of Class: " + connection.getClass());

                        resultSet = pstmt.executeQuery();
                        while (resultSet.next())
                        {
                                System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
                        }

                }
                catch (Exception e)
                {
                        connection.rollback();
                        e.printStackTrace();
                }
        }
}

Every time I connect to a database, I call the constructor with the specific connection string.

However, I have doubts if this is an efficient solution, or I am doing something wrong ...

P.S. I'm using a project without Spring.

I would appreciate your advice, Thanks in advance,

Greetings.

Answer 1

No, this is definitely not an efficient solution. You are creating a new whole pool of Connections each time you want to grab a Connection. Try something like this.

private static ComboPooledDataSource createDataSource(String db, String user, String pass) {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setJdbcUrl("jdbc:mysql://X.X.X.X:3306/"+db);
    cpds.setUser(user);
    cpds.setPassword(pass);

    // Optional Settings
    cpds.setInitialPoolSize(5);
    cpds.setMinPoolSize(5);
    cpds.setAcquireIncrement(5);
    cpds.setMaxPoolSize(20);
    cpds.setMaxStatements(100);

    return cpds;
}

private static final class DbUserPassword {
    final String db;
    final String user;
    final String password;

    DbUserPassword( String db, String user, String password ) {
        this.db = db;
        this.user = user;
        this.password = password;
    }

    @Override
    public boolean equals( Object o ) {
        if ( o instanceof DbUserPassword ) {
            DbUserPassword that = (DbUserPassword) o;
            return ( this.db.equals( that.db ) && this.user.equals( that.user ) && this.password.equals( that.password ) );
        }
        else {
            return false;
        }
}

    @Override
    public int hashCode() {
        return db.hashCode() ^ user.hashCode() ^ password.hashCode();
    }

}

// MT: protected by class' lock
private static HashMap<DbUserPassword,ComboPooledDataSource> poolMap = new HashMap<>();

public static synchronized ComboPooledDataSource getDataSource(String db, String user, String pass) {
    DbUserPassword dbup = new DbUserPassword( db, user, pass );
    ComboPooledDataSource out = poolMap.get(dbup);
    if ( out == null ) {
        out = createDataSource( db, user, pass );
        poolMap.put( dbup, out );
    }
    return out;
}