[Sp-re-ing] 07 - 동적 SQL

오늘 만들 것

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities.

mybatis - MyBatis | DynamicSQL

오늘은 게시판 API를 만들지 않습니다.

MyBatis 홈페이지에도 강조된 핵심 기능 중 하나인 동적 SQL을 둘러봅니다.

아래에 제가 만든 예제도 굳이 따라 만들지 않고 눈으로만 읽어보셔도 충분합니다.

시작하기 전에: MySQL 테이블 생성

기존 예제인 lab03 테이블을 그대로 사용합니다.
데이터 변경이 필요 없기 때문에 복제하지 않습니다.

시작

이번 글은 Lab03의 코드에 기능을 추가하는 방식으로 진행합니다.
Lab03는 이전 글에서 확인할 수 있습니다.

별도의 프로젝트에서 진행하고자 하면, Lab03과 동일하게 프로젝트를 생성한 뒤 코드를 복사해서 준비합니다.
Lab03의 코드를 그대로 사용한다면 아래 예제 코드의 패키지명에 유의합니다.

아래 예제 코드는 별도의 프로젝트를 생성하는 방식으로 진행합니다.

동적 SQL이 뭐지?

동적 SQL의 개념은 정말 간단합니다.
그저 동적(Dynamic)으로 작동하는 SQL입니다.

다양한 조건을 이용해 회원을 조회하는 기능을 구현한다고 가정해보겠습니다.
이름으로 회원을 조회한다면 이런 SQL을 이용할겁니다.

