[MyBatis] 동적 SQL(Dynamic SQL)

동적 SQL(Dynamic SQL)

동적 SQL은 전달값에 따라 엘리먼트에 다른 SQL 명령을 등록하는 기능이다. 즉, SQL문을 동적으로 생성하는 기능을 말한다.

동적 SQL은 OGNL(Object Graph Navigation Language) 표현식을 사용하여 구현할 수 있다.

예제를 보자

이름을 전달받지 못한 경우 MYHEWON 테이블에 저장된 모든 회원정보를 검색하여 검색결과를 객체로 제공하고

이름을 전달받은 경우 MYHEWON 테이블에 저장된 해당 이름의 회원정보를 검색하여 검색결과를 객체로 제공하는 엘리먼트를 작성해보자.

동적 SQL 기능을 사용하여 전달값에 따라 SQL 명령을 다르게 등록되어 사용되도록 설정할 것이다.

xml 매퍼 파일

[MyHewonMapper.xml]

<select id="selectDynamicHewonList" parameterType="string" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<if test="name != null and name != ''"><!-- 조건 : 전달값(이름)이 있는 경우 >> [true] -->
		where hewon_name=#{name}
	</if>
	order by hewon_id
</select>

If 엘리먼트 : 조건에 의해 엘리먼트 내용(SQL의 부분 명령)의 포함여부를 설정하는 엘리먼트

→ if 엘리먼트의 조건이 참인 경우 엘리먼트 내용을 SQL 명령에 포함한다.

- test 속성 : false(엘리먼트 내용 미포함) 또는 true(엘리먼트 내용 포함) 중 하나를 속성값으로 설정한다.

→ parameterType 속성값으로 전달값에 대한 조건식을 작성하여 test 속성값으로 제공한다.

인터페이스 매퍼 파일

public interface MyHewonMapper {
	//...
    	//...
	List<MyHewon> selectDynamicNameHewonList(String name);
}

DAO

public class MyHewonDAO extends AbstractSession {
	private static MyHewonDAO _dao;

	private MyHewonDAO() {
		// TODO Auto-generated constructor stub
	}
	
	static {
		_dao=new MyHewonDAO();
	}
	
	public static MyHewonDAO getDAO() {
		return _dao;
	}
	
	//...
    	//...
	
    	//추상메소드 선언
	public List<MyHewon> selectDynamicNameHewonList(String name) {
		SqlSession sqlSession=getSqlSessionFactory().openSession(true);
		try {
			return sqlSession.getMapper(MyHewonMapper.class).selectDynamicNameHewonList(name);
		} finally {
			sqlSession.close();
		}
	}

}

choose 엘리먼트 / trim 엘리먼트 / wehre 엘리먼트

이번엔 아이디와 이름을 전달받지 못한 경우 MYHEWON 테이블에 저장된 모든 회원정보를 검색하여 검색결과를 객체로 제공하고

아이디 또는 이름을 전달받은 경우 MYHEWON 테이블에 저장된 해당 아이디 또는 이름의 회원정보를 검색하여 검색결과를 객체로 제공하는 엘리먼트를 작성해보자

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<if test="id != null and id != ''">
		where hewon_id = #{id}
	</if>
	<if test="name != null and name != ''">
		where hewon_name = #{name}
	</if>
	order by hewon_id
</select>

하지만 이렇게 작성할 경우, 아이디와 이름를 모두 전달받은 경우 WHERE 문장이 2번 포함되어 에러가 발생한다는 문제점이 발생한다.

이 문제를 해결해보자

해결법1)

choose 엘리먼트를 이용해 아이디와 이름를 모두 전달받아도 하나의 WHERE 문장만 포함되도록 동적 SQL 기능 구현하기

choose : 조건에 맞는 하나의 엘리먼트 내용(SQL의 부분 명령)이 SQL 명령에 포함되도록 설정하는 엘리먼트

→ 하위 엘리먼트 : when 엘리먼트(1개 이상), otherwise 엘리먼트(0개 또는 1개)

when : 조건에 의해 엘리먼트 내용을 SQL 명령에 포함하는 엘리먼트

→ when 엘리먼트의 조건에 맞는 엘리먼트 내용을 SQL 명령에 포함한 후 choose 엘리먼트 종료

- test 속성 : false(엘리먼트 내용 미포함) 또는 true(엘리먼트 내용 포함) 중 하나를 속성값으로 설정

otherwise : when 엘리먼트의 모든 조건이 거짓인 경우 엘리먼트 내용을 SQL 명령에 포함하는 엘리먼트

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<choose>
		<when test="id != null and id != ''">
			where hewon_id = #{id}
		</when>
		<when test="name != null and name != ''">
			where hewon_name = #{name}
		</when>
	</choose>
	order by hewon_id
 </select>

해결법2)

