一、前言
关于使用mybatis报"org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型"错误异常,详情日志如下
二、解决方法
1.原因分析 - 对于数据库SUMA、SUMB、SUMC属性数据类型NUMBER(20)对于mybatis配置应改为jdbcType=INTEGER,另外CREATED_DATE、UPDATED_DATE数据类型时间戳对于改为systimestamp类型
<?xml version="1.0" encoding="UTF-8" ?> @b@<!DOCTYPE mapper @b@ PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" @b@ "http://mybatis.org/dtd/mybatis-3-mapper.dtd">@b@<mapper namespace="com.xwood.portal.info.dao.NewInfoDAO"> @b@ @b@ <!-- 批量新增 -->@b@ <insert id="batchInsert" parameterType="com.xwood.portal.info.dto.NewInfoDTO">@b@ INSERT INTO PMS_NEWINFO_INFO@b@ (ID,@b@ FOLDER_NAME,@b@ WEB_SITE_ID,@b@ FOLDER_NAME,@b@ TITLE,@b@ CONTENTBODY, @b@ SUMA,@b@ SUMB,@b@ SUMC, @b@ CREATED_BY,@b@ CREATED_DATE,@b@ UPDATED_BY,@b@ UPDATED_DATE,@b@ DATA_STATUS)@b@ <foreach collection="batchInserts" separator="union all"@b@ item="newinfo" index="index" >@b@ select@b@ sys_guid(),@b@ #{newinfo.folderName,jdbcType=VARCHAR},@b@ #{newinfo.webSiteId,jdbcType=VARCHAR},@b@ #{newinfo.title,jdbcType=VARCHAR},@b@ #{newinfo.contentBody,jdbcType=VARCHAR}, @b@ #{newinfo.daySuma,jdbcType=VARCHAR},@b@ #{newinfo.daySumb,jdbcType=VARCHAR},@b@ #{newinfo.daySumc,jdbcType=VARCHAR}, @b@ 'sys',@b@ sysdate,@b@ 'sys',@b@ sysdate,@b@ '1'@b@ from dual@b@ </foreach>@b@ </insert>@b@ @b@ @b@</mapper>
2. 应该改配置如下
<?xml version="1.0" encoding="UTF-8" ?> @b@<!DOCTYPE mapper @b@ PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" @b@ "http://mybatis.org/dtd/mybatis-3-mapper.dtd">@b@<mapper namespace="com.xwood.portal.info.dao.NewInfoDAO"> @b@ @b@ <!-- 批量新增 -->@b@ <insert id="batchInsert" parameterType="com.xwood.portal.info.dto.NewInfoDTO">@b@ INSERT INTO PMS_NEWINFO_INFO@b@ (ID,@b@ FOLDER_NAME,@b@ WEB_SITE_ID,@b@ FOLDER_NAME,@b@ TITLE,@b@ CONTENTBODY, @b@ SUMA,@b@ SUMB,@b@ SUMC, @b@ CREATED_BY,@b@ CREATED_DATE,@b@ UPDATED_BY,@b@ UPDATED_DATE,@b@ DATA_STATUS)@b@ <foreach collection="batchInserts" separator="union all"@b@ item="newinfo" index="index" >@b@ select@b@ sys_guid(),@b@ #{newinfo.folderName,jdbcType=VARCHAR},@b@ #{newinfo.webSiteId,jdbcType=VARCHAR},@b@ #{newinfo.title,jdbcType=VARCHAR},@b@ #{newinfo.contentBody,jdbcType=VARCHAR}, @b@ #{newinfo.daySuma,jdbcType=INTEGER},@b@ #{newinfo.daySumb,jdbcType=INTEGER},@b@ #{newinfo.daySumc,jdbcType=INTEGER}, @b@ 'sys',@b@ systimestamp,@b@ 'sys',@b@ systimestamp,@b@ '1'@b@ from dual@b@ </foreach>@b@ </insert>@b@ @b@ @b@</mapper>