Mybatis-Error setting null parameter

Question

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.

MacMac · Accepted Answer · 2016-04-29 11:53:43Z

22

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,

#{myNullParamenter, jdbcType=VARCHAR}

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.

<if test="ids !=null and ids.length > 0">
    ID IN
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</if>

I would also recommend against using HashMaps. The new Mapper classes are much better.

edited Apr 29, 2016 at 11:53

MacMac

35.5k55 gold badges153 silver badges224 bronze badges

answered Dec 11, 2011 at 17:49

Andy

8,9498 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mouna · Accepted Answer · 2014-05-26 15:05:18Z

20

The problem is that since the 3.0.x versions the default JDBC type for null parameters is Types.OTHER which not supported by some JDBC drivers like Oracle 10g.

Here a post that explain this issue.

The solution I found is very simple, I set jdbcTypeForNull to NULL in the configuration file.

<configuration>
    <properties resource="mybatis-config.properties" />
    <settings>
        <setting name="jdbcTypeForNull" value="NULL" />
    </settings>

    <environments default="development">
    ....
    </environments>

    <mappers>
    ....
   </mappers>
</configuration>

edited May 26, 2014 at 15:05

answered May 26, 2014 at 13:55

Mouna

3,3993 gold badges30 silver badges39 bronze badges

Collectives™ on Stack Overflow

Mybatis-Error setting null parameter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related