mybatis search data first return only 1 rows, and second return all rows

57 views
Skip to first unread message

赵利斌

unread,
Sep 3, 2021, 5:21:00 AM9/3/21
to mybatis-user
I use springBoot + mybatis to search mysql data, I found that some search only return 1 row, but if I use the same condition search again, it returns all rows. 
below is the main code:
```
public void doBusiness(WorkerEntity workerEntity) {
    
        Date accountTime = DateUtils.stringToDate(workerEntity.getAccountantTime(), DateStyle.YYYY_MM_DD);
        String tableSuffix = DateUtils.yyyyMM(accountTime);


        long minId = workerEntity.getStartId();
        long maxId = workerEntity.getEndId();
        int totalCount = 0;

        // define beginId and endId
        long beginId;
        long endId;
        for ( ; minId <= maxId; ) {
            beginId = minId;
            endId = Math.min(beginId + 599, maxId);
            minId = endId + 1;

            PaymentProceedsDto proceedsDto = new PaymentProceedsDto();
            proceedsDto.setAccountantTime(accountTime);
            // other condition
            ....
            proceedsDto.setStartBatchId(beginId);
            proceedsDto.setEndBatchId(endId);
            updatePaymentService.searchAndUpdate(proceedsDto, workerEntity, tableSuffix);
        }
        ...
    }
```
```

@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
    public void searchAndUpdate(PaymentProceedsDto proceedsDto, WorkerEntity workerEntity, String tableSuffix) {
        List<Long> proceedsIdList = paymentProceedsService.querySumProceedsUnAccountList(proceedsDto);
        log.info("fetch sum proceeds unAccount ids end first, batchId: {}, start: {}, end: {}, size: {}",
                workerEntity.getRequestBatchCode(), proceedsDto.getStartBatchId(), proceedsDto.getEndBatchId(),
                proceedsIdList.size());
        
        if (proceedsIdList.size() > 0 && proceedsIdList.size() < 10) {
            List<Long> proceedsIdList2 = paymentProceedsService.querySumProceedsUnAccountList(proceedsDto);
            log.info("fetch sum proceeds unAccount ids end second, batchId: {}, start: {}, end: {}, size: {}",
                    workerEntity.getRequestBatchCode(), proceedsDto.getStartBatchId(), proceedsDto.getEndBatchId(),
                    proceedsIdList2.size());
          
        }
      ...
    }
```
mapper:

```
<select id="querySumProceedsUnAccountList" resultType="java.lang.Long">
    select id
    from <include refid="table_name"/>
    where id between #{proceedsDto.startBatchId} and #{proceedsDto.endBatchId}
    and <include refid="querySumProceedsUnAccountCondition"/>
  </select>
  <sql id="querySumProceedsUnAccountCondition">
    accountant_time = #{proceedsDto.accountantTime}
    and enter_accounts_state = #{proceedsDto.enterAccountsState}
    and enter_ce_state = #{proceedsDto.enterCeState}
    and ebs_summary_state = #{proceedsDto.ebsSummaryState}
    and is_del = 0
  </sql>
```
if my loop batch size is 599, also like sql id between 1 and 599. it need return 600 size, but first search just return 1 row, and same condition second search return 600 rows.

and I also try set the batch size to 1000, first search also just return 1 row, and second search return 1001 rows.

here is first search log:
```
[xxx] [DEBUG] [2021-09-03 15:58:07.532] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==>  Preparing: select id from 
payment_proceeds_current where id between ? and ? and accountant_time = ? and enter_accounts_state = ? and enter_ce_state = ? and ebs_summary_state = ? and
 is_del = 0[TID:N/A]
[xxx] [DEBUG] [2021-09-03 15:58:07.532] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==> Parameters: 134744124(Long)
, 134744723(Long), 2021-05-31 00:00:00.0(Timestamp), 1(Integer), 2(Integer), 2(Integer)[TID:552267.108.16306558875320157]
[xxx] [DEBUG] [2021-09-03 15:58:07.534] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] <==      Total: 1[TID:552267.10
8.16306558875320157]
[xxx] [INFO ] [2021-09-03 15:58:07.535] [c.b.r.r.e.i.UpdatePaymentServiceImpl:searchAndUpdate:44] [pool-6-thread-1] fetch sum 
proceeds unAccount ids end first, batchId: 20210901225304381, start: 134744124, end: 134744723, size: 1[TID:N/A]

```
second search log:

```
[xxx] [DEBUG] [2021-09-03 15:58:07.535] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==>  Preparing: select id from 
payment_proceeds_current where id between ? and ? and accountant_time = ? and enter_accounts_state = ? and enter_ce_state = ? and ebs_summary_state = ? and
 is_del = 0[TID:N/A]
[xxx] [DEBUG] [2021-09-03 15:58:07.536] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==> Parameters: 134744124(Long)
, 134744723(Long), 2021-05-31 00:00:00.0(Timestamp), 1(Integer), 2(Integer), 2(Integer)[TID:552267.108.16306558875360159]
[xxx] [DEBUG] [2021-09-03 15:58:07.538] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] <==      Total: 600[TID:552267.
108.16306558875360159]
[xxx] [INFO ] [2021-09-03 15:58:07.539] [c.b.r.r.e.i.UpdatePaymentServiceImpl:searchAndUpdate:49] [pool-6-thread-1] fetch sum 
proceeds unAccount ids end second, batchId: 20210901225304381, start: 134744124, end: 134744723, size: 600[TID:N/A]

```
The mybatis local-cache-scope is statement and cache-enabled is false.
```
mybatis:
  mapper-locations:
    - classpath*:mapper/**/*Mapper.xml
  configuration:
    cache-enabled: false
    local-cache-scope: statement
```

I also try to upgrade mybatis version from 3.4.5 to 3.5.6, but it's not work.

is someone know how to resolve this problem, thanks.

hangbin yu

unread,
Dec 18, 2021, 6:29:27 AM12/18/21
to mybatis-user
Provide you with an idea to solve this problem.
Debug your code.
Steps:
1. Find the method selectList which in class org.apache.ibatis.session.defaults.DefaultSqlSession 
  private <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler);
2. check your second parameter(Object parameter) is correct.
You also can deep into the real query method which in selectList to debug your program.
Reply all
Reply to author
Forward
0 new messages