关于数据库中decimal类型数据没有默认值时的数据插入问题

最新推荐文章于 2025-06-24 13:18:41 发布

原创最新推荐文章于 2025-06-24 13:18:41 发布 · 1.1w 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #Biddecimal #decimal

编码问题专栏收录该内容

8 篇文章

订阅专栏

当数据库中decimal类型字段无默认值，Mybatis在接收到0值时会将其解析为null导致插入错误。解决方案包括：1.为字段设置默认值；2.修改Mybatis映射文件，移除Integer和BigDecimal的空值判断条件。

<insert id="insertActivity" parameterType="TDeptActivityResultDto" useGeneratedKeys="true" keyProperty="id">
        insert into t_dept_activity(
        <if test="id != null and id != 0">ID,</if>
        <if test="deptId != null and deptId != 0">DEPT_ID,</if>
        <if test="activityName != null and activityName != ''">ACTIVITY_NAME,</if>
        <if test="activityDescription != null and activityDescription != ''">ACTIVITY_DESCRIPTION,</if>
        <if test="activityStartTime != null">ACTIVITY_START_TIME,</if>
        <if test="activityEndTime != null">ACTIVITY_END_TIME,</if>
        <if test="matchRuleType != null and matchRuleType != ''">MATCH_RULE_TYPE,</if>
        <if test="victoryCounts != null and victoryCounts != ''">VICTORY_COUNTS,</if>
        <if test="goodsCounts != null and goodsCounts != ''">GOODS_COUNTS,</if>
        <if test="goodsId != null and goodsId != ''">GOODS_ID,</if>
        <if test="exchangeDeadline != null">EXCHANGE_DEADLINE,</if>
        <if test="entryFee != null and entryFee != ''">ENTRY_FEE,</if>
        <if test="activityStatus != null and activityStatus != ''">ACTIVITY_STATUS,</if>
        <if test="createBy != null and createBy != ''">CREATE_BY,</if>
        <if test="remark != null and remark != ''">remark,</if>
        CREATE_TIME,
        DELETE_FLG
        )values(
        <if test="id != null and id != 0">#{id},</if>
        <if test="deptId != null and deptId != 0">#{deptId},</if>
        <if test="activityName != null and activityName != ''">#{activityName},</if>
        <if test="activityDescription != null and activityDescription != ''">#{activityDescription},</if>
        <if test="activityStartTime != null">#{activityStartTime},</if>
        <if test="activityEndTime != null">#{activityEndTime},</if>
        <if test="matchRuleType != null and matchRuleType != ''">#{matchRuleType},</if>
        <if test="victoryCounts != null and victoryCounts != ''">#{victoryCounts},</if>
        <if test="goodsCounts != null and goodsCounts != ''">#{goodsCounts},</if>
        <if test="goodsId != null and goodsId != ''">#{goodsId},</if>
        <if test="exchangeDeadline != null">#{exchangeDeadline},</if>
        <if test="entryFee != null and entryFee != ''">#{entryFee},</if>
        <if test="activityStatus != null and activityStatus != ''">#{activityStatus},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        sysdate(),0
        )
    </insert>

先贴代码，此时因为数据库中 ENTRY_FEE 字段没有默认值，当页面传入0时，会报"entryFee don't have a default value"的错误，而直接运行插入sql是可以正常插入的，这是因为mybatis在存储Integer、Bigdecimal等数据类型时，会将0解析成null，结果在存储的时候就报错了。

有两种解决方法：

1. 给数据库的integer和decimal数据类型的字段设置默认值。

2. 去掉entryFee（integer/Bigdecimal）的判空条件,即<if test="entryFee != null and entryFee != ''">#{entryFee},</if> 改为<if test="entryFee != null">#{entryFee},</if>即可。

其实仔细查看代码会发现，在Bigdecimal类型数据做插入操作时，页面是0，它的inval的值是null。做查询时，DB中的值为0时，java代码中的intval的值为null。