trim 엘리먼트 이용해 아이디와 이름를 모두 전달받아 하나의 WHERE 문장만 포함되도록 동적 SQL 기능 구현하기

trim : 엘리먼트 내용에 필요한 부분 명령을 추가하거나 불필요한 부분 명령을 삭제하여 SQL 명령에 포함하는 엘리먼트

trim은 하위 엘리먼트로 여러개의 if 엘리먼트를 사용한다.

- prefix 속성 : trim 엘리먼트에 엘리먼트 내용이 존재할 경우 엘리먼트 내용 앞에 추가될 부분 SQL 명령을 속성값 설정

- suffix 속성 : trim 엘리먼트에 엘리먼트 내용이 존재할 경우 엘리먼트 내용 뒤에 추가될 부분 SQL 명령을 속성값 설정

- prefixOverrides 속성 : trim 엘리먼트에 엘리먼트 내용이 존재할 경우 엘리먼트 내용에서 제거될 앞에 존재하는 부분 SQL 명령을 속성값으로 설정

- suffixOverrides 속성 : trim 엘리먼트에 엘리먼트 내용이 존재할 경우 엘리먼트 내용에서 제거될 뒤에 존재하는 부분 SQL 명령을 속성값으로 설정

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<trim prefix="where" prefixOverrides="or|and">
		<if test="id != null and id != ''">
			hewon_id = #{id}
		</if>
		<if test="name != null and name != ''">
			${choice} hewon_name = #{name}
		</if>
	</trim>
	order by hewon_id	
</select>

해결법3)

where 엘리먼트를 이용해 구현해보기

where : 엘리먼트 내용이 있는 경우 엘리먼트 내용 앞부분에 [and] 또는 [or] 키워드가 있으면 제거하고 엘리먼트 앞부분에 [where] 키워드를 추가하여 SQL 명령에 포함하는 엘리먼트

<select id="selectDynamicHewonList" parameterType="map" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<where>
		<if test="id != null and id != ''">
			hewon_id = #{id}
		</if>
		<if test="name != null and name != ''">
			${choice} hewon_name = #{name}
		</if>
	</where>
	order by hewon_id
</select>

회원정보를 전달받아 MYHEWON 테이블에 저장된 회원정보를 변경하는 엘리먼트를 선언해보자.

→ 아이디를 비교하여 아이디를 제외한 컬럼값을 전달값으로 변경한다.

xml 매퍼

<update id="updateHewon" parameterType="MyHewon">
	update myhewon set hewon_name=#{name}, hewon_phone=#{phone}, hewon_email=#{email}
			, hewon_status=#{status} where hewon_id=#{id}
</update>

인터페이스매퍼

public interface MyHewonMapper {
	//...
    	//...
	int updateHewon(MyHewon hewon);
}

DAO

public class MyHewonDAO extends AbstractSession {
	private static MyHewonDAO _dao;

	private MyHewonDAO() {
		// TODO Auto-generated constructor stub
	}
	
	static {
		_dao=new MyHewonDAO();
	}
	
	public static MyHewonDAO getDAO() {
		return _dao;
	}
	
	//...
    	//...
	
	public int updateHewon(MyHewon hewon) {
		SqlSession sqlSession=getSqlSessionFactory().openSession(true);
		try {
			return sqlSession.getMapper(MyHewonMapper.class).updateHewon(hewon);
		} finally {
			sqlSession.close();
		}
	} 
}

JSP

<%@page import="xyz.itwill.dao.MyHewonDAO"%>
<%@page import="xyz.itwill.dto.MyHewon"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	/*
	//MYHEWON 테이블에서 아이디가 [xxx]인 회원의 이름을 [로빈훗]으로 변경
	MyHewon hewon=new MyHewon();//기본 생성자에 의해 생성된 객체의 필드에는 기본값 저장
	hewon.setId("xxx");
	hewon.setName("로빈훗");
	
	//DTO 객체를 전달받아 DAO 클래스의 메소드를 호출하여 회원정보를 변경 처리
	// => DTO 객체에는 아이디와 이름만 필드에 저장되어 있고 나머지 필드에는 기본값 저장
	// => MYHEWON 테이블에 저장된 회원정보가 비정상적으로 변경
	MyHewonDAO.getDAO().updateHewon(hewon);
	*/	

	//DTO 객체에 변경값외에 기존값을 객체 필드에 저장 
	MyHewon hewon=new MyHewon(); 
	hewon.setId("xxx");
	hewon.setName("로빈훗");
	hewon.setPhone("010-5678-2135");
	hewon.setEmail("xxx@itwill.xyz");
	hewon.setStatus(4);

	MyHewonDAO.getDAO().updateHewon(hewon);
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>MYBATIS</title>
</head>
<body>
	<h1>회원정보 변경</h1>
	<hr>
	<h3>회원정보를 성공적으로 변경 하였습니다.</h3>
