Apache DBCP 2: new session created if prepared statement with array is used

Question

I use Spring Boot, Apache DBCP 2, PostgreSQL 10 and generate the JdbcTemplate via

@Bean
public JdbcTemplate getJdbcTemplate() {
    try {
        Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    }
    Properties props = new Properties();
    props.setProperty(PGProperty.USER.getName(), user);
    props.setProperty(PGProperty.PASSWORD.getName(), password);
    props.setProperty(PGProperty.APPLICATION_NAME.getName(), applicationName);
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:postgresql://" + host + "/db",
            props);
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);
    PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);

    return new JdbcTemplate(dataSource);
}

I access the DB via

@Autowired
private JdbcTemplate jdbc;
...
UUID uuid1 = UUID.randomUUID();
UUID uuid2 = UUID.randomUUID();
Array array = jdbc.getDataSource().getConnection().createArrayOf("UUID", 
    new Object[] { uuid1, uuid2 });
jdbc.query("SELECT * FROM my_table WHERE id = ANY (?)", //
    ps -> ps.setArray(1, array), //
    rs -> {
        ...
    });

Everytime this select query is called a new session to the DB is created.

If I ommit the prepared statement and call the DB via

jdbc.query("SELECT * FROM my_table "
        + "WHERE id IN ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb')",
        rs -> {
            ...
        });

everything works fine: no new session to the DB will be created.

How can I use the prepared statement in the right way that no new session will be created?

Answer 1

Your usage of jdbc.getDataSource().getConnection().createArrayOf is obtaining new connections, and leaking connections as you never close that obtained connection.

If you want to obtain the connection used by JdbcTemplate, you need to use DataSourceUtils.getConnection.

However, you don't need to create that array, JdbcTemplate has explicit support for using lists for IN, see Passing in lists of values for IN clause in the Spring documentation. Or alternatively, consider the approach using SqlTypeValue described in Handling complex types for stored procedure calls.