MyBatis+Oracle in the execution of insert time and space value error reporting: source code to find solutions

To facilitate the test, the Demo code is given first:

mybatis-oracle-config.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 4 
 5 <configuration>
 6     <properties>
 7         <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
 8         <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521/orcl"/>
 9     </properties>   
10     <environments default="dev">
11         <environment id="dev">        
12             <transactionManager type="JDBC" />
13                <dataSource type="POOLED">
14                 <property name="driver" value="${driver}"></property>
15                 <property name="url" value="${url}"></property>
16                 <property name="username" value="gys"></property>
17                 <property name="password" value="gys"></property>
18             </dataSource>
19         </environment>
20 
21     </environments>
22     <mappers>       
23         <mapper resource="mapper/oracle/user.xml"></mapper>
24     </mappers>
25 </configuration>

user.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <mapper namespace="dao.oracle.IUserMapper">
 5     <insert id="insertUser" parameterType="model.oracle.User">
 6         insert into users
 7         (name,age)
 8         values
 9         (#{name},#{age})
10     </insert>
11 
12 </mapper>

main() entry method

public static void main(String[] args) throws Exception{  
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=builder.build(Resources.getResourceAsStream("mybatis-oracle-config.xml"),"dev");
        SqlSession sqlSession=sqlSessionFactory.openSession(true);
        IUserMapper userMapper=sqlSession.getMapper(IUserMapper.class);
        User user=new User();
        //user.setName("a");//Intentionally annotated, not set. Simulated null value
        user.setAge(20);
        int count=userMapper.insertUser(user);
        System.out.println(count == 1 ? "Insert success" : "Insert failure");
        List<User> list=userMapper.getUserList();
        for (User user1 : list) {
            System.out.println(user1.toString());
        }
        sqlSession.close();
    }

For source code analysis, please refer to this blog: Source code analysis of Mybatis+Oracle with insert null error reporting

Summarize the causes of null value error:

mybatis is in the build phase, and does not know the specific JDBC type of this parameter. mybatis will give him a default 1111 number;

In the running stage of mybatis, the null value + 111 number condition makes mybatis call the setNull() method of the precompiler in the Oracle driver;

Because oracle does not recognize the 1111 number, an exception is thrown directly.

Method 1: construction time approach

In the sql of user.xml, the specific JDBC type type is specified for the parameter, so that oracle precompiler can know how to handle this null value in the way of varchar or NUMERIC.

The modified code is shown in red.  

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.oracle.IUserMapper">
    <insert id="insertUser" parameterType="model.oracle.User">
        insert into users
        (name,age)
        values
        (#{name,jdbcType=VARCHAR},#{age})
    </insert>
</mapper>

Method 2: run time method

Write a custom typeHandler.

When MyBatis does not know the specific JDBC type, specify the JDBC type in the custom typeHandler.

There are two kinds of fields commonly used in database, one is VARCHAR field and the other is numeric field. So define two typehandlers.

public class MyNumberNullTypeHandler extends IntegerTypeHandler {
    @Override
    public void setParameter(PreparedStatement ps, int i, Integer parameter, JdbcType jdbcType) throws SQLException {
        super.setParameter(ps, i, parameter, JdbcType.NUMERIC);
    }
}
public class MyStringNullTypeHandler extends StringTypeHandler {
    @Override
    public void setParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {       
        super.setParameter(ps,i,parameter,JdbcType.VARCHAR);
    }
}

Add the following configuration after properties in mybatis-oracle-config.xml

  <typeHandlers>
       <typeHandler handler="handler.oracle.MyStringNullTypeHandler" />
        <typeHandler handler="handler.oracle.MyIntegerNullTypeHandler" />
    </typeHandlers>

After comprehensive comparison, sensory method 2 is a once and for all method.

Keywords: Java Mybatis Oracle xml JDBC

Added by fifin04 on Wed, 15 Apr 2020 18:10:12 +0300