Java分批次获取数据，每次获取一百条数据（Oracle数据库语法）

背景介绍：

在做mule项目迁移值XXL-JOB中，看到的一种分批次获取数据的编程思路，在此记录一下。

实例Demo：

1.获取表总记录数

    <select id="getProviderCount" resultType="java.lang.Integer">
        select count(1) as count from view_esb_business_provider
    </select>

2.定义每次需要取多少行记录数

int rows = 100;

3.计算需要取多少次

private List<Integer> groovy(Integer count, int rows) {
        Double tempIndex = (double) (count / rows);
        String index = Double.toString(tempIndex).substring(0, Double.toString(tempIndex).indexOf("."));
        List<Integer> list = new ArrayList<Integer>();
        for (int i = 1; i <= Integer.parseInt(index) + 1; i++) {
            list.add(i);
        }
        return list;
    }

4.获取数据

    <select id="getProvidersByCounter"
            resultType="com.hdyy.timetask.ls_p_dyfdb_yonghugongyingshang_swwdb.entity.ProviderEntity">
        select '-10185'+CONVERT(varchar(30),CorpID) as CorpID,CorpName,ClassCorp,DistrictCode,PostCode,CorpType,Address,LawMan,SimpleName,ParentCorp,SpellCode,IsProvider,IsCustom,HasCatalog,UsePipe,Link,IsValid,CreateTime,LastOpTime,AutoRecvDays,WLCenter,checktype from
            (select p.*,row_number() over (order by corpid asc) as dr from
                    (select * from view_esb_business_provider) p) x
        where dr &gt;= ((#{param1,jdbcType=INTEGER} - 1) * #{param2,jdbcType=INTEGER} +1 )
        and dr &lt;= (#{param1,jdbcType=INTEGER} * #{param2,jdbcType=INTEGER})
    </select>

5.主函数：

@Test
    public void contextLoads() {

        Integer count = hddyfMapper.getProviderCount();
        System.out.println("count: " + count);
        int rows = 100;
        List<Integer> list = groovy(count, rows);
        XxlJobLogger.log("共需"+ list.size() +"次上传");
        log.info("共需"+ list.size() +"次上传");
        for (Integer i:list) {
            System.out.println("i: " + i);
            //执行接口
            List<ProviderEntity> providersByCounter = hddyfMapper.getProvidersByCounter(i, rows);
            System.out.println("providersBy : " + providersByCounter.size());
        }
    }

部分执行结果截图：

Tip:

这个为记录排序，用来标记记录的位置

select p.*,row_number() over (order by corpid asc) as dr
        from (select * from view_esb_business_provider) p