MyIbatis学习 （二）--利用Generator插件生成基于数据库方言的分页语句
来源：互联网 发布：仓库管理系统java 编辑：程序博客网 时间：2024/05/17 03:53
众所周知，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";@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(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 youpage.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>
1 0
- MyIbatis学习 （二）--利用Generator插件生成基于数据库方言的分页语句
- Mybatis分页-利用Mybatis Generator插件生成基于数据库方言的分页语句，统计记录总数
- 利用mybatis generator插件生成基于分页语句及分页解决方案
- Mybatis3分页, 基于Mybatis Generator插件生成分页语句
- Mybatis3分页, 基于Mybatis Generator插件生成MYSQL分页语句
- MyIbatis学习 （一）--使用Generator自动生成代码
- iBatis3基于方言(Dialect)的分页
- myibatis的学习
- mybatis利用generator自动生成的代码学习篇
- maven 项目（二） mybatis-generator 反向数据库生成代码
- mybatis generator 分页插件
- maven插件-利用mybatis-generator自动生成代码
- mybatis-利用mybatis-generator(maven插件)自动生成代码
- 基于FPGA的图像处理（二）--System Generator入门
- 分页插件（二）
- MyBatis Generator 以及 Generator 分页插件
- 基于SQL语句的分页
- MyBatis Generator实现分页插件
- EditText焦点的获取和TextWatcher的使用
- 开始学习python 要做web
- 与大数据相关的三个层面
- Windows程序的打包,部署(vs项目打包vs2013)
- Python学习笔记--处理log文件的简单小程序
- MyIbatis学习 （二）--利用Generator插件生成基于数据库方言的分页语句
- 深入探索 Kdump
- 差分盒维数的计算方法
- C++中string用法总结
- UI基础
- 天使迪玛利亚
- 机器视觉中常用的数据测试集 计算机视觉数据集
- 关于QT移植及触摸屏的问题
- C语言返回文件大小的功能(fseek和ftell的使用)
