[MyBatis] 동적 SQL

조건에 맞는 SQL을 동적(Dynamic)으로 생성할 수 있다.

if

DB 테이블

test_table

id	string_value	number_value
1	hoge	100
2	hoge	200
3	fuga	300
4	piyo	400

소스 코드

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    select *
      from test_table
     where string_value = 'hoge'
     <if test="numberValue != null"> <!-- if 태그에서 조건 분기 -->
       and number_value = #{numberValue}
     </if>
  </select>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                session
                    .selectList("sample.mybatis.selectTest") // 매개 변수 미설정
                    .forEach(System.out::println);

                Map<String, Object> param = new HashMap<>();
                param.put("numberValue", 100);

                session
                    .selectList("sample.mybatis.selectTest", param) // 매개 변수 설정
                    .forEach(System.out::println);
            }
        }
    }
}

실행 결과

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where string_value = 'hoge' 
[DEBUG] s.m.selectTest  - ==> Parameters: 
[DEBUG] s.m.selectTest  - <==      Total: 2
TestTable [id=1, stringValue=hoge, numberValue=100]
TestTable [id=2, stringValue=hoge, numberValue=200]

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where string_value = 'hoge' and number_value = ? 
[DEBUG] s.m.selectTest  - ==> Parameters: 100(Integer)
[DEBUG] s.m.selectTest  - <==      Total: 1
TestTable [id=1, stringValue=hoge, numberValue=100]

설명

• <if> 태그를 사용하여 조건이 충족된 경우에만 SQL을 추가 시키거나 삭제할 수 있게 된다.
• test 속성에서 조건식을 기술한다.
  • 이 가운데는 검색 조건에 전달할 매개 변수의 값을 참조할 수 있다.
  • AND나 OR 조건을 작성할 때, and, or를 사용한다 ("&&", "||"가 아니다!).

choose, when, otherwise

DB 테이블

test_table

id	value
1	hoge
2
3	piyo

소스 코드

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    select *
      from test_table
    <choose>
      <when test="value == null">
        where value is null
      </when>
      <otherwise>
        where value = #{value}
      </otherwise>
    </choose>
  </select>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                TestTable testTable = session.selectOne("sample.mybatis.selectTest");
                System.out.println(testTable);

                Map<String, Object> param = new HashMap<>();
                param.put("value", "hoge");

                testTable = session.selectOne("sample.mybatis.selectTest", param);
                System.out.println(testTable);
            }
        }
    }
}

실행 결과

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where value is null 
[DEBUG] s.m.selectTest  - ==> Parameters: 
[DEBUG] s.m.selectTest  - <==      Total: 1
TestTable [id=2, value=null]

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where value = ? 
[DEBUG] s.m.selectTest  - ==> Parameters: hoge(String)
[DEBUG] s.m.selectTest  - <==      Total: 1
TestTable [id=1, value=hoge]

설명

• <choose> 태그를 사용하면 여러 옵션 중 하나를 적용하는 조건을 정의할 수 있다.
• <when> 태그에서 지정한 조건이 충족된 경우 SQL을 작성한다.
• <otherwise> 태그는 그 이외의 모든 조건이 충족되지 않은 경우 SQL을 작성한다.

where, trim

DB 테이블

test_table

id	string	number
1	hoge	100
2	hoge	200
3	fuga	200
4	piyo	400

소스 코드

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    select *
      from test_table
    <where>
      <if test="string != null">
        and string = #{string}
      </if>
      <if test="number != null">
        and number = #{number}
      </if>
    </where>
  </select>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                // 매개 변수를 빈 오브젝트로 실행
                Map<String, Object> param = new HashMap<>();
                selectAndPrint(session, param);

                // 매개 변수를 string만 지정해서 실행
                param = new HashMap<>();
                param.put("string", "hoge");
                selectAndPrint(session, param);

                // 매개 변수를 string과 number를 지정해서 실행
                param = new HashMap<>();
                param.put("string", "hoge");
                param.put("number", 200);
                selectAndPrint(session, param);
            }
        }
    }

    private static void selectAndPrint(SqlSession session, Map<String, Object> param) {
        session
            .selectList("sample.mybatis.selectTest", param)
            .forEach(System.out::println);
        System.out.println();
    }
}

실행 결과

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table 
[DEBUG] s.m.selectTest  - ==> Parameters: 
[DEBUG] s.m.selectTest  - <==      Total: 4
TestTable [id=1, string=hoge, number=100]
TestTable [id=2, string=hoge, number=200]
TestTable [id=3, string=fuga, number=200]
TestTable [id=4, string=piyo, number=300]

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table WHERE string = ? 
[DEBUG] s.m.selectTest  - ==> Parameters: hoge(String)
[DEBUG] s.m.selectTest  - <==      Total: 2
TestTable [id=1, string=hoge, number=100]
TestTable [id=2, string=hoge, number=200]

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table WHERE string = ? and number = ? 
[DEBUG] s.m.selectTest  - ==> Parameters: hoge(String), 200(Integer)
[DEBUG] s.m.selectTest  - <==      Total: 1
TestTable [id=2, string=hoge, number=200]

