Creating thread safe MyBatis sessions from a java.sql.Connection

Question

I'm trying to use MyBatis with a Snowflake database. My situation is similar to that of this poster:

Configure mybatis to use an existing connection

Essentially, I can get a java.sql.Connection Object, but I cannot get that by way of a DataSource, or other steps normally done by RDBMS databases like Oracle. One proposed solution is to do something like this:

SqlSession snowflakeSession = snowflakeSqlSessionFactory.openSession(getSnowflakeConnection());

These will be in a multi-threaded environment. If someone closes the snowflakeSession object, does that close the underlying java.sql.Connection object? I plan to re-use these sessions, but only within a thread. Is this necessary?

For Oracle, I can do this:

        OracleDataSource result = new OracleDataSource();
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String connectionString = jdbcUrl;
        String username = jdbcUserName; 
        String password = jdbcPassword;
        OracleDataSource oracleDataSource = (OracleDataSource)result; 
        oracleDataSource.setURL(connectionString);
        oracleDataSource.setPassword(password);
        oracleDataSource.setUser(username);
        String timeoutKey = "oracle.jdbc.ReadTimeout";
        Properties connectionProperties;
        try {
            connectionProperties = oracleDataSource.getConnectionProperties();
            if(connectionProperties==null) {
                connectionProperties = new Properties();
            }
            connectionProperties.put(timeoutKey, 60 /* minutes */ * 60 /* seconds per minutes */ * 1000 /* ms per seconds */);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;

If I could create say a SnowflakeDataSource object, I could easily create the connection in the same way as I do for Oracle. I have to be careful how to create the connection because it's also the same point that MyBatis scans the XML mapper files, or re-uses what it already found from a previous connection request.

Why can't you use a `DataSource`? If you can create JDBC connections, you can use a `DataSource` (or implement your own). — Mark Rotteveel, Jul 02 '20 at 20:22
@MarkRotteveel I'm editing my question to show my approach for Oracle, which I can't find for Snowflake — Woodsman, Jul 03 '20 at 02:45
You don't need to use a Snowflake specific `DataSource`, you can use - for example - HikariCP, and get connection pooling as well. — Mark Rotteveel, Jul 03 '20 at 08:04

score 1 · Accepted Answer · 2020-07-03T04:49:56.463

It does not appear to be documented but the Snowflake's JDBC Driver package does offer a basic DataSource class that can fetch entirely new connection objects when DataSource::getConnection() is called:

import net.snowflake.client.jdbc.SnowflakeBasicDataSource;

SnowflakeBasicDataSource ds = new SnowflakeBasicDataSource();

ds.setUrl("jdbc:snowflake://account.region.snowflakecomputing.com/");

ds.setUser("user");
ds.setPassword("password");
ds.setWarehouse("wh");
ds.setDatabaseName("db");
ds.setSchema("schema");
ds.setRole("role");

// Other arbitrary connection or session properties can be passed
// via URL params in the ds.setUrl(...) call above

// Use ds.setOauthToken(...)
// or  ds.setAuthenticator(...)
// or  ds.setPrivateKey(...)
// or  ds.setPrivateKeyFile(...)
// for alternative modes of authentication

Connection conn = ds.getConnection();

Creating thread safe MyBatis sessions from a java.sql.Connection

1 Answers1