BackEnd/Spring
[Spring] Dynamic SQL
best
2016. 4. 20. 16:30
1. if
MyBatis에서 가장 공통적으로 사용되는 요소
파라미터 타입안에 다른 클래스(타입)이 포함되어 캡슐화를 이룰 경우 .(dot) 연산자로 접근 가능하다.
실습
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 |
<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> |
cs |
2. choose ( when, otherwise )
여러 구문 중 하나만 실행할 수 있다.
실습
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 |
<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> |
cs |
3. trim ( where, set )
<where></where> 이나 <set></set> 를 사용할 때 내부에 컨텐츠가 존재하면 키워드를 포함시킨다.
실습
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40 |
<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> |
cs |
4. foreach
Collection의 반복 처리 기능. IN 키워드를 사용할 때 종종 사용된다.
Collection의 속성은 파라미터가 List일 경우 list, Array(배열)일 경우 array를 사용한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45 |
<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> |
cs |