투입된 프로젝트의 서비스에 데이터를 조회하는 화면이 하나 있었다. 검색 조건을 고르고 검색어를 넣으면 그 값을 포함한 데이터가 나온다. 그런데 이 화면을 쓰는 업무는 여러 건을 함께 확인하는 일이 잦았고, 검색어를 하나밖에 못 받으니 일일이 한 건씩 조회해야 했다. 번거롭다는 요청이 들어왔다.

입력창은 그대로 두고, 콤마나 공백으로 검색어를 여러 개 이어 넣으면 각각을 포함하는 데이터를 한 번에 돌려주도록 바꿨다. 이 글은 그때 작업한 내용 중 핵심만 추린 기록이다.

기존 쿼리는 검색어 하나를 LIKE로 찾는다

<sql id="ticketSearch">
    <if test="(method_info != null and method_info != '')">
        <if test="method_id == 'gkId'">
            AND GK_ID = #{method_info}
        </if>
        <if test="method_id == 'parentTicketId'">
            AND UPPER(PRNT_TKT_ID) LIKE CONCAT('%',UPPER(REPLACE(#{method_info}, '_', '#_')),'%') ESCAPE '#'
        </if>
        <if test="method_id == 'ticketId'">
            AND UPPER(TKT_ID) LIKE CONCAT('%',UPPER(REPLACE(#{method_info}, '_', '#_')),'%') ESCAPE '#'
        </if>
        <if test="method_id == 'guid'">
            AND GUID LIKE CONCAT('%',#{method_info},'%')
        </if>
    </if>
</sql>

<select id="list" parameterType="map" resultMap="ticketVO">
    SELECT
        컬럼 1,
        컬럼 2,
        컬럼 3
    FROM
        테이블 A
    WHERE
        DEL_YN_FG = 'N' 
        <include refid="ticketSearch"/>
</select>

검색 조건(method_id)에 따라 비교할 컬럼을 고르고, 화면에서 넘어온 문자열(method_info) 하나와 비교한다. GK ID만 =로 정확히 일치하는 값을 찾고, 부모 티켓 ID와 티켓 ID는 UPPER로 대소문자를 맞춘 뒤 LIKE로 포함 여부를 본다. GUID도 LIKE다.

REPLACE(..., '_', '#_')와 ESCAPE '#'은 와일드카드 처리다. LIKE에서 _는 “아무 글자 하나"라는 뜻이라 ID 안에 있는 _를 글자 그대로 찾으려면 이스케이프해야 한다.

IN이 아니라 OR로 묶는다

여러 값을 한 번에 찾을 때 먼저 떠오르는 건 IN (...)이다. 하지만 IN은 값이 정확히 같을 때만 걸린다. 이 화면은 검색어를 “포함하는” 데이터를 찾아야 하니 LIKE 조건 여러 개를 OR로 이어야한다. MyBatis에서는 <foreach>의 separator에 OR를 주었다.

범위는 GK ID, 부모 티켓 ID, 티켓 ID 세 가지로 잡았다. GUID는 기존처럼 단일 검색으로 남겼다.

서비스에서 검색어를 배열로 쪼갠다

protected void setMultipleSearchInfo(Map<String, Object> reqMap){
    String method_info = GkUtils.nvl(reqMap, METHOD_INFO);
    String method_id = GkUtils.nvl(reqMap, METHOD_ID);

    if( !(Arrays.asList("gkId", "parentTicketId", "ticketId").contains(method_id))
            && StringUtil.isEmpty(method_info)) return;

    method_info = method_info.replaceAll("\\s{2,}", " ").trim();
    String[] splitStr = method_info.split("[\\s,]+");

    if("gkId".equals(method_id)){
        splitStr = Arrays.stream(splitStr)
                .map(str -> GkUtils.getPlainGkId(str))
                .toArray(String[]::new);
    }
    reqMap.put("method_info_list", splitStr);
}

하는 일은 네 가지.

  • 검색 조건이 세 가지 중 하나인지 확인한다.

  • 연속된 공백을 하나로 줄이고 앞뒤 공백을 걷어낸다.

  • split("[\\s,]+")로 공백과 콤마를 구분자 삼아 자른다. 정규식 끝의 + 덕분에 A1, B2처럼 콤마 뒤에 공백이 붙어도 구분자 하나로 처리된다. 사실 이 +만으로도 연속 공백은 한 덩어리가 되니 바로 앞 줄의 replaceAll은 없어도 결과가 같다.

  • GK ID면 값마다 GkUtils.getPlainGkId를 거친 뒤, 배열을 method_info_list라는 이름으로 파라미터 Map에 넣는다.

foreach의 괄호가 삭제 필터를 지킨다

<sql id="ticketSearch">
    <if test="(method_info != null and method_info != '')">
        <if test="method_id == 'gkId'">
            <choose>
                <when test="method_info_list != null and method_info_list.length > 1">
                    AND
                    <foreach collection="method_info_list" item="item" open="(" close=")" separator="OR">
                        GK_ID LIKE CONCAT('%', REPLACE(#{item}, '_', '#_'), '%') ESCAPE '#'
                    </foreach>
                </when>
                <otherwise>
                    AND GK_ID = #{method_info}
                </otherwise>
            </choose>
        </if>
        <if test="method_id == 'parentTicketId'">
            <choose>
                <when test="method_info_list != null and method_info_list.length > 1">
                    AND
                    <foreach collection="method_info_list" item="item" open="(" close=")" separator="OR">
                        PRNT_TKT_ID LIKE CONCAT('%', REPLACE(#{item}, '_', '#_'), '%') ESCAPE '#'
                    </foreach>
                </when>
                <otherwise>
                    AND PRNT_TKT_ID LIKE CONCAT('%',REPLACE(#{method_info}, '_', '#_'),'%') ESCAPE '#'
                </otherwise>
            </choose>
        </if>
        <if test="method_id == 'ticketId'">
            <choose>
                <when test="method_info_list != null and method_info_list.length > 1">
                    AND
                    <foreach collection="method_info_list" item="item" open="(" close=")" separator="OR">
                        TKT_ID LIKE CONCAT('%', REPLACE(#{item}, '_', '#_'), '%') ESCAPE '#'
                    </foreach>
                </when>
                <otherwise>
                    AND TKT_ID LIKE CONCAT('%',REPLACE(#{method_info}, '_', '#_'),'%') ESCAPE '#'
                </otherwise>
            </choose>
        </if>
        <if test="method_id == 'guid'">
            AND GUID LIKE CONCAT('%',#{method_info},'%')
        </if>
    </if>
</sql>

<select id="list" parameterType="map" resultMap="ticketVO">
    SELECT
        컬럼 1,
        컬럼 2,
        컬럼 3
    FROM
        테이블 A
    WHERE
        DEL_YN_FG = 'N' 
        <include refid="ticketSearch"/>
</select>

<choose>로 배열에 값이 두 개 이상일 때만 <foreach>를 타고, 하나뿐이면 기존 조건을 그대로 쓴다. <foreach>는 배열을 돌며 조건을 하나씩 찍어 사이사이에 separator인 OR를 넣고, 전체를 open과 close의 괄호로 감싼다. 티켓 ID 조건으로 A1, B2 C3를 넣으면 WHERE 절은 이렇게 된다.

WHERE
    DEL_YN_FG = 'N'
    AND (
        TKT_ID LIKE CONCAT('%', REPLACE(?, '_', '#_'), '%') ESCAPE '#'
        OR TKT_ID LIKE CONCAT('%', REPLACE(?, '_', '#_'), '%') ESCAPE '#'
        OR TKT_ID LIKE CONCAT('%', REPLACE(?, '_', '#_'), '%') ESCAPE '#'
    )
-- ?에는 A1, B2, C3가 차례로 바인딩된다

SQL에서는 AND가 OR보다 먼저 묶인다. 괄호 없이 DEL_YN_FG = 'N' AND a OR b OR c가 되면 실제로는 (DEL_YN_FG = 'N' AND a) OR b OR c로 해석된다. 괄호가 빠지면 b나 c에 걸리는 삭제된 데이터까지 조회된다.