mapper
void insertBatch(@Param("inventoryAuthHisList") List<InventoryAuthHis> inventoryAuthHisList);
xml
<insert id="insertBatch">
insert into T_INVENTORY_AUTH_HIS (HIS_OPER_ID, HIS_OPER_DATE, HIS_OPER_TYPE, AUTH_ID, INVENTORY_ID, USER_ID, AUTH_TYPE, AREA_ID)
<foreach collection="inventoryAuthHisList" item="item" open="(" separator=" union all " close=")">
select #{item.hisOperId}, #{item.hisOperDate}, #{item.hisOperType}, #{item.authId}, #{item.inventoryId}, #{item.userId}, #{item.authType}, #{item.areaId} from dual
</foreach>
</insert>
上面的批量插入报错
在执行SQL时MyBatis会自动通过对象中的属性给SQL中参数赋值,它会自动将Java类型转换成数据库的类型。
而一旦传入的是null它就无法准确判断这个类型应该是什么,就有可能将类型转换错误,从而报错。
jdbcType了解一下啊,参考jdbcType网址
Mybatis | JdbcType | Oracle | MySql |
---|---|---|---|
JdbcType | ARRAY | ||
JdbcType | BIGINT | BIGINT | |
JdbcType | BINARY | ||
JdbcType | BIT | BIT | |
JdbcType | BLOB | BLOB | BLOB |
JdbcType | BOOLEAN | ||
JdbcType | CHAR | CHAR | CHAR |
JdbcType | CLOB | CLOB | CLOB |
JdbcType | CURSOR | ||
JdbcType | DATE | DATE | DATE |
JdbcType | DECIMAL | DECIMAL | DECIMAL |
JdbcType | DOUBLE | NUMBER | DOUBLE |
JdbcType | FLOAT | FLOAT | FLOAT |
JdbcType | INTEGER | INTEGER | INTEGER |
JdbcType | LONGVARBINARY | ||
JdbcType | LONGVARCHAR | LONG | VARCHAR |
JdbcType | NCHAR | NCHAR | |
JdbcType | NCLOB | NCLOB | |
JdbcType | NULL | ||
JdbcType | NUMERIC | NUMERIC/NUMBER | NUMERIC |
JdbcType | NVARCHAR | ||
JdbcType | OTHER | ||
JdbcType | REAL | REAL | REAL |
JdbcType | SMALLINT | SMALLINT | SMALLINT |
JdbcType | STRUCT | ||
JdbcType | TIME | TIME | |
JdbcType | TIMESTAMP | TIMESTAMP | TIMESTAMP/DATETIME |
JdbcType | TINYINT | TINYINT | |
JdbcType | UNDEFINED | ||
JdbcType | VARBINARY | ||
JdbcType | VARCHAR | VARCHAR | VARCHAR |
因此修改一下xml
看数据表字段类型改
<insert id="insertBatch">
insert into T_INVENTORY_AUTH_HIS (HIS_OPER_ID, HIS_OPER_DATE, HIS_OPER_TYPE, AUTH_ID, INVENTORY_ID, USER_ID, AUTH_TYPE, AREA_ID)
<foreach collection="inventoryAuthHisList" item="item" open="(" separator=" union all " close=")">
select #{item.hisOperId}, #{item.hisOperDate}, #{item.hisOperType}, #{item.authId}, #{item.inventoryId}, #{item.userId}, #{item.authType}, #{item.areaId,jdbcType=DOUBLE} from dual
</foreach>
</insert>