MyBatis 通用分页

_winds

分页分为真分页和假分页，而
MyBatis
本身没有提供基于数据库方言的分页功能，而是基于
JDBC
的游标分页，很容易出现性能问题。网上提供的一个解决方案感觉还不错，是基于
MyBatis
本身的插件机制，通过拦截
Sql
做分页。

        

首先，我们需要根据不同数据库来加载不同的分页
SQL
，这里我们参考
Hibernate
，定义一个数据库方言接口

Dialect.java


package com.iflytek.mybatis.page.dialect;
/**
* @author xdwang
*
* @ceate 2012-12-19 下午7:45:24
*
* @description 数据库方言接口
*
*/
public interface Dialect {
public static enum Type {
MYSQL {
public String getValue() {
return "mysql";
}
},
MSSQL {
public String getValue() {
return "sqlserver";
}
},
ORACLE {
public String getValue() {
return "oracle";
}
}
}
/**
* @descrption 获取分页SQL
* @author xdwang
* @create 2012-12-19下午7:48:44
* @param sql
*            原始查询SQL
* @param offset
*            开始记录索引（从零开始）
* @param limit
*            每页记录大小
* @return 返回数据库相关的分页SQL语句
*/
public abstract String getPageSql(String sql, int offset, int limit);
}

　　然后分别定义不同类型数据库的具体分页，这里我们列举3个比较常用的，MySQL、MSSQL、Oracle

MySql5Dialect.java

package com.iflytek.mybatis.page.dialect;
/**
* @author xdwang
*
* @ceate 2012-12-19 下午7:50:44
*
* @description MySQL数据库实现
*
*/
public class MySql5Dialect implements Dialect {
protected static final String SQL_END_DELIMITER = ";";
public String getPageSql(String sql, boolean hasOffset) {
return MySql5PageHepler.getPageSql(sql, -1, -1);
}
public String getPageSql(String sql, int offset, int limit) {
return MySql5PageHepler.getPageSql(sql, offset, limit);
}
public boolean supportsLimit() {
return true;
}
}

　　MySql5PageHepler.java

package com.iflytek.mybatis.page.dialect;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
*
* @author xdwang
*
* @ceate 2012-12-19 下午8:41:21
*
* @description MySql辅助方法
*
*/
public class MySql5PageHepler {
/**
* @descrption 得到查询总数的sql
* @author xdwang
* @create 2012-12-19下午8:41:10
* @param querySelect
* @return
*/
public static String getCountString(String querySelect) {
querySelect = getLineSql(querySelect);
int orderIndex = getLastOrderInsertPoint(querySelect);
int formIndex = getAfterFormInsertPoint(querySelect);
String select = querySelect.substring(0, formIndex);
// 如果SELECT 中包含 DISTINCT 只能在外层包含COUNT
if (select.toLowerCase().indexOf("select distinct") != -1 || querySelect.toLowerCase().indexOf("group by") != -1) {
return new StringBuffer(querySelect.length()).append("select count(1) count from (").append(querySelect.substring(0, orderIndex)).append(" ) t").toString();
} else {
return new StringBuffer(querySelect.length()).append("select count(1) count ").append(querySelect.substring(formIndex, orderIndex)).toString();
}
}
/**
* 得到最后一个Order By的插入点位置
*
* @return 返回最后一个Order By插入点的位置
*/
private static int getLastOrderInsertPoint(String querySelect) {
int orderIndex = querySelect.toLowerCase().lastIndexOf("order by");
if (orderIndex == -1 || !isBracketCanPartnership(querySelect.substring(orderIndex, querySelect.length()))) {
throw new RuntimeException("My SQL 分页必须要有Order by 语句!");
}
return orderIndex;
}
/**
* 得到分页的SQL
*
* @param offset
*            偏移量
* @param limit
*            位置
* @return 分页SQL
*/
public static String getPageSql(String querySelect, int offset, int limit) {
querySelect = getLineSql(querySelect);
String sql = querySelect.replaceAll("[^\\s,]+\\.", "") + " limit " + offset + " ," + limit;
return sql;
}
/**
* 将SQL语句变成一条语句，并且每个单词的间隔都是1个空格
*
* @param sql
*            SQL语句
* @return 如果sql是NULL返回空，否则返回转化后的SQL
*/
private static String getLineSql(String sql) {
return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");
}
/**
* 得到SQL第一个正确的FROM的的插入点
*/
private static int getAfterFormInsertPoint(String querySelect) {
String regex = "\\s+FROM\\s+";
Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(querySelect);
while (matcher.find()) {
int fromStartIndex = matcher.start(0);
String text = querySelect.substring(0, fromStartIndex);
if (isBracketCanPartnership(text)) {
return fromStartIndex;
}
}
return 0;
}
/**
* 判断括号"()"是否匹配,并不会判断排列顺序是否正确
*
* @param text
*            要判断的文本
* @return 如果匹配返回TRUE,否则返回FALSE
*/
private static boolean isBracketCanPartnership(String text) {
if (text == null || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')'))) {
return false;
}
return true;
}
/**
* 得到一个字符在另一个字符串中出现的次数
*
* @param text
*            文本
* @param ch
*            字符
*/
private static int getIndexOfCount(String text, char ch) {
int count = 0;
for (int i = 0; i < text.length(); i++) {
count = (text.charAt(i) == ch) ? count + 1 : count;
}
return count;
}
}

