Mybatis通用分页
分页分为真分页和假分页,而 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添加一个标识和一个插件
Xml代码
<properties> <property name="dialect" value="mysql" /> </properties> <plugins> <plugin interceptor="com.iflytek.mybatis.page.interceptor.PaginationInterceptor"> </plugin> </plugins>
然后和MyBatis默认提供分页的方式一样,直接调用
Java代码
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 中,然后再需要分页的查询语句中引用,只是需要在每个模块下分页的地方都引用,相对来说比较麻烦点(其实也还好)。