Windows-Java/Tomcat連携/フレームワーク/Struts/連携/struts連携1/SQL記述xml - プログラミングの勉強をしよう@Wiki

Top > Windows-Java > Tomcat連携 > フレームワーク > Struts > 連携 > struts連携1 > SQL記述xml

SQL記述xml

書式

<sqlMap namespace="SQLMAP用のXMLの任意の値">
 
    別名定義（クラス等の別名を設定
    <typeAlias alias="別名" type="データ型(クラス）" />
 
    抽出情報設定(select)
    <select id="任意のID" resultClass="データ型">
        SQLステートメント
        select
            id
          , name
        from
            test1
        order by
            id
    </select>
 
    ステートメント設定(select、insert、update、delete)
    <statement id="任意のID" parameterClass="パラメータのデータ型(クラス含む)" resultClass="返却用のデータ型(クラス含む)">
        select
            count(*)
 
        from
            test2
 
    </statement>
 
</sqlMap>
 
 

SQLを記述したxml定義

dao/sqlmap/Test1.xml

<?xml version="1.0" encoding="Shift_JIS" standalone="no"?>
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd"
>
 
<sqlMap namespace="Test1">
 
    <typeAlias alias="test1" type="model.Test1" />
 
    <select id="getTest1List" resultClass="test1">
        select
            id
          , name
        from
            test1
        order by
            id
    </select>
 
</sqlMap>
 

dao/sqlmap/Test2.xml

<?xml version="1.0" encoding="Shift_JIS" standalone="no"?>
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd"
>
 
<sqlMap namespace="Test2">
 
    <typeAlias alias="test2" type="model.Test2" />
 
    <!-- resultMap -->
    <resultMap id="reserve-result" class="reserve">
        <result property="reserveId" column="reserve_id" />
        <result property="person" column="reserve_person" />
        <result property="content" column="content" />
        <result property="date" column="rDay" />
        <result property="start" column="start" />
        <result property="end" column="end" />
        <result property="room.roomId" column="room_id" />
        <result property="room.roomNo" column="room_no" />
        <result property="room.buildingId" column="room_buildingId" />
    </resultMap>
 
    <statement id="getReservation" parameterClass="int" resultMap="reserve-result">
        select
            reserve_id
          , reserve_person
          , reserve.roomid as room_id
          , room.roomno as room_no
          , rDay
          , start
          , end
          , content
          , room.buildingid as room_buildingId
 
        from
            test1, test2
 
        where
            reserve.reserve_id = #value#
            and reserve.roomid = room.roomId
 
    </statement>
 
    <statement id="CheckCnt" parameterClass="reserve" resultClass="int">
        select
            count(*)
 
        from
            test2
 
    </statement>
 
    <statement id="getReservationsForDate" parameterClass="java.util.Map" resultMap="reserve-result">
        select
            reserve_id
          , reserve_person
          , reserve.roomid as room_id
          , room.roomno as room_no
          , rDay
          , start
          , end
          , content
          , room.buildingid as room_buildingId
 
        from
            reserve, room
 
        where
            reserve.roomid = #roomid#
            and reserve.rDay = '2010-08-27'
            and reserve.roomid = room.roomId
 
    </statement>
 
    <statement id="insertReservation" parameterClass="reserve" resultClass="int">
        insert into reserve(
            reserve_person
          , roomid
          , rDay
          , start
          , end
          , content) values(
            #person#
          , #room.roomId#
          , #date#
          , #start#
          , #end#
          , #content#)
 
    </statement>
 
    <statement id="updateReservation" parameterClass="reserve" resultClass="int">
        update
            reserve
 
        set
            reserve_person = #person#
          , rDay = #date#
          , start = #start#
          , end = #end#
          , content = #content#
 
        where
            reserve_id = #reserveId#
 
    </statement>
 
    <statement id="deleteReservation" parameterClass="int" resultClass="int">
        delete from
            reserve
        where
            reserve_id = #value#
 
    </statement>
 
</sqlMap>
 
 

---