[03] SQL문 응용태그 : 동적 SQL, <association>, <collection>

SQL문 심화활용

Mybatis에서는 sql문과 java간의 상호작용을 돕는 태그들이 많다.

그중 동적SQL에 활용되는 태그들과
HasOne관계에 쓸 수 있는 <association>태그,

HasMany관계에 쓸 수 있는 <collection> 태그를 공부해보자.

---

 

 

---

동적SQL

Mybatis는 동적 sql을 만들수 있는 태그를 지원한다.

검색기능에 검색조건이 있다면 동적 sql을 유용하게 활용할 수 있다.

---

동적SQL

• 공식사이트 - 왼쪽 메뉴 [동적SQL]
  >>동적 SQL에 사용 가능한 태그들에 대한 설명이 나와있다.
• <if>
  - 조건을 만족시킬경우 안의 구문을 쿼리에 포함시킨다.
  - 다중 if문은 if구문을 여러개 사용하면 된다.
  - test 속성 :
    " " 안에 조건을 입력한다.
    이때 습관적으로 EL구문을 쓰지 않도록 유의한다.
    " " 안에서는 마치 자바언어를 쓰듯 키값과 연산을 그냥 입력할 수 있다.
• <choose>
  <when> <otherwise>
  - if-else의 역할을 하며, <choose>태그는 <when>태그와 <otherwise>태그를 감싼다.
  - test 속성 : <when>태그 안에 기술한다.
• <where>
  - 기존 쿼리의 WHERE절을 동적으로 구현할 때 쓰인다.
    구문이 WHERE로 시작하도록 만든다.
  - 내부에 <if>태그 등을 둬서 쿼리문을 제어한다.
  - 만약 완성된 sql문이 'AND'나 'OR'로 시작할경우 제거해준다.
• <set>
  - UPDATE 하고자 하는 칼럼을 동적으로 포함시키기 위해 사용한다.
    구문이 SET으로 시작하도록 만든다.
  - 내부에 <if>태그 등을 둬서 쿼리문을 제어한다.
  - 만약 완성된 sql문이 ','로 끝날경우 제거해준다.
• <trim>
  - 쿼리 구문의 특정 부분을 없앨 때 쓰인다.
    <where>태그와 <set> 태그의 역할을 대체할 수 있다.
  - prefix 속성 : 
    태그안의 내용이 완성될 때 처음 시작할 단어를 지정한다.
  - prefixOverrides 속성 : 
    태그안의 내용이 완성될 때 제거해야할 접두사를 지정한다.
    prefix로 "where"을 줬을때 첫 AND나 OR을 제거하는 용도로 활용된다.
    (<where>태그 대체)
  - suffixOverrides 속성 : 
    태그안의 내용이 완성될때 마지막으로 제거해야할 접미사를 지정한다.
    prefix로 "set"을 줬을때 마지막  ','를 제거하는 용도로 활용된다.
    (<set>태그 대체)
• <foreach>
  - collection에 대한 반복처리를 제공한다.
  - item 속성 : 반복될 때 접근 가능한 객체 변수
  - index 속성 : 반복되는 횟수를 가리키는 변수 , 0 부터 순차적으로 증가
  - collection 속성 : 전달받은 인자로 반복에 쓰이는 Collection 객체 , List 나 Array 형태만 가능
  - open 속성 : 해당 구문이 시작될 때 삽입할 문자열
  - close 속성 : 해당 구문이 종료될 때 삽입할 문자열
  - separator 속성 : 반복되는 객체를 나열 할 때 사용할 구분자
• <bind>
  - 특정 문장을 미리 생성하여 쿼리에 적용해야 할 경우 사용한다
    변수를 선언하는것과 비슷한 원리이다.
  - name 속성 :
  키값을 입력한다. 
  sql 구문에서 사용할 때는 #{키값}으로 접근한다.
  - value 속성 : 
  입력할 구문을 넣어준다.
  이때, ‘_parameter’를 통해 전달받은 객체에 접근할 수 있으며 getter메소드로 필드값을 꺼낸다.

<!-- where 구문 예시 -->
SELECT *
FROM BOARD
<where>
    <if test="writer !=null">
        WRITER = #{writer}
    </if>
    <if test="title != null">
        AND TITLE = #{title}
    </if>
</where>

<!-- trim으로 구현한 where -->
SELECT *
FROM BOARD
<trim prefix="WHERE" prefixOverrides="AND|OR">
    <if test="writer !=null">
        writer = #{writer}
    </if>
    <if test="title != null">
        AND title = #{title}
    </if>
</trim>

