在项目开发的时候,Oracle表中的informCentralManId,informProvinceManId,informCityManId字段类型是 NUMBER,当这几个字段插入NULL的时候,就会报java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型问题
原因:在mybatis中,字段没有指定类型,当为NULL的时候,可以使当字符串处理了。
处理:在对应的字段上加上 jdbcType=INTEGER
这是报错前的sql:
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(
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
) cd
修复后的sql:
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(
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
) cd