Create fixed size of database connection inside Executors.withFixedSizePool

Question

I'm building a multi-threaded system that uses a central database.

The problem I'm having is that I want to create a fixed size of database connections.

I used Executors.newFixedSizePool to create a maximum number of pooling threads equal to MAX_N.

I'm executing my threads using execute function available by ExecutorService.

I want to build my system in a way that each pool thread within my pool holds a single connection to the database as long as this pool thread is still alive. So, when I execute my threads within the pool, there is only MAX_N connections to the database. Also, since Executors.newFixedSizePool pays attention to the number of pooling thread, and creates new ones if some of them stopped because of an exception, I want the same to be hold for my database connection.

So what I'm looking for is to create a fixed size of pool thread using Executors.newFixedSizePool, in a way that each pooling thread has it's own connection to database. If a pooling thread terminates this connection is closed, and if a new pooling thread is constructed, a new connection is also constructed and linked to this pooling thread.

I googled a way to do this but failed to find anything helpful. Is there a correct way to do this?

Is it be an issue if you create a connection pool separately and when a worker required a connection it should get from connection pool.I think if a dedicated connection then may it is not always using it. — gati sahu
It doesn't solve my problem. I need the connections count to be constant. For example, if a connection got closed accidentally, I want a new connection to be created. — Said A. Sryheni
that task will be delegate to connection pool.Make sure connection pool size should be same with pool size.If it is associated with thread then thread creation also include connection creation time . — gati sahu
By creating a connection pool do you mean to create it as an ExecutorService? or to create a connection pool using my database? — Said A. Sryheni

Jose Zevallos Jose Zevallos · Accepted Answer · 2017-07-03T12:16:03

you should use a Jdbc Connection Pool. then from anywhere in the code that you need a connection to the database, just get a connection to the database from the pool (the Connection Pool will be in charge of maintaining the desired number of connections to your database)

for example, if you use BoneCP:

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * <dependency>
 * <groupId>com.jolbox</groupId>
 * <artifactId>bonecp</artifactId>
 * <version>0.8.0.RELEASE</version>
 * </dependency>
 */
public class BoneCPexample {

    public static final int TOTAL_CONNECTIONS_TO_DATABASE = 20;

    public static void main(String[] args) throws SQLException {
        BoneCPexample boneCPexample = new BoneCPexample();
        boneCPexample.doTheWork();
    }

    private void doTheWork() throws SQLException {


        String jdbcUrlString = "jdbc:postgresql://localhost/test_database";  // jdbc:postgresql://host:port/database
        BoneCPConfig bcpConfig = new BoneCPConfig();
        bcpConfig.setJdbcUrl(jdbcUrlString);
        bcpConfig.setUsername("postgres");
        bcpConfig.setPassword("mi-password");
        bcpConfig.setPartitionCount(1);
        bcpConfig.setMinConnectionsPerPartition(TOTAL_CONNECTIONS_TO_DATABASE);
        bcpConfig.setMaxConnectionsPerPartition(TOTAL_CONNECTIONS_TO_DATABASE);
        bcpConfig.setConnectionTimeoutInMs(1 * 1000);
        bcpConfig.setDefaultAutoCommit(false);
        bcpConfig.setConnectionTestStatement("select now()");
        bcpConfig.setIdleConnectionTestPeriodInMinutes(5);


        BoneCP boneCP = new BoneCP(bcpConfig);
        Connection connection = boneCP.getConnection();

        Statement statement = connection.createStatement();
        statement.execute("select * from mytable");

    }
}

Create fixed size of database connection inside Executors.withFixedSizePool

1 Answers