H2 connection pool

Question

I want to make a connection pool for my h2 database. But I think my pool opens new connection every time I call getConnection(). I guess there should be a fixed amount of reusable connections, but if I run this code :

Connection conn = DataSource.getInstance().getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("SELECT * FROM NODE_USERS;");
        while (rs.next()) {
            System.out.println(rs.getString("login"));
        }
        try {
            // wait a bit
            Thread.sleep(20000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        stmt.close();
        rs.close();
        conn.close();  

DataSource:

public class DataSource {

    private static volatile DataSource datasource;
    private BasicDataSource ds;

    private DataSource() throws IOException, SQLException, PropertyVetoException {
        ds = new BasicDataSource();
        ds.setUsername("sa");
        ds.setPassword("sa");
        ds.setUrl("jdbc:h2:tcp://localhost/~/test");       
        ds.setMinIdle(5);
        ds.setMaxActive(10);
        ds.setMaxIdle(20);
        ds.setMaxOpenPreparedStatements(180);

    }

    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
        if (datasource == null) {
            synchronized (DataSource.class) {
                if (datasource == null) {
                    datasource = new DataSource();
                }
            }
            datasource = new DataSource();
        }
        return datasource;
    }

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

}

and then execute select * from information_schema.sessions;, there will be two rows. What is wrong? Also I was trying H2 tutorial example, but I've got the same result.

Answer 1

You are using a connection pool, namely BasicDataSource. It will create a configured number of connections initially and then when getConnection() is called, it will either reused a free pooled connection or create a new one, up to a configured limit (or no limit if configured so). When the connection obtained is "closed" using Connection.close(), it is actually returned to the pool instead of being closed right away.

You basically have no control over how many open connctions there will be at a given time, apart of configuring the minimum and maximum allowed open connections. You observing two open connections therefore doesn't prove anything. If you want to see whether there is a limit on open connections, configure the BasicDataSource to use at most 2 connections using BasicDataSource.maxActive(), then try to obtain three connections at the same time. And for another test, with the same configuration, try obtaining two connections, returning them using Connection.close() and then obtaining another two connections.