Oracle、MySQL分页实例
- SQL执行代码
ORACLE:
SELECT * FROM (
SELECT ROWNUM AS NUMROW, Z.* from (
select * from CHANNEL T where 1=1 and T.ID = 1 ORDER BY T.ID desc
) Z where rownum<=10
) WHERE NUMROW >= 1
MySQL:获取(0 ,10],10表示向后获取10条数据。
select * from fm_sell T
where 1=1 and T.SERIAL_NUM = '20141124' ORDER BY T.ID desc LIMIT 0, 10
- Mybatis中分页应用与优化
Oracle: <sql id="WhereClase"> <where> 1=1 <if test="id != null"> AND T.ID = #{id,jdbcType=BIGINT} </if> ... </where> </sql> <sql id="OrderBy"> ORDER BY T.ID desc </sql> <sql id="PageSQLHead"> SELECT * FROM (SELECT ROWNUM AS NUMROW, Z.* from ( </sql> <sql id="PageSQLFoot"> <![CDATA[ ) Z where rownum<=#{pageLastItem}) WHERE NUMROW >= #{pageFristItem} ]]> </sql> <select id="queryCount" parameterClass="ChannelQuery" resultClass="java.lang.Integer"> select count(1) from CHANNEL T <include refid="WhereClase"/> <include refid="OrderBy"/> </select> <select id="queryPage" parameterClass="ChannelQuery" resultMap="channelResult"> <include refid="PageSQLHead" /> select <include refid="Base_Column_List" /> from CHANNEL T <include refid="WhereClase" /> <include refid="OrderBy" /> <include refid="PageSQLFoot" /> </select>
MySQL: <resultMap id="BaseResultMap" type="fmSell"> <id column="ID" property="id" jdbcType="BIGINT" /> <result column="SERIAL_NUM" property="serialNum" jdbcType="VARCHAR" /> ... </resultMap> <sql id="Base_Column_List"> T.ID, T.SERIAL_NUM, T.STOCK_ID, T.GMT_CREATE, T.GMT_MODIFY </sql> <sql id="WhereClase"> <where> 1=1 <if test="serialNum != null and serialNum != ''"> AND T.SERIAL_NUM = #{serialNum,jdbcType=VARCHAR} </if> ... </where> </sql> <!-- 分页起始 --> <sql id="GetPagerSql"> LIMIT #{pageIndex}, #{pageSize} </sql> <sql id="OrderBy"> ORDER BY T.ID desc </sql> <!-- 分页记录 --> <select id="selectPagerByQuery" parameterType="fmSellQuery" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM fm_sell T <include refid="WhereClase"/> <include refid="OrderBy"/> <include refid="GetPagerSql"/> </select> <!-- 总记录数 --> <select id="selectCountsByQuery" parameterType="fmSellQuery" resultType="java.lang.Integer"> SELECT COUNT(1) FROM fm_sell T <include refid="WhereClase"/> <include refid="OrderBy"/> </select>