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

  <typeAlias alias="item" type="com.ibatis.jpetstore.domain.Item"/>

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

  <cacheModel id="quantityCache" type="LRU">
    <flushInterval hours="24"/>
    <flushOnExecute statement="updateInventoryQuantity"/>
    <property name="size" value="100"/>
  </cacheModel>

  <select id="getItemListByProduct" resultClass="item" parameterClass="string" cacheModel="itemCache">
    SELECT
      ITEMID,
      LISTPRICE,
      UNITCOST,
      SUPPLIER AS supplierId,
      I.PRODUCTID AS "product.productId",
      NAME AS "product.name",
      DESCN AS "product.description",
      CATEGORY AS "product.categoryId",
      STATUS,
      ATTR1 AS attribute1,
      ATTR2 AS attribute2,
      ATTR3 AS attribute3,
      ATTR4 AS attribute4,
      ATTR5 AS attribute5
    FROM ITEM I, PRODUCT P
    WHERE P.PRODUCTID = I.PRODUCTID
    AND I.PRODUCTID = #value#
  </select>

  <select id="getItem" resultClass="item" parameterClass="string" cacheModel="quantityCache">
    select
      ITEMID,
      LISTPRICE,
      UNITCOST,
      SUPPLIER AS supplierId,
      I.PRODUCTID AS "product.productId",
      NAME AS "product.name",
      DESCN AS "product.description",
      CATEGORY AS "product.categoryId",
      STATUS,
      ATTR1 AS attribute1,
      ATTR2 AS attribute2,
      ATTR3 AS attribute3,
      ATTR4 AS attribute4,
      ATTR5 AS attribute5,
      QTY AS quantity
    from ITEM I, INVENTORY V, PRODUCT P
    where P.PRODUCTID = I.PRODUCTID
      and I.ITEMID = V.ITEMID
      and I.ITEMID = #value#
  </select>

  <select id="getInventoryQuantity" resultClass="int" parameterClass="string">
    SELECT QTY AS value
    FROM INVENTORY
    WHERE ITEMID = #itemId#
  </select>

  <update id="updateInventoryQuantity" parameterClass="map">
    UPDATE INVENTORY SET
      QTY = QTY - #increment#
    WHERE ITEMID = #itemId#
  </update>

</sqlMap>
