Dynamic SQL(동적 SQL)

Dynamic SQL(동적 SQL)

• 쿼리를 실행할 때 프로그램의 실행 중에 쿼리 문자열을 동적으로 생성하는 방식
• 조건에 따라 쿼리의 구조를 유연하게 변경할 수 있음

특징

1. 유연성 : 다양한 조건에 따라 쿼리를 수정하거나 생성
2. 조건부 쿼리 : 특정 조건이 충족될 때만 쿼리의 일부를 포함하거나 제외
3. 사용자 정의 입력 : 사용자가 입력한 값에 따라 쿼리를 동적으로 구성

if

• 동적 SQL에서 가장 공통적으로 사용되는 것
• where의 일부로 포함될 수 있음

//사용자가 블로그 제목을 검색하지 않았을 경우 ACTIVE 상태인 모든 블로그를 반환하고, 제목을 입력한 경우에는 해당 제목이 포함되는 블로그만 반환하는 쿼리
<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

choose, when, otherwise

• JAVA에서의 switch구문과 유사

//
<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise> <!-- 위의 두 조건이 모두 false인 경우 실행 -->
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim, where, set

• trim : SQL 쿼리의 특정 부분에서 불필요한 문자열이나 공백을 자동으로 처리

  //블로그 게시물을 업데이트하는 동시에 조건에 따라 검색하는 쿼리
  <update id="updateBlog" parameterType="Blog">
  UPDATE BLOG
  <set> <!-- update할 필드를 지정 -->
    <trim prefix=", " suffixOverrides=","> <!- 어떤 조건이 참일 경우 각 필드 앞에 ,가 붙으며, 마지막 부분에 ,가 있으면 제거 -->
      <if test="title != null">title = #{title},</if>
      <if test="content != null">content = #{content},</if>
      <if test="state != null">state = #{state},</if>
    </trim>
  </set>
  <where> <!-- 검색 조건 추가 -->
    <if test="id != null">AND id = #{id}</if>
    <if test="author != null">AND author_name = #{author}</if>
  </where>
  </update>

foreach

• collection에 대한 반복처리
• foreach 엘리먼트는 SQL 쿼리에서 반복적인 작업을 수행할 수 있도록 해줌(리스트나 배열의 각 요소를 SQL 쿼리에 동적으로 추가할 수 있음)
• collection 속성을 사용하여 반복할 데이터 소스 지정
• item : 현재 반복 중인 요소를 참조하는 변수
• index : 현재 반복의 인덱스를 나타내는 변수. 몇 번째 반복인지를 알 수 있음
• open과 close 속성을 사용하여 반복문이 시작하고 끝나는 위치를 지정할 수 있음
• separator 속성을 통해 각 반복 요소 사이에 추가할 문자열을 지정할 수 있음
• Map을 사용하면 index는 key가 되고, item은 value가 됨

//주어진 id 목록에 포함된 블로그 게시물을 검색
<select id="selectPostIn" resultType="domain.blog.Post"> <!-- 쿼리 결과를 Post 클래스의 인스턴스로 변환-->
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list" <!-- item부터 index까지 list를 순회 -->
        open="ID in (" separator="," close=")" nullable="true"> <!-- 쿼리의 시작 부분에 ID in (을 추가, 각 요소 사이에 ,을 추가, 쿼리의 끝 부분에 )을 추가 -->
          #{item}
    </foreach>
  </where>
</select>

script

• script 태그 안에서 Mybatis의 다양한 조건문(if, choose, when, otherwise)과 반복문(foreach)을 사용할 수 있음
• 여러 SQL 구문을 결합하거나 조건에 따라 쿼리의 일부를 추가하거나 생략할 수 있음
• 태그들을 결합하기 위해 필요함
• script 태그 안에 있는 것들이 쿼리문임

<select id="findPosts" resultType="domain.blog.Post">
  <script>
    SELECT *
    FROM POST
    <where>
      <if test="title != null">AND title = #{title}</if>
      <if test="author != null">AND author_name = #{author}</if>
      <foreach item="item" collection="tags" open="AND tags IN (" separator="," close=")">
        #{item}
      </foreach>
    </where>
  </script>
</select>

bind

• OGNL(Object-Graph Navigation Language) 표현식을 통해 변수 생성
• OGNL : 객체의 속성에 접근하거나 계산을 수행하는 데 사용되는 언어. Mybatis에서는 이 표현식을 사용하여 파라미터나 다른 변수에 접근할 수 있음
• 변수를 SQL 쿼리의 컨텍스트에 바인딩할 수 있도록 해줌

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> <!-- pattern이라는 변수 생성. %My Blog%와 같은 패턴 생성 -->
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

Multi-db Vendor Support

• _databaseId 변수를 사용하여 현재 사용 중인 DB 종류를 확인하고 이에 따라 다른 SQL 구문 실행 가능
• selectKey : 새로 생성할 레코드의 ID를 자동으로 생성하는 데 사용
• if : 특정 조건에 따라 SQL 구문을 선택적으로 실행

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

Pluggable Scripting Languages For Dynamic SQL

• Mybatis 3.2이상 : 플러그인 형태로 스크립트 언어를 사용할 수 있도록 지원
• 다양한 스크립트 언어로 동적 SQL을 작성할 수 있음
• 내장된 언어
  1. XML : 기본적으로 사용되는 언어. 모든 동적 태그 지원. 이전에 다룬 동적 태그를 그대로 사용할 수 있음.
  2. Raw : 기능이 조금 부족하지만 XML보다 속도가 빠름. 파라미터를 치환한 후 DB 드라이버에 SQL을 전달