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

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

待尝试