mybatis连接Oracle执行begin..end批量操作返回行数问题

如下是mapper文件里的sql代码

<update id="updateByTransBatchIdAndBankId"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        update T_PLAT_ORDER  set STATE = 1
        where ORDER_ID = #{item.orderId,jdbcType=VARCHAR} and state in (0,11)
    </foreach>
</update>

如下是Dao接口方法

package com.cn.yft.ora.dao;

public interface TPlatOrderDAO {
    /**
     * 根据订单流水号更新交易状态为0或4的订单状态为1
     * @param list
     * @return
     */
    int updateByTransBatchIdAndBankId(List<TBankOrder> list);
}

程序运行时，发现有坑。不管实际更新几条，包括0条，mybatis并不打印执行行数。通过程序打印出来，发现结果始终是一个固定的值。而且，生产与本地还不一样。生产打印出来都是-1， 本地环境打印出来都是1，在Navicat里连接本地Oracle数据库里执行结果也是1。

 

程序中的jdbc配置

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
<!--        <property name="minIdle" value="${jdbc.minIdle}"/>-->
<!--        <property name="maxIdle" value="${jdbc.maxIdle}"/>-->
    </bean>
    
    <bean id="sqlSessionFactoryDb" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
        <property name="mapperLocations" value="classpath*:com/cn/yft/ora/mapper/*.xml"/>
    </bean>
    
    <!-- 定义事务管理配置 -->
    <bean id="txManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

 

 

怎么发现的这个坑呢？ 由于程序要根据这个批量操作数据的影响行数，来判断是否进行后续逻辑处理。却发现后续逻辑竟然一直不处理。通过添加log，这才发现，原来这个影响行数并不是我们的预期结果。

 

mybatis执行begin..end语句 为什么不返回影响行数呢？
原因未暂查出来。

那么，问题还得修复呀。

我的临时解决办法，是不用begin..end。

 

像下面这样，直接干掉begin..end行不行呢？

<update id="updateByTransBatchIdAndBankId"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" close=";" separator=";">
        update T_PLAT_ORDER  set STATE = 1
        where ORDER_ID = 'ddd' and state in (0,11)
    </foreach>
</update>

 

经过测试，直接干到begin..end是有些草率的。当入参List里只有一条数据时执行没问题，毕竟，这只是执行了一个单条update语句，显然不会有问题。而一旦存在多条update语句时，就会遇到包含Oracle错误码的java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符

2022-02-21 10:35:21,397 DEBUG [main] jdbc.BaseJdbcLogger (com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId:145) - ==>  Preparing: update T_PLAT_ORDER set STATE = 1 where ORDER_ID = 'ddd' and state in (0,11) ; update T_PLAT_ORDER set STATE = 1 where ORDER_ID = 'ddd' and state in (0,11) ; 
2022-02-21 10:35:21,397 DEBUG [main] jdbc.BaseJdbcLogger (com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId:145) - ==> Parameters: 
2022-02-21 10:35:21,437 INFO  [main] xml.XmlBeanDefinitionReader (org.springframework.beans.factory.xml.XmlBeanDefinitionReader:317) - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2022-02-21 10:35:21,484 INFO  [main] support.SQLErrorCodesFactory (org.springframework.jdbc.support.SQLErrorCodesFactory:126) - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符

### The error may involve com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId-Inline
### The error occurred while setting parameters
### SQL: update T_PLAT_ORDER  set STATE = 1         where ORDER_ID = 'ddd' and state in (0,11)      ;       update T_PLAT_ORDER  set STATE = 1         where ORDER_ID = 'ddd' and state in (0,11)      ;
### Cause: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符


    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:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
    at com.sun.proxy.$Proxy32.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy33.updateByTransBatchIdAndBankId(Unknown Source)
    at com.cn.yft.ora.dao.TPlatOrderDAOTest.updateByTransBatchIdAndBankId(TPlatOrderDAOTest.java:39)
    ...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy36.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    ... 34 more

 

 

接下来，依然沿着干掉begin..end的方针，这次呢，我们转变sql，改成in操作。因为我们这个批处理操作的场景用in完全可以，这样也不用纠结多条update部分成功怎么办的情况。ok，问题暂时解决。

<update id="updateByTransBatchIdAndBankId"  parameterType="java.util.List">
    update T_PLAT_ORDER  set STATE = 1
    where ORDER_ID in
    <foreach collection="list" item="item" index="index"  close=")" open="(" separator=",">
      #{item.orderId,jdbcType=VARCHAR}
    </foreach>
     and state in (0,11)
</update>

 

2022-02-21 10:55:02,732 DEBUG [main] jdbc.BaseJdbcLogger (com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId:145) - ==> Preparing: update T_PLAT_ORDER set STATE = 1 where ORDER_ID in ( ? , ? , ? ) and state in (0,11)
2022-02-21 10:55:02,734 DEBUG [main] jdbc.BaseJdbcLogger (com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId:145) - ==> Parameters: 3f12bcd8-3afe-4743-ae14-c5d53fbc1a76(String), 2022020911335800504754(String), 2022021817531700189078(String)
2022-02-21 10:55:02,744 DEBUG [main] jdbc.BaseJdbcLogger (com.cn.yft.ora.dao.TPlatOrderDAO.updateByTransBatchIdAndBankId:145) - <== Updates: 2

 

 

后续：mybatis连接Oracle执行begin..end批量update操作，未返回实际影响行数。本次曲线救国，具体原因日后还需再分析和定位。