当mybatis做批量插入时,插入的字段可能没值,此时不做处理的话,mybatis会报异常,执行失败
根据mybatis的官网介绍,此时需要添加对应的jdbcType类型映射,以处理null值
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–>修改为TEXT |
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 |
此时,在所有可能为空的字段取值中添加jdbcType=XXX(一般全部添加即可)
如代码
<!--插入所有列清单-->
<sql id="insertAllCol">
<trim prefix="(" suffix=")" suffixOverrides=",">
FPH,
EFFECTIVE_TAX_AMOUNT,
PURCHASER_TAXNO,
INVOICE_STATE,
DEDUCTIBLE_MODE,
AMOUNT,
OVERDUE_CHECK_MARK,
ABNORMAL_TYPE,
NSRSBH,
ANTI_FAKE_CODE,
UPDATE_TIME,
DEDUCTIBLE_PERIOD,
AGENCY_DRAWBACK,
RESALE_CERTIFICATE_NUMBER,
INVOICE_NO,
CREATE_TIME,
INV_ISSUE_DATE,
TAX,
AUDIT_STATE,
DEDUCTIBLE_TYPE,
DEDUCTIBLE_DATE,
MANAGEMENT_STATUS,
SALES_TAXNAME,
DEDUCTIBLE_STATE,
FLOW_ID,
INVOICE_CATAGORY,
SALES_TAXNO,
INVOICE_CODE,
ORIGINAL_PERIOD,
INFO_SOURCES,
</trim>
</sql>
<sql id="insertAllValueWithItem" databaseId="oracle">
<trim prefix=" SELECT " suffix=" FROM dual " suffixOverrides=",">
#{item.fph,jdbcType=VARCHAR},
#{item.effectiveTaxAmount,jdbcType=NUMERIC},
#{item.purchaserTaxno,jdbcType=VARCHAR},
#{item.invoiceState,jdbcType=DATE},
#{item.deductibleMode,jdbcType=VARCHAR},
#{item.amount,jdbcType=NUMERIC},
#{item.overdueCheckMark,jdbcType=VARCHAR},
#{item.abnormalType,jdbcType=VARCHAR},
#{item.nsrsbh,jdbcType=VARCHAR},
#{item.antiFakeCode,jdbcType=VARCHAR},
#{item.updateTime,jdbcType=DATE},
#{item.deductiblePeriod,jdbcType=VARCHAR},
#{item.agencyDrawback,jdbcType=VARCHAR},
#{item.resaleCertificateNumber,jdbcType=VARCHAR},
#{item.invoiceNo,jdbcType=VARCHAR},
#{item.createTime,jdbcType=DATE},
#{item.invIssueDate,jdbcType=VARCHAR},
#{item.tax,jdbcType=NUMERIC},
#{item.auditState,jdbcType=NUMERIC},
#{item.deductibleType,jdbcType=VARCHAR},
#{item.deductibleDate,jdbcType=VARCHAR},
#{item.managementStatus,jdbcType=VARCHAR},
#{item.salesTaxname,jdbcType=VARCHAR},
#{item.deductibleState,jdbcType=VARCHAR},
#{item.flowId,jdbcType=NUMERIC},
#{item.invoiceCatagory,jdbcType=VARCHAR},
#{item.salesTaxno,jdbcType=VARCHAR},
#{item.invoiceCode,jdbcType=VARCHAR},
#{item.originalPeriod,jdbcType=VARCHAR},
#{item.infoSources,jdbcType=VARCHAR},
</trim>
</sql>
注:union all和union的区别
union all连接查询,结果不去重
union做连接查询结果去重