Mybatis的insert into select命令未结束问题解决

最新推荐文章于 2024-06-19 15:23:57 发布

原创最新推荐文章于 2024-06-19 15:23:57 发布 · 1w 阅读

7 ·

CC 4.0 BY-SA版权

spring 同时被 2 个专栏收录

9 篇文章

订阅专栏

数据库

5 篇文章

订阅专栏

本文记录了一次使用MyBatis过程中遇到的SQL语法错误，并详细展示了错误信息及解决方案。通过调整XML映射文件中的参数类型声明和插入语句的格式，成功解决了ORA-00933错误。

Mybatis中使用insert into select命令，错误如下：

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

### The error may involve cn.hna.fiber.billing.common.dao.FbPayBalanceMapper.presentBalance-Inline
### The error occurred while setting parameters
### SQL: insert into FB_PAY_BALANCE      (BALANCE_ID, PAY_ID, BALANCE_TYPE_ID, BALANCE, REAL_BALANCE, USED_BALANCE, EFF_DATE, EXP_DATE)  (  select seq_balance_id.nextval,a.pay_id,?,?, ?, ?,   ?, ?  from fb_user_info a  )
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at $Proxy19.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at $Proxy21.presentBalance(Unknown Source)
        at cn.hna.fiber.billing.utils.service.impl.PresentBalanceImpl.present(PresentBalanceImpl.java:62)
        at cn.hna.fiber.billing.utils.PresentBalanceMain.main(PresentBalanceMain.java:24)

原先的配置:

<insert id="presentBalance" parameterType="******">
    insert into FB_PAY_BALANCE 
    (BALANCE_ID, PAY_ID, BALANCE_TYPE_ID, BALANCE, REAL_BALANCE, USED_BALANCE, EFF_DATE, EXP_DATE)
	(
	select SEQ_BALANCE_ID.nextval,a.pay_id,#{balanceTypeId},#{balance}, #{realBalance}, #{usedBalance}, 
	#{effDate}, #{expDate}
	from fb_user_info a
	)
  </insert>

修改配置后，问题解决。

<insert id="presentBalance" parameterType="cn.hna.fiber.billing.common.pojo.FbPayBalance" useGeneratedKeys="false" >
    insert into FB_PAY_BALANCE 
    (BALANCE_ID, PAY_ID, BALANCE_TYPE_ID, BALANCE, REAL_BALANCE, USED_BALANCE, EFF_DATE, EXP_DATE)
	(
	select SEQ_BALANCE_ID.nextval,a.pay_id,#{balanceTypeId},#{balance}, #{realBalance}, #{usedBalance}, 
	#{effDate}, #{expDate}
	from fb_user_info a
	)
  </insert>