最近学习了一下,怎样使用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"?>
- <project
- xsi: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);
- @Override
- public 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();
- }
- @Override
- public Object plugin(Object target) {
- return Plugin.wrap(target, this);
- }
- @Override
- public 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 {
- @Override
- public 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构造为分页SQL
- StringBuffer 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来分页查询,所以实现时:
- @Override
- public 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
测试:
- @Test
- public void testQueryWithPage() {
- RowBounds rowBounds = new RowBounds(0 , 2);
- List<BlogEntity> blogs = blogDao.queryWithPage(rowBounds);
- for(BlogEntity blog : blogs) {
- System.out.println("--blog:" + blog);
- }
- }
在实现拦截器时,还有另一种方法,道理是一样的,会在一篇博客中简单说一下。