夜晚福利来啦,看这美女写博客,要的就是这个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 |