MyBatis分页实现 - Oracle

最近学习了一下，怎样使用MyBatis实现分页。

同样的参考了很多博客：

http://haohaoxuexi.iteye.com/blog/1851081

http://xdwangiflytek.iteye.com/blog/1750641

自己总结如下：

1.准备

要实现分页，首先要有MyBatis的基本环境：Mybatis的jar包，配置文件，映射文件，Oracle驱动包......

在练习时，使用Maven，附上pom文件

mybatis-parent的pom.xml

<?xml version="1.0"?><projectxsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><modelVersion>4.0.0</modelVersion><groupId>org.ygy.demo</groupId><artifactId>mybatis-parent</artifactId><version>0.0.1-SNAPSHOT</version><packaging>pom</packaging><name>mybatis-parent</name><url>http://maven.apache.org</url><dependencyManagement><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.10</version><scope>test</scope></dependency> <dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.1.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>0.12.0</version></dependency><dependency><groupId>com.oracle</groupId><artifactId>ojdbc14</artifactId><version>10.2.0.4.0</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency></dependencies></dependencyManagement><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>com.oracle</groupId><artifactId>ojdbc14</artifactId></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><source>1.6</source><target>1.6</target></configuration></plugin></plugins></build><modules><module>mybatis-hello</module><module>mybatis-relevance</module><module>mybatis-common</module><module>mybatis-page</module></modules></project>

自身项目 mybatis-page的pom没有特殊配置

在使用Maven，引入Oracle依赖的时候，挺纠结的，需要费点事，详情可以搜一下，以后可能会分享下

MyBatisUtil类

package org.ygy.demo.mybatis.common.util;import java.io.IOException;import java.io.InputStream;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MyBatisUtil {private static SqlSessionFactory factory = null;private static void initialFactory() {String resource = "mybatis-config.xml";try {InputStream in = Resources.getResourceAsStream(resource);factory = new SqlSessionFactoryBuilder().build(in);} catch (IOException e) {e.printStackTrace();}}public static SqlSession getSession() {if (factory == null) {initialFactory();}return factory.openSession();}}

2.实现分页

使用MyBatis实现分页的一种方法是使用拦截器，自定义一个拦截器插件，然后，将SQL语句转换成3层嵌套的SQL语句.

一下代码参考上面的博客，自己加了注释，

主要是实现一个拦截器插件：

package org.ygy.demo.mybatis.page.dialect;import java.sql.Connection;import java.util.Properties;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.logging.Log;import org.apache.ibatis.logging.LogFactory;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.plugin.Signature;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.session.RowBounds;/** *  * @author yuguiyang * @description 然后就是实现mybatis提供的拦截器接口，编写我们自己的分页实现，原理就是拦截底层JDBC操作相关的Statement对象， *              把前端的分页参数如当前记录索引和每页大小通过拦截器注入到sql语句中 *              ，即在sql执行之前通过分页参数重新生成分页sql,而具体的分页sql实现是分离到Dialect接口中去。 * @time 2013-8-23 * @version V1.0 */@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })public class PageInterceptor implements Interceptor {private final static Log log = LogFactory.getLog(PageInterceptor.class);@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();BoundSql boundSql = statementHandler.getBoundSql();//元数据MetaObject metaObject = MetaObject.forObject(statementHandler);//分页参数RowBounds rowBounds = (RowBounds) metaObject.getValue("delegate.rowBounds");if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {//如果没有提供RowBounds的，则不做操作} else {//获取配置文件参数Configuration configuration = (Configuration) metaObject.getValue("delegate.configuration");//读取配置文件中的配置，确认是什么分页实现Dialect dialect = DialectFactory.createDialect(configuration.getVariables().getProperty("dialect"));String originalSql = (String) metaObject.getValue("delegate.boundSql.sql");metaObject.setValue("delegate.boundSql.sql",dialect.changeToPageSql(originalSql, rowBounds.getOffset(), rowBounds.getLimit()));metaObject.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);metaObject.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);//输出日志if (log.isDebugEnabled()) {log.debug("生成分页SQL : " + boundSql.getSql());}}return invocation.proceed();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {}}

package org.ygy.demo.mybatis.page.dialect;/** *  * @author yuguiyang * @description Oracle的分页实现 * @time 2013-8-26 * @version V1.0 */public class OracleDialect implements Dialect {@Overridepublic String changeToPageSql(String sql, int start, int pageSize) {//去除SQL手尾的空格sql = sql.trim();boolean isForUpdate = false;if (sql.toLowerCase().endsWith(" for update")) {sql = sql.substring(0, sql.length() - 11);isForUpdate = true;}//将SQL构造为分页SQLStringBuffer pageSql = new StringBuffer(sql.length() + 100);pageSql.append("select * from ( select row_.*, rownum rownum_ from ( ");pageSql.append(sql);pageSql.append(" ) row_ ) where rownum_ > " + start + " and rownum_ <= " + (start + pageSize));if (isForUpdate) {pageSql.append(" for update");}return pageSql.toString();}}

接下来是配置文件：

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-config.dtd">    <configuration><!-- 配置数据库分页的实现 -->    <properties>          <property name="dialect" value="oracle" />         <property name="databaseType" value="oracle"></property>     </properties>            <!-- 全局别名设置，在映射文件中只需写别名，而不必写出整个类路径  -->    <typeAliases>           <typeAlias type="org.ygy.demo.mybatis.entity.PersonEntity" alias="PersonEntity"></typeAlias>         <typeAlias type="org.ygy.demo.mybatis.entity.BlogEntity" alias="BlogEntity"></typeAlias>    </typeAliases>         <!-- 配置插件 -->    <plugins>    <!-- 配置拦截器插件 -->       <!--  <plugin interceptor="org.ygy.demo.mybatis.page.dialect.PageInterceptor"></plugin>        <plugin interceptor="org.ygy.demo.mybatis.interceptor.SimpleInterceptor"></plugin> -->        <plugin interceptor="org.ygy.demo.mybatis.interceptor.PageInterceptor"></plugin>      </plugins>      <environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="oracle.jdbc.OracleDriver" /><property name="url" value="jdbc:oracle:thin:@192.168.17.254:1521:dpweb" /><property name="username" value="dptest" /><property name="password" value="dptest" /></dataSource></environment></environments><!-- 配置映射文件地址 --><mappers> <mapper resource="org/ygy/demo/mybatis/mapper/blog-mapper.xml"></mapper></mappers></configuration>

注意：上面的拦截器在实现的时候，使用的是RowBounds来分页查询，所以实现时：

@Overridepublic List<BlogEntity> queryWithPage(RowBounds rowBounds) {SqlSession session = MyBatisUtil.getSession();List<BlogEntity> blogs = session.selectList(NAMESPACE + ".queryWithPage" , null , rowBounds);session.commit();session.close();return blogs;}

也需要传递RowBounds

测试：

@Testpublic void testQueryWithPage() { RowBounds rowBounds = new RowBounds(0 , 2);List<BlogEntity> blogs = blogDao.queryWithPage(rowBounds);for(BlogEntity blog : blogs) {System.out.println("--blog:" + blog);}}

在实现拦截器时，还有另一种方法，道理是一样的，会在一篇博客中简单说一下。