<!-- set 구문 예시 -->
UPDATE MEMBER
<set>
    <if test="userName != null">
        USER_NAME = #{userName},
    </if>
    <if test="userPwd != null">
        USER_PWD = #{userPwd},
    </if>
</set>

<!-- bind 구문 예시 -->
<bind name="pattern" value="%'+_parameter.getTitle()+%'" />
SELECT *
FROM BOARD
WHERE TITLE LIKE #{pattern}

 

 

동적 sql 예시 : 검색기능 설정

• 검색조건으로 제목, 내용, 작성자가 있는 경우 사용자의 선택값에 따라 동적으로 쿼리를 완성해야한다.
  이때, <if>나 <choose>를 활용할 수 있다.

<!-- 검색페이지에서 페이징처리를 하기 위해 총 게시글 수를 구하는 쿼리문 -->
<select id="selectSearchCount" resultType="_int">
    select 
           count(*)
      from board b
      join member on (board_writer=user_no)
     where b.status = 'Y' 
       <if test="condition=='writer'">
       and user_id
       </if>
       <if test="condition=='title'">
       and board_title
       </if>
       <if test="condition=='content'">
       and board_content 
       </if>
           like '%' || #{keyword} || '%'	
</select>

  

 

---

쿼리문 심화1 : <association>

mapper.xml 파일의 <resultMap>태그에 쓸 수 있는 <association>태그가 있다.

1:1 관계(HasOne관계)에 있는 두 테이블에서 데이터를 가져와야 할때,

각각 sql문을 만들지 않고 한번에 join해서 가져올 수 있도록 하는 태그이다.

---

HasOne 관계의 sql문 짜기

• 하나의 게시글에는 한명의 작성자가 존재한다.
  Board테이블과 Member테이블에서 각각의 정보를 가져오는 대신 한번에 join으로 가져오는 쿼리문을 짜본다.

select
       board_no  --Board
     , board_title  --Board
     , board_content  --Board
     , user_id  --Member
     , user_name  --Member
     , gender  --Member
  from board
  join member on (board_writer=user_no)
 where board_no = 글번호

 

 

vo 클래스(Board) 수정

• Member객체를 담을 수 있는 필드를 추가한다.
  (getter setter 메소드도 추가)

	private Member mem;
	...
	public Member getMem() { return mem; }
	public void setMem(Member mem) { this.mem = mem; }

 

 

mapper.xml 작성하기

• 상단의 sql문을 가지고 있는 select태그를 작성 한 후, resultMap 값으로 boardResultSet 을 준다.
• boardResultSet의 resultMap 작성시 <association>태그를 활용한다.
• 이때 활용가능한 방법은 2가지가 있다.
  1)Board 필드를 담는 resultMap안에 Member 필드를 담는다.
  >> 이때는 <association>태그 안에 javaType 속성을 기재해준다 (속성값=Member의 풀클래스명or별칭) 
  2)Board와 Member의 resultMap을 따로 만든다
  >> 이때는 <association>태그 안에 resultMap 속성을 기재해준다 (속성값=Member의 id) 

<!-- 1:1관계(has one)에서는 association사용해서 매핑한다 -->
<!-- 1) association 방법1 -->
<resultMap id="boardResultSet" type="Board">
    <id column="board_no" property="boardNo" />
    <result column="board_title" property="boardTitle" />
    <result column="board_content" property="boardContent" />
    <result column="count" property="count" />
    <association property="mem" javaType="Member">
        <result column="user_id" property="userId" />
        <result column="user_name" property="userName" />
        <result column="gender" property="gender" />
    </association>
</resultMap>
<!-- 2) association 방법2, 기존에 만들어둔 map이 있을경우 -->
<resultMap id="boardResultSet" type="Board">
    <id column="board_no" property="boardNo" />
    <result column="board_title" property="boardTitle" />
    <result column="board_content" property="boardContent" />
    <result column="count" property="count" />
    <association property="mem" resultMap="memberResultSet" />
</resultMap>
<resultMap id="memberResultSet" type="Member">
    <result column="user_id" property="userId" />
    <result column="user_name" property="userName" />
    <result column="gender" property="gender" />
</resultMap>

 

 

화면에 뿌릴때는?

• b.mem이 하나의 객체로 반환되기 때문에 다시금 필드명을 붙여줌으로써 데이터에 접근할 수 있다.

	게시글제목 : ${ b.boardTitle }
	게시글내용 : ${ b.boardContent }
	
	작성자아이디 : ${ b.mem.userId }
	작성자이름 : ${ b.mem.userName }
	작성자성별 : ${ b.mem.gender }

 

 

---

쿼리문 심화2 : <collection> 

