不同数据库下的Mybatis映射文件的写法

来源：互联网发布：电视盒直播软件下载编辑：程序博客网时间：2024/06/06 03:08

C#项目，oracle 、mysql 、sqlserver数据库下，mybatis的映射文件里，一些常用功能的写法

字符串模糊匹配

oracle

{colume_name} Like '%' || #{para_name}# || '%')

mysql

USER_NAME Like '%$UserName$%'

sqlserver

USER_NAME Like '%' + #UserName# + '%'

数据库分页

oracle

<select  id="ManagementNewsInfo.Select_ByPage" resultMap="ManagementNewsInfoResult">      SELECT * FROM      (      SELECT      *,      ROW_NUMBER() OVER(ORDER BY CREATE_TIME DESC) AS ROWINDEX      FROM MANAGEMENT_NEWS_INFO      <dynamic prepend="WHERE">        <isNotEmpty prepend="AND" property="TITLE">          (TITLE Like '%' || #TITLE# || '%')        </isNotEmpty>      </dynamic>      ) T      WHERE T.ROWINDEX > #StartNumber# AND (#EndNumber#)>=T.ROWINDEX    </select>

StartNumber 和 EndNumber就是分页的起止索引。

mysql

<select id="DealRecordInfo.Search_ByPage" resultMap="DealRecordInfoResult">      <![CDATA[   SELECT * FROM (  SELECT  *FROM deal_record_info ]]>      <dynamic prepend="WHERE">       ……      </dynamic>      <![CDATA[      ) T ORDER BY  $sort$ $order$ limit #PageSize# offset #PageIndex# ]]>    </select>

sort为排序字段名

order为 desc/asc

PageSize为页面元素个数

PageIndex为分页索引（从0开始）

sqlserver

    <select id="SysUser.Search_ByPage" resultMap="SysUserResult">      SELECT * FROM      (      SELECT      *,      ROW_NUMBER() OVER(ORDER BY ID) AS ROWINDEX      FROM SYS_USER      <dynamic prepend="WHERE">        ……      </dynamic>      ) T      WHERE T.ROWINDEX > #StartNumber# AND (#EndNumber#)>=T.ROWINDEX    </select>

StartNumber 和 EndNumber就是分页的起止索引。
动态传入排序字段如下写法

      SELECT      *,ROW_NUMBER()      OVER(      <dynamic prepend="order by">        <isEqual prepend="order by" property="orderByDes" compareValue="1">          A.LAST_COMMENT_TIME desc        </isEqual>        <isEqual prepend="order by" property="orderByDes" compareValue="2">          A.NO_OF_COMMENT desc        </isEqual>      </dynamic>      ) AS ROWINDEX

根据传入的orderByDes的值动态排序

时间比较

oracle

to_date(#{para_time}#,'yyyy-mm-dd')>={colume_name}

使用to_date函数完成时间比较

mysql

CREATE_TIME BETWEEN #strDateFrom# and #strDateTo#

利用between比较时间段

sqlserver

CREATE_TIME >=#startTimeFrom#

startTimeFrom为传入的时间字符串

批量处理

oracle

待尝试

mysql

批量插入

<insert id="DealRecordInfo.InsertBatch" parameterClass="ArrayList">      INSERT INTO {table_name}({colume_name})      VALUES      <iterate conjunction="," open="" close="">        (#[].{colume_name}#)      </iterate> </insert>

批量更新

<update id="DealRecordInfo.UpdateBatch" parameterClass="DealRecordInfo">      update {table_name}      set {colume_name}= #{para_name}#      where ID in      <iterate property="IdList" conjunction="," open="(" close=")">        #IdList[]#      </iterate>    </update>

IdList[]为传入的数组

sqlserver

待尝试

0 0