Mybatis4: Dynamic SQL

업데이트: November 10, 2020
10 분 소요

비트캠프 서초본원 엄진영 강사님의 수업을 듣고 정리했습니다.

Mybatis4: Dynamic SQL

1. Dynamic SQL?

Dynamic sql

조건에 따라 SQL을 달리 생성한다.
mybatis는 이를 위해 조건에 따라 SQL을 변경하거나, 동일한 SQL을 반복적으로 생성할 수 있는 문법을 제공한다.
조건을 모두 검사하여 SQL문이 확정되기 전까지 dynamic하고, 상황에 따라 최종적으로 만들어지는 SQL문이 바뀐다.

2. `<if>`

`<if>` 사용 전

게시글을 조회하는데, 사용자로부터 게시글의 번호를 입력 받아 조회한다. 만약 오류가 발생하면 전체 게시글을 출력한다.
이 경우 조건문을 사용하지 않으면, 두 개의 SQL문을 사용해야 한다.

  Scanner keyScan = new Scanner(System.in);
  System.out.print("게시글 번호? ");
  String str = keyScan.nextLine();
  keyScan.close();

  List<Board> list = null;

  try {
    //  => 게시글 번호가 주어지면 특정 게시글만 조회하는
    //     select1 SQL을 실행한다.
    list = sqlSession.selectList("BoardMapper.select1", Integer.parseInt(str));
  } catch (Exception e) {
    //  => 게시글 번호가 없으면 전체 게시글을 조회하는
    //     select2 SQL을 실행한다.
    list = sqlSession.selectList("BoardMapper.select2");
  }

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %d\n", 
      board.getNo(),
      board.getTitle(),
      board.getRegisteredDate(),
      board.getViewCount());
  }

<!-- 번호가 들어올 때 -->
  <select id="select1" resultMap="BoardMap" parameterType="int">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    where board_id = #{value}
  </select>
  
  <!-- 번호가 들어오지 않을 때 -->
  <select id="select2" resultMap="BoardMap">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
  </select>

if 조건문

조건에 따라 생성할 SQL문을 제어할 수 있다.
문법) SQL문

`<if>` 사용하기 I

Mapper 파일에서 조건으로 SQL을 제어한다.
조건에 따라 여러 개의 SQL을 생성할 필요가 없어 편리하다.
조건문을 사용하여 하나의 SQL문으로 조건에 따라 여러 상황을 처리할 수 있지만, SQL문을 작성하는 것이 복잡하다

  Scanner keyScan = new Scanner(System.in);
  System.out.print("게시글 번호? ");
  String str = keyScan.nextLine();
  keyScan.close();

  List<Board> list = null;

  try {
    // 게시글 번호가 주어지면 해당 게시글만 출력한다.
    list = sqlSession.selectList("BoardMapper.select3", Integer.parseInt(str));
  } catch (Exception e) {
    //e.printStackTrace();

    // 게시글 번호가 없거나 예외가 발생하면 전체 게시글을 출력한다.
    list = sqlSession.selectList("BoardMapper.select3");

    // 이때 같은 SQL 문(select3)을 실행한다.
  }

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %d\n", 
      board.getNo(),
      board.getTitle(),
      board.getRegisteredDate(),
      board.getViewCount());
  }

  <select id="select3" resultMap="BoardMap" parameterType="int">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    <if test="no != null">
      where board_id = #{no}
    </if>
  </select>

`<if>` 사용하기 II

