MyBatis - jdbcTypeForNull Oracle

Question

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.

Solution 1

"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

Solution 2

For those using the mybatis Spring Boot starter, this is the property you can add to application.properties to correct this problem:

mybatis.configuration.jdbc-type-for-null=NULL

Solution 3

Maybe its late, but here is the solution:

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

    sessionFactory.setDataSource(dataSource);

    sessionFactory.setTypeAliasesPackage("com.xxx.mapper");

    SqlSessionFactory sqlSessionFactory = sessionFactory.getObject();

    sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);

    return sqlSessionFactory;
}

Comments

[David]

YAML configuration really needs quotes around NULL. Thank you @pratiyush.