　　OracleDialect.java

package com.iflytek.mybatis.page.dialect;
/**
* @author xdwang
*
* @ceate 2012-12-19 下午7:54:56
*
* @description Oracle数据库实现
*
*/
public class OracleDialect implements Dialect {
public String getPageSql(String sql, int offset, int limit) {
sql = sql.trim();
boolean isForUpdate = false;
if (sql.toLowerCase().endsWith(" for update")) {
sql = sql.substring(0, sql.length() - 11);
isForUpdate = true;
}
StringBuffer pageSql = new StringBuffer(sql.length() + 100);
pageSql.append("select * from ( select row_.*, rownum rownum_ from ( ");
pageSql.append(sql);
pageSql.append(" ) row_ ) where rownum_ > " + offset + " and rownum_ <= " + (offset + limit));
if (isForUpdate) {
pageSql.append(" for update");
}
return pageSql.toString();
}
}

 　　SQLServerDialect.java

package com.iflytek.mybatis.page.dialect;
/**
* @author xdwang
*
* @ceate 2012-12-19 下午7:53:14
*
* @description SQLServer数据库实现
*
*/
public class SQLServerDialect implements Dialect {
public String getPageSql(String sql, int offset, int limit) {
sql = sql.trim();
StringBuffer pageSql = new StringBuffer(sql.length() + 100);
// 其实这里还是有一点问题的，就是排序问题，指定死了，有解决的提供一下，等复习到Hibernate看看Hibernat内部是如何实现的。
pageSql.append("select * from(select a.*,row_number() over (order by id desc) rownum from( ");
pageSql.append(sql);
pageSql.append(") a )b where rownum> " + offset + " and rownum <= " + (offset + limit));
return pageSql.toString();
}
}

 　　然后我们定义拦截器

PaginationInterceptor.java

package com.iflytek.mybatis.page.interceptor;
import java.sql.Connection;
import java.util.Properties;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.statement.StatementHandler;
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;
import com.iflytek.mybatis.page.dialect.Dialect;
import com.iflytek.mybatis.page.dialect.MySql5Dialect;
import com.iflytek.mybatis.page.dialect.OracleDialect;
/**
*
* @author xdwang
*
* @ceate 2012-12-19 下午8:01:31
*
* @description 然后就是实现mybatis提供的拦截器接口，编写我们自己的分页实现，原理就是拦截底层JDBC操作相关的Statement对象，
*              把前端的分页参数如当前记录索引和每页大小通过拦截器注入到sql语句中
*              ，即在sql执行之前通过分页参数重新生成分页sql,而具体的分页sql实现是分离到Dialect接口中去。
*
*
*/
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PaginationInterceptor implements Interceptor {
private final static Log log = LogFactory.getLog(PaginationInterceptor.class);
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");
if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {
return invocation.proceed();
}
Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");
Dialect.Type databaseType = null;
try {
databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
} catch (Exception e) {
// ignore
}
if (databaseType == null) {
throw new RuntimeException("the value of the dialect property in configuration.xml is not defined : " + configuration.getVariables().getProperty("dialect"));
}
Dialect dialect = null;
switch (databaseType) {
case MYSQL:
dialect = new MySql5Dialect();
break;
case MSSQL:
dialect = new MySql5Dialect();
break;
case ORACLE:
dialect = new OracleDialect();
break;
default:
dialect = new MySql5Dialect();
}
String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getPageSql(originalSql, rowBounds.getOffset(), rowBounds.getLimit()));
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
if (log.isDebugEnabled()) {
log.debug("生成分页SQL : " + boundSql.getSql());
}
return invocation.proceed();
}
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
public void setProperties(Properties properties) {
}
}

 　　Ok，搞定了，下面看看如何使用，其实和直接调用MyBatis原生的假分页方式一样。只需要在mybatis-config.xml添加一个标识和一个插件

<properties>
<property name="dialect" value="mysql" />
</properties>
<plugins>
<plugin interceptor="com.iflytek.mybatis.page.interceptor.PaginationInterceptor">
</plugin>
</plugins>

　　然后和MyBatis默认提供分页的方式一样，直接调用

public List<Student> getStudentsByPage(){
List<Student> students = new ArrayList<Student>();
SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
try {
//从第一条开始，取4条记录
RowBounds rowBounds = new RowBounds(1,4);
Student student=new Student();
student.setName("xdwang");
students = sqlSession.selectList("com.iflytek.dao.mapper.StudentMapper.selectByPageList", student, rowBounds);
sqlSession.commit();
} finally {
sqlSession.close();
}
return students;
}

Ok
，搞定，当然，上面我们也可以将需要拦截添加的
Sql
写在
mapper.xml
中，然后再需要分页的查询语句中引用，只是需要在每个模块下分页的地方都引用，相对来说比较麻烦点（其实也还好）。