I am working on a project in Android Studio that requires to paginate a very large amount of data.

It's about 30,000 pieces.

fastify.post<{Body: ProductPagingBody}>(
    //Route
    '/list_paging',
    {
        schema: {
            body: ProductPagingBodySchema
        }
    },
    async(request, reply) => {
        const { store_code } = request.user
        const { page_no, page_size } = request.body
        
        try {
            const result: ProductList = await Product.getProductList(store_code, page_no, page_size)

            if (result) {
                reply.send(result)
            }else {
                reply.send({
                    code: 404,
                    error: 'ProductInfo Error',
                    message: 'No matches productInfo'
                })
            }
        } catch (e) {
            reply.send({
                code: 401,
                error: 'ProductInfo Error',
                message: 'Failed to get productInfo'
            })
        }
    }
)

I request product list with paging number and paging size. But, when the 4th request, no more requests have been returned. If restart again, it will work normally. But it stops at the same place.

Page 1, 2, 3 in a row... I would like to know if there is a good way to deal with this when you continue to request to.

--------------------------------Add

static async getProductList(page_no: number, page_size: number): Promise<ProductList> {

    const rawData: ProductList = await getManager().query(
        `SELECT 
            ...
        FROM
            (
                SELECT /*+ INDEX(T1 PK1) */
                    ROWNUM AS RNUM, T1.*
                FROM
                    (
                        SELECT
                            ...
                            FROM (
                                ...
                            ) H
                            ORDER BY H.BARCODE
                    ) T1
                WHERE
                    ROWNUM <= ${page_no * page_size}
            ) T2
        WHERE
        ${page_no == 1 ? page_no : (page_no - 1) * page_size + 1} <= RNUM`
    );

    const productList: ProductList = rawData

    return productList;
}