众所周知,Mybatis本身没有提供基于数据库方言的分页功能,而是基于JDBC的游标分页,很容易出现性能问题。网上有很多分页的解决方案,不外乎是基于Mybatis本机的插件机制,通过拦截Sql做分页。但是在像Oracle这样的数据库上,拦截器生成的Sql语句没有变量绑定,而且每次语句的都要去拦截,感觉有点浪费性能。
Mybatis Generator是Mybatis的代码生成工具,可以生成大部分的查询语句。
本文提供的分页解决方案是新增Mybatis Generator插件,在用Mybatis Generator生成Mybatis代码时,直接生成基于数据库方言的Sql语句,解决Oralce等数据库的变量绑定,且无需使用Mybatis拦截器去拦截语句判断分页。
一、编写分页对象
package com.page;
import java.util.List;
/**
* @Description: Page类
* @author BRUCE
* @date 2014年11月5日 下午8:05:07
*
* @param <T>
*/
public class Page<T> {
public static final int DEFAULT_PAGE_SIZE = 15;
// 分页查询开始记录位置
private int begin;
// 分页查看下结束位置
private int end;
// 每页显示记录数
private int length;
// 查询结果总记录数
private int totalRecords;
// 当前页码
private int currentPage;
// 总共页数
private int totalPage;
//对应的当前页记录
private List<T> resultList;
public Page() {
this.currentPage = 1;
this.length = DEFAULT_PAGE_SIZE;
this.begin = (getCurrentPage() - 1) * getLength();
this.end = getCurrentPage() * getLength();
}
/**
* 构造函数
*
* @param begin
* @param length
*/
public Page(int begin, int length) {
this.begin = begin;
this.length = length;
this.end = this.begin + this.length;
this.currentPage = (int) Math.floor((this.begin * 1.0d) / this.length) + 1;
}
/**
* @param begin
* @param length
* @param totalRecords
*/
public Page(int begin, int length, int totalRecords) {
this(begin, length);
this.totalRecords = totalRecords;
}
/**
* 设置页数,自动计算数据范围.
*
* @param currentPage
*/
public Page(int currentPage) {
this.currentPage = currentPage;
currentPage = currentPage > 0 ? currentPage : 1;
this.begin = this.length * (currentPage - 1);
this.end = this.length * currentPage;
}
/**
* @return the begin
*/
public int getBegin() {
return begin;
}
/**
* @return the end
*/
public int getEnd() {
return end;
}
/**
* @param end
* the end to set
*/
public void setEnd(int end) {
this.end = end;
}
/**
* @param begin
* the begin to set
*/
public void setBegin(int begin) {
this.begin = begin;
if (this.length != 0) {
this.currentPage = (int) Math.floor((this.begin * 1.0d) / this.length) + 1;
}
}
/**
* @return the length
*/
public int getLength() {
return length;
}
/**
* @param length
* the length to set
*/
public void setLength(int length) {
this.length = length;
if (this.begin != 0) {
this.currentPage = (int) Math.floor((this.begin * 1.0d) / this.length) + 1;
}
}
/**
* @return the totalRecords
*/
public int getTotalRecords() {
return totalRecords;
}
/**
* @param totalRecords
* the totalRecords to set
*/
public void setTotalRecords(int totalRecords) {
this.totalRecords = totalRecords;
this.totalPage = (int) Math.floor((this.totalRecords * 1.0d) / this.length);
if (this.totalRecords % this.length != 0) {
this.totalPage++;
}
}
/**
* @return the currentPage
*/
public int getCurrentPage() {
return currentPage;
}
/**
* @param currentPage
* the currentPage to set
*/
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
this.currentPage = currentPage;
currentPage = currentPage > 0 ? currentPage : 1;
this.begin = this.length * (currentPage - 1);
this.end = this.length * currentPage;
}
/**
* @return the totalPage
*/
public int getTotalPage() {
if (totalPage == 0) {
return 1;
}
return totalPage;
}
/**
* @param totalPage
* the totalPage to set
*/
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
@Override
public String toString() {
final StringBuilder builder = new StringBuilder("begin=").append(begin).append(", end=")
.append(end).append(", length=").append(length).append(", totalRecords=").append(
totalRecords).append(", currentPage=").append(currentPage).append(", totalPage=")
.append(totalPage);
return builder.toString();
}
}
二、编写Mybatis Generator Oracle Dialect插件
package com.page.plugin;
import java.util.List;
import org.mybatis.generator.api.CommentGenerator;
import org.mybatis.generator.api.IntrospectedTable;
import org.mybatis.generator.api.PluginAdapter;
import org.mybatis.generator.api.dom.java.Field;
import org.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
import org.mybatis.generator.api.dom.java.JavaVisibility;
import org.mybatis.generator.api.dom.java.Method;
import org.mybatis.generator.api.dom.java.Parameter;
import org.mybatis.generator.api.dom.java.TopLevelClass;
import org.mybatis.generator.api.dom.xml.Attribute;
import org.mybatis.generator.api.dom.xml.Document;
import org.mybatis.generator.api.dom.xml.TextElement;
import org.mybatis.generator.api.dom.xml.XmlElement;
/**
* @Description: Myibatis对应的Oracle分页插件
* @author BRUCE
* @date 2014年11月5日 下午5:48:40
*
*/
public class OraclePaginationPlugin extends PluginAdapter {
/**
* Page类所在的类路径:其中Page类中一定要有begin和end属性(即:开始记录位置和结束记录位置)
*/
public static final String pageClassPath = "com.page.Page";
@Override
public boolean modelExampleClassGenerated(TopLevelClass topLevelClass,
IntrospectedTable introspectedTable) {
// add field, getter, setter for limit clause
addPage(topLevelClass, introspectedTable, "page");
return super.modelExampleClassGenerated(topLevelClass,
introspectedTable);
}
@Override
public boolean sqlMapDocumentGenerated(Document document,
IntrospectedTable introspectedTable) {
XmlElement parentElement = document.getRootElement();
// 产生分页语句前半部分
XmlElement paginationPrefixElement = new XmlElement("sql");
paginationPrefixElement.addAttribute(new Attribute("id",
"OracleDialectPrefix"));
XmlElement pageStart = new XmlElement("if");
pageStart.addAttribute(new Attribute("test", "page != null"));
pageStart.addElement(new TextElement(
"select * from ( select row_.*, rownum rownum_ from ( "));
paginationPrefixElement.addElement(pageStart);
parentElement.addElement(paginationPrefixElement);
// 产生分页语句后半部分
XmlElement paginationSuffixElement = new XmlElement("sql");
paginationSuffixElement.addAttribute(new Attribute("id",
"OracleDialectSuffix"));
XmlElement pageEnd = new XmlElement("if");
pageEnd.addAttribute(new Attribute("test", "page != null"));
pageEnd.addElement(new TextElement(
"<![CDATA[ ) row_ ) where rownum_ > #{page.begin} and rownum_ <= #{page.end} ]]>"));
paginationSuffixElement.addElement(pageEnd);
parentElement.addElement(paginationSuffixElement);
return super.sqlMapDocumentGenerated(document, introspectedTable);
}
@Override
public boolean sqlMapSelectByExampleWithoutBLOBsElementGenerated(
XmlElement element, IntrospectedTable introspectedTable) {
XmlElement pageStart = new XmlElement("include"); //$NON-NLS-1$
pageStart.addAttribute(new Attribute("refid", "OracleDialectPrefix"));
element.getElements().add(0, pageStart);
XmlElement isNotNullElement = new XmlElement("include"); //$NON-NLS-1$
isNotNullElement.addAttribute(new Attribute("refid",
"OracleDialectSuffix"));
element.getElements().add(isNotNullElement);
return super.sqlMapUpdateByExampleWithoutBLOBsElementGenerated(element,
introspectedTable);
}
/**
* @param topLevelClass
* @param introspectedTable
* @param name
*/
private void addPage(TopLevelClass topLevelClass,
IntrospectedTable introspectedTable, String name) {
topLevelClass
.addImportedType(new FullyQualifiedJavaType(pageClassPath));
CommentGenerator commentGenerator = context.getCommentGenerator();
Field field = new Field();
field.setVisibility(JavaVisibility.PROTECTED);
field.setType(new FullyQualifiedJavaType(pageClassPath));
field.setName(name);
commentGenerator.addFieldComment(field, introspectedTable);
topLevelClass.addField(field);
char c = name.charAt(0);
String camel = Character.toUpperCase(c) + name.substring(1);
Method method = new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setName("set" + camel);
method.addParameter(new Parameter(new FullyQualifiedJavaType(
pageClassPath), name));
method.addBodyLine("this." + name + "=" + name + ";");
commentGenerator.addGeneralMethodComment(method, introspectedTable);
topLevelClass.addMethod(method);
method = new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setReturnType(new FullyQualifiedJavaType(pageClassPath));
method.setName("get" + camel);
method.addBodyLine("return " + name + ";");
commentGenerator.addGeneralMethodComment(method, introspectedTable);
topLevelClass.addMethod(method);
}
/**
* This plugin is always valid - no properties are required
*/
public boolean validate(List<String> warnings) {
return true;
}
}
三、编写Mybatis Generator MySQL Dialect插件
package com.page.plugin;
import java.util.List;
import org.mybatis.generator.api.CommentGenerator;
import org.mybatis.generator.api.IntrospectedTable;
import org.mybatis.generator.api.PluginAdapter;
import org.mybatis.generator.api.dom.java.Field;
import org.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
import org.mybatis.generator.api.dom.java.JavaVisibility;
import org.mybatis.generator.api.dom.java.Method;
import org.mybatis.generator.api.dom.java.Parameter;
import org.mybatis.generator.api.dom.java.TopLevelClass;
import org.mybatis.generator.api.dom.xml.Attribute;
import org.mybatis.generator.api.dom.xml.TextElement;
import org.mybatis.generator.api.dom.xml.XmlElement;
/**
* @Description: Myibatis对应的MySQL分页插件
* @author BRUCE
* @date 2014年11月5日 下午8:04:23
*
*/
public final class MySQLPaginationPlugin extends PluginAdapter {
/**
* Page类所在的类路径:其中Page类中一定要有begin和length属性(即:开始记录位置和每页的记录数)
*/
public static final String pageClassPath = "com.page.Page";
@Override
public boolean modelExampleClassGenerated(TopLevelClass topLevelClass,
IntrospectedTable introspectedTable) { // add field, getter, setter for limit clause
addPage(topLevelClass, introspectedTable, "page"); return super.modelExampleClassGenerated(topLevelClass, introspectedTable);
} @Override
public boolean sqlMapSelectByExampleWithoutBLOBsElementGenerated(XmlElement element,
IntrospectedTable introspectedTable) {
XmlElement page = new XmlElement("if");
page.addAttribute(new Attribute("test", "page != null"));
page.addElement(new TextElement("limit #{page.begin} , #{page.length}"));
element.addElement(page); return super.sqlMapUpdateByExampleWithoutBLOBsElementGenerated(element, introspectedTable);
} /**
* @param topLevelClass
* @param introspectedTable
* @param name
*/
private void addPage(TopLevelClass topLevelClass, IntrospectedTable introspectedTable,
String name) {
topLevelClass.addImportedType(new FullyQualifiedJavaType(pageClassPath));
CommentGenerator commentGenerator = context.getCommentGenerator();
Field field = new Field();
field.setVisibility(JavaVisibility.PROTECTED);
field.setType(new FullyQualifiedJavaType(pageClassPath));
field.setName(name);
commentGenerator.addFieldComment(field, introspectedTable);
topLevelClass.addField(field); char c = name.charAt(0);
String camel = Character.toUpperCase(c) + name.substring(1);
Method method = new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setName("set" + camel);
method.addParameter(new Parameter(new FullyQualifiedJavaType(pageClassPath), name));
method.addBodyLine("this." + name + "=" + name + ";");
commentGenerator.addGeneralMethodComment(method, introspectedTable);
topLevelClass.addMethod(method);
method = new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setReturnType(new FullyQualifiedJavaType(pageClassPath));
method.setName("get" + camel);
method.addBodyLine("return " + name + ";");
commentGenerator.addGeneralMethodComment(method, introspectedTable);
topLevelClass.addMethod(method);
} /**
* This plugin is always valid - no properties are required
*/
public boolean validate(List<String> warnings) {
return true;
}
}
四、增加插件到Mybatis Generator配置文件中
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration >
<classPathEntry location="D:/ojdbc5-11.2.0.2.0.jar" />
<context id="context1" >
<plugin type="org.mybatis.generator.plugins.CaseInsensitiveLikePlugin"></plugin>
<plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin>
<!-- Pagination:Oracle -->
<plugin type="com.page.plugin.OraclePaginationPlugin"></plugin>
<!-- Pagination:MySql -->
<!-- <plugin type="com.page.plugin.MySQLPaginationPlugin"></plugin> -->
<!-- 取消注释 -->
<commentGenerator>
<property name="suppressDate" value="true" />
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!-- 配置连接数据信息 -->
<jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver" connectionURL="jdbc:oracle:thin:@192.168.1.233:1521:orcl" userId="cmss" password="cmss" />
<!-- 配置自动生成的Model的保存路径与其它参数 -->
<javaModelGenerator targetPackage="com.entity.main" targetProject="MyIbatisGeneratorForPage\src\main\java" />
<!-- 配置自动生成的Mappper.xml映射的保存路径与其它参数 -->
<sqlMapGenerator targetPackage="config.myibatis.xml" targetProject="MyIbatisGeneratorForPage\src\main\resources" />
<!-- 配置自动生成的Mappper.java接口的保存路径与其它参数 -->
<javaClientGenerator targetPackage="com.dao" targetProject="MyIbatisGeneratorForPage\src\main\java" type="XMLMAPPER" />
<!-- <table schema="" tableName="T_SYS_USER"></table>
<table schema="" tableName="T_SYS_ROLE"></table>
<table schema="" tableName="T_SYS_ORG"></table>
<table schema="" tableName="T_SYS_ROLE_RES"></table>
<table schema="" tableName="T_SYS_RESOURCE"></table>
<table schema="" tableName="T_SYS_USER_ROLE"></table>
<table schema="" tableName="T_SYS_CONFIG"></table>
<table schema="" tableName="T_SYS_LOG"></table>
<table schema="" tableName="T_SYS_CODE"></table>
<table schema="" tableName="T_CORE_PARAM"></table>
-->
<!-- 生成表对应的操作与实体对象 -->
<table schema="" tableName="T_CORE_ORG_HIS">
<columnOverride column="id" javaType="Long" />
</table>
</context>
</generatorConfiguration>
五、测试
import com.jiuyu.cms.entity.main.TCoreOrgExample;
import com.jiuyu.cms.entity.main.TCoreOrgexampleample;
import com.jiuyu.cms.entity.main.TCoreOrgexampleample.Criteria;
import com.jiuyv.util.Page;
import com.jiuyv.Dao.TCoreOrgMapper;
/**
* @Description: 测试类
* @author BRUCE
* @date 2014年11月5日 下午8:30:50
*
*/
public class Test {
/**
* @param args
*/
public static void main(String[] args) {
//get spring mapper instance
TCoreOrgMapper mapper = SpringBeanProxy.getCtx().getBean(
TCoreOrgMapper.class);
Page page = new Page(0, 10);
TCoreOrgExample example = new TCoreOrgExample();
Criteria criteria = example.createCriteria();
criteria.andMsgCodeEqualTo("222");
criteria.andOrgIdLike("%8888888888888%");
example.setPage(page);
example.setOrderByClause("LAST_UPD_TM DESC");//根据LAST_UPD_TM排序
// set count,up to you
page.setTotalRecords(mapper.countByexample(example));
page.setResultList(mapper.selectByexample(example));
int row = mapper.selectByexample(example).size();
System.out.println("============row:" + row + "================");
}
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------
maven管理pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.jiuyu</groupId>
<artifactId>CmsConsole</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>CmsConsole Maven Webapp</name>
<url>http://maven.apache.org</url>
<repositories>
<repository>
<id>repo1</id>
<name>maven.org</name>
<url>http://repo1.maven.org/maven2/</url>
</repository>
<repository>
<id>jboss</id>
<name>jboss</name>
<url>http://repository.jboss.org/maven2/</url>
</repository>
<repository>
<id>geotools</id>
<name>geotools</name>
<url>http://maven.geotools.fr/repository/</url>
</repository>
<repository>
<id>jahia</id>
<name>jahia</name>
<url>http://maven.jahia.org/maven2/</url>
</repository>
<repository>
<id>vars</id>
<name>vars</name>
<url>http://vars.sourceforge.net/maven2/</url>
</repository>
<repository>
<id>jasper</id>
<name>jasper</name>
<url>http://jasperreports.sourceforge.net/maven2/</url>
</repository>
<repository>
<id>mirrors.ibiblio.org</id>
<name>mirrors.ibiblio.org</name>
<url>http://mirrors.ibiblio.org/pub/mirrors/maven2/</url>
</repository>
<repository>
<id>apache.nexus</id>
<name>ASF Nexus Staging</name>
<url>https://repository.apache.org/content/groups/staging/</url>
</repository>
<repository>
<id>compass-project.org</id>
<name>Compass</name>
<url>http://repo.compass-project.org</url>
</repository>
<repository>
<id>atlassian</id>
<name>atlassian</name>
<url>http://repository.atlassian.com/maven2</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</project>