Is the underlying Snowflake driver pooling the connections here?

Question

Using this code:

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;


public Connection fetchSnowflakeConnection(SnowflakeSettings settings) {
    Properties properties = getProperties(settings);
    try {
        String connectStr = null;
        if (StringUtils.isBlank(settings.url)) {
            connectStr = "jdbc:snowflake://us-east-1.blah.com";
        } else {
            connectStr = "jdbc:snowflake://" + settings.url;
        }

        Driver driver = DriverManager.getDriver(connectStr);
        LOG.info("Snowflake driver version: {}.{}", driver.getMajorVersion(),
                driver.getMinorVersion());
        return DriverManager.getConnection(connectStr, properties);
    } catch (Exception e) {
        LOG.error("Problem getting Snowflake connection with these settings: " + settings.toString(),
                e);
    }
    return null;
}

Is the connection pooled? I am using the base java DriverManager but in Mark's world it is getting the Snowflake driver which is pooling the connection. So

DriverManager.getConnection(connectStr, properties);

is getting a pooled connection. Right?

I don't know Snowflake, but in most JDBC drivers, this wouldn't pool connections. In general, if you want connection pooling, you explicitly use a data source that provides connection pooling (eg c3p0, HikariCP, DBCP or others). — Mark Rotteveel, Jun 29 '20 at 14:13
I agree here . Its not being connection pooled.I have used HikariCP earlier though with Snowflake. — Ankur Srivastava, Jun 29 '20 at 15:10

score 2 · Accepted Answer · 2020-06-29T15:54:30.127

Is the connection pooled?

No, what you get with a DriverManager.getConnection(…) API call is a newly built, independent connection object.

A connection pool is a cache management function, which this API and the Snowflake JDBC driver implementation called underneath does not carry or perform.

The inbuilt Java classes under the java.sql package offer no connection pooling features, and you'll need to use a third-party library on top of the drivers, or roll your own cache management.

M o st driver implementations are kept simple to allow users to operate with a connection pooling pattern of choice at a higher level, and do not shove-in any implicit connection pooling.

P.s. Although DataSource implementations can carry some form of connection pooling, Snowflake's implementation (SnowflakeBasicDataSource, as of June 2020) is an aptly-named basic one which generates a new connection per call and does not participate in pooling.

I didn't think so. There is a post here that confused me: https://community.snowflake.com/s/question/0D50Z000098T2J9/how-to-connect-to-snowflake-db-using-connection-pooldbcp2-in-java Just wanted a confimation. Thanks! — markthegrea, Jun 29 '20 at 15:56

score 0 · Answer 2 · answered Jun 29 '20 at 15:36

You can do like this below for Hikari CP related settings.I configured it once.

..

private static final String PRIVATE_KEY_FILE = "<Path to p8 file>";
private static HikariConfig config = new HikariConfig();
private static HikariDataSource ds;

private DataSource() {}

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

..

    static {
    config.setJdbcUrl( "<Snowflake JDBC URL" );
   config.setUsername( "<username>" );
    config.setPassword( "<password>" );
    config.addDataSourceProperty( "cachePrepStmts" , "true" );
    config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
    config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );


   try {
  config.addDataSourceProperty("java.security.PrivateKey", PrivateKeyReader.get(PRIVATE_KEY_FILE));
} catch (Exception e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}
    ds = new HikariDataSource( config );
}

Is the underlying Snowflake driver pooling the connections here?

2 Answers2

..

Linked