Mybatis中jdbcType和javaType对应关系

Mybatis中javaType和jdbcType对应关系

JDBCType	JavaType
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	boolean
BOOLEAN	boolean
TINYINT	byte
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
FLOAT	double
DOUBLE	double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
CLOB	Clob
BLOB	Blob
ARRAY	Array
DISTINCT	mapping of underlying type
STRUCT	Struct
REF	Ref
DATALINK	java.net.URL

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

正确的使用方式：

<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>

可能出错的使用方式：

<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>

若存在传入参数为空的时候，将会报错如下：

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

错误日志是在：org.apache.ibatis.type.BaseTypeHandler 这个类的打出的。根据异常上面的代码 ：

if(parameter ==null){
    if (jdbcType == null) {
        try {
            ps.setNull(i, JdbcType.OTHER.TYPE_CODE);
        } catch (SQLException e) {
            throw new TypeException("Error setting null parameter. Most JDBC drivers require that the JdbcType must be specified for all nullable parameters. Cause: " + e, e);
        }
    } else {
        ps.setNull(i, jdbcType.TYPE_CODE);
    }
} else {
    setNonNullParameter(ps, i, parameter, jdbcType);
}

我们可以看出如果为空的时候，尝试使用 JdbcType.OTHER.TYPE_CODE该类型进行赋值操作，导致SQL报错无效的类型。

转自：https://blog.csdn.net/gaokao2011/article/details/88728197