Cause: java.sql.SQLException: 无效的列类型: 1111(Mybatis中insert插入空值时，需要指定JdbcType)

夜晚福利来啦,看这美女写博客,要的就是这个feel!!!
今天在工作中使用mybatis,在insert向表中插入值时候,遇到一个问题.幸得下面这位博主的文章,问题圆满解决,先将内容整理如下;

首先感谢博主makemyownlife

一.异常再现

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #6 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 
; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; 无效的列类型: 1111; nested exception is java.sql.SQLException: 无效的列类型: 1111 

二.异常原因

MyBatis 插入空值时，需要指定JdbcType 
mybatis insert空值报空值异常，但是在pl/sql不会提示错误，主要原因是mybatis无法进行转换， 

我当时业务是记录订单信息,故涉及插入null值,而我并没有指定jdbcType,进而导致报错;

三.相关代码

1.修改之前的xml文件

Xml代码  收藏代码
<insert id="insertCustomerLog" parameterType="map">  
      insert into customer_log  
      (  
      ID,  
      CUSTOMER_SERVICE_USER_NAME,  
      user_name,  
      CONTENT,  
      LOG_FIRST_TYPE,  
      STATUS,  
      LINKED_ID,  
      FEE,  
      ACCOUNT_FIRST_TYPE,  
      ACCOUNT_SECOND_TYPE,  
      ACCOUNT_THIRD_TYPE,  
      LOG_SECOND_TYPE,  
      LOG_IP,  
      MEMO  
      )  
      values  
      (  
             seq_customer_log.nextval ,  
            #{customerServiceUserName} ,  
            #{username},  
            #{content},  
            #{logFirstType},  
            #{status},  
            #{linkedId},  
            #{fee},  
            #{accountFirstType},  
            #{accountSecondType},  
            #{accountThirdType},  
            #{logSecondType},  
            #{logIp},  
            #{memo}  
      )  
  </insert>  

2.修改后的xml文件

Xml代码  收藏代码
<insert id="insertCustomerLog1" parameterType="com.diyicai.customer.domain.CustomerLog">  
        insert into customer_log  
               (  
               ID,  
               CUSTOMER_SERVICE_USER_NAME,  
               user_name ,  
               CONTENT,  
               LOG_FIRST_TYPE,  
               STATUS,  
               LINKED_ID,  
               FEE,  
               ACCOUNT_FIRST_TYPE,  
               ACCOUNT_SECOND_TYPE,  
               ACCOUNT_THIRD_TYPE,  
               LOG_SECOND_TYPE,  
               LOG_IP,  
               MEMO  
               )  
               values  
               (  
               seq_customer_log.nextval ,  
               #{customerServiceUserName,jdbcType=VARCHAR} ,  
               #{username,jdbcType=VARCHAR},  
               #{content,jdbcType=VARCHAR},  
               #{logFirstType,jdbcType=NUMERIC},  
               #{status,jdbcType=NUMERIC},  
               #{linkedId,jdbcType=VARCHAR},  
               #{fee,jdbcType=NUMERIC},  
               #{accountFirstType,jdbcType=NUMERIC},  
               #{accountSecondType,jdbcType=NUMERIC},  
               #{accountThirdType,jdbcType=NUMERIC},  
               #{logSecondType,jdbcType=NUMERIC},  
               #{logIp,jdbcType=VARCHAR},  
               #{memo,jdbcType=VARCHAR}  
               )  
    </insert>  

3.Mybatis JdbcType与Oracle、MySql数据类型对应列表

java.sql.Types 值	Java 类型	IBM DB2	Oracle	Sybase	SQL	Informix	IBM Content Manager
BIGINT	java.lang.long	BIGINT	NUMBER (38, 0)	BIGINT	BIGINT	INT8	DK_CM_BIGINT
BINARY	byte[]	CHAR FOR BIT DATA	RAW	BINARY	IMAGE	BYTE	DK_CM_BLOB
BIT	java.lang.Boolean	N/A	BIT	BIT	BIT	BIT	DK_CM_SMALLINT
BLOB	byte[]	BLOB	BLOB	BLOB	BLOB	BLOB	DK_CM_BLOB
CHAR	java.lang.String	CHAR, GRAPHIC	CHAR	CHAR	CHAR	CHAR	DK_CM_CHAR
CLOB	java.lang.String	CLOB, DBCLOB	CLOB	CLOB	CLOB	CLOB	DK_CM_CLOB
DATE	java.sql.Date	DATE	DATE	DATE	DATE	DATE	DK_CM_DATE
DECIMAL	java.math.BigDecimal	DECIMAL	NUMBER	DECIMAL, MONEY, SMALLMONEY	DECIMAL	DECIMAL	DK_CM_DECIMAL
DOUBLE	java.lang.Double	DOUBLE	DOUBLE PRECISION	DOUBLE PRECISION	DOUBLE PRECISION	DOUBLE PRECISION	DK_CM_DOUBLE
FLOAT	java.lang.Double	FLOAT	FLOAT	FLOAT	FLOAT	FLOAT	DK_CM_DOUBLE
INTEGER	java.lang.Integer	INTEGER	INTEGER	INT	INTEGER	INTEGER	DK_CM_INTEGER
JAVA_OBJECT	java.lang.Object	JAVA_OBJECT	JAVA_OBJECT	JAVA_OBJECT	JAVA_OBJECT	OPAQUE	N/A
LONGVARBINARY	byte[]	LONG VARCHAR FOR BIT DATA	LONG RAW	IMAGE	IMAGE	BYTE	DK_CM_BLOB
LONGVARCHAR	java.lang.String	LONG VARCHAR, LONG VARGRAPHIC	LONG	TEXT	TEXT	TEXT	DK_CM_VARCHAR(3500)
NUMERIC	java.math.BigDecimal	NUMERIC	NUMBER	NUMERIC	NUMERIC	NUMERIC	DK_CM_DECIMAL
OTHER	java.lang.Object	OTHER	OTHER	OTHER	OTHER	OTHER	N/A
REAL	java.lang.Float	REAL	REAL	REAL	REAL	REAL	DK_CM_DOUBLE
SMALLINT	java.lang.Integer	SMALLINT	SMALLINT	SMALLINT	SMALLINT	SMALLINT	DK_CM_INTEGER
TIME	java.sql.Time	TIME	DATE	TIME	TIME	DATETIME HOUR TO SECOND	DK_CM_TIME
TIMESTAMP	java.sql.Timestamp	TIMESTAMP	DATE	DATETIME, SMALLDATETIME	DATETIME	DATETIME YEAR TO FRACTION (5)	DK_CM_TIMESTAMP
TINYINT	java.lang.Bute	SMALLINT	TINYINT	TINYINT	TINYINT	TINYINT	DK_CM_INTEGER
VARBINARY	byte[]	VARCHAR FOR BIT DATA	RAW	VARBINARY	IMAGE	BYTE	DK_CM_BLOB
VARCHAR	java.lang.String	VARCHAR, VARGRAPHIC	VARCHAR	VARCHAR	VARCHAR	VARCHAR	DK_CM_VARCHAR