Implementing Pagination in Oracle Database with Spring Boot and MyBatis

 

Implementing Pagination in Oracle Database with Spring Boot and MyBatis

Pagination is a common requirement when working with large datasets, especially in web applications where performance and user experience are critical. If you're using Oracle Database and MyBatis with Spring Boot, you might find pagination a bit challenging due to the differences in how Oracle handles it compared to databases like MySQL.

In this blog post, we'll dive into how to implement pagination with Oracle Database using MyBatis. We'll cover both modern and legacy approaches, tailored to your Oracle Database version, and walk through a practical implementation.

---

Oracle Database Pagination Basics

Oracle's approach to pagination depends on the database version you're using:

1. Oracle 12c and Later: Introduced OFFSET and FETCH FIRST clauses, making pagination straightforward.
2. Oracle 11g and Earlier: Requires using ROWNUM in combination with subqueries to achieve pagination.

Let's explore both methods in detail.

Oracle 12c and Later: Using OFFSET and FETCH FIRST

Starting from Oracle 12c, pagination has become much easier with the introduction of OFFSET and FETCH FIRST clauses. Here is an example of how you can use these clauses in a MyBatis XML Mapper.

<select id="getChatHistoriesPageable" parameterType="map" resultType="com.example.dto.ChatHistoryDto.List">
    SELECT
        id,
        user_id,
        message,
        created_at
    FROM
        chat_history
    WHERE
        user_id = #{userId}
    ORDER BY
        created_at DESC
    OFFSET #{offset} ROWS FETCH NEXT #{size} ROWS ONLY
</select>

Explanation

• OFFSET #{offset} ROWS: Skips the specified number of rows.
• FETCH NEXT #{size} ROWS ONLY: Retrieves the specified number of rows.

Example:

• Page 1 with 10 rows per page: offset = 0
• Page 2 with 10 rows per page: offset = 10



Oracle 11g and Earlier: Using ROWNUM with Subqueries

If you're working with Oracle 11g or an older version, you'll need to use ROWNUM in combination with subqueries. Here's how you can define the query in a MyBatis XML Mapper:

<select id="getChatHistoriesPageable" parameterType="map" resultType="com.example.dto.ChatHistoryDto.List">
    SELECT * FROM (
        SELECT
            a.*,
            ROWNUM AS rn
        FROM (
            SELECT
                id,
                user_id,
                message,
                created_at
            FROM
                chat_history
            WHERE
                user_id = #{userId}
            ORDER BY
                created_at DESC
        ) a
        WHERE ROWNUM <= #{offset} + #{size}
    )
    WHERE rn > #{offset}
</select>

Explanation

• Inner Query: Retrieves rows along with their ROWNUM values.
• Outer Query: Filters the rows based on ROWNUM to return the desired range.

---

Integrating MyBatis with Spring Boot

Now that we understand the query structure, let's see how to integrate this with MyBatis and Spring Boot.

MyBatis Mapper Interface

First, define the MyBatis Mapper interface that will interact with the XML Mapper.

package com.example.mapper;

import com.example.dto.ChatHistoryDto;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ChatHistoryMapper {
    List<ChatHistoryDto.List> getChatHistoriesPageable(@Param("userId") Long userId, 
                                                       @Param("offset") int offset, 
                                                       @Param("size") int size);
}

• @Param Annotation: This binds the method parameters to the named parameters in the SQL query.
  
  

DTO Class for Result Mapping

Create a DTO class to map the query results.

package com.example.dto;

import lombok.Data;

import java.time.LocalDateTime;

public class ChatHistoryDto {

    @Data
    public static class List {
        private Long id;
        private Long userId;
        private String message;
        private LocalDateTime createdAt;
    }
}

Service Layer Implementation

The service layer fetches the paginated data and wraps it in a Page object.

package com.example.service;

import com.example.dto.ChatHistoryDto;
import com.example.mapper.ChatHistoryMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@RequiredArgsConstructor
public class ChatHistoryService {
    private final ChatHistoryMapper chatHistoryMapper;

    public Page<ChatHistoryDto.List> getChatHistories(Long userId, int page, int size) {
        int offset = page * size; // Calculate the offset
        List<ChatHistoryDto.List> results = chatHistoryMapper.getChatHistoriesPageable(userId, offset, size);

        // Total count should ideally be fetched via a separate query
        int totalCount = 100; // Example value

        return new PageImpl<>(results, PageRequest.of(page, size), totalCount);
    }
}

• PageImpl Class: Used to create a pageable response.
• PageRequest Class: Encapsulates pagination information like page number and size.
  
  

REST Controller Implementation

Finally, expose the service method via a REST API.

package com.example.controller;

import com.example.dto.ChatHistoryDto;
import com.example.service.ChatHistoryService;
import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequiredArgsConstructor
public class ChatHistoryController {
    private final ChatHistoryService chatHistoryService;

    @GetMapping("/api/chat-histories")
    public Page<ChatHistoryDto.List> getChatHistories(
            @RequestParam Long userId,
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size) {
        return chatHistoryService.getChatHistories(userId, page, size);
    }
}

API Endpoint Example:

GET /api/chat-histories?userId=1&page=0&size=10

---

Conclusion

Implementing pagination with Oracle Database and MyBatis in a Spring Boot application can be straightforward once you understand the appropriate SQL syntax for your Oracle version. By leveraging MyBatis XML Mappers and Spring Data's Page abstraction, you can create a clean and maintainable solution for paginated APIs.

We hope this guide helps you implement pagination in your projects. Feel free to leave comments or questions below!