<?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">

  <typeAlias alias="product" type="com.ibatis.jpetstore.domain.Product"/>

  <cacheModel id="productCache" type="LRU">
    <flushInterval hours="24"/>
    <property name="size" value="100"/>
  </cacheModel>

  <select id="getProduct" resultClass="product" parameterClass="string" cacheModel="productCache">
    SELECT
      PRODUCTID,
      NAME,
      DESCN as description,
      CATEGORY as categoryId
    FROM PRODUCT
    WHERE PRODUCTID = #productId#
  </select>

  <select id="getProductListByCategory" resultClass="product" parameterClass="string" cacheModel="productCache">
    SELECT
      PRODUCTID,
      NAME,
      DESCN as description,
      CATEGORY as categoryId
    FROM PRODUCT
    WHERE CATEGORY = #value#
  </select>

  <select id="searchProductList" resultClass="product" >
    select
      PRODUCTID,
      NAME,
      DESCN as description,
      CATEGORY as categoryId
    from PRODUCT
    <dynamic prepend="WHERE">
      <iterate property="keywordList" open="" close="" conjunction="OR">
        lower(name) like #keywordList[]# OR lower(category) like #keywordList[]# OR lower(descn) like #keywordList[]#
      </iterate>
    </dynamic>
  </select>

</sqlMap>
