How to create a paging when join the tables in Chamomile using Mybatis?

MyBatis Pageable
When writing queries using MyBatis, dynamic queries are developed as follows for sorting and pagination.

<!-- User List Lookup -->  
<select id="groupListData" parameterType="net.lotte.chamomile.admin.group.vo.GroupVO"  
    resultType="net.lotte.chamomile.admin.group.vo.GroupVO">  
   <![CDATA[  
    SELECT
        GROUP_ID groupId, /* Group_ID */
        GROUP_NAME groupName, /* Group_Name */
        GROUP_DESC groupDesc, /* Group_Description */
        USE_YN useYn, /* Usage */
        SYS_INSERT_DTM sysInsertDtm, /* System_Insert_Date&Time */
        SYS_INSERT_USER_ID sysInsertUserId, /* System_Insert_User_ID */
        SYS_UPDATE_DTM sysUpdateDtm, /* System_Update_Date&Time */
        SYS_UPDATE_USER_ID sysUpdateUserId /* System_Update_User_ID */
     FROM   
       CHMM_GROUP_INFO A  
    ]]>  
    <!-- Search Condition -->  
    <where>  
       <if test="searchGroupId != null and searchGroupId != ''"> AND GROUP_ID LIKE CONCAT(CONCAT('%',#{searchGroupId}),'%')  </if>  
       <if test="searchGroupName != null and searchGroupName != ''"> AND GROUP_NAME LIKE CONCAT(CONCAT('%',#{searchGroupName}),'%')  </if>  
       <if test="searchUseYn != null and searchUseYn != ''"> AND USE_YN = #{searchUseYn}  </if>  
    </where>  
       ORDER BY   
<choose>  
          <when test="orderCol != null and orderCol != ''">  
             <choose>  
                <when test="orderCol eq 'groupId'"> GROUP_ID </when>  
                <when test="orderCol eq 'groupName'"> GROUP_NAME </when>  
                <otherwise> GROUP_ID </otherwise>  
             </choose>  
             <choose>  
                <when test="sortAsc eq 'true'"> ASC, </when>  
                <otherwise> DESC, </otherwise>  
             </choose>  
          </when>  
       </choose>  
</select>

By coding this way, SQL becomes detached from the core business logic. Moreover, we sometimes write longer queries for pagination and sorting than the key SQL call.
Hence, Chamomile aids in query building using the Spring Data’s Pageable and PageableInterceptor.
The usage is as follows.

1. Receive parameters with Spring Pageable in the controller region.
   Input from the screen is transferred to Pageable as parameters as follows.

{
       "page": "0",
       "size": "10",
       "sort": "groupId,desc"
     }

Also, build the controller as follows.

@GetMapping(path = "/list")  
     public ChamomileResponse<Page<ResourceVO>> getResourceList(ResourceQuery request, Pageable pageable) {  
         Page<ResourceVO> results = resourceService.getResourceList(request, pageable);  
         return new ChamomileResponse<>(results);  
     }

2. Pass Pageable as a parameter in the Mapper interface.

Page<ResourceVO> findResourceListData(ResourceQuery query, Pageable pageable);

3. Write the query.

<!-- Resource List lookup -->  
     <select id="findResourceListData" parameterType="net.lotte.chamomile.admin.resource.api.dto.ResourceQuery"  
             resultType="net.lotte.chamomile.admin.resource.domain.ResourceVO">  
               SELECT  
       RESOURCE_ID resourceId, /* Resource ID */  
       RESOURCE_URI resourceUri, /* Resource Path */  
       RESOURCE_NAME resourceName, /* Resource Name */  
       RESOURCE_HTTPMETHOD resourceHttpMethod, /* http method */  RESOURCE_DESC resourceDesc, /* Resource Description */  
       SECURITY_ORDER securityOrder, /* Resource Order */  
       USE_YN useYn, /* Use Y_N */  
       SYS_INSERT_DTM sysInsertDtm, /* System Insert Date&Time */  
       SYS_INSERT_USER_ID sysInsertUserId, /* System Insert User ID */  
       SYS_UPDATE_DTM sysUpdateDtm, /* System Update Date&Time */  
       SYS_UPDATE_USER_ID sysUpdateUserId /* System Update User ID */  
       FROM  CHMM_RESOURCE_INFO A  <!-- Search Condition -->  
       <where>  
         <if test="searchResourceId != null and searchResourceId != ''">AND RESOURCE_ID LIKE CONCAT(CONCAT('%',#{searchResourceId}),'%')</if>  
         <if test="searchResourceName != null and searchResourceName != ''">AND RESOURCE_NAME LIKE CONCAT(CONCAT('%',#{searchResourceName}),'%')</if>  
         <if test="searchUseYn != null and searchUseYn != ''">AND USE_YN = #{searchUseYn}</if>  
       </where>  
             </select>

By utilizing the `PageableInterceptor` provided by Chamomile effectively, queries that focus on business logic can be written. The returned type can be classified into three categories:

1. Page – Regular pagination is applied and totalCount is called to distinguish pages.

2. Slice – Applied when carrying out scroll pagination and it does not call totalCount, hence improving performance compared to Page.

3. List – Applied when simply looking up Lists and has slightly better performance than Slice as it does not conduct a search for data on the next page.

The three types of return types can be effectively used according to the requirements to simplify complex pagination queries.