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.
Advertisement
Answer
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();