mapper.xml 파일의 <resultMap>태그에 쓸 수 있는 <collection>태그가 있다.
1:N 관계(HasMany관계)에 있는 두 테이블에서 데이터를 가져와야 할때,
각각 sql문을 만들지 않고 한번에 join해서 가져올 수 있도록 하는 태그이다.

---

HasMany 관계의 sql문 짜기

• 하나의 게시글에는 여러개의 댓글이 존재할 수 있다.
  Board테이블과 Reply 테이블에서 각각의 정보를 가져오는 대신 한번에 join으로 가져오는 쿼리문을 짜본다.
• join할때 유의할점은 댓글이 없을경우 조회가 안된다는 점이다 
  >> 외부조인을 해야한다.
• sql실행시 결과에서 Board의 정보는 댓글의 갯수만큼 중복되어 나오는 것이 정상이다.
  댓글이 없다면 댓글컬럼의 데이터는 null인 상태로 1줄만 조회될 것이다.

select
       board_no  --Board
     , board_title  --Board
     , board_content  --Board
     , reply_no  --Reply
     , reply_content  --Reply
     , r.create_date  --Reply
  from board
  left join reply r on (board_no=ref_bno)
 where board_no= 게시글번호

sql을 실행했을 때의 결과물

 

 

vo 클래스(Board)  수정

• 여러개의 댓글을 담을 수 있도록 ArrayList필드를 추가해준다.
  (getter setter 메소드도 추가)

    private ArrayList<Reply> replyList; 
    ...
    public ArrayList<Reply> getReplyList() { return replyList;}
    public void setReplyList(ArrayList<Reply> replyList) { this.replyList = replyList; }

 

 

mapper.xml 작성하기

• 상단의 sql문을 가지고 있는 select태그를 작성 한 후, resultMap 값으로 boardResultSet 을 준다.
• boardResultSet의 resultMap 작성시 <collection>태그를 활용한다.
• 이때 활용가능한 방법은 2가지가 있다.
  1)Board 필드를 담는 resultMap안에 Reply필드를 담는다.
  >> 이때는 <collection>태그 안에 ofType 속성을 기재해준다 (속성값=ArrayList의 제네릭타입) 
  2)Board와 Reply의 resultMap을 따로 만든다
  >> 이때는 <collection>태그 안에 resultMap 속성을 기재해준다 (속성값=Reply의 id)

<!-- 2. 1:n 관계에서는 collection 사용 -->
<!-- 2-1) collection 방법1 -->
<resultMap type="Board" id="boardResultSet">
    <id column="board_no" property="boardNo" />
    <result column="board_title" property="boardTitle" />
    <result column="board_content" property="boardContent" />
    <collection property="replyList" ofType="Reply">
        <id column="reply_no" property="replyNo" />
        <result column="reply_content" property="replyContent" />
        <result column="create_date" property="createDate" />
    </collection>
</resultMap>
<!-- 2-2) collection 방법2 -->
<resultMap type="Board" id="boardResultSet">
    <id column="board_no" property="boardNo" />
    <result column="board_title" property="boardTitle" />
    <result column="board_content" property="boardContent" />
    <collection property="replyList" resultMap="replyResultSet">
    </collection>
</resultMap>
<resultMap type="Reply" id="replyResultSet">
    <id column="reply_no" property="replyNo" />
    <result column="reply_content" property="replyContent" />
    <result column="create_date" property="createDate" />
</resultMap>

 

 

화면에 뿌릴때는?

• b.replyList는 ArrayList로 반환되기 때문에 반복문을 활용해서 데이터를 뿌릴 수 있다.

	게시글제목 : ${ b.boardTitle }
	게시글내용 : ${ b.boardContent }
	
	<c:forEach var="r" items="${ b.replyList }">
		댓글번호 : ${ r.replyNo }
		댓글내용 : ${ r.replyContent }
		작설일 : ${ r.createDate }
	</c:forEach>

 

 

만약 한개의 게시글이 아닌 전체 게시글 조회라면?

• sql문에서 where절을 삭제해서 전체 게시글을 조회한다고 가정해보자
• Dao : selectList()메소드를 사용한다.
• jsp : 반복문을 중첩해서 사용하면 된다.

<c:forEach var="b" items="${ list }">		
    <div class="board">
        게시글번호 : ${ b.boardNo } <br>
        게시글제목 : ${ b.boardTitle } <br>
        게시글내용 : ${ b.boardContent } <br>

        <c:forEach var="r" items="${ b.replyList }">
            <div class="reply">
                댓글번호 : ${ r.replyNo }<br>
                댓글내용 : ${ r.replyContent }<br>
                작성일 : ${ r.createDate } <br>
            </div>				
        </c:forEach>
    </div>
</c:forEach>

 

 

 

 

이미지 출처 instagram @teaaalexis