IBATIS SQLMAP模板

来源：互联网发布：网络电视ssid怎么设置编辑：程序博客网时间：2024/05/20 14:19

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

<typeAlias alias="deviceQueryVO"
type="com.boco.itms.itil.utcs.vo.DeviceQueryVO" />

<insert id="insertDevice" parameterClass="itmsItilDevice">
   <![CDATA[
   INSERT INTO
    ITMS_ITIL_UTC_DEVICE
    (ORG_ID,DEVICENAME,DEVICEID,DEVICETYPEID,DEVICESUBTYPEID,CATEGORYID,
    COMMODEID,COMPACTID,INSTALLADDRESS,CONSTRUCT,INSTALL_DATE,
    MANUFACTURE_DATE,SERIALNUMBER,LONGITUDEVALUE,LATITUDEVALUE,DELETEFLAG,STATUS)
   VALUES
    (#orgId#,#deviceName#,#deviceId#,#deviceTypeId#,#deviceSubTypeId#,#categoryId#,
    #comModeId#,#comPactId#,#installAddress#,#construct#,#installDate#,
    #manufactureDate#,#serialNumber#,#longitude#,#latitude#,#deleteFlag#,#status#)
   ]]>
</insert>

<update id="updateDevice" parameterClass="itmsItilDevice">
   <![CDATA[
   UPDATE
    ITMS_ITIL_UTC_DEVICE a
   SET
    ORG_ID = #orgId#,DEVICENAME = #deviceName#,DEVICEID = #deviceId#,DEVICETYPEID = #deviceTypeId#,
    DEVICESUBTYPEID = #deviceSubTypeId#,CATEGORYID = #categoryId#,
    COMMODEID = #comModeId#,COMPACTID = #comPactId#,INSTALLADDRESS = #installAddress#,
    CONSTRUCT = #construct#,INSTALL_DATE = #installDate#,MANUFACTURE_DATE = #manufactureDate#,
    SERIALNUMBER = #serialNumber#,LONGITUDEVALUE = #longitude#,
    LATITUDEVALUE = #latitude#,DELETEFLAG = #deleteFlag#,STATUS = #status#
   WHERE
    a.DEVICEID = #deviceId#
]]>
</update>

<update id="deleteDevice" parameterClass="String">
   <![CDATA[
   UPDATE
    ITMS_ITIL_UTC_DEVICE a
   SET
    a.DELETEFLAG = 1
   WHERE
    a.DEVICEID = #deviceId#
]]>
</update>


<select id="getDeviceSimpleByOrg" parameterClass="java.lang.String"
   resultClass="itmsItilDeviceSimple">
   <![CDATA[
   SELECT
          a.ORG_ID as orgId,a.DEVICENAME as deviceName,a.DEVICEID as deviceId,
          a.DEVICETYPEID as deviceTypeId,a.DEVICESUBTYPEID as deviceSubTypeId,
          a.LONGITUDEVALUE as longitude,a.LATITUDEVALUE as latitude,
          a.DELETEFLAG as deleteFlag,a.STATUS as status,
          b.DEVICETYPENAME as deviceTypeName, c.DEVICESUBTYPENAME as deviceSubTypeName, d.ORGNAME as orgName
     FROM
        ITMS_ITIL_UTC_DEVICE a
     LEFT JOIN
          ITMS_ITIL_UTC_DEVICETYPE b on a.DEVICETYPEID = b.DEVICETYPEID
      LEFT JOIN
          ITMS_ITIL_UTC_DEVICESUBTYPE c on a.DEVICESUBTYPEID = c.DEVICESUBTYPEID
     LEFT JOIN
          ITMS_UUMS_ORGANIZATIONS d on a.ORG_ID = d.ORGID
     WHERE
          a.DELETEFLAG = '0'
     AND
        a.ORG_ID in ($orgIds$)
      ORDER BY
         a.ORG_ID
   ]]>
</select>


<select id="getDeviceById" parameterClass="java.lang.String"
   resultClass="itmsItilDevice">
   <![CDATA[
   SELECT
   a.ORG_ID as orgId,a.DEVICENAME as deviceName,a.DEVICEID as deviceId,
    a.DEVICETYPEID as deviceTypeId,a.DEVICESUBTYPEID as deviceSubTypeId,
    a.CATEGORYID as categoryId,a.COMMODEID as comModeId,
    a.COMPACTID as comPactId,a.INSTALLADDRESS as installAddress,a.CONSTRUCT as construct,
    a.INSTALL_DATE as installDate,a.INTERSECTIONID as intersectionId,
    a.MANUFACTURE_DATE as manufactureDate,a.SERIALNUMBER as serialNumber,
    a.LONGITUDEVALUE as longitude,a.LATITUDEVALUE as latitude,
    a.DELETEFLAG as deleteFlag,a.STATUS as status,
    b.DEVICETYPENAME as deviceTypeName,
    c.DEVICESUBTYPENAME as deviceSubTypeName,
    d.ORGNAME as orgName,
    e.VALUE as comModeName,
    f.CATEGORYNAME as categoryName,
    g.REGIONNAME as regionName,
    h.COMPACTNAME as comPactName,
    i.INTERSECTIONNAME as intersectionName,
    j.REGIONID as regionId
     FROM
        ITMS_ITIL_UTC_DEVICE a
     LEFT JOIN
          ITMS_ITIL_UTC_DEVICETYPE b on a.DEVICETYPEID = b.DEVICETYPEID
      LEFT JOIN
          ITMS_ITIL_UTC_DEVICESUBTYPE c on a.DEVICESUBTYPEID = c.DEVICESUBTYPEID
     LEFT JOIN
          ITMS_UUMS_ORGANIZATIONS d on a.ORG_ID = d.ORGID
      LEFT JOIN
   ITMS_DICT_COMMODE e on a.COMMODEID = e.ID
LEFT JOIN
   ITMS_ITIL_UTC_CATEGORY f on a.CATEGORYID = f.CATEGORYID
LEFT JOIN
   ITMS_DICT_COMPACT h on a.COMPACTID = h.COMPACTID
   LEFT JOIN
   ITMS_UTC_INTERSECTION i on a.INTERSECTIONID = i.INTERSECTIONID
LEFT JOIN
     ITMS_UTC_REGION_DEVICE_MAPPING j on a.DEVICEID = j.DEVICEID
       LEFT JOIN
   ITMS_UTC_REGION g on j.REGIONID = g.REGIONID
   WHERE
   a.DELETEFLAG = '0'
    AND
   a.DEVICEID = #deviceId#
   ]]>
</select>


<select id="getDeviceId" parameterClass="java.lang.String"
   resultClass="java.lang.String">
   <![CDATA[
   SELECT
   DEVICEID
     FROM
        ITMS_ITIL_UTC_DEVICE
   WHERE
   DEVICEID = #deviceId#
   ]]>
</select>


<select id="getDeviceName" parameterClass="java.lang.String"
   resultClass="itmsItilDeviceSimple">
   <![CDATA[
   SELECT
   DEVICEID as deviceId,DEVICENAME as deviceName
     FROM
        ITMS_ITIL_UTC_DEVICE
   WHERE
   DEVICENAME = #deviceName#
   ]]>
</select>


<select id="getDeviceSimpleByOrgWithPageCount"
   parameterClass="deviceQueryVO" resultClass="java.lang.Integer">
   <![CDATA[
   select
        count(distinct d.DEVICE_ID)
        from
        (select * from ITMS_ITIL_VMS_DEVICE a,itms_itil_vms_devtype b where a.device_type_id= b.devicetypeid and b.devtype in ('TP','GDTP') and a.deleteflag='0') d
]]>
   <dynamic prepend="WHERE">
    <isNotEmpty prepend="AND" property="orgIds">
     d.ORG_ID in ($orgIds$)
    </isNotEmpty>
   </dynamic>
</select>

<select id="getDeviceSimpleByOrgWithPage"
   parameterClass="deviceQueryVO" resultClass="itmsItilDeviceSimple">
   <![CDATA[
   SELECT
   *
   FROM (
   SELECT
      z.*, rownum as linenum
   FROM (
   SELECT
   a.DEVICE_ID as deviceId, a.DEVICE_NAME as deviceName, a.DEVICE_TYPE_ID as deviceTypeId, a.ORG_ID as orgId, a.SITE_ID as siteId,
   c.DEVICETYPENAME as deviceTypeName, b.ROADSECT_NAME as siteName, d.ORGNAME as orgName, a.LONGITUDEVALUE as longitude, a.LATITUDEVALUE as latitude
   FROM
   ITMS_ITIL_VMS_DEVICE a
   LEFT JOIN
   ITMS_ITIL_ROADSECT b on a.SITE_ID = b.ROADSECT_ID
   LEFT JOIN
   ITMS_ITIL_VMS_DEVTYPE c on a.DEVICE_TYPE_ID = c.DEVICETYPEID
   LEFT JOIN
   ITMS_UUMS_ORGANIZATIONS d on a.ORG_ID = d.ORGID
   WHERE
   a.DELETEFLAG = '0'
   AND
      c.devtype in ('TP','GDTP')
   AND
   a.ORG_ID in ($orgIds$)
   ]]>
   <![CDATA[
     ORDER BY
      a.DEVICE_ID
    ) z
    WHERE ROWNUM <= #endIndex#
   )
    WHERE
     LINENUM > #startIndex#
    ]]>
</select>
</sqlMap>