마이바티스 동적 SQL 처리 기법

쿼리 조건에 따라 SQL 동적 생성 방법

1. 동적 SQL 실습 사례

회원 정보 검색 시스템

1.1 데이터베이스 테이블 생성

-- 회원 테이블
CREATE TABLE members(
	member_id INT PRIMARY KEY auto_increment,
	member_nick VARCHAR(20) NOT NULL UNIQUE,
	member_gender CHAR(2) NOT NULL,
	member_age INT NOT NULL,
	member_city VARCHAR(30) NOT NULL
);

1.2 엔티티 클래스 작성

package com.example.mybatis.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * 회원 테이블 엔티티 클래스
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Members {
    private int memberId;
    private String memberNick;
    private String memberGender;
    private int memberAge;
    private String memberCity;
}

1.3 DAO 인터페이스 정의

DAO 인터페이스에서 다중 조건 조회 메소드 정의

MemberDao

package com.example.mybatis.dao;

import com.example.mybatis.entity.Members;

import java.util.HashMap;
import java.util.List;

public interface MemberDao {
    // 다중 조건 조회 시 조건이 불확실할 경우 HashMap을 파라미터로 직접 사용
    List<Members> findMembers(HashMap<String,Object> conditions);
    
    // 전용 엔티티 클래스를 사용한 다중 조건 조회
    // List<Members> findMembers(MemberSearchCondition condition);
    
    // 다중 도시 기반 조회
    List<Members> findMembersByCities(List<String> cities);
}

MemberMapper.xml

<?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="com.example.mybatis.dao.MemberDao">
    <resultMap id="MemberMap" type="com.example.mybatis.entity.Members">
        <id column="member_id" property="memberId"/>
        <result column="member_nick" property="memberNick"/>
        <result column="member_gender" property="memberGender"/>
        <result column="member_age" property="memberAge"/>
        <result column="member_city" property="memberCity"/>
    </resultMap>
    
    <select id="findMembers" resultMap="MemberMap">
        SELECT member_id, member_nick, member_gender, member_age, member_city
        FROM members
        <trim prefix="WHERE" prefixOverrides="AND | OR" suffix="ORDER BY member_age">
            <if test="gender != null">
                AND member_gender = #{gender}
            </if>
            <if test="minAge != null">
                AND member_age >= #{minAge}
            </if>
            <if test="maxAge != null">
                AND member_age <= #{maxAge}
            </if>
            <if test="city != null">
                AND member_city = #{city}
            </if>
        </trim>
    </select>
    
    <select id="findMembersByCities" resultMap="MemberMap">
        SELECT * FROM members
        WHERE member_city IN
        <foreach collection="list" item="city" open="(" close=")" separator=",">
            #{city}
        </foreach>
    </select>

</mapper>

테스트 클래스

package com.example.mybatis.dao;

import com.example.mybatis.entity.Members;
import com.example.mybatis.util.MyBatisUtil;
import org.junit.Test;
import java.util.*;

import static org.junit.Assert.*;

public class MemberDaoTest {

    @Test
    public void findMembers() {
        Map<String, Object> conditions = new HashMap<>();
        conditions.put("gender", "여");
        conditions.put("minAge", 20);
        conditions.put("maxAge", 30);
        
        List<Members> membersList = MyBatisUtil.getMapper(MemberDao.class).findMembers(conditions);
        membersList.forEach(System.out::println);
    }

    @Test
    public void findMembersByCities() {
        List<String> cities = Arrays.asList("서울", "부산", "대구");
        List<Members> membersList = MyBatisUtil.getMapper(MemberDao.class).findMembersByCities(cities);
        membersList.forEach(System.out::println);
    }
}

1.4 동적 SQL 태그 활용

1.4.1 where 태그

동적 조건에서 첫 번째 조건의 'and' 자동 제거

<select id="findMembers" resultMap="MemberMap">
    SELECT member_id, member_nick, member_gender, member_age, member_city
    FROM members
    <where>
        <if test="gender != null">
            AND member_gender = #{gender}
        </if>
        <if test="minAge != null">
            AND member_age >= #{minAge}
        </if>
        <if test="maxAge != null">
            AND member_age <= #{maxAge}
        </if>
    </where>
</select>

1.4.2 trim 태그

suffix 속성을 통해 접미사 추가 가능

<select id="findMembers" resultMap="MemberMap">
    SELECT member_id, member_nick, member_gender, member_age, member_city
    FROM members
    <trim prefix="WHERE" prefixOverrides="AND | OR" suffix="ORDER BY member_age">
        <if test="gender != null">
            AND member_gender = #{gender}
        </if>
        <if test="minAge != null">
            AND member_age >= #{minAge}
        </if>
        <if test="maxAge != null">
            AND member_age <= #{maxAge}
        </if>
    </trim>
</select>

1.4.3 foreach 태그

SQL IN 절 처리에 사용되는 컬렉션 타입 파라미터 처리

<select id="findMembersByCities" resultMap="MemberMap">
    SELECT * FROM members
    WHERE member_city IN
    <foreach collection="cities" item="city" open="(" close=")" separator=",">
        #{city}
    </foreach>
</select>

1.5 모호한 쿼리 처리: #{} vs ${}

• ${key}: 파라미터 값을 먼저 가져와 SQL 문에 직접 삽입 후 컴파일 실행 - SQL 인젝션 위험 존재
• #{key}: 파라미터 값을 먼저 가져와 SQL 프리컴파일 후, 프리컴파일된 SQL에 파라미터 값을 설정