if문을 여러번 사용하여 SQL문을 작성할 수도 있다.
사용자로부터 검색 키워드를 입력 받아 조회한다. (제목, 내용, 번호로 검색하기)

  Scanner keyScan = new Scanner(System.in);

  System.out.print("항목(1:번호, 2:제목, 3: 내용, 그 외: 전체)? ");
  String item = keyScan.nextLine();

  System.out.print("검색어? ");
  String keyword = keyScan.nextLine();

  keyScan.close();

  // SQL 매퍼에 여러 개의 파라미터 값을 넘길 때 주로 Map을 사용한다.
  HashMap<String, Object> params = new HashMap<>();
  params.put("item", item);
  params.put("keyword", keyword);

  List<Board> list = sqlSession.selectList("BoardMapper.select4", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n",
      board.getNo(),
      board.getTitle(),
      board.getContent(),
      board.getRegisteredDate(),
      board.getViewCount());
  }

  <select id="select4" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
      <if test="item == 1">
        where board_id = #{keyword}
      </if>
      <if test="item == 2">
        where title like concat('%', #{keyword}, '%')
      </if>
      <if test="item == 3">
        where contents like concat('%', #{keyword}, '%')
      </if>
  </select>

3. `<where>`

`<where>`사용 전

여러 개의 조건을 합쳐서 검색을 하는 경우, 조건이 빠지게 되면 문제가 발생한다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("번호? ");
  String value = keyScan.nextLine();
  if (value.length() > 0) {
    params.put("no", value);
  }

  System.out.print("제목? ");
  value = keyScan.nextLine();
  if (value.length() > 0) {
    params.put("title", value);
  }

  System.out.print("내용? ");
  value = keyScan.nextLine();
  if (value.length() > 0) {
    params.put("content", value);
  }

  keyScan.close();

  List<Board> list = sqlSession.selectList("BoardMapper.select5", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n", 
      board.getNo(),
      board.getTitle(), 
      board.getContent(), 
      board.getRegisteredDate(), 
      board.getViewCount());
}

  <select id="select5" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    where
      <if test="no != null">
        board_id = #{no}
      </if>
      <if test="title != null">
        or title like concat('%', #{title}, '%')
      </if>
      <if test="content != null">
        or contents like concat('%', #{content}, '%')
      </if>
  </select>

select5를 실행할 때 사용자가 no값을 입력하지 않으면, where 바로 뒤에 or이 오는 잘못된 SQL문을 생성한다.
잘못 생성된 SQL 문 예)
- select board_id, title, contents, created_date, view_count from x_board where or title like concat(‘%’, ?, ‘%’) <== or 앞에 조건문이 없다. or contents like concat(‘%’, ?, ‘%’)
위와 같은 문제를 해결하기 위해 or앞에 실행에 영향을 끼치지 않는 조건문을 삽입한다.
앞에 조건이 없을 경우, where 1=0이라는 임의 조건을 삽입한다. 단 임의 조건은 실행에 영향을 끼치지 않아야 한다.

  <select id="select6" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    where 1=0
      <if test="no != null">
        or board_id = #{no}
      </if>
      <if test="title != null">
        or title like concat('%', #{title}, '%')
      </if>
      <if test="content != null">
        or contents like concat('%', #{content}, '%')
      </if>
  </select>

그러나 이 방법도 문제가 있다. 세 조건문이 모두 실행되지 않는다면 전체 검색 결과가 나와야 하는데 그렇게 작동하지 않는다. 다시 말해서, 선택 조건이 없다는 것은 모든 데이터를 선택한다는 의미인데, select8에서는 조건을 입력하지 않으면, 값이 검색되지 않는다.

where 사용하기

<where>를 사용하면 or/and 앞에 조건이 없을 때 or/and를 자동으로 제거한다.
where 조건이 없을 때는 where을 생성하지 않는다.

  <select id="select7" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    <where>
      <if test="no != null">
        board_id = #{no}
      </if>
      <if test="title != null">
        or title like concat('%', #{title}, '%')
      </if>
      <if test="content != null">
        or contents like concat('%', #{content}, '%')
      </if>
      </where>
  </select>

`<trim>` 사용하기

<where> 대신에 <trim>을 사용하면 불필요한 SQL 코드 제거할 수 있다.
or/and 앞에 조건이 없을 때 or/and를 자동으로 제거한다.
<where>보다 정교하게 사용할 수 있지만, <where>을 사용할 수 있다면 <where>을 사용하는게 낫다.

  <select id="select8" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    <trim prefix="where" prefixOverrides="OR | AND"> 
      <if test="no != null">
        board_id = #{no}
      </if>
      <if test="title != null">
        or title like concat('%', #{title}, '%')
      </if>
      <if test="content != null">
        or contents like concat('%', #{content}, '%')
      </if>
    </trim>
  </select>

4. `<choose>`

여러 개의 조건문을 사용할 때, <choose>를 사용할 수 있다.
프로그래밍 언어에서 if ~ else처럼 사용할 수 있다.

  Scanner keyScan = new Scanner(System.in);

  System.out.print("항목(1:번호, 2:제목, 그 외: 내용)? ");
  String item = keyScan.nextLine();

  System.out.print("검색어? ");
  String keyword = keyScan.nextLine();

  keyScan.close();

  // SQL 매퍼에 여러 개의 파라미터 값을 넘길 때 주로 Map을 사용한다.
  HashMap<String, Object> params = new HashMap<>();
  if (item.equals("1")) {
    params.put("item", "no");
  } else if (item.equals("2")) {
    params.put("item", "title");
  } else {
    params.put("item", "content");
  }
  params.put("keyword", keyword);

  List<Board> list = sqlSession.selectList("BoardMapper.select21", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n", 
      board.getNo(),
      board.getTitle(), 
      board.getContent(), 
      board.getRegisteredDate(), 
      board.getViewCount());
  }

  <select id="select21" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    where
    <choose>
      <when test="item == 'no'">
        board_id = #{keyword}
      </when>
      <when test='item == "title"'>
        title like concat('%', #{keyword}, '%')
      </when>
      <otherwise>
        contents like concat('%', #{keyword}, '%')
      </otherwise>
    </choose>
  </select>

5. `<set>`

`<set>` 사용 전

dynamic sql을 사용하지 않으면, 비슷한 일을 하는 sql문을 여러 개 만들어 사용해야 한다.
아래 자바 코드는 게시글 변경하는 코드인데, dynamic sql을 사용하지 않으면 제목만 바꿀 경우, 내용만 바꿀 경우, 둘 다 바꿀 경우에 대해 각각의 update SQL을 준비해야 한다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("제목? ");
  String value = keyScan.nextLine();
  if (value.length() > 0) {
    params.put("title", value);
  }

  System.out.print("내용? ");
  value = keyScan.nextLine();
  if (value.length() > 0) {
    params.put("content", value);
  }

  keyScan.close();

  params.put("no", 1); // 1번 게시글 변경

  int count = 0;
  if (params.get("title") != null && params.get("content") != null) {
    count = sqlSession.update("BoardMapper.update3", params);
  } else if (params.get("title") != null) {
    count = sqlSession.update("BoardMapper.update1", params);
  } else if (params.get("content") != null) {
    count = sqlSession.update("BoardMapper.update2", params);
  }
  System.out.println(count);

  sqlSession.commit();
  sqlSession.close();

  <!-- 제목만 변경하는 update -->
  <update id="update1" parameterType="map">
    update x_board set
      title=#{title}
    where board_id=#{no}
  </update>

  <!-- 내용만 변경하는 update -->
  <update id="update2" parameterType="map">
    update x_board set
      contents=#{content}
    where board_id=#{no}
  </update>

  <!-- 제목과 내용을 변경하는 update -->
  <update id="update3" parameterType="map">
    update x_board set
      title=#{title},
      contents=#{content}
    where board_id=#{no}
  </update>

특정 컬럼의 값만 바꾸기 위해 여러 개의 SQL을 만드는 것은 번거롭다. 그래서 전체 컬럼의 값을 바꾸는 방식을 주로 사용한다.
문제점? 바꾸지 않아도 될 항목까지 변경하기 때문에 성능이 떨어진다

`<set>` 사용하기

<set>을 사용하면 제목만 바꾸는 경우, 내용만 바꾸는 경우, 둘 다 바꾸는 경우에 대해 한 개의 SQL로 처리할 수 있다.

  <update id="update4" parameterType="map">
    update x_board 
    <set>
      <if test="title != null">title=#{title},</if>   
      <if test="content != null">contents=#{content}</if>
      <!-- 현업에서는 ,를 앞에 붙인다. 예) ,contents=#{contents} -->
    </set> 
    where board_id=#{no}
  </update>

<if> : 이용하여 사용자가 입력한 항목만 변경한다.
<set> : SQL 앞,뒤에 붙은 콤마(,)를 자동으로 제거한다.

6. `<foreach>`

`<foreach>` 사용 전

게시물 번호를 여러 개 지정하여 조회를 하려고 한다. <foreach>를 사용하지 않고 여러 개시글을 조회하기 위해서는, 조회하려는 게시글의 수만큼 if문을 사용하여야 한다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("조회할 게시물 번호들(예: 1 6 8 10; 최대 5개)? ");
  String[] values = keyScan.nextLine().split(" ");

  int index = 0;
  for (String value : values) {
    params.put("no" + index++, value);
    // {"no0":1, "no1":6, "no2":8, "no3":10}
  }

  keyScan.close();

  List<Board> list = sqlSession.selectList("BoardMapper.select22", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n", 
      board.getNo(), 
      board.getTitle(), 
      board.getContent(), 
      board.getRegisteredDate(), 
      board.getViewCount());
  }

  <select id="select22" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    <where>
      <if test="no0 != null">
        board_id = #{no0}
      </if>
      <if test="no1 != null">
        or board_id = #{no1}
      </if>
      <if test="no2 != null">
        or board_id = #{no2}
      </if>
      <if test="no3 != null">
        or board_id = #{no3}
      </if>
      <if test="no4 != null">
        or board_id = #{no4}
      </if>
    </where>
  </select>

`<foreach>` 사용하기 I

<foreach>는 자바 코드로 반복문을 여러번 돌려야 하는 경우에 사용하기 적합하다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("조회할 게시물 번호들(예: 1 6 8 10; 최대 5개)? ");
  String[] values = keyScan.nextLine().split(" ");

  ArrayList<Object> noList = new ArrayList<>();
  for (String value : values) {
    noList.add(value);
  }
  // ArrayList를 맵에 담아서 넘겨준다.
  // ArrayList를 바로 넘기면 SQL에서는 꺼낼 방법이 없다.

  keyScan.close();

  List<Board> list = sqlSession.selectList("BoardMapper.select23", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n", 
      board.getNo(), 
      board.getTitle(), 
      board.getContent(), 
      board.getRegisteredDate(), 
      board.getViewCount());
  }

noList에서 값을 하나씩 꺼내서 no라고 하는데, 더이상 꺼낼 값이 없으면 if문의 조건을 만족하지 못한다. 따라서 반복문을 빠져나간다.

	<select id="select23" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
    <where>
      <foreach collection="noList" item="no">
      <!--  noList에서 값을 하나 깨내서 "no"라고 하겠다는 의미 -->
        <if test="no != ''">
          or board_id = #{no}
        </if>
      </foreach>
    </where>
  </select>

위의 반복문을 자바 코드로 표현하면 다음과 같다.

  ArrayList<Object> = map.get("noList");
  for(Object no : list) {
    if(!no.equals("")) {
      sql += " or board_id = " + no;
    }
  }

`<foreach>` 사용하기 II

위의 select문과 동일한 기능을 하지만, foreach문이 다르게 작성되었다.
SQL문에서 값을 여러 개 넣기 위해서는 각각의 파라미터를 괄호로 묶어서 표현하면 된다.

  <select id="select24" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date, <!-- comments -->
      view_count /* comments */
    from x_board
    <where>
      board_id in /* (1, 2, 11, 12) */
      <foreach collection="noList" item="no" open="(" separator="," close=")">
        #{no}
      </foreach>
    </where>
  </select>

`<foreach>` 사용하기 III

검색어를 입력받아 일치하는 게시글을 조회하는 경우에도 <foreach>를 사용하면 편리하다.
사용자가 입력한 검색어를 게시글 제목에 포함하고 있는 게시글을 조회하려고 한다. 사용자가 입력한 검색어를 띄어쓰기로 쪼개어, 각각의 키워드를 포함하는 게시글을 모두 검색 결과에 노출한다.
- 예) aa 1을 검색어로 입력하면, 제목에 aa 또는 1을 포함하는 게시글이 검색된다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("검색? ");
  String[] values = keyScan.nextLine().split(" ");

  ArrayList<Object> words = new ArrayList<>();
  for (String value : values) {
    words.add(value.trim());
  }
  params.put("words", words);

  keyScan.close();

  List<Board> list = sqlSession.selectList("BoardMapper.select25", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n",
      board.getNo(),
      board.getTitle(),
      board.getContent(),
      board.getRegisteredDate(),
      board.getViewCount());
  }

  <select id="select25" resultMap="BoardMap" parameterType="map">
    select 
      board_id,
      title, 
      contents, 
      created_date, <!-- comments -->
      view_count /* comments */
    from x_board
    <where>
      <foreach collection="words" item="word">
        or title like concat('%', #{word}, '%')
      </foreach>
    </where>
  </select>

7. `<bind>`

<bind>는 변수를 만들 때 사용할 수 있다.
<bind>를 사용하면, like문의 문자열 패턴을 만들 때 편하다.
검색어를 입력하면, 제목에 해당 검색어를 포함하는 게시글을 조회하려고 한다. 이 때, 사용자가 입력하는 검색어를 파라미터로 받는 변수를 만들고, 이를 SQL문에 넣어 사용한다.

  HashMap<String, Object> params = new HashMap<>();

  Scanner keyScan = new Scanner(System.in);

  System.out.print("제목? ");
  String value = keyScan.nextLine();
  params.put("title", value);

  keyScan.close();

  List<Board> list = sqlSession.selectList("BoardMapper.select26", params);

  for (Board board : list) {
    System.out.printf("%d, %s, %s, %s, %d\n",
      board.getNo(), 
      board.getTitle(), 
      board.getContent(),
      board.getRegisteredDate(), 
      board.getViewCount());
  }

  <select id="select26" resultMap="BoardMap" parameterType="map">
    <!-- bind는 변수를 만드는 태그 -->
    <bind name="titlePattern" value="'%' + _parameter.title + '%'"/>
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count
    from x_board
    <where>
        title like #{titlePattern}
    </where>
  </select>

8. `<sql>`

<sql> 태그를 사용하면 여러 SQL 문에서 중복적으로 사용하는 SQL 일부를 별도로 관리할 수 있다.
중복된 SQL을 메서드처럼 별도로 관리하기 때문에 사용하여 SQL을 관리하기가 쉬워진다.

<!-- 중복해서 사용되는 SQL을 별도로 정의하기 -->
  <sql id="sql1">
    select 
      board_id,
      title, 
      contents, 
      created_date,
      view_count 
    from x_board
  </sql>

중복해서 사용하는 SQL문을 별도로 정의한 후, 아래와 같이 사용할 수 있다.

	<select id="select1" resultMap="BoardMap" parameterType="int">
    <include refid="sql1"/>
    where board_id = #{value}
  </select>
  
  <select id="select2" resultMap="BoardMap" parameterType="int">
    <include refid="sql1"/>
  </select>

	<select id="select3" resultMap="BoardMap" parameterType="int">
    <include refid="sql1"/>
    <if test="no != null">
      where board_id = #{no}
    </if>
  </select>

Twitter Facebook LinkedIn

유지연

Mybatis4: Dynamic SQL

Mybatis4: Dynamic SQL

1. Dynamic SQL?

Dynamic sql

2. `<if>`

`<if>` 사용 전

if 조건문

`<if>` 사용하기 I

`<if>` 사용하기 II

3. `<where>`

`<where>`사용 전

where 사용하기

`<trim>` 사용하기

4. `<choose>`

5. `<set>`

`<set>` 사용 전

`<set>` 사용하기

6. `<foreach>`

`<foreach>` 사용 전

`<foreach>` 사용하기 I

`<foreach>` 사용하기 II

`<foreach>` 사용하기 III

7. `<bind>`

8. `<sql>`

공유하기

참고

2022-02-22 TIL(at work)

[HTTP] 8. HTTP 헤더2 - 캐시와 조건부 요청

[HTTP] 7. HTTP 헤더1 - 일반 헤더

[HTTP] 6. HTTP 상태 코드

유지연

Mybatis4: Dynamic SQL

1. Dynamic SQL?

Dynamic sql

2. <if>

<if> 사용 전

if 조건문

<if> 사용하기 I

<if> 사용하기 II

3. <where>

<where>사용 전

where 사용하기

<trim> 사용하기

4. <choose>

5. <set>

<set> 사용 전

<set> 사용하기

6. <foreach>

<foreach> 사용 전

<foreach> 사용하기 I

<foreach> 사용하기 II

<foreach> 사용하기 III

7. <bind>

8. <sql>

공유하기

참고

2022-02-22 TIL(at work)

[HTTP] 8. HTTP 헤더2 - 캐시와 조건부 요청

[HTTP] 7. HTTP 헤더1 - 일반 헤더

[HTTP] 6. HTTP 상태 코드

2. `<if>`

`<if>` 사용 전

`<if>` 사용하기 I

`<if>` 사용하기 II

3. `<where>`

`<where>`사용 전

`<trim>` 사용하기

4. `<choose>`

5. `<set>`

`<set>` 사용 전

`<set>` 사용하기

6. `<foreach>`

`<foreach>` 사용 전

`<foreach>` 사용하기 I

`<foreach>` 사용하기 II

`<foreach>` 사용하기 III

7. `<bind>`

8. `<sql>`