[ibatis] sql문 예제1 select

[ibatis] sql문 예제1 select

<select id="AuthGrpMenu.selectList" parameterClass="java.util.Map" resultClass="authGrpMenu" >

<![CDATA[

SELECT 

ROW_NUMBER() OVER(ORDER BY tbl2.DISP_ORDER ASC) AS rowNo,

tbl2.MENU_ID AS menuId,

tbl2.MENU_NM AS menuNm,

tbl2.DISP_ORDER AS dispOrder,

tbl2.LVL AS lvl,

tbl2.UPPER_MENU_ID AS upperMenuId,

tbl2.MENU_URL AS menuUrl,

tbl2.USE_YN AS useYn,

tbl1.AUTH_GRP_CD AS authGrpCd,

tbl1.REG_DT AS regDt,

tbl1.REG_ID AS regId,

tbl1.MOD_DT AS modDt,

tbl1.MOD_ID AS modId,

(CASE WHEN tbl1.AUTH_GRP_CD > 0 THEN 'Y' ELSE 'N' END) AS authYn

 FROM (

  SELECT * 

   FROM dbo.AUTH_GRP_MENU

]]>

<dynamic prepend="WHERE">

<isNotEmpty property="sAuthGrpCd" prepend="AND">

<![CDATA[

(AUTH_GRP_CD = #sAuthGrpCd#)

]]>

</isNotEmpty>

</dynamic>

<![CDATA[

  ) tbl1

RIGHT OUTER JOIN dbo.MENU_TREE tbl2

ON (tbl1.MENU_ID = tbl2.MENU_ID)

]]>

<dynamic prepend="WHERE">

<isNotEmpty property="sUpperMenuId" prepend="AND">

<![CDATA[

(tbl2.UPPER_MENU_ID = #sUpperMenuId#)

]]>

</isNotEmpty>

<isNotEmpty property="likeUpperMenuId" prepend="AND">

<![CDATA[

(tbl2.UPPER_MENU_ID LIKE #likeUpperMenuId#)

]]>

</isNotEmpty>

<isNotEmpty property="sLvl" prepend="AND">

<![CDATA[

(tbl2.LVL = #sLvl#)

]]>

</isNotEmpty>

<isNotEmpty property="sUseYn" prepend="AND">

<![CDATA[

(tbl2.USE_YN = #sUseYn#)

]]>

</isNotEmpty>

<isNotEmpty property="sAuthYn" prepend="AND">

<![CDATA[

(tbl1.AUTH_GRP_CD > 0)

]]>

</isNotEmpty>

</dynamic>

</select>

--------------------------------------------------------------------------------------------------------

<select id="MenuTree.selectCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">

<![CDATA[

SELECT 

COUNT(*) AS rowCountAll

 FROM dbo.MENU_TREE tbl1

]]>

<dynamic prepend="WHERE">

<isNotEmpty property="sMenuId" prepend="AND">

<![CDATA[

(tbl1.MENU_ID = #sMenuId#)

]]>

</isNotEmpty>

<isNotEmpty property="sLvl" prepend="AND">

<![CDATA[

(tbl1.LVL = #sLvl#)

]]>

</isNotEmpty>

<isNotEmpty property="sUpperMenuId" prepend="AND">

<![CDATA[

(tbl1.UPPER_MENU_ID = #sUpperMenuId#)

]]>

</isNotEmpty>

<isNotEmpty property="sUseYn" prepend="AND">

<![CDATA[

(tbl1.USE_YN = #sUseYn#)

]]>

</isNotEmpty>

</dynamic>

</select>

--------------------------------------------------------------------------------------------------------

<select id="PopupNotice.selectList" parameterClass="java.util.Map" resultClass="popupNotice" >

<![CDATA[

SELECT 

ttbl1.rowNo,

ttbl1.authGrpCd,

ttbl1.adminId,

ttbl1.seqNo,

ttbl1.systemGbn,

ttbl1.msgGbn,

ttbl1.noticeContents,

ttbl1.confirmYn,

ttbl1.regDt,

ttbl1.regId,

ttbl1.modDt,

ttbl1.modId

FROM (

SELECT 

ROW_NUMBER() OVER(ORDER BY tbl1.AUTH_GRP_CD ASC, ADMIN_ID ASC, SEQ_NO DESC) AS rowNo,

tbl1.AUTH_GRP_CD AS authGrpCd,

tbl1.ADMIN_ID AS adminId,

tbl1.SEQ_NO AS seqNo,

tbl1.SYSTEM_GBN AS systemGbn,

tbl1.MSG_GBN AS msgGbn,

tbl1.NOTICE_CONTENTS AS noticeContents,

tbl1.CONFIRM_YN AS confirmYn,

tbl1.REG_DT AS regDt,

tbl1.REG_ID AS regId,

tbl1.MOD_DT AS modDt,

tbl1.MOD_ID AS modId

 FROM dbo.POPUP_NOTICE tbl1

]]>

<dynamic prepend="WHERE">

<isNotEmpty property="sAuthGrpCd" prepend="AND">

<![CDATA[

(tbl1.AUTH_GRP_CD = #sAuthGrpCd#)

]]>

</isNotEmpty>

<isNotEmpty property="sAdminId" prepend="AND">

<![CDATA[

(tbl1.ADMIN_ID = #sAdminId#)

]]>

</isNotEmpty>

<isNotEmpty property="sConfirmYn" prepend="AND">

<![CDATA[

(tbl1.CONFIRM_YN = #sConfirmYn#)

]]>

</isNotEmpty>

</dynamic>

<![CDATA[

) ttbl1

]]>

<dynamic prepend="WHERE">

<isNotEmpty prepend="AND" property="pMinRowNo">

<![CDATA[

(ttbl1.rowNo >= #pMinRowNo#)

]]>

</isNotEmpty>

<isNotEmpty prepend="AND" property="pMaxRowNo">

<![CDATA[

(ttbl1.rowNo <= #pMaxRowNo#)

]]>

</isNotEmpty>

</dynamic>

</select>

---------------------------------------------------------------------------------------------------------

<select id="ZipCd.select.any" parameterClass="java.util.Map" resultClass="ZipCd.object">

<![CDATA[

SELECT 

$fields$

FROM $table$

]]>

<dynamic prepend="WHERE">

<isNotEmpty prepend="AND" property="ZIP_CD">

<![CDATA[

([ZIP_CD] = #zipCd#)

]]>

</isNotEmpty>

</dynamic>

<dynamic prepend="GROUP BY">

<isNotEmpty prepend="AND" property="group">

<![CDATA[

$group$

]]>

</isNotEmpty>

</dynamic>

<dynamic prepend="ORDER BY">

<isNotEmpty prepend="AND" property="sort">

<![CDATA[

$sort$

]]>

</isNotEmpty>

</dynamic>

  </select>

---------------------------------------------------------------------------------------------------------

<select id="Haksa.selectListHaksaUser" parameterClass="java.util.Map" resultClass="haksaUser" >

<![CDATA[

SELECT ROWNUM rowNo, tbl1.*

 FROM (

SELECT  

aaa as aaaaa

FROM VIEW_LIB_HAKJUK1

  WHERE JAEHAKCD != '99'

]]>

<isNotEmpty property="sCondition1_3" prepend="AND">

<![CDATA[

($sCondition1_1$ $sCondition1_2$ #sCondition1_3#)

]]>

</isNotEmpty>

<isEqual property="sCondition1_4" compareValue="AND">

<isNotEmpty property="sCondition2_3" prepend="AND">

<![CDATA[

($sCondition2_1$ $sCondition2_2$ #sCondition2_3#)

]]>

</isNotEmpty>

</isEqual>

<isEqual property="sCondition1_4" compareValue="OR">

<isNotEmpty property="sCondition2_3" prepend="OR">

<![CDATA[

($sCondition2_1$ $sCondition2_2$ #sCondition2_3#)

]]>

</isNotEmpty>

</isEqual>

<isEqual property="sCondition2_4" compareValue="AND">

<isNotEmpty property="sCondition3_3" prepend="AND">

<![CDATA[

($sCondition3_1$ $sCondition3_2$ #sCondition3_3#)

]]>

</isNotEmpty>

</isEqual>

<isEqual property="sCondition2_4" compareValue="OR">

<isNotEmpty property="sCondition3_3" prepend="OR">

<![CDATA[

($sCondition3_1$ $sCondition3_2$ #sCondition3_3#)

]]>

</isNotEmpty>

</isEqual>

<isNotEmpty property="pMaxRowCount" prepend="AND">

<![CDATA[

(ROWNUM <= #pMaxRowCount#)

]]>

</isNotEmpty>

<isEqual property="pSortCol1Nm" compareProperty="pSortCol2Nm">

<![CDATA[

ORDER BY $pSortCol1Nm$ $pSortTypeNm$

]]>

</isEqual>

<isNotEqual property="pSortCol1Nm" compareProperty="pSortCol2Nm">

<![CDATA[

ORDER BY $pSortCol1Nm$ $pSortTypeNm$, $pSortCol2Nm$ $pSortTypeNm$

]]>

</isNotEqual>

<![CDATA[

) tbl1

]]>

</select>

---------------------------------------------------------------------------------------------------------

[ibatis] sql문 예제1 select