Weird MyBatis Error, “Error setting null for parameter [number] with JdbcType OTHER”
I had a very weird error today, when connecting MyBatis to Oracle Database, the complete error is like this,
Error setting null for parameter #2 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
After researching for a while, i found out that it happen because i miss to fill a parameter on mybatis xml query file. This is my xml file,
<?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="mybatistesting.MyMapper" > <select resultType="java.util.Map" id="select" parameterType="java.util.Map"> SELECT * FROM RISALAH WHERE UUID = #{UUID} AND CONTENT = #{CONTENT} </select> </mapper>
And this is my main java file,
package mybatistesting; import java.util.HashMap; import java.util.Map; import mybatistesting.config.MyBatisSqlSessionFactory; import org.apache.ibatis.session.SqlSession; public class MyBatisTesting { public static void main(String[] args) throws Exception { SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true); MyMapper mapper = sqlSession.getMapper(MyMapper.class); Map map1 = new HashMap(); map1.put("UUID", "1"); for (Map map : mapper.select(map1)) { System.out.println("map "+map); } } }
As you can see, i only set UUID as query parameter on my java file, despite that i need at least 2 parameter on my xml file. Here is how to fix it,
package mybatistesting; import java.util.HashMap; import java.util.Map; import mybatistesting.config.MyBatisSqlSessionFactory; import org.apache.ibatis.session.SqlSession; public class MyBatisTesting { public static void main(String[] args) throws Exception { SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true); MyMapper mapper = sqlSession.getMapper(MyMapper.class); Map map1 = new HashMap(); map1.put("UUID", "1"); map1.put("CONTENT", "1"); for (Map map : mapper.select(map1)) { System.out.println("map "+map); } } }
Hope it helps others, cheers 😉
2 Comments
Neelesh
about 4 years agoBut what if 'CONTENT' is actually null (and a nullable field in DB) ? In order to fix this I had made changes in mybatis query. So your query would become SELECT * FROM RISALAH WHERE UUID = #{UUID} AND CONTENT = #{CONTENT,jdbcType=NULL} That is, assuming UUID will never be null. Otherwise jdbcType=NULL should be added there as well.
Replyedwin
about 4 years agowow cool, thanks for the info bro