[Spring] Dynamic SQL

1. if 

MyBatis에서 가장 공통적으로 사용되는 요소

파라미터 타입안에 다른 클래스(타입)이 포함되어 캡슐화를 이룰 경우 .(dot) 연산자로 접근 가능하다.

실습

    <select id="getAllEmployeeInfo" parameterType="EmployeesVO" resultType="EmployeesVO">
        SELECT    /* [ArticleDAO.getAllEmployeeInfo] [20160420] */
                EMPLOYEE_ID employeeId
                , FIRST_NAME firstName
                <if test="firstName != null and firstName != ''">
                , LAST_NAME lastName
                , EMAIL email
                , SALARY salary
                , COMMISSION_PCT commissionPct
                , HIRE_DATE hireDate
                , D.DEPARTMENT_NAME departmentName
                </if>
        FROM    EMPLOYEES
        <if test="firstName != null and firstName != ''">
        E , DEPARTMENTS D
        </if>
        WHERE    LAST_NAME = #{lastName}
        <if test="firstName != null and firstName != ''">
        AND     E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND        FIRST_NAME = #{firstName}
        </if>
    </select>

2. choose ( when, otherwise )

여러 구문 중 하나만 실행할 수 있다.

실습

    <select id="getAllEmployeeInfo" parameterType="EmployeesVO" resultType="EmployeesVO">
        SELECT    /* [ArticleDAO.getAllEmployeeInfo] [20160420] */
                EMPLOYEE_ID employeeId
                , FIRST_NAME firstName
                <choose>
                // 여러개가 참일 때는 위에 있는 것이 출력된다.
                    <when test="firstName != null and firstName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                        , D.DEPARTMENT_NAME departmentName
                    </when>
                    <when test="lastName != null and lastName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                    </when>
                    <otherwise>
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                    </otherwise>
                </choose>
        FROM    EMPLOYEES
        <if test="firstName != null and firstName != ''">
        E , DEPARTMENTS D
        </if>
        <if test="firstName != null and firstName != ''">
        E , DEPARTMENTS D
        </if>
        WHERE    LAST_NAME = #{lastName}
        <if test="firstName != null and firstName != ''">
        AND     E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND        FIRST_NAME = #{firstName}
        </if>
    </select> 

3. trim ( where, set )

<where></where> 이나 <set></set> 를 사용할 때 내부에 컨텐츠가 존재하면 키워드를 포함시킨다.

실습

    <select id="getAllEmployeeInfo" parameterType="EmployeesVO" resultType="EmployeesVO">
        SELECT    /* [ArticleDAO.getAllEmployeeInfo] [20160420] */
                EMPLOYEE_ID employeeId
                , FIRST_NAME firstName
                <choose>
                여러개가 참일 때는 위에 있는 것이 출력된다.
                    <when test="firstName != null and firstName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                        , D.DEPARTMENT_NAME departmentName
                    </when>
                    <when test="lastName != null and lastName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                    </when>
                    <otherwise>
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                    </otherwise>
                </choose>
        FROM    EMPLOYEES
        <if test="firstName != null and firstName != ''">
        E , DEPARTMENTS D
        </if>
        <trim prefix="WHERE" prefixOverrides="AND">
            <if  test="lastName != null and lastName != ''">
            LAST_NAME = #{lastName}
            </if>
            <if test="firstName != null and firstName != ''">
            E.DEPARTMENT_ID = D.DEPARTMENT_ID
            FIRST_NAME = #{firstName}
            </if>
        </trim>
    </select> 

4. foreach

Collection의 반복 처리 기능. IN 키워드를 사용할 때 종종 사용된다. 

Collection의 속성은 파라미터가 List일 경우 list, Array(배열)일 경우 array를 사용한다.

    <select id="getAllEmployeeInfo" parameterType="map" resultType="EmployeesVO">
        SELECT    /* [ArticleDAO.getAllEmployeeInfo] [20160420] */
                EMPLOYEE_ID employeeId
                , FIRST_NAME firstName
                <choose>
                <!-- 여러개가 참일 때는 위에 있는 것이 출력된다. -->
                    <when test="employee.firstName != null and employee.firstName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                        , D.DEPARTMENT_NAME departmentName
                    </when>
                    <when test="employee.lastName != null and employee.lastName != ''">
                        , LAST_NAME lastName
                        , EMAIL email
                    </when>
                    <otherwise>
                        , LAST_NAME lastName
                        , EMAIL email
                        , SALARY salary
                        , COMMISSION_PCT commissionPct
                        , HIRE_DATE hireDate
                    </otherwise>
                </choose>
        FROM    EMPLOYEES
        <if test="employee.firstName != null and employee.firstName != ''">
        E , DEPARTMENTS D
        </if>
        WHERE 1 = 1
        <if  test="employee.lastName != null and employee.lastName != ''">
        AND        LAST_NAME = #{lastName}
        </if>
        <if test="employee.firstName != null and employee.firstName != ''">
        AND        E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND        FIRST_NAME = #{firstName}
        </if>
        <if test="managerIds != null">
        AND        MANAGER_ID IN
        <foreach collection="managerIds" item="managerId" separator=", " open="(" close=")">
            #{managerId}
        </foreach>
        </if>
    </select>