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

[赵利斌]

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]

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.