关于mybatis中对mysql和Oracle数据库分页插件的使用

你的名字 2021-07-09 00:45 907阅读 0赞

首先是Oracle数据库：在mybatis相对应的mapper.xml文件里：

<sql id="OracleDialectPrefix">
        
        <if test="page != null">
          select * from ( select row_.*, rownum rownum_ from ( 
        </if>
      </sql>
      <sql id="OracleDialectSuffix">
        
        <if test="page != null">
          <![CDATA[ ) row_ ) where rownum_ > #{page.startRow} and rownum_ <= #{page.startRow} + #{page.pageSize} ]]>
        </if>
      </sql>

在Oracle数据库中要有两条sql包着的分页语句，然后具体用的时候：

<select id="selectByExampleForCamera" parameterType="com.vrview.ssm.model.example.DeviceExample" resultMap="ResultMapForCamera">
        <include refid="OracleDialectPrefix" />
        select
        <if test="distinct">
          distinct
        </if>
        <include refid="Base_Column_List" />
        from CFG_DEVICE
        <if test="_parameter != null">
          <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
          order by ${orderByClause}
        </if>
        <include refid="OracleDialectSuffix" />
      </select>

要在查询语句一前一后。

2、再看msql的分页

分页代码：

用到语句中：

<select id="selectByExampleWithPolicy" parameterType="cn.vrview.sa.model.example.LevDeviceExample" resultMap="BaseResultWithPolicyMap">
        select
        <if test="distinct">
          distinct
        </if>
        <include refid="Base_Column_List" />
        from LEV_DEVICE
        <if test="_parameter != null">
          <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
          order by ${orderByClause}
        </if>
        <include refid="MysqlDialectSuffix" />
      </select>

只要放在select语句句末就行。