wiki:ibatissqlmap

sqlmap.xml셋팅

<typeAlias> 태그

  • 클래스 풀 네임을 한단어로 대체한다.
<typeAlias alias="product" type="kr.shop.model.domain.Product"/>
<typeAlias alias="productImg" type="kr.shop.model.domain.ProductImage"/>

<resultMap> 태그

  • sql 컬럼과 자바빈의 퍼로퍼티를 맵핑시킨다.
 <resultMap id="ProductResult" class="product">
    <result property="code" column="code"/>
    <result property="season" column="season"/>
    <result property="category" column="category"/>
    <result property="name" column="name"/>
    <result property="price" column="price"/>
    <result property="productImg" column="code" select="getProductImage" />

 </resultMap>
  • select 속성은 속성값에 해당하는 select id 를 수행한다.
  • column 속성은 select 속성의 파라메터로 사용
  • 결과는 property 속성 값에 맵핑한다.

<select> 태그

  • sql의 select 문장을 수행한다.
  • 결과값은 resultMap 속성값에 해당하는 <resultMap>에 맵핑된다.
   <select id="selectAllProducts" resultMap="ProductResult">
    select * from product
  </select>

  • resultClass 속성값에 해당하는 자바빈즈의 퍼로퍼티에 맵핑된다.
  • parameterClass select 구문의 파라메터 타입을 정의한다.
  <select id="getProductImage" resultClass="productImg" parameterClass="int">
   select * from product_img where code=#code#
  </select>

<dynamic> 태그

  • sqlmap의 동적태그
  • <idEqual> 태그는 property 값과 compareValue 값이 동일검사 후 true일 경우 태그내 문장을 수행한다.
  <select id="selectAllProductsByKeyWord" resultMap="keyWordResultMap" parameterClass="map">
    select * from product
    <dynamic prepend="where">
     <isEqual property="keyField" compareValue="name" >
      name like #keyValue#
     </isEqual>
    </dynamic>
  </select>

<insert> 태그

  • sql 의 insert 문장을 수행한다.
  <insert id="insertProductImg" parameterClass="productImg">
 insert into product_img(num, code, name,tempname, regdate,imgtype)
    values(product_img_seq.NextVal,#code#, #name#,#tempname#,sysdate,#imgtype#)   
  </insert>

  • insert 하위 태그로 selectKey 태그는 select 문장 수행 결과를 결과로 다시 반환한다.

<update> 태그

  • sql의 update 문장을 수행한다.
  <update id="updateAccount" parameterClass="Account">
    update ACCOUNT set
      ACC_FIRST_NAME = #firstName#,
      ACC_LAST_NAME = #lastName#,
      ACC_EMAIL = #emailAddress#
    where
      ACC_ID = #id#
  </update>

<delete> 태그

  • sql의 delete 문장을 수행한다.
  <delete id="deleteAccountById" parameterClass="int">
    delete from ACCOUNT where ACC_ID = #id#
  </delete>

Product.xml 전체 내용 (sqlmap 파일 예제)

<?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="Product">
  <!-- Use type aliases to avoid typing the full classname every time. -->
  <typeAlias alias="product" type="kr.shop.model.domain.Product"/>
  <typeAlias alias="productImg" type="kr.shop.model.domain.ProductImage"/>

  <!-- Result maps describe the mapping between the columns returned
       from a query, and the class properties.  A result map isn't
       necessary if the columns (or aliases) match to the properties
       exactly. -->
  <resultMap id="ProductResult" class="product">
    <result property="code" column="code"/>
    <result property="season" column="season"/>
    <result property="category" column="category"/>
    <result property="name" column="name"/>
    <result property="price" column="price"/>
 <result property="productImg" column="code" select="getProductImage" />
  </resultMap>
 
  <resultMap id="keyWordResultMap" class="product">
 <result property="code" column="code"/>
    <result property="season" column="season"/>
    <result property="category" column="category"/>
    <result property="name" column="name"/>
    <result property="price" column="price"/>
 <result property="productImg" column="code" select="getProductImage" />
</resultMap>
 
  <parameterMap class="map" id="keyWordMap">
   <parameter property="keyField"  jdbcType="NUMERIC"/>
   <parameter property="keyValue"/>
  </parameterMap>
 
  <!-- Select with no parameters using the result map for Account class. -->
  <select id="selectAllProducts" resultMap="ProductResult">
    select * from product
  </select>
 
  <!--<select id="selectAllProductsByKeyWord" resultMap="keyWordResultMap" parameterMap="keyWordMap">
    select * from product where ? like ?
  </select>
 
  -->
  <select id="selectAllProductsByKeyWord" resultMap="keyWordResultMap" parameterClass="map">
    select * from product
    <dynamic prepend="where">
     <isEqual property="keyField" compareValue="name" >
      name like #keyValue#
     </isEqual>
    </dynamic>
  </select>
  <!-- A simpler select example without the result map.  Note the
       aliases to match the properties of the target result class. -->
  <select id="selectProductByCode" parameterClass="int" resultClass="product">
    select
    *
    from product
    where code = #code#
  </select>
 
  <select id="getProductImage" resultClass="productImg" parameterClass="int">
   select * from product_img where code=#code#
  </select>
  
  <!-- Insert example, using the Account parameter class -->
  <insert id="insertProduct" parameterClass="product">
    insert into
     product(code,season,category,name,quantity,color,company,inventory,banpum,designer, enterdate,admin,price,contents)
  values(product_seq.NextVal,#season#,#category#,#name#,#quantity#,#color#,#company#,#inventory#,#banpum#,#designer#,sysdate,#admin#,#price#,#contents#)
  <selectKey resultClass="int">
   select max(code) code from product
  </selectKey>
  
  </insert>
 
  <insert id="insertProductImg" parameterClass="productImg">
 insert into product_img(num, code, name,tempname, regdate,imgtype)
    values(product_img_seq.NextVal,#code#, #name#,#tempname#,sysdate,#imgtype#)   
  </insert>
  <!-- Update example, using the Account parameter class --><!--
  <update id="updateAccount" parameterClass="Account">
    update ACCOUNT set
      ACC_FIRST_NAME = #firstName#,
      ACC_LAST_NAME = #lastName#,
      ACC_EMAIL = #emailAddress#
    where
      ACC_ID = #id#
  </update>
  --><!-- Delete example, using an integer as the parameter class --><!--
  <delete id="deleteAccountById" parameterClass="int">
    delete from ACCOUNT where ACC_ID = #id#
  </delete>
--></sqlMap>