</body>
</html>

set 엘리먼트

이번엔 마찬가지로 회원정보를 전달받아 MYHEWON 테이블에 저장된 회원정보를 변경하는 엘리먼트를 선언할 건데,

아이디를 비교하여 전달값이 있는 경우에만 아이디를 제외한 컬럼값을 전달값으로 변경하도록 설정해보자.

if 엘리먼트의 test 속성값으로 조건식을 사용할 경우 비교 연산자(>, <, >=, <=)를 사용하면 에러가 발생하므로
기호로 표현된 비교 연산자 대신 문자로 표현된 연산자(gt, lt, gte, lte) 사용하여 표현해야 한다.

<update id="updateDynamicHewon" parameterType="MyHewon">
	update myhewon 
		<trim prefix="set" suffixOverrides=",">
			<if test="name != null and name != ''">	
				hewon_name=#{name},
			</if> 
			<if test="phone != null and phone != ''">
				hewon_phone=#{phone},
			</if>
			<if test="email != null and email != ''">
				hewon_email=#{email},
			</if>
			<if test="status gte 1 and status lte 4">
				hewon_status=#{status}
			</if>
		</trim>
		where hewon_id=#{id}
</update>

이를 set 엘리먼트를 이용해 다시 작성해보자

set : 엘리먼트 내용이 있는 경우 엘리먼트 내용 뒷부분에 [,] 기호가 있으면 제거하고 엘리먼트 앞부분에 [set] 키워드를 추가하여 SQL 명령에 포함하는 엘리먼트

<update id="updateDynamicHewon" parameterType="MyHewon">
	update myhewon 
		<set>
			<if test="name != null and name != ''">	
				hewon_name=#{name},
			</if> 
			<if test="phone != null and phone != ''">
				hewon_phone=#{phone},
			</if>
			<if test="email != null and email != ''">
				hewon_email=#{email},
			</if>
			<if test="status gte 1 and status lte 4">
				hewon_status=#{status}
			</if>
		</set>
		where hewon_id=#{id}
</update>

인터페이스 매퍼

public interface MyHewonMapper {
	//...
    	//...
	int updateDynamicHewon(MyHewon hewon); 
}

DAO 클래스

public class MyHewonDAO extends AbstractSession {
	private static MyHewonDAO _dao;

	private MyHewonDAO() {
		// TODO Auto-generated constructor stub
	}
	
	static {
		_dao=new MyHewonDAO();
	}
	
	public static MyHewonDAO getDAO() {
		return _dao;
	}
	
	//...
    	//...
	public int updateDynamicHewon(MyHewon hewon) {
		SqlSession sqlSession=getSqlSessionFactory().openSession(true);
		try {
			return sqlSession.getMapper(MyHewonMapper.class).updateDynamicHewon(hewon);
		} finally {
			sqlSession.close();
		}
	}
}

JSP

[hewonDynamicUpdate.jsp]

<%@page import="xyz.itwill.dao.MyHewonDAO"%>
<%@page import="xyz.itwill.dto.MyHewon"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	//MYHEWON 테이블에서 아이디가 [xxx]인 회원의 이름을 [홍경래]으로 변경
	MyHewon hewon=new MyHewon();
	hewon.setId("xxx"); 
	hewon.setName("홍경래");
	hewon.setPhone("010-1247-5411");
	
	MyHewonDAO.getDAO().updateDynamicHewon(hewon);
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>MYBATIS</title>
</head>
<body>
	<h1>회원정보 변경</h1>
	<hr>
	<h3>회원정보를 성공적으로 변경 하였습니다.</h3>
</body>
</html>

foreach 엘리먼트

foreach : 전달받은 List 객체의 요소값을 반복적으로 제공받아 SQL 명령에 포함하는 엘리먼트

collection 속성 : 반복 처리하기 위한 List 객체의 이름을 속성값으로 설정
item 속성 : list 객체의 요소값을 저장하여 SQL 명령으로 사용하기 위한 이름을 속성값으로 설정
open 속성 : foreach 엘리먼트로 제공될 엘리먼트 내용 앞부분에 추가될 부분 SQL 명령을 속성값으로 설정
close 속성 : foreach 엘리먼트로 제공될 엘리먼트 내용 뒷부분에 추가될 부분 SQL 명령을 속성값으로 설정
separator 속성 : List 객체의 요소값을 구분하기 위한 부분 SQL 명령을 속성값으로 설정

foreach 엘리먼트를 이용해 여러개의 아이디를 전달받아 MYHEWON 테이블에 저장된 해당 아이디의 회원정보를 검색하여 검색결과를 객체로 제공하는 엘리먼트를 선언해보자.

만약 아이디가 하나도 전달되지 않은 경우 MYHEWON 테이블의 모든 검색하여 객체로 제공할것이다.

