쿼리의 페이징을 옵션에 따라 포함시키거나 제외하는 동적 SQL

 

오라클에서 쿼리의 페이징을 옵션에 따라 포함시키거나 제외하는 동적 SQL을 작성하는 예를 살펴보겠습니다. MyBatis에서는 <if> 태그를 사용하여 쿼리 내에서 페이징 여부를 조건에 따라 다르게 처리할 수 있습니다.

예제: 페이징을 조건에 따라 포함하거나 제외하는 동적 쿼리

이 예제에서는 pageNum과 pageSize라는 페이징 조건이 주어졌을 때, 페이징을 적용한 SQL과 페이징을 적용하지 않은 SQL을 동적으로 생성하는 방법을 설명합니다.

 

728x90

1. SQL Mapper 파일 (XML)

<mapper namespace="com.example.mapper.UserMapper">

  <!-- 동적 검색 SQL with 페이징 -->
  <select id="selectUsers" resultType="User">
    <!-- 기본 쿼리 -->
    SELECT * 
    FROM users
    WHERE 1=1
    <if test="name != null">
      AND name = #{name}
    </if>
    <if test="age != null">
      AND age = #{age}
    </if>

    <!-- 페이징 옵션이 있으면 -->
    <if test="pageNum != null and pageSize != null">
      -- 오라클에서 페이징을 처리하는 방식
      AND ROWNUM <= #{pageNum} * #{pageSize}
      AND ROWNUM > (#{pageNum} - 1) * #{pageSize}
    </if>

    <!-- 페이징 옵션이 없으면 단순 조회 -->
    <if test="pageNum == null or pageSize == null">
      <!-- 추가적인 조건이 있을 수 있음 -->
    </if>
  </select>

</mapper>

2. Java 인터페이스

package com.example.mapper;

import com.example.domain.User;
import java.util.List;

public interface UserMapper {
  List<User> selectUsers(String name, Integer age, Integer pageNum, Integer pageSize);
}

3. Service 클래스 (Java)

package com.example.service;

import com.example.domain.User;
import com.example.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserService {

  @Autowired
  private UserMapper userMapper;

  public List<User> searchUsers(String name, Integer age, Integer pageNum, Integer pageSize) {
    return userMapper.selectUsers(name, age, pageNum, pageSize);
  }
}

4. Controller 클래스 (Java)

package com.example;

import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
public class UserController {

  @Autowired
  private UserService userService;

  public List<User> getUsers(@RequestParam(required = false) String name, 
                             @RequestParam(required = false) Integer age, 
                             @RequestParam(required = false) Integer pageNum,
                             @RequestParam(required = false) Integer pageSize) {
    return userService.searchUsers(name, age, pageNum, pageSize);
  }
}

 

728x90

동적 SQL 설명

1. 기본 조건: 기본 쿼리에서 조건이 없다면, WHERE 1=1을 사용하여 쿼리가 정상적으로 실행되도록 합니다. 그 후 조건이 있는 경우에만 필터링을 추가합니다.
2. 페이징 조건:
   • 페이징 조건(pageNum, pageSize)이 모두 주어지면 ROWNUM을 사용하여 쿼리 결과에 페이징을 적용합니다.
   • ROWNUM <= #{pageNum} * #{pageSize}로 결과의 범위를 설정하고, ROWNUM > (#{pageNum} - 1) * #{pageSize}를 사용하여 시작 지점을 설정합니다.
3. 페이징 옵션 없음: pageNum과 pageSize가 없으면 페이징을 적용하지 않고 전체 데이터를 조회하는 쿼리가 실행됩니다.

참고 사항

• ROWNUM: 오라클에서 페이징을 처리할 때 ROWNUM을 사용합니다. 하지만, 이 방법은 ORDER BY와 함께 사용해야 할 때, 서브쿼리로 ROWNUM을 처리해야 할 수도 있습니다.
• ROWNUM 제약: ROWNUM을 직접 사용하면 정렬된 결과에 대해 정확한 페이징을 얻기 어려울 수 있으므로, 필요하다면 서브쿼리로 ROWNUM을 사용하는 방법을 고려해야 합니다.

예시: 서브쿼리에서 ROWNUM을 사용하는 방법 (정렬된 페이징)

<mapper namespace="com.example.mapper.UserMapper">
  <select id="selectUsers" resultType="User">
    <!-- 서브쿼리로 페이징 처리 -->
    SELECT * FROM (
      SELECT * 
      FROM users
      WHERE 1=1
      <if test="name != null">
        AND name = #{name}
      </if>
      <if test="age != null">
        AND age = #{age}
      </if>
      ORDER BY name -- 예시로 name으로 정렬
    )
    WHERE ROWNUM <= #{pageNum} * #{pageSize}
    AND ROWNUM > (#{pageNum} - 1) * #{pageSize}
  </select>
</mapper>

위와 같이 ORDER BY가 필요한 경우, 서브쿼리 내에서 ROWNUM을 사용하여 페이징을 처리하는 방법도 있습니다.