I am using MyBatis with an Oracle 11g R2 database. I am using MyBatis 3.3 with ojdbc6 12.1.0.2. My issue is whenever I tried to insert an object that is null I get the following.
org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #8 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111
My understanding is in the latest version of JDBC null is mapped to JdbcType.OTHERS which no all drivers handle, apparently Oracle is one of them.
I tried the following in my MyBatis configuration, but still no luck.
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage("org.ohtech.innovationexchange.core.domain");
sessionFactory.setTransactionFactory(springManagedTransactionFactory());
sessionFactory.setConfigurationProperties(getProperties());
return sessionFactory.getObject();
}
@Bean(name = "transactionManager")
public DataSourceTransactionManager dataSourceTransactionManager() throws PropertyVetoException{
return new DataSourceTransactionManager(dataSource());
}
@Bean
public SpringManagedTransactionFactory springManagedTransactionFactory() {
return new SpringManagedTransactionFactory();
}
private Properties getProperties() {
final Properties myBatisProperties = new Properties();
myBatisProperties.put("jdbcTypeForNull", "NULL");
return myBatisProperties;
}
I can make it work by doing the following in my mapper files but it seems really repetitive and ugly. Not sure why MyBatis is not using my properties I am passing the SqlSessionFactory bean.
"jdbcTypeForNull" is not a 'property' but a 'setting'. Can not be set by java config currently, I guess. You need a config.xml like this:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="jdbcTypeForNull" value="NULL" />
</settings>
</configuration>
and use sessionFactory.setConfigLocation(...).
For the difference between settings and properties please refer to the documentation: https://mybatis.github.io/mybatis-3/configuration.html