ibatis 存储过程写法

ibatis 存储过程写法

<?xml version= " 1.0 " encoding= " utf-8 " ?>
<sqlMap  namespace= " DepartmentInfoModel " xmlns= " http://ibatis.apache.org/mapping " xmlns:xsi= " http://www.w3.org/2001/XMLSchema-instance "  >

  <alias>
    <typeAlias alias= " DepartmentInfoModel " type= " Dscf.Global.Employee.Model.DepartmentInfoModel,Dscf.Global " />
  </alias>

  <!--门店树 传参-->
  <parameterMaps>
    <parameterMap id= " selectMap_Dept_DeptInfo "  class= " DepartmentInfoModel ">
      <parameter property= " Idenid " column= " Idenid "/>
    </parameterMap>
  </parameterMaps>

  <resultMaps>
    <resultMap id= " selectMap_T_DepartmentInfo "  class= " DepartmentInfoModel ">
      <result property= " DepId " column= " DepId "/>
      <result property= " DepName " column= " DepName "/>
      <result property= " ParentDepId " column= " ParentDepId "/>
      <result property= " DepCode " column= " DepCode "/>
      <result property= " CustomerServicePhone " column= " CustomerServicePhone "/>
      <result property= " RevolvingLoanPhone " column= " RevolvingLoanPhone "/>
      <result property= " EarlyRepayPhone " column= " EarlyRepayPhone "/>
      <result property= " Email " column= " Email "/>
      <result property= " SignAddress " column= " SignAddress "/>
      <result property= " SignZipCode " column= " SignZipCode "/>
      <result property= " IsDeleted " column= " IsDeleted "/>
      <result property= " SignCity " column= " SignCity "/>
      <result property= " IsEnable " column= " IsEnable "/>
      <result property= " LastOperateId " column= " LastOperateId "/>
      <result property= " LastUpdateTime " column= " LastUpdateTime "/>
      <result property= " CreateTime " column= " CreateTime "/>
      <result property= " OperateId " column= " OperateId "/>
      <result property= " IsReceiveEmail " column= " IsReceiveEmail "/>
    </resultMap>

    <!--门店树 返回值 -->
    <resultMap id= " selectMap_T_DepartmentInfoTree "  class= " DepartmentInfoModel ">
      <result property= " DepId " column= " DepId "/>
      <result property= " SignZipCode " column= " SignZipCode "/>
      <result property= " IsDeleted " column= " IsDeleted "/>
      <result property= " SignCity " column= " SignCity "/>
      <result property= " IsEnable " column= " IsEnable "/>
      <result property= " LastOperateId " column= " LastOperateId "/>
      <result property= " LastUpdateTime " column= " LastUpdateTime "/>
      <result property= " CreateTime " column= " CreateTime "/>
      <result property= " OperateId " column= " OperateId "/>
      <result property= " DepName " column= " DepName "/>
      <result property= " ParentDepId " column= " ParentDepId "/>
      <result property= " DepCode " column= " DepCode "/>
      <result property= " CustomerServicePhone " column= " CustomerServicePhone "/>
      <result property= " RevolvingLoanPhone " column= " RevolvingLoanPhone "/>
      <result property= " EarlyRepayPhone " column= " EarlyRepayPhone "/>
      <result property= " Email " column= " Email "/>
      <result property= " SignAddress " column= " SignAddress "/>

      <result property= " ParentName " column= " ParentName "/>
      <result property= " sort " column= " sort "/>
      <result property= " level " column= " level "/>
      <result property= " IsReceiveEmail " column= " IsReceiveEmail "/>
    </resultMap>
  </resultMaps>


  <statements>
    <!-- 查询  需要后动修改分页时的排序字段 -->
    < select id= " select_T_DepartmentInfo " resultMap= " selectMap_T_DepartmentInfo "   resultClass= " DepartmentInfoModel "      parameterClass= " DepartmentInfoModel ">
      SELECT
      <isNotNull property= " TopNums ">
        <![CDATA[ top $TopNums$]]>
      </isNotNull>
      MAX(row_n) over(partition by  1)  as TotalItems, *
      FROM
      (
      <!-- ********* 必须要修改 order by a.Id ********* -->
      SELECT ROW_NUMBER() OVER ( PARTITION BY  1 ORDER BY a.DepId) AS row_n,a.DepId,a.DepName,a.ParentDepId,a.DepCode,a.CustomerServicePhone,a.RevolvingLoanPhone,a.EarlyRepayPhone,a.Email,a.SignAddress,a.SignZipCode,a.IsDeleted,a.SignCity,a.IsEnable,a.LastOperateId,a.LastUpdateTime,a.CreateTime,a.OperateId,a.IsReceiveEmail
      FROM  T_DepartmentInfo  as a
      < dynamic prepend= " where ">

        <isNotNull prepend= " and " property= " DepId ">
          <![CDATA[ a.DepId=#DepId#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " IsReceiveEmail ">
          <![CDATA[ a.IsReceiveEmail=#IsReceiveEmail#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " DepName ">
          <![CDATA[ a.DepName=#DepName#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " ParentDepId ">
          <![CDATA[ a.ParentDepId=#ParentDepId#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " DepCode ">
          <![CDATA[ a.DepCode=#DepCode#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " CustomerServicePhone ">
          <![CDATA[ a.CustomerServicePhone=#CustomerServicePhone#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " RevolvingLoanPhone ">
          <![CDATA[ a.RevolvingLoanPhone=#RevolvingLoanPhone#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " EarlyRepayPhone ">
          <![CDATA[ a.EarlyRepayPhone=#EarlyRepayPhone#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " Email ">
          <![CDATA[ a.Email=#Email#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " SignAddress ">
          <![CDATA[ a.SignAddress=#SignAddress#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " SignZipCode ">
          <![CDATA[ a.SignZipCode=#SignZipCode#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " IsDeleted ">
          <![CDATA[ a.IsDeleted=#IsDeleted#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " SignCity ">
          <![CDATA[ a.SignCity=#SignCity#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " IsEnable ">
          <![CDATA[ a.IsEnable=#IsEnable#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " LastOperateId ">
          <![CDATA[ a.LastOperateId=#LastOperateId#  ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " LastUpdateTime_B ">
          <![CDATA[ a.LastUpdateTime>=#LastUpdateTime_B# ]]>
        </isNotNull>
        <isNotNull prepend= " and " property= " LastUpdateTime_E ">
          <![CDATA[ a.LastUpdateTime<=#LastUpdateTime_E# ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " CreateTime_B ">
          <![CDATA[ a.CreateTime>=#CreateTime_B# ]]>
        </isNotNull>
        <isNotNull prepend= " and " property= " CreateTime_E ">
          <![CDATA[ a.CreateTime<=#CreateTime_E# ]]>
        </isNotNull>

        <isNotNull prepend= " and " property= " OperateId ">
          <![CDATA[ a.OperateId=#OperateId#  ]]>
        </isNotNull>

        <!-- 一个例子 -->
        <!--<isNotEmpty prepend= " and " property= " 属性名 ">
                       字段名 like  #属性名# 
               </isNotEmpty>-->
      </ dynamic>
      )  as a
      < dynamic prepend= " where ">
        <isNotNull property= " PrevPageNums ">
          <![CDATA[ a.row_n>$PrevPageNums$]]>
        </isNotNull>
      </ dynamic>


    </ select>

    <!-- 数据分析 树-->
    <procedure id= " select_T_DepartmentInfoTree " parameterMap= " selectMap_Dept_DeptInfo "  resultMap= " selectMap_T_DepartmentInfoTree " >
      Proc_LoanStorDept
    </procedure>



    <!-- 添加 -->
    <insert id= " insert_T_DepartmentInfo " parameterClass= " DepartmentInfoModel ">
      <selectKey property= " DepId " type= " post " resultClass= " int ">
        ${selectKey}
      </selectKey>
      INSERT INTO T_DepartmentInfo
      (
      DepName,ParentDepId,DepCode,CustomerServicePhone,RevolvingLoanPhone,EarlyRepayPhone,Email,SignAddress,SignZipCode,IsDeleted,SignCity,IsEnable,LastOperateId,LastUpdateTime,CreateTime,OperateId,IsReceiveEmail
      ) VALUES
      (
      #DepName#,#ParentDepId#,#DepCode#,#CustomerServicePhone#,#RevolvingLoanPhone#,#EarlyRepayPhone#,#Email#,#SignAddress#,#SignZipCode#,#IsDeleted#,#SignCity#,#IsEnable#,#LastOperateId#,#LastUpdateTime#,#CreateTime#,#OperateId#,#IsReceiveEmail#
      )

    </insert>

    <!-- 更新  -->
    <update id= " update_T_DepartmentInfo " parameterClass= " DepartmentInfoModel ">
      UPDATE T_DepartmentInfo SET

      DepName=#DepName#,
      ParentDepId=#ParentDepId#,
      DepCode=#DepCode#,
      CustomerServicePhone=#CustomerServicePhone#,
      RevolvingLoanPhone=#RevolvingLoanPhone#,
      EarlyRepayPhone=#EarlyRepayPhone#,
      Email=#Email#,
      SignAddress=#SignAddress#,
      SignZipCode=#SignZipCode#,
      IsDeleted=#IsDeleted#,
      SignCity=#SignCity#,
      IsEnable=#IsEnable#,
      LastOperateId=#LastOperateId#,
      LastUpdateTime=#LastUpdateTime#,
      CreateTime=#CreateTime#,
      OperateId=#OperateId#,
      IsReceiveEmail=#IsReceiveEmail#
      <!--  -->
      WHERE T_DepartmentInfo.DepId=#DepId#
    </update>

    <!--删除-->
    <delete id= " delete_T_DepartmentInfo " parameterClass= " DepartmentInfoModel ">
      DELETE FROM T_DepartmentInfo  where DepId=#DepId#
    </delete>

    <!-- 删除-->
    <delete id= " delete_flag_T_DepartmentInfo " parameterClass= " DepartmentInfoModel ">
      UPDATE  T_DepartmentInfo  set IsDeleted =  1  where DepId=#DepId#
    </delete>
  </statements>
</sqlMap>

 

 

 

posted on 2016-04-25 10:46  笨小孩做开发 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/dullbaby/p/5430090.html