parameterType 속성값으로 ArrayList 클래스(List 인터페이스)를 설정하여 List 객체를 전달받아 SQL 명령에서 [list] 이름으로 표현하여 사용할 것이다.

xml 매퍼

<select id="selectMultiDynamicHewonList" parameterType="list" resultMap="myHewonResultMap">
	select hewon_id, hewon_name, hewon_phone, hewon_email, hewon_status from myhewon
	<if test="list != null">
		where hewon_id in
		<foreach collection="list" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>
	</if> 
</select>

인터페이스 매퍼

public interface MyHewonMapper {
	//...
    	//...
	List<MyHewon> selectMultiDynamicHewonList(List<String> idList);
}

DAO

public class MyHewonDAO extends AbstractSession {
	private static MyHewonDAO _dao;

	private MyHewonDAO() {
		// TODO Auto-generated constructor stub
	}
	
	static {
		_dao=new MyHewonDAO();
	}
	
	public static MyHewonDAO getDAO() {
		return _dao;
	}
	
	//...
    	//...
	public List<MyHewon> selectMultiDynamicHewonList(List<String> idList) {
		SqlSession sqlSession=getSqlSessionFactory().openSession(true);
		try {
			return sqlSession.getMapper(MyHewonMapper.class).selectMultiDynamicHewonList(idList);
		} finally {
			sqlSession.close();
		}
	}
}

JSP

[hewonListDynamicMultiSelect.jsp]

<%@page import="java.util.ArrayList"%>
<%@page import="java.util.HashMap"%>
<%@page import="java.util.Map"%>
<%@page import="xyz.itwill.dto.MyHewon"%>
<%@page import="xyz.itwill.dao.MyHewonDAO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String ids=request.getParameter("ids");
 
	List<String> idList=null;
	if(ids!=null && !ids.equals("")) {//전달값이 있는 경우
		idList=new ArrayList<>();
		for(String id : ids.split(",")) {
			idList.add(id.trim());
		}
	}
	
	List<MyHewon> hewonList=MyHewonDAO.getDAO().selectMultiDynamicHewonList(idList);
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>MYBATIS</title>
<style type="text/css">
table {
	border: 1px solid black;
	border-collapse: collapse;
}

td {
	border: 1px solid black;
	text-align: center;
	padding: 3px;
}

.id { width: 150px; }
.name { width: 150px; }
.phone { width: 200px; }
.email { width: 200px; }
.status { width: 100px; }
</style>
</head>
<body>
	<h1>회원목록</h1>
	<hr>
	<table>
		<tr>
			<td class="id">아이디</td>
			<td class="name">이름</td>
			<td class="phone">전화번호</td>
			<td class="email">이메일</td>
			<td class="status">공개범위</td>
		</tr>
		<% if(hewonList.isEmpty()) { %>
			<tr>
				<td colspan="5">검색된 회원정보가 없습니다.</td>
			</tr>
		<% } else { %>
			<% for(MyHewon hewon : hewonList) { %>
			<tr>
				<td><%=hewon.getId() %></td>
				<td><%=hewon.getName() %></td>
				<td><%=hewon.getPhone() %></td>
				<td><%=hewon.getEmail() %></td>
				<td><%=hewon.getStatus() %></td>
			</tr>
			<% } %>
		<% } %>
	</table>
	<br>
	
	<form method="post">
		아이디 : <input type="text" name="ids">
		<button type="submit">검색</button>
		<b>[검색하고자 하는 아이디가 여러개인 경우 ,로 구분하여 입력해 주세요.]</b>
	</form>	
</body>
</html>

저작자표시 비영리 변경금지

'학원 > 복기' 카테고리의 다른 글

[Spring] Spring 프레임워크란? (0)	2023.07.24
[MyBatis] 인터페이스 기반의 매퍼 파일만 사용해 매퍼 등록하기 (0)	2023.07.24
[Mybatis] cache 엘리먼트 (0)	2023.07.23
[Mybatis] 전달값 표현식 / bind 엘리먼트 (0)	2023.07.23
[MyBatis] 매퍼에 등록된 SQL 명령에 필요한 값 전달하기 / Map 객체 이용하기 (0)	2023.07.21

개뿌

[MyBatis] 동적 SQL(Dynamic SQL)

동적 SQL(Dynamic SQL)

choose 엘리먼트 / trim 엘리먼트 / wehre 엘리먼트

set 엘리먼트

foreach 엘리먼트

'학원 > 복기' 카테고리의 다른 글

티스토리툴바

[MyBatis] 동적 SQL(Dynamic SQL)

동적 SQL(Dynamic SQL)

choose 엘리먼트 / trim 엘리먼트 / wehre 엘리먼트

set 엘리먼트

foreach 엘리먼트

'학원 > 복기' 카테고리의 다른 글

'학원/복기' Related Articles

티스토리툴바