mybatis oracle update返回值,mybatis+oracle执行update操作不能正确返回更新条数

我司对于业务重要性的要求我们使用了oracle+sharding jdbc +mybatis

切到oracle 后发现执行update 不能返回条数，返回了-1，如果同时更新两个分表返回-2，实际上数据库已经更新成功。

由于我们封装了mybatis插件，为排除插件问题，最后我使用官方demo中sharding-jdbc-spring-boot-data-mybatis-example测试复现了这个问题。

使用mysql+mybatis是没有问题的。

使用oracle错误和配置

日志

updateOrderById] <== Updates: -2

[DEBUG] 2018-01-29 16:23:11,734 --main-- [org.mybatis.spring.SqlSessionUtils] Creating a new SqlSession

[DEBUG] 2018-01-29 16:23:11,739 --main-- [org.mybatis.spring.SqlSessionUtils] SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7bef452c] was not registered for synchronization because synchronization is not active

[DEBUG] 2018-01-29 16:23:11,747 --main-- [org.springframework.jdbc.datasource.DataSourceUtils] Fetching JDBC Connection from DataSource

[DEBUG] 2018-01-29 16:23:11,748 --main-- [org.mybatis.spring.transaction.SpringManagedTransaction] JDBC Connection [io.shardingjdbc.core.jdbc.core.connection.ShardingConnection@426e505c] will not be managed by Spring

[DEBUG] 2018-01-29 16:23:11,752 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] ==> Preparing: update t_order set status = 'jkl' WHERE user_id = ?

[DEBUG] 2018-01-29 16:23:11,772 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] ==> Parameters: 51(Long)

[INFO ] 2018-01-29 16:23:11,865 --main-- [Sharding-JDBC-SQL] Logic SQL: update t_order set status = 'jkl' WHERE user_id = ?

[INFO ] 2018-01-29 16:23:11,865 --main-- [Sharding-JDBC-SQL] SQLStatement: DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), conditions=Conditions(conditions={Column(name=user_id, tableName=t_order)=Condition(column=Column(name=user_id, tableName=t_order), operator=EQUAL, positionValueMap={}, positionIndexMap={0=0})}), sqlTokens=[TableToken(beginPosition=7, originalLiterals=t_order)], parametersIndex=1))

[INFO ] 2018-01-29 16:23:11,865 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_1 ::: update t_order_0 set status = 'jkl' WHERE user_id = ? ::: [51]

[INFO ] 2018-01-29 16:23:11,865 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_1 ::: update t_order_1 set status = 'jkl' WHERE user_id = ? ::: [51]

[DEBUG] 2018-01-29 16:23:12,069 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] <== Updates: -2

[DEBUG] 2018-01-29 16:23:12,070 --main-- [org.mybatis.spring.SqlSessionUtils] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7bef452c]

[DEBUG] 2018-01-29 16:23:12,070 --main-- [org.springframework.jdbc.datasource.DataSourceUtils] Returning JDBC Connection to DataSource

oracle配置

sharding.jdbc.datasource.names=ds_0,ds_1

sharding.jdbc.datasource.ds_0.type=org.apache.tomcat.jdbc.pool.DataSource

sharding.jdbc.datasource.ds_0.driver-class-name=oracle.jdbc.OracleDriver

sharding.jdbc.datasource.ds_0.url=jdbc:oracle:thin:@10.164.204.25:1521/www.haiercash.com

sharding.jdbc.datasource.ds_0.username=hcmsg

sharding.jdbc.datasource.ds_0.password=hcmsg

sharding.jdbc.datasource.ds_0.validation-query=SELECT 1 from dual

sharding.jdbc.datasource.ds_1.type=org.apache.tomcat.jdbc.pool.DataSource

sharding.jdbc.datasource.ds_1.driver-class-name=oracle.jdbc.OracleDriver

sharding.jdbc.datasource.ds_1.url=jdbc:oracle:thin:@10.164.204.26:1521/hcmessagedb

sharding.jdbc.datasource.ds_1.username=hcmsg

sharding.jdbc.datasource.ds_1.password=hcmsg

sharding.jdbc.datasource.ds_1.validation-query=SELECT 1 from dual

sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id

sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds_${user_id % 2}

sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds_${0..1}.t_order_${0..1}

sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id

sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id % 2}

sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id

sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds_${0..1}.t_order_item_${0..1}

sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id

sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_${order_id % 2}

sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id

sharding.jdbc.config.sharding.props.sql.show=true

使用mysql也就是demo默认的配置日志如下：

updateOrderById] <== Updates: 10

[DEBUG] 2018-01-29 16:28:09,268 --main-- [org.mybatis.spring.SqlSessionUtils] Creating a new SqlSession

[DEBUG] 2018-01-29 16:28:09,278 --main-- [org.mybatis.spring.SqlSessionUtils] SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e868ef5] was not registered for synchronization because synchronization is not active

[DEBUG] 2018-01-29 16:28:09,288 --main-- [org.springframework.jdbc.datasource.DataSourceUtils] Fetching JDBC Connection from DataSource

[DEBUG] 2018-01-29 16:28:09,289 --main-- [org.mybatis.spring.transaction.SpringManagedTransaction] JDBC Connection [io.shardingjdbc.core.jdbc.core.connection.ShardingConnection@25b865b5] will not be managed by Spring

[DEBUG] 2018-01-29 16:28:09,293 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] ==> Preparing: update t_order set status = 'jkl' WHERE user_id = ?

[DEBUG] 2018-01-29 16:28:09,316 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] ==> Parameters: 51(Long)

[INFO ] 2018-01-29 16:28:09,455 --main-- [Sharding-JDBC-SQL] Logic SQL: update t_order set status = 'jkl' WHERE user_id = ?

[INFO ] 2018-01-29 16:28:09,455 --main-- [Sharding-JDBC-SQL] SQLStatement: DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), conditions=Conditions(conditions={Column(name=user_id, tableName=t_order)=Condition(column=Column(name=user_id, tableName=t_order), operator=EQUAL, positionValueMap={}, positionIndexMap={0=0})}), sqlTokens=[TableToken(beginPosition=7, originalLiterals=t_order)], parametersIndex=1))

[INFO ] 2018-01-29 16:28:09,456 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_1 ::: update t_order_0 set status = 'jkl' WHERE user_id = ? ::: [51]

[INFO ] 2018-01-29 16:28:09,456 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_1 ::: update t_order_1 set status = 'jkl' WHERE user_id = ? ::: [51]

[DEBUG] 2018-01-29 16:28:09,771 --main-- [io.shardingjdbc.example.spring.boot.mybatis.repository.OrderRepository.updateOrderById] <== Updates: 10

[DEBUG] 2018-01-29 16:28:09,772 --main-- [org.mybatis.spring.SqlSessionUtils] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e868ef5]

[DEBUG] 2018-01-29 16:28:09,772 --main-- [org.springframework.jdbc.datasource.DataSourceUtils] Returning JDBC Connection to DataSource

used sharding-jdbc version 2.0.2

程序:

在OrderRepository

int updateOrderById(long orderId);

mapping

update t_order set status = 'jkl' WHERE user_id = #{orderId,jdbcType=INTEGER}

麻烦帮忙解答下是bug还是我配置问题。