SELECT SEQ, USER_NAME, USER_ID, USER_GROUP
     , CASE WHEN LOGIN_FAIL > 5 THEN 'Y' ELSE 'N' END AS LOCKED
  FROM USER
 WHERE USER_NAME LIKE CONCAT('%', #{q}, '%')

회원 번호로 조회한다면 이런 SQL을 이용하겠죠.

SELECT SEQ, USER_NAME, USER_ID, USER_GROUP
     , CASE WHEN LOGIN_FAIL > 5 THEN 'Y' ELSE 'N' END AS LOCKED
  FROM USER
 WHERE SEQ = #{q}

유저 그룹으로 조회한다면 이렇게 할겁니다.

SELECT SEQ, USER_NAME, USER_ID, USER_GROUP
     , CASE WHEN LOGIN_FAIL > 5 THEN 'Y' ELSE 'N' END AS LOCKED
  FROM USER
 WHERE USER_GROUP LIKE CONCAT('%', #{q}, '%')

이렇게 세 SQL을 보면 중복되는 부분이 많다는 것을 알 수 있습니다.

어차피 조건만 달라지고 불러와야 하는 데이터는 동일하다면 하나로 합칠 수 없을까요?

이런 목적으로 나온 개념이 동적 SQL입니다.

동적 SQL은 중복 코드를 줄이고, 코드 재사용성을 높일 수 있게 만들어줍니다.

사용자 상세 검색하기

Lab03에서 우린 국가별로 사용자를 검색하는 예제를 작성했었습니다.
이번엔 여러 조건으로 조회할 수 있는 예제를 작성해봅니다.

Controller 수정

package net.jetalab.spreinglab07.controller;

import net.jetalab.spreinglab07.dao.UserDAO;
import net.jetalab.spreinglab07.dto.UserDTO;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@MapperScan(basePackages = "net.jetalab.spreinglab07.dao")
public class UserController {

    @Autowired
    private UserDAO userDAO;

    @RequestMapping("/users")
    public List<UserDTO> users(@RequestParam(value = "country", defaultValue = "") String country, @RequestParam(value = "name", defaultValue = "") String name, @RequestParam(value = "seq", defaultValue = "0") int seq) throws Exception {
        final UserDTO param = new UserDTO(seq, name, country);
        final List<UserDTO> userList = userDAO.selectUsers(param);
        return userList;
    }

}

21번째 줄: 파라미터로 name과 seq를 더 받을 수 있도록 수정되었습니다.

 

SQL Mapper 수정

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="net.jetalab.spreinglab07.dao.UserDAO">

    <select id="selectUsers" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="net.jetalab.spreinglab07.dto.UserDTO">
        SELECT `seq`, `name`, `country`
        FROM lab03
        WHERE 1 = 1
        <if test='seq != null and 0 lt seq'>
            AND `seq` = #{seq}
        </if>
        <if test='country != null and country != ""'>
            AND `country` LIKE CONCAT('%', #{country}, '%')
        </if>
        <if test='name != null and name != ""'>
            AND `name` LIKE CONCAT('%', #{name}, '%')
        </if>
    </select>

</mapper>

11번째 줄 ~ 19번째 줄: 다양한 조건에 따라 WHERE절에 AND 조건을 붙일 수 있도록 수정되었습니다.

 

테스트

한 명 뿐이군요

이름에 '나'가 들어가고 국가명에 '한'이 들어가는 사용자가 출력되었습니다.

전달한 파라미터에 seq는 없기 때문에 생략하고, country와 name 조건이 추가될 것임을 예상할 수 있습니다.

아래와 같이 SQL이 실행되었습니다.

SELECT `seq`, `name`, `country`
FROM lab03
WHERE 1 = 1
AND `country` LIKE CONCAT('%', '한', '%')
AND `name` LIKE CONCAT('%', '나', '%')

 

사용자 seq만 검색하기

사용자가 너무 많기 때문에 네트워크 비용을 줄이기 위해 seq만 검색하는 API를 별도로 둔다고 가정해보겠습니다.

API와 SQL을 별도로 분리해야 합니다.

 

Controller 수정

package net.jetalab.spreinglab07.controller;

import net.jetalab.spreinglab07.dao.UserDAO;
import net.jetalab.spreinglab07.dto.UserDTO;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@MapperScan(basePackages = "net.jetalab.spreinglab07.dao")
public class UserController {

    @Autowired
    private UserDAO userDAO;

    @RequestMapping("/users")
    public List<UserDTO> users(@RequestParam(value = "country", defaultValue = "") String country, @RequestParam(value = "name", defaultValue = "") String name, @RequestParam(value = "seq", defaultValue = "0") int seq) throws Exception {
        final UserDTO param = new UserDTO(seq, name, country);
        final List<UserDTO> userList = userDAO.selectUsers(param);
        return userList;
    }

    @RequestMapping("/seq")
    public List<Integer> seq(@RequestParam(value = "country", defaultValue = "") String country, @RequestParam(value = "name", defaultValue = "") String name, @RequestParam(value = "seq", defaultValue = "0") int seq) throws Exception {
        final UserDTO param = new UserDTO(seq, name, country);
        final List<Integer> seqList = userDAO.selectUserSeq(param);
        return seqList;
    }

}

27번째 줄 ~ 32번째 줄: /seq API가 추가되었습니다.

 

SQL Mapper 수정

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="net.jetalab.spreinglab07.dao.UserDAO">

    <select id="selectUsers" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="net.jetalab.spreinglab07.dto.UserDTO">
        SELECT `seq`, `name`, `country`
        FROM lab03
        WHERE 1 = 1
        <if test='seq != null and 0 lt seq'>
            AND `seq` = #{seq}
        </if>
        <if test='country != null and country != ""'>
            AND `country` LIKE CONCAT('%', #{country}, '%')
        </if>
        <if test='name != null and name != ""'>
            AND `name` LIKE CONCAT('%', #{name}, '%')
        </if>
    </select>

    <select id="selectUserSeq" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="Integer">
        SELECT `seq`
        FROM lab03
        WHERE 1 = 1
        <if test='seq != null and 0 lt seq'>
            AND `seq` = #{seq}
        </if>
        <if test='country != null and country != ""'>
            AND `country` LIKE CONCAT('%', #{country}, '%')
        </if>
        <if test='name != null and name != ""'>
            AND `name` LIKE CONCAT('%', #{name}, '%')
        </if>
    </select>

</mapper>

22번째 줄 ~ 35번째 줄: seq만 요청하는 SQL문이 추가되었습니다.

 

테스트

오지나의 seq인 4만 출력됩니다.

잘 작동하는 것을 볼 수 있습니다.

 

하지만 앞서 이야기했었던 '코드 재사용성'에 대해서는 의문이 듭니다.

각 SQL의 첫 번째 줄만 제외하고는 동일한 코드가 반복되기 때문이죠.

WHERE절의 중복을 제거해보겠습니다.

 

SQL Mapper 수정

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="net.jetalab.spreinglab07.dao.UserDAO">

    <sql id="findUserConditions">
        <if test='seq != null and 0 lt seq'>
            AND `seq` = #{seq}
        </if>
        <if test='country != null and country != ""'>
            AND `country` LIKE CONCAT('%', #{country}, '%')
        </if>
        <if test='name != null and name != ""'>
            AND `name` LIKE CONCAT('%', #{name}, '%')
        </if>
    </sql>

    <select id="selectUsers" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="net.jetalab.spreinglab07.dto.UserDTO">
        SELECT `seq`, `name`, `country`
        FROM lab03
        WHERE 1 = 1
        <include refid="findUserConditions"></include>
    </select>

    <select id="selectUserSeq" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="Integer">
        SELECT `seq`
        FROM lab03
        WHERE 1 = 1
        <include refid="findUserConditions"></include>
    </select>

</mapper>

6번째 줄 ~ 16번째 줄: 중복되는 WHERE절 조건을 별도로 분리하였습니다.

23번째 줄, 31번째 줄: 별도로 빼놓았던 SQL을 <include />를 이용해 삽입할 수 있습니다.

 

자주 쓰는 용법

MyBatis에선 꽤 많은 기능을 제공합니다.

이 섹션에서는 위 예제에서 나온 기능들을 조금 더 자세히 살펴봅니다.

 

<if />

<if test="{비교 조건}">
    SELECT 'RUN' FROM DUAL
</if>

비교 조건을 만족하면 <if /> 태그 안의 SQL을 실행할 SQL에 포함합니다.

 

비교 조건은 Java와 크게 유사합니다.

==, .isEqual(): 값이 같음
!=, isNotEqual(): 값이 다름
<, .isLessThan(), lt: 미만
<=, .isLessEqual(),  lte: 이하
>, .isGreaterThan(), gt: 초과
>=, isGreaterEqual(), gte: 이상
.isNull(): Null임
.isNotNull(): Null이 아님
.isEmpty(): 빈 값임
.isNotEmpty(): 빈 값이 아님
!: 논리적 Not

 

lt, lte, gt, gte에 경우 괄호 <와 >가 XML의 일부로 인식될 수 있기 때문에 사용합니다.

위 용법을 이용해 아래와 같은 조건을 만들 수 있습니다.

<select id="findActiveBlogLike" resultType="Blog">
    SELECT *
    FROM BLOG
    WHERE state = 'ACTIVE'
    <if test="title.isNull()">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
    <if test="sense gte 3">
        AND category = 'hot'
    </if>
</select>

 

<sql />

<sql id="currentYYYYMMDD">
    (SELECT TO_CHAR(SYSDATE, 'yyyymmdd') FROM DUAL)
</sql>

SQL 코드 조각을 미리 선언합니다.

 

한 Mapper 안에서 동일한 SQL을 여러번 사용할 때 유용합니다.

온전한 하나의 SQL문을 선언할 수도 있고, WHERE절에 들어갈 조건 중 일부를 뗄 수도 있습니다.

 

<include />

<include refid="currentYYYYMMDD"></include>

선언해 둔 SQL 조각을 사용합니다.

Java의 변수를 선언한 것과 유사하다고 생각할 수 있습니다.

 

위 용법만 보면 잘 이해가 안 되지만 아래와 같이 사용할 수 있습니다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="net.jetalab.spreinglab07.dao.UserDAO">

    <sql id="currentYYYYMMDD">
        (SELECT TO_CHAR(SYSDATE, 'yyyymmdd') FROM DUAL)
    </sql>

    <select id="selectUsers" parameterType="net.jetalab.spreinglab07.dto.UserDTO"
            resultType="net.jetalab.spreinglab07.dto.UserDTO">
        SELECT `seq`, `name`, `country`
             , <include refid="currentYYYYMMDD"></include> AS foundAt
        FROM lab03
        WHERE 1 = 1
          AND registedAt = <include refid="currentYYYYMMDD"></include>
    </select>

</mapper>

위 예제는 현재 시간을 조회하는 Inner Query를 만들어두고, SELECT절과 WHERE절에서 모두 사용하는 예제입니다.

 

*

MyBatis에서 자주 사용하는 동적 SQL 용법에 대해 알아보았습니다.

실무에서는 복잡한 SQL을 작성하기 위해 다양한 기능을 활용합니다.

자세히 알아보고자 한다면 MyBatis 공식 문서를 확인해 보는 것도 좋습니다.

 

예제 코드

본 포스트의 예제 코드는 GitHub에 공개되어 있습니다.

https://github.com/jETA-Kor/sp-re-ing/tree/master/lab07

 

참고 문서

• MyBatis - 마이바티스 3 | 동적 SQL
• MyBatis - 동적 쿼리 사용하기
• [MyBatis] 동적 쿼리 if문 문법 총 정리
• [MyBatis] 동적 쿼리 IF문 :: Dongram개발