Mybatis Generator实现分页功能

it2024-12-08 17

Mybatis Generator实现分页功能

分类： IBATIS 2013-07-17 17:03 882人阅读评论(1) 收藏举报 mybatis ibatis generator page 分页众所周知，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 { @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( "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>  <plugin type="com.tgwoo.core.dao.plugin.OraclePaginationPlugin"></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.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_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 you page.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>  <plugin type="com.tgwoo.test.core.dao.mybatis.generator.plugin.pagination.OraclePaginationPlugin"></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.0.2:1521:test" userId="test" password="test123" /> <javaTypeResolver> <property name="forceBigDecimals" value="false" /> </javaTypeResolver>  <javaModelGenerator targetPackage="com.tgwoo.test.dao.model" targetProject=".\src\main\java"> <property name="enableSubPackages" value="false" /> <property name="trimStrings" value="true" /> </javaModelGenerator>  <sqlMapGenerator targetPackage="com.tgwoo.test.dao" targetProject=".\src\main\resources"> <property name="enableSubPackages" value="false" /> </sqlMapGenerator>  <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

查看文本打印 ? package org.leef.db.mybatis.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.ShellRunner; 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 { @Override public boolean modelExampleClassGenerated(TopLevelClass topLevelClass, IntrospectedTable introspectedTable) { // add field, getter, setter for limit clause addPage(topLevelClass, introspectedTable, "page"); addDialect(topLevelClass, introspectedTable, "dialect"); 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")); XmlElement pageDialect1 = new XmlElement("if"); pageDialect1.addAttribute(new Attribute("test", "dialect == 'oralce'")); pageStart.addElement(pageDialect1); pageDialect1.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")); XmlElement pageDialect2 = new XmlElement("if"); pageDialect2.addAttribute(new Attribute("test", "dialect == 'oralce'")); pageEnd.addElement(pageDialect2); pageDialect2.addElement(new TextElement( "<![CDATA[ ) row_ ) where rownum_ > #{page.begin} and rownum_ <= #{page.end} ]]>")); //----- mysql语句。 XmlElement mysqlDialect = new XmlElement("if"); mysqlDialect.addAttribute(new Attribute("test", "dialect == 'mysql'")); pageEnd.addElement(mysqlDialect); mysqlDialect.addElement(new TextElement( "limit #{page.start} , #{page.limit}")); 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 addDialect(TopLevelClass topLevelClass, IntrospectedTable introspectedTable, String name) { CommentGenerator commentGenerator = context.getCommentGenerator(); Field field = new Field(); field.setVisibility(JavaVisibility.PRIVATE); field.setType(new FullyQualifiedJavaType("String")); field.setName(name + " = \"mysql\""); 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( "String"), name)); method.addBodyLine("this." + name + "=" + name + ";"); commentGenerator.addGeneralMethodComment(method, introspectedTable); topLevelClass.addMethod(method); method = new Method(); method.setVisibility(JavaVisibility.PUBLIC); method.setReturnType(new FullyQualifiedJavaType( "String")); method.setName("get" + camel); method.addBodyLine("return " + name + ";"); commentGenerator.addGeneralMethodComment(method, introspectedTable); topLevelClass.addMethod(method); } /** * @param topLevelClass * @param introspectedTable * @param name */ private void addPage(TopLevelClass topLevelClass, IntrospectedTable introspectedTable, String name) { topLevelClass.addImportedType(new FullyQualifiedJavaType( "com.hnisi.e3itm.base.util.Page")); CommentGenerator commentGenerator = context.getCommentGenerator(); Field field = new Field(); field.setVisibility(JavaVisibility.PROTECTED); field.setType(new FullyQualifiedJavaType("com.hnisi.e3itm.base.util.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.hnisi.e3itm.base.util.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.hnisi.e3itm.base.util.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; } public static void generate() { String config = PaginationPlugin.class.getClassLoader().getResource( "generatorConfig.xml").getFile(); String[] arg = { "-configfile", config, "-overwrite" }; ShellRunner.main(arg); } public static void main(String[] args) { generate(); } }

///

mysql

转载于:https://www.cnblogs.com/isoftware/p/3750219.html

相关资源：数据结构—成绩单生成器

最新回复(0)