설명

• <where> 태그를 사용하면, 자식 요소가 어떤 문자열이 존재하는 경우에만 WHERE 절을 선두에 추가해주게 된다.
• 또한 <where> 태그 내의 문자열이 AND 또는 OR로 시작하면 자동으로 그 조건 연결문이 제거된다.
• 이 동작은 <if> 등 만으로 작성하게 되면 상당히 복잡한 작성이 되어 버리지만, 이 <where> 태그를 사용해서 간단히 작성할 수 있다.

trim 태그로 대체

위의 <where>를 사용한 정의는 <trim> 태그를 사용하여 다음과 같이 바꿀 수 있다.

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    select *
      from test_table
    <trim prefix="where" prefixOverrides="AND|OR">
      <if test="string != null">
        and string = #{string}
      </if>
      <if test="number != null">
        and number = #{number}
      </if>
    </trim>
  </select>
</mapper>

• prefix 속성에 앞에 추가할 문자열를 추가한다.
• prefixOverrides 속성에 처음부터 제거하는 문자열을 파이프 (|)로 구분한다.

set, trim

DB 테이블

test_table

id	string	number
1	hoge	100
2	hoge	200
3	fuga	200
4	piyo	100

소스 코드

TestTable.java

package sample.mybatis;

public class TestTable {
    private int id;
    private String string;
    private Integer number;

    public TestTable id(int id) {
        this.id = id;
        return this;
    }

    public TestTable string(String string) {
        this.string = string;
        return this;
    }

    public TestTable number(int number) {
        this.number = number;
        return this;
    }
}

sample_mapper.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="sample.mybatis">

  <update id="updateTest">
    update test_table
    <set>
      <if test="string != null">
        string = #{string},
      </if>
      <if test="number != null">
        number = #{number},
      </if>
    </set>
    where id = #{id}
  </update>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                session.update("sample.mybatis.updateTest",
                        new TestTable().id(1).number(555));

                session.update("sample.mybatis.updateTest",
                        new TestTable().id(3).string("update").number(999));

                session.commit();
            }
        }
    }
}

실행 결과

[DEBUG] s.m.updateTest  - ==>  Preparing: update test_table SET number = ? where id = ? 
[DEBUG] s.m.updateTest  - ==> Parameters: 555(Integer), 1(Integer)
[DEBUG] s.m.updateTest  - <==    Updates: 1
[DEBUG] s.m.updateTest  - ==>  Preparing: update test_table SET string = ?, number = ? where id = ? 
[DEBUG] s.m.updateTest  - ==> Parameters: update(String), 999(Integer), 3(Integer)
[DEBUG] s.m.updateTest  - <==    Updates: 1
test_table

test_table

id	string	number
1	hoge	555
2	hoge	200
3	update	999
4	piyo	300

설명

• <set> 태그를 사용하면 내부 문자열이 비어 있지 않은 경우 앞에 SET 절이 추가된다.
• 또한 끝에 쉼표 (,)가 자동으로 제거된다.

trim 태그로 대체

<where> 때와 마찬가지로 <trim>로 대체 할 수도 있다.

sample_mapper.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="sample.mybatis">

  <update id="updateTest">
    update test_table
    <trim prefix="set" suffixOverrides=",">
      <if test="string != null">
        string = #{string},
      </if>
      <if test="number != null">
        number = #{number},
      </if>
    </trim>
    where id = #{id}
  </update>
</mapper>

• suffixOverrides 속성에 마지막에 제거하는 문자을 지정한다.

foreach

bind

소스 코드

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    <bind name="parameter" value="'@@@' + _parameter + '@@@'"/>
    select *
      from test_table
     where value = #{parameter}
  </select>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                session.selectList("sample.mybatis.selectTest", "abc");
            }
        }
    }
}

실행 결과

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where value = ? 
[DEBUG] s.m.selectTest  - ==> Parameters: @@@abc@@@(String)

설명

• <bind> 태그에서 임시 변수를 정의 할 수 있다.
• name 속성에 임시 변수의 이름을 정의한다.
• value 속성에 OGNL(Object Graph Navigation Language) 표현식을 사용한 값의 정의가 있다.
• _parameter는 매개 변수로 전달된 값을 참조할 암시 변수이다 (특수 매개 변수 이름 참조).