基于IBATIS的自定义jsp分页标签

最新推荐文章于 2021-08-30 22:43:58 发布

原创最新推荐文章于 2021-08-30 22:43:58 发布 · 170 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#iBATIS #JSP #SQL #Oracle #Apache

Ibatis 专栏收录该内容

8 篇文章

订阅专栏

本文提供了一套在IBATIS框架下针对SQL SERVER及ORACLE数据库的通用分页实现方案，通过定义特定的XML映射文件实现了动态SQL的构造，支持自定义当前页数与每页显示记录数，并提供了灵活的排序功能。

IBATIS中SQLSERVER和ORACLE的集合通用分页代码

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

<sql id="PaginationTop">
   <isNotEmpty property="CURRENTPAGE"><isNotEmpty property="PAGESIZE">
    <![CDATA[
     SELECT TOP $PAGESIZE$ * FROM (
    ]]>
   </isNotEmpty></isNotEmpty>
</sql>


<sql id="PaginationMid">
   <isNotEmpty property="CURRENTPAGE"><isNotEmpty property="PAGESIZE">
    <![CDATA[
     ) TEMP WHERE ID NOT IN
             (SELECT TOP $CURRENTPAGE$ ID FROM (
    ]]>
   </isNotEmpty></isNotEmpty>
</sql>

<sql id="PaginationEnd">
   <isNotEmpty property="CURRENTPAGE"><isNotEmpty property="PAGESIZE">
    <![CDATA[
     ) TEMP)
    ]]>
   </isNotEmpty></isNotEmpty>
</sql>


<sql id="orderBy">
   <dynamic prepend="ORDER BY">
    <isNotEmpty prepend="," property="sortField">
     $sortField$
    </isNotEmpty>
   </dynamic>
</sql>

<sql id="paginationStart">
   <dynamic prepend="">
    <isNotEmpty property="RowNumStart">
     <![CDATA[
       select * from(select row_.*, rownum ROWNUM_ from(
       ]]>
    </isNotEmpty>
    <isEmpty property="RowNumStart">
     <![CDATA[
      select * from(select row_.*, rownum ROWNUM_ from(
       ]]>
    </isEmpty>
   </dynamic>
</sql>

<sql id="paginationEnd">
   <dynamic prepend="">
    <isNotEmpty property="RowNumEnd">
     <![CDATA[
         )row_ where rownum<=#RowNumEnd#) where ROWNUM_ >= #RowNumStart#
        ]]>
    </isNotEmpty>
    <isEmpty property="RowNumEnd">
     <![CDATA[
       )row_ where rownum<=17) where ROWNUM_ >= 1
       ]]>
    </isEmpty>
   </dynamic>
</sql>

<sql id="orderBy">
   <dynamic prepend="ORDER BY">
    <isNotEmpty prepend="," property="sortField">
     $sortField$
    </isNotEmpty>
   </dynamic>
</sql>
</sqlMap>