[Spring Boot] 동적 쿼리(Dynamic SQL)

동적쿼리 Dynamic SQL

상황에 따라서 쿼리가 반복(for)되거나 변경(if) 되어야 할 경우가 있다.
이때 쿼리 안에서 for문이나 if 문을 사용할 수도 있는데 이를 동적 쿼리라고 부른다.

사용방법은 jsp 에서 사용한 c:if 나 c:foreach 와 유사하며,
다양한 기능들이 있지만 기본적으로 if 와 foreach 를 사용한다.

SQL 프로시저를 통해 함수를 만들어 사용할 수 있지만 수정을 위해서는 DB를 건드려야 하기 때문에 잘 사용되지는 않는다.

✨ 조건문

회원가입 시 사용자가 입력하지 않은 값을 null이 아닌 다른 값으로 채워넣기 위한 쿼리를 작성해보자!

📌 if문

다이나믹 쿼리에서 사용하는 if 문에는 else가 없다. (c:if 처럼)
그래서 n개의 조건문이 있을 경우 if 를 여러번 사용해줘야 한다.

<mapper>
  	<!-- if 문 사용 (esle 사용불가) -->
	<insert id="join" parameterType="kr.co.gudi.dto.MemberDTO">
		INSERT INTO member(id,pw,name,age,gender,email) VALUES
			<if test="!email.equals('')">		
				(#{id},#{pw},#{name},#{age},#{gender},#{email})
			</if>
			<if test="email.equals('')">
				(#{id},#{pw},#{name},#{age},#{gender},'이메일 없음')
			</if>
	</insert> 

• test="조건" 형식으로 사용한다.
  ❌ 데이터가 null로 들어오는지 공백으로 들어오는지를 먼저 확인하고 해줘야 한다. (아니면 둘 다 처리를 해주거나)

📌 choose - when - otherwise

  	<!-- choose 사용 (else 사용가능) -->
	<insert id="join" parameterType="kr.co.gudi.dto.MemberDTO">
		INSERT INTO member(id,pw,name,age,gender,email) VALUES
			<choose>
				<when test="!email.equals('')">
					(#{id},#{pw},#{name},#{age},#{gender},#{email})
				</when>
				<otherwise>
					(#{id},#{pw},#{name},#{age},#{gender},'이메일 없음')
				</otherwise>
			</choose>
	</insert>
</mapper>

• else 를 사용하기 위하고 싶다면 choose 를 사용해주면 된다.
• <when test="조건"> 형식으로 if 를 사용하며
• <otherwise> 로 else 를 사용한다.

---

조건문을 사용해 검색하기 (like)

들어오는 문자열과 일치하는 내용을 검색하는 쿼리를 작성해보자!

컬럼은 id, name, email 이 있으며 이 중 하나를 선택해 검색하는 형태로 진행된다.

view

<form action="list.do" method="get">
	<select name="opt">
		<option value="id">아이디</option>
		<option value="name">이름</option>
		<option value="email">이메일</option>
	</select>
	<input type="text" name="keyword" placeholder="검색어를 입력하세요"/>
	<button>검색</button>
</form>

controller

@RequestMapping(value= {"/","/list.do"})
	public String home(Model model, @RequestParam Map<String, String> param) {

		List<MemberDTO> list = member_service.list(param);
		model.addAttribute("list", list);
		return "list";
	}

service

public List<MemberDTO> list(Map<String, String> param) {
	return member_dao.list(param);
}

dto

List<MemberDTO> list(Map<String, String> param);

mapper

<select id="list" resultType="kr.co.gudi.dto.MemberDTO" parameterType="map">
	SELECT id, name, email FROM member
	<where>
		<if test="keyword != null and !keyword.equals('') and opt == 'id'">
			id LIKE CONCAT('%',#{keyword},'%')
		</if>
		<if test="keyword != null and !keyword.equals('') and opt == 'name'">
			name LIKE CONCAT('%',#{keyword},'%')
		</if>
		<if test="keyword != null and !keyword.equals('') and opt == 'email'">
			email LIKE CONCAT('%',#{keyword},'%')
		</if>
	</where>
</select>

• 검색하지 않아도 list를 보여줘야 하기 때문에 <where> 를 사용해준다.
  ▶ 검색 조건이 없을 경우 select id, name, email from member 만 처리된다.
• 검색을 했을 경우 null이 아니고 and 공백도 아니며 and select 의 name 이 (id|name|email) 인 경우 아래 조건문을 실행한다.
  ▶ and를 사용할 때에는 && 가 아닌 AND 를 사용해줘야 한다.

---

❗❗ where

where 문은 사용해 줄 때에는 <where></where> 를 사용해주는 것이 좋다.

쿼리문 뒤에 where을 입력할 경우 조건이 없으면 에러가 발생하고 별도로 다 작성할 경우 코드 중복이 발생하기 때문에 <where>조건/반복 쿼리문</where> 형태로 작성해주는 것이 좋다!

---

✨ 반복문

이번에는 반복문을 사용해 여러개의 값 중 일치하는 것을 출력하는 코드를 작성해보자!
여러개의 값 중 일치하는 것을 뽑기 위해서는 OR 나 IN 을 사용해주면 된다.

📌 foreach

foreach 를 사용할 때에는 몇가지 옵션을 지정해줘야 한다.

• collection="컬렉션프레임워크" : 어떤 컬렉션 프레임워크를 사용하는지
• item="개별로 뽑아내는 값" : 개별로 뽑아낼 값의 이름
• separator="구분자" : 값과 값 사이에 들어갈 값이 있는지
• open="시작 시 넣을 값", colose="종료 시 넣을 값" : 시작 / 종료 시 앞 / 뒤에 넣을 값이 있는지

view

<div class="search_layer">
	<form action="multi.do" method="post">
		이름이 
			<ul>
				<li>
					<input type="text" name="userName"/>
					<input type="button" value="또는" onclick="add(this)"/>
				</li>
			</ul>
		인 회원 찾기
		<button>찾기</button>
	</form>
</div>
</body>
<script>
	function add(elem){
		$('ul').append('<li>'+$(elem).parent().html()+'</li>');
	}
</script>

• 버튼을 클릭할 경우 onclick 이벤트가 발생하면서 add() 함수가 실행된다.
  ▶ add() 함수는 button 의 부모 태그의 html 을 추가해준다.

controller

@PostMapping(value="/multi.do")
public String multi(Model model, @RequestParam List<String> userName) {
	
	member_service.multi(userName, model);
	return "list";
}

• 하나의 key로 여러 값이 들어오기 때문에 1) List 2) String[] 3) HttpServletRequest 로 받아줘야 한다.
  → 배열로 받으면 for 문으로 값을 빼줘야 확인이 가능하지만, List로 받으면 값을 빼지 않아도 logger로 확인할 수 있다.

• 서비스에서 값을 넘겨줄 것이기 때문에 model을 함께 넘겨준다.

service

public void multi(List<String> userName, Model model) {
	List<MemberDTO> list = member_dao.multi(userName);
	model.addAttribute("list", list);
}

dao

List<MemberDTO> multi(List<String> userName);

mapper

📌 OR

<select id="multi" resultType="kr.co.gudi.dto.MemberDTO" parameterType="list">
	SELECT id, name, email FROM member 
	<where> 
		<foreach collection="list" item="name" separator="OR">
			name = #{name}
		</foreach>
	</where>
</select> 

• 값 중간에 OR 를 넣어준다. (데이터가 n개가 들어와도 모두 처리가 가능하다.)
• parameterType 을 작성하면 들어온 값을 그대로 작성할 수 있다.

📌 IN

<select id="multi" resultType="kr.co.gudi.dto.MemberDTO" parameterType="list">
	SELECT id, name, email FROM member 
	<where>
		name IN
		<foreach collection="list" item="name" open="("  separator="," close=")">
			#{name}
		</foreach>
	</where>
</select>

• where 조건이 있을 때 name IN 은 무조건 들어가고,
• () 안에 들어갈 조건은 반복시켜준다.
• 앞 뒤에 ( 와 ) 가 들어가야 하기 때문에 open 과 close를 작성해준다.