I used mybatis-spring-1.0.3-SNAPSHOT mybatis-3.0.6 spring3.0.6.I tried to delete record from a table like this:
<delete id="deleteNote" parameterType="hashMap">
DELETE FROM BBSCS_NOTE
<where>
<if test="ids !=null and ids.length > 0">
<foreach collection="ids" item="id" open="(" close=")" separator=",">
ID IN #{id}
</foreach>
</if>
<if test="toID != null and toID != ''">AND TOID = #{toID}</if>
<if test="fromID != null and fromID != ''">AND FROMID = #{fromID}</if>
<if test="noteType != -1">AND NOTETYPE = #{noteType}</if>
</where>
</delete>
As you have seen,it's a dynamic sql.The java test code like this:
Map map = new HashMap();
String ids[] = {"1","2","3"};
map.put("ids", ids);
noteService.del(map);
When I executed java test code,there was some exception like this:
org.springframework.jdbc.UncategorizedSQLException: Error setting null parameter. Most JDBC drivers require that the JdbcType must be specified for all nullable parameters. Cause: java.sql.SQLException: Invalid column type
; uncategorized SQLException for SQL []; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
Why?Can you give me some advice to solve this problem?Thanks.
Best Answer
OK I see a few problems. First, when setting a null parameter into a Prepared Statement or a Callable Statement MyBatis needs to know the jdbc type. Like this,
You're also generating your 'in clause incorrectly. You need to use the foreach tag to only generate list of the values. Move the "ID IN" part out of the foreach tag.
I would also recommend against using HashMaps. The new Mapper classes are much better.