mybatis做批量插入时，当数据为null时的处理

当mybatis做批量插入时，插入的字段可能没值，此时不做处理的话，mybatis会报异常，执行失败根据mybatis的官网介绍，此时需要添加对应的jdbcType类型映射，以处理null值mybatis和数据库字段的对应关系MybatisJdbcTypeOracleMySqlJdbcTypeARRAYJdbcTypeBIGINTBIGINTJdbcTypeBINARY...

记录学习日常

6891人浏览 · 2020-11-25 19:41:56

记录学习日常 · 2020-11-25 19:41:56 发布

当mybatis做批量插入时，插入的字段可能没值，此时不做处理的话，mybatis会报异常，执行失败

根据mybatis的官网介绍，此时需要添加对应的jdbcType类型映射，以处理null值

mybatis和数据库字段的对应关系
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做连接查询结果去重