When using SimpleJdbcInsert with Snowflake, it appears Spring JDBC is closing the underlying ResultSet when interrogating the metadata when it executes the sql. This problem does not occur when using jdbcTemplate without SimpleJdbcInsert (see the two variations in the code below).
Is this a bug in Spring or perhaps the Snowflake jdbc driver?
Info: spring-boot-starter-jdbc: 2.2.4-RELEASE snowflake-jdbc: 3.12.0
DDL for table:
create or replace table MYTABLE (MESSAGE TEXT)
Sample code:
@SpringBootApplication
@Slf4j
public class SpringJdbcBug implements ApplicationRunner {
@Autowired private JdbcTemplate jdbcTemplate;
public static void main(String[] args) {
SpringApplication.run(SpringJdbcBug.class, args);
}
@Override
public void run(ApplicationArguments args) {
testQuery();
insertRecord("Hello word");
}
private void testQuery() {
int count = jdbcTemplate.queryForObject("select count(*) from MYTABLE", Integer.class);
log.debug("record count for table MYTABLE={}", count);
}
private void insertRecord(String message) {
log.debug("insertRecord called");
boolean useJdbc = false;
if (useJdbc) {
// this code works fine
String query="insert into MYTABLE (MESSAGE) values (?)";
Boolean rc = jdbcTemplate.execute(query, (PreparedStatementCallback<Boolean>) pstmt -> {
pstmt.setString(1, message);
return pstmt.execute();
});
} else {
// this code does not work
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("MYTABLE");
Map<String, Object> parameters = new HashMap<>();
parameters.put("MESSAGE", message);
simpleJdbcInsert.execute(parameters);
}
}
}
DataSource creation:
@Bean
public JdbcTemplate jdbcTemplate() {
log.info("Creating data source, driver={}, username={}, url={}", jdbcDriverClass, jdbcUsername, jdbcUrl);
SnowflakeBasicDataSource basicDataSource = new SnowflakeBasicDataSource();
basicDataSource.setSsl(true);
basicDataSource.setUser(jdbcUsername);
basicDataSource.setPassword(jdbcPassword);
basicDataSource.setUrl(jdbcUrl);
return new JdbcTemplate(basicDataSource);
}
application.properties (datasource only)
spring.autoconfigure.exclude=org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
spring.datasource.name=snowflake-datasource
spring.datasource.driver-class-name=net.snowflake.client.jdbc.SnowflakeDriver
spring.datasource.username=jpardi
spring.datasource.password=${SF_PASSWORD:password_empty}
spring.datasource.url=jdbc:snowflake://${snowflake.account}.${snowflake.region}.snowflakecomputing.com/?db=${vault.database.name}&schema=${vault.schema.name}&role=${vault.role.name}&warehouse=${vault.warehouse.name}&tracing=FINE
log:
2020-03-01 15:59:32.392 JPARDIPC snowpiptest DEBUG 17092 --- [ main] s.c.j.SnowflakeDatabaseMetaDataResultSet:175 : failed to close
net.snowflake.client.jdbc.SnowflakeSQLException: Result set has been closed.
at net.snowflake.client.jdbc.SnowflakeBaseResultSet.raiseSQLExceptionIfResultSetIsClosed(SnowflakeBaseResultSet.java:94)
at net.snowflake.client.jdbc.SnowflakeBaseResultSet.getStatement(SnowflakeBaseResultSet.java:940)
at net.snowflake.client.jdbc.SnowflakeDatabaseMetaDataResultSet.close(SnowflakeDatabaseMetaDataResultSet.java:157)
at org.springframework.jdbc.support.JdbcUtils.closeResultSet(JdbcUtils.java:125)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.locateTableAndProcessMetaData(GenericTableMetaDataProvider.java:337)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.initializeWithTableColumnMetaData(GenericTableMetaDataProvider.java:222)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.lambda$createMetaDataProvider$0(TableMetaDataProviderFactory.java:80)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.createMetaDataProvider(TableMetaDataProviderFactory.java:52)
at org.springframework.jdbc.core.metadata.TableMetaDataContext.processMetaData(TableMetaDataContext.java:171)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compileInternal(AbstractJdbcInsert.java:277)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compile(AbstractJdbcInsert.java:261)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.checkCompiled(AbstractJdbcInsert.java:309)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecute(AbstractJdbcInsert.java:335)
at org.springframework.jdbc.core.simple.SimpleJdbcInsert.execute(SimpleJdbcInsert.java:117)
at com.pardi.snowpipetest.SpringJdbcBug.insertRecord(SpringJdbcBug.java:57)
at com.pardi.snowpipetest.SpringJdbcBug.run(SpringJdbcBug.java:28)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:775)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:765)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
at com.pardi.snowpipetest.SpringJdbcBug.main(SpringJdbcBug.java:22)
...... other log statements ......
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-03-01 15:59:32.705 JPARDIPC snowpiptest ERROR 17092 --- [ main] o.s.boot.SpringApplication :826 : Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:778)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:765)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
at com.pardi.snowpipetest.SpringJdbcBug.main(SpringJdbcBug.java:22)
Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Boolean (java.lang.Integer and java.lang.Boolean are in module java.base of loader 'bootstrap')
at net.snowflake.client.jdbc.SnowflakeDatabaseMetaDataResultSet.getBoolean(SnowflakeDatabaseMetaDataResultSet.java:345)
at net.snowflake.client.jdbc.SnowflakeBaseResultSet.getBoolean(SnowflakeBaseResultSet.java:167)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.processTableColumns(GenericTableMetaDataProvider.java:414)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.locateTableAndProcessMetaData(GenericTableMetaDataProvider.java:346)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.initializeWithTableColumnMetaData(GenericTableMetaDataProvider.java:222)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.lambda$createMetaDataProvider$0(TableMetaDataProviderFactory.java:80)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.createMetaDataProvider(TableMetaDataProviderFactory.java:52)
at org.springframework.jdbc.core.metadata.TableMetaDataContext.processMetaData(TableMetaDataContext.java:171)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compileInternal(AbstractJdbcInsert.java:277)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compile(AbstractJdbcInsert.java:261)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.checkCompiled(AbstractJdbcInsert.java:309)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecute(AbstractJdbcInsert.java:335)
at org.springframework.jdbc.core.simple.SimpleJdbcInsert.execute(SimpleJdbcInsert.java:117)
at com.pardi.snowpipetest.SpringJdbcBug.insertRecord(SpringJdbcBug.java:57)
at com.pardi.snowpipetest.SpringJdbcBug.run(SpringJdbcBug.java:28)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:775)
... 5 common frames omitted
Comment From: wilkinsona
This looks like a bug in the JDBC driver to me. From the javadoc for ResultSet.close():
Calling the method close on a ResultSet object that is already closed is a no-op.