Oracle批量新增或插入报java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型问题

在项目开发的时候，Oracle表中的informCentralManId，informProvinceManId，informCityManId字段类型是 NUMBER，当这几个字段插入NULL的时候，就会报java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型问题

原因：在mybatis中，字段没有指定类型，当为NULL的时候，可以使当字符串处理了。

处理：在对应的字段上加上 jdbcType=INTEGER

这是报错前的sql：

<insert id="insertBatch" parameterType="java.util.List">
    INSERT INTO T_INFORM_RULE(
    id,
    foreign_key,
    threshold_grade,
    alarm_frequency,
    inform_way,
    inform_central_man_id,
    inform_province_man_id,
    inform_city_man_id,
    business_key,
    created_by,
    created_time,
    updated_by,
    updated_time
    )
    select TIR_ID_SEQ.NEXTVAL,cd.* from(
    <foreach collection="tInformRules" item="item" index="index" separator="union all">
      select
      #{item.foreignKey},
      #{item.thresholdGrade},
      #{item.alarmFrequency},
      #{item.informWay},
      #{item.informCentralManId},
      #{item.informProvinceManId},
      #{item.informCityManId},
      #{item.businessKey},
      #{item.createdBy},
      #{item.createdTime},
      #{item.updatedBy},
      #{item.updatedTime}
      from dual
    </foreach>
    ) cd
  </insert>

修复后的sql：

<insert id="insertBatch" parameterType="java.util.List">
    INSERT INTO T_INFORM_RULE(
    id,
    foreign_key,
    threshold_grade,
    alarm_frequency,
    inform_way,
    inform_central_man_id,
    inform_province_man_id,
    inform_city_man_id,
    business_key,
    created_by,
    created_time,
    updated_by,
    updated_time
    )
    select TIR_ID_SEQ.NEXTVAL,cd.* from(
    <foreach collection="tInformRules" item="item" index="index" separator="union all">
      select
      #{item.foreignKey},
      #{item.thresholdGrade},
      #{item.alarmFrequency},
      #{item.informWay},
      #{item.informCentralManId,jdbcType=INTEGER},
      #{item.informProvinceManId,jdbcType=INTEGER},
      #{item.informCityManId,jdbcType=INTEGER},
      #{item.businessKey},
      #{item.createdBy},
      #{item.createdTime},
      #{item.updatedBy},
      #{item.updatedTime}
      from dual
    </foreach>
    ) cd
  </insert>