Mybatis分页-利用Mybatis Generator插件生成基于数据库方言的分页语句，统计记录总数

?

众所周知，Mybatis本身没有提供基于数据库方言的分页功能，而是基于JDBC的游标分页，很容易出现性能问题。网上有很多分页的解决方案，不外乎是基于Mybatis本机的插件机制，通过拦截Sql做分页。但是在像Oracle这样的数据库上，拦截器生成的Sql语句没有变量绑定，而且每次语句的都要去拦截，感觉有点浪费性能。

Mybatis Generator是Mybatis的代码生成工具，可以生成大部分的查询语句。

本文提供的分页解决方案是新增Mybatis Generator插件，在用Mybatis Generator生成Mybatis代码时，直接生成基于数据库方言的Sql语句，解决Oralce等数据库的变量绑定，且无需使用Mybatis拦截器去拦截语句判断分页。

一、编写Mybatis Generator Dialect插件

/**

 * Copyright (C) 2011 Tgwoo Inc. * http://www.tgwoo.com/ */package com.tgwoo.core.dao.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;/** * @author pan.wei * @date 2011-11-30 下午08:36:11 */public class OraclePaginationPlugin extends PluginAdapter {@Overridepublic boolean modelExampleClassGenerated(TopLevelClass topLevelClass,IntrospectedTable introspectedTable) {// add field, getter, setter for limit clauseaddPage(topLevelClass, introspectedTable, "page");return super.modelExampleClassGenerated(topLevelClass,introspectedTable);}@Overridepublic 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);}@Overridepublic 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("com.tgwoo.core.dao.pojo.Page"));CommentGenerator commentGenerator = context.getCommentGenerator();Field field = new Field();field.setVisibility(JavaVisibility.PROTECTED);field.setType(new FullyQualifiedJavaType("com.tgwoo.core.dao.pojo.Page"));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("com.tgwoo.core.dao.pojo.Page"), name));method.addBodyLine("this." + name + "=" + name + ";");commentGenerator.addGeneralMethodComment(method, introspectedTable);topLevelClass.addMethod(method);method = new Method();method.setVisibility(JavaVisibility.PUBLIC);method.setReturnType(new FullyQualifiedJavaType("com.tgwoo.core.dao.pojo.Page"));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="E:\work\eclipseWorkspace\myEclipse\CTSPMTS\WebRoot\WEB-INF\lib\ojdbc14.jar" /><context id="oracle" ><plugin type="org.mybatis.generator.plugins.CaseInsensitiveLikePlugin"></plugin> <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin> <!-- Pagination --> <plugin type="com.tgwoo.core.dao.plugin.OraclePaginationPlugin"></plugin>   <commentGenerator>     <property name="suppressDate" value="true" />      <property name="suppressAllComments" value="true" />    </commentGenerator>        <jdbcConnection driverconnectionURL="jdbc:oracle:thin:@192.168.0.2:1521:ctspmt" userId="ctspmt" password="ctspmt123" />    <javaModelGenerator targetPackage="com.tgwoo.ctspmt.model" targetProject="CTSPMTS/src" />    <sqlMapGenerator targetPackage="com.tgwoo.ctspmt.data" targetProject="CTSPMTS/src" />    <javaClientGenerator targetPackage="com.tgwoo.ctspmt.data" targetProject="CTSPMTS/src" type="XMLMAPPER" /><!--      <table schema="ctspmt" tableName="mt_e_interface_log"/>    --><!--    <table schema="ctspmt" tableName="mt_e_msg" />    <table schema="ctspmt" tableName="mt_e_msg_log" />    <table schema="ctspmt" tableName="mt_e_msg_receiver" />    <table schema="ctspmt" tableName="st_e_org" />    <table schema="ctspmt" tableName="st_e_role" />    <table schema="ctspmt" tableName="st_e_user" />    <table schema="ctspmt" tableName="mt_e_user_msg_conf" />    <table schema="ctspmt" tableName="mt_j_user_device" />    <table schema="ctspmt" tableName="st_j_user_role" />    <table schema="ctspmt" tableName="ST_E_UNIQUE_KEY" />  --><table schema="ctspmt" tableName="mt_v_msg_item" /> </context> </generatorConfiguration>

?

三、示例

/**

 * Copyright (C) 2011 Tgwoo Inc. * http://www.tgwoo.com/ */package com.tgwoo.ctspmt.test;import com.tgwoo.core.config.SpringBeanProxy;import com.tgwoo.core.dao.pojo.Page;import com.tgwoo.ctspmt.data.MtVMsgItemMapper;import com.tgwoo.ctspmt.model.MtVMsgItemExample;/** * @author pan.wei * @date 2011-11-25 下午01:26:17 */public class Test {/** * @param args */public static void main(String[] args) {//get spring mapper instance MtVMsgItemMapper mapper = SpringBeanProxy.getCtx().getBean(MtVMsgItemMapper.class);MtVMsgItemExample ex = new MtVMsgItemExample();Page page = new Page(0, 10);ex.setPage(page);ex.createCriteria().andMsgCodeEqualTo("222");// set count,up to youpage.setCount(mapper.countByExample(ex));int row = mapper.selectByExample(ex).size();System.out.println("============row:" + row + "================");}}

?

四、分页类

?

package com.tgwoo.core.dao.pojo;/** * @author pan.wei * @date 2011-12-1 上午11:36:12 */public class Page {// 分页查询开始记录位置private int begin;// 分页查看下结束位置private int end;// 每页显示记录数private int length;// 查询结果总记录数private int count;// 当前页码private int current;// 总共页数private int total;public Page() {}/** * 构造函数 *  * @param begin * @param length */public Page(int begin, int length) {this.begin = begin;this.length = length;this.end = this.begin + this.length;this.current = (int) Math.floor((this.begin * 1.0d) / this.length) + 1;}/** * @param begin * @param length * @param count */public Page(int begin, int length, int count) {this(begin, length);this.count = count;}/** * @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.current = (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.current = (int) Math.floor((this.begin * 1.0d) / this.length) + 1;}}/** * @return the count */public int getCount() {return count;}/** * @param count *            the count to set */public void setCount(int count) {this.count = count;this.total = (int) Math.floor((this.count * 1.0d) / this.length);if (this.count % this.length != 0) {this.total++;}}/** * @return the current */public int getCurrent() {return current;}/** * @param current *            the current to set */public void setCurrent(int current) {this.current = current;}/** * @return the total */public int getTotal() {if (total == 0) {return 1;}return total;}/** * @param total *            the total to set */public void setTotal(int total) {this.total = total;}}

?五、生成后的代码

1、Example代码

?

?

package com.tgwoo.ctspmt.model;import com.tgwoo.core.dao.pojo.Page;import java.math.BigDecimal;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.List;public class MtVMsgItemExample {    protected String orderByClause;    protected boolean distinct;    protected List<Criteria> oredCriteria;    protected Page page;       ...

?2、mapper.xml

?

... <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.tgwoo.ctspmt.model.MtVMsgItemExample" >    <include refid="OracleDialectPrefix" />    select    <if test="distinct" >      distinct    </if>    <include refid="Base_Column_List" />    from CTSPMT.MT_V_MSG_ITEM    <if test="_parameter != null" >      <include refid="Example_Where_Clause" />    </if>    <if test="orderByClause != null" >      order by ${orderByClause}    </if>    <include refid="OracleDialectSuffix" />  </select>...  <sql id="OracleDialectPrefix" >    <if test="page != null" >      select * from ( select row_.*, rownum rownum_ from (     </if>  </sql>  <sql id="OracleDialectSuffix" >    <if test="page != null" >      <![CDATA[ ) row_ ) where rownum_ > #{page.begin} and rownum_ <= #{page.end} ]]>    </if>  </sql>...

?

?

附件是Mybatis Generatorjar包。

其他数据库的方言可以按照Oracle的去改写，有写好的希望能共享下。

?

?

-------------------------------------------------------------------

maven管理：

1、pom.xml

<build><plugins><plugin><groupId>org.mybatis.generator</groupId><artifactId>mybatis-generator-maven-plugin</artifactId><version>1.3.1</version><executions><execution><id>Generate MyBatis Artifacts</id><goals><goal>generate</goal></goals></execution></executions><dependencies><dependency><groupId>com.oracle</groupId><artifactId>ojdbc14</artifactId><version>10.2.0.4.0</version></dependency></dependencies></plugin></plugins></build>

?

?

?

?2、generatorConfig.xml

?

<?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><context id="oracleGenerator" targetRuntime="MyBatis3"><plugin type="org.mybatis.generator.plugins.CaseInsensitiveLikePlugin"></plugin><plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin><!-- Pagination --><plugintype="com.tgwoo.test.core.dao.mybatis.generator.plugin.pagination.OraclePaginationPlugin"></plugin><!-- 取消注释 --><commentGenerator><property name="suppressDate" value="true" /><property name="suppressAllComments" value="true" /></commentGenerator><!-- 配置连接数据信息 --><jdbcConnection driveruserId="test"password="test123" /><javaTypeResolver><property name="forceBigDecimals" value="false" /></javaTypeResolver><!-- 配置自动生成的Model的保存路径与其它参数 --><javaModelGenerator targetPackage="com.tgwoo.test.dao.model"targetProject=".\src\main\java"><property name="enableSubPackages" value="false" /><property name="trimStrings" value="true" /></javaModelGenerator><!-- 配置自动生成的Mappper.xml映射的保存路径与其它参数 --><sqlMapGenerator targetPackage="com.tgwoo.test.dao"targetProject=".\src\main\resources"><property name="enableSubPackages" value="false" /></sqlMapGenerator><!-- 配置自动生成的Mappper.java接口的保存路径与其它参数 --><javaClientGenerator type="XMLMAPPER"targetPackage="com.tgwoo.test.dao" targetProject=".\src\main\java"><property name="enableSubPackages" value="false" /></javaClientGenerator><!-- 生成表对应的操作与实体对象 --><table schema="test" tableName="testTable"><columnOverride column="id" javaType="Long" /></table></context></generatorConfiguration>

?

?3、run

Goals:mybatis-generator:generate

?

4、注意事项

报插件无法找到或者无法实例化的一般是分页插件和maven插件不在同一classloader下引起的，需要在mybatis-generator-maven-plugin的dependencies中增加dependency。

4 楼 hgdakfg 2012-03-21 hellostory 写道如果是做【高级查询】分页显示，你这个如何实现？

写视图. 5 楼 panqili2120 2012-05-28 很强大 支持 呵呵，但在我们用的Page一般的begin是从1开始的， 也就是说上面的SQL的这部分也改成row_ ) where rownum_ >= #{page.begin}这样 6 楼 duohuoteng 2012-07-06 cv