iBatis - FK 일때 맵핑 시키기

게시판 맵핑 xml 파일: SqlMapBoard.xml
로그인 맵핑 xml 파일: SqlMapMember.xml

MemberBoardiBatis.war

일때 게시판 데이터베이스 테이블에서의 USER_ID는 로그인 데이터베이스 테이블에서의 USER_ID의 FK 라고 한다면

SqlMapBoard에서의 USER_ID는 SqlMapMember의 USER_ID값을 가져와야 한다.

<SqlMapBoard.xml>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="kr.co.bit.board.dao.iBatis.Board">                                  <!-- namespace 설정 -->
<typeAlias alias="board" type="kr.co.bit.board.model.Board"/>

  <resultMap class="board" id="boardResult">
    <result column="SEQ" property="seq" />
    <result column="USER_ID"  property="member"  select="kr.co.bit.member.dao.iBatis.Member.selectMemberByUserId"/>
     <!-- kr.co.bit.member.dao.iBatis.Member 는 참조하려는  (SqlMapMember.xml) 의 네임스페이스고 그 네임스페이스의
           selectMemberByUserId라는 선언된 sql을 사용하여 member table에 있는 USER_ID를 가져와서 member(property="member")
           에 저장한다.     

select 문에서 USER_ID,USER_PW,EMAIL,USER_NAME 을 가져와서 member에 저장 하게 된다. 그래서 member.get~으로 데이터를 가져올수 있다.

      -->

    <result column="TITLE"  property="title" />
    <result column="CONTENTS"  property="contents" />
    <result column="REGIST_DATE" property="registDate" />
    <result column="READ_COUNT" property="readCount" />
  </resultMap>
 
  <resultMap class="board" id="boardAttachfileResult">
    <result column="SEQ" jdbcType="DECIMAL" property="seq" />
    <result column="USER_ID"  property="member"  select="kr.co.bit.member.dao.iBatis.Member.selectMemberByUserId"/>
    <result column="TITLE" jdbcType="VARCHAR" property="title" />
    <result column="CONTENTS" jdbcType="VARCHAR" property="contents" />
    <result column="REGIST_DATE" jdbcType="DATE" property="registDate" />
    <result column="READ_COUNT" jdbcType="DECIMAL" property="readCount" />
    <result column="SEQ" jdbcType="DECIMAL" property="attachfileList"  select="kr.co.bit.board.dao.iBatis.Board.selectListByBoardSeq"/>
   
  </resultMap>

  <select id="selectListCount" parameterClass="board" resultClass="int">
      select
          count(0)
      from
          TB_MEMBER_BOARD
  </select>
 
  <select id="selectBoard" parameterClass="int" resultMap="boardResult">
      select
          SEQ, USER_ID, TITLE, CONTENTS, REGIST_DATE, READ_COUNT
      from
          TB_MEMBER_BOARD
      where
          SEQ = #paramSeq#
  </select>
 
  <statement id="selectList" parameterClass="board" resultMap="boardResult">
        <![CDATA[
        select
            SEQ, USER_ID, TITLE, CONTENTS, REGIST_DATE, READ_COUNT
        from
            (
            select *
            from
                ( select * from TB_MEMBER_BOARD order by SEQ ASC )
            where rownum <= #startRow#
            order by SEQ desc
            )
        where rownum <= #countPerPage#
        ]]>
  </statement>
 
  <update id="selectBoardCount" parameterClass="int">
      update
          TB_MEMBER_BOARD
      set
          READ_COUNT = READ_COUNT + 1 where SEQ = #seq#
  </update>
 
  <insert id="insert" parameterClass="board">
      <selectKey keyProperty="seq" resultClass="long">
          select SEQ_TB_MEMBER_BOARD.NEXTVAL from DUAL;
      </selectKey>
      insert into TB_MEMBER_BOARD
          (SEQ, TITLE, CONTENTS, USER_ID, READ_COUNT, REGIST_DATE)
      values
          (#seq#, #title#, #contents#, #member.userId#, 0, sysdate)
  </insert>
 
</sqlMap>


<SqlMapMember.xml>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="kr.co.bit.member.dao.iBatis.Member" > 
<!-- namespace 위에서 이 이름으로 접근하여 selectMemberByUserId 으로 sql문에 접근한다-->

  <typeAlias alias="member" type="kr.co.bit.member.model.Member"/>
   
  <resultMap id="memberResult" class="member" >
    <result column="USER_ID" property="userId" />
    <result column="USER_PW" property="userPw"/>
    <result column="USER_NAME" property="userName" />
    <result column="EMAIL" property="email"/>
   <!--   <result column="REGIST_DATE" property="registDate" jdbcType="DATE" />-->
  </resultMap>
 
     <select id="selectMemberByUserId" parameterClass="String" resultMap="memberResult">
         select
             USER_ID, USER_PW, USER_NAME, EMAIL
         from
             MEMBER_BOARD
         where
             USER_ID = #userId#
     </select>
 
     <insert id="insertMember" parameterClass="member">
         insert into MEMBER_BOARD
             (USER_ID, USER_PW, USER_NAME, EMAIL, REGIST_DATE)
         values
             (#userId#, #userPw#, #userName#, #email#, sysdate)
     </insert>
    
</sqlMap>