众所周知,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 huangjixin
* @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> <!-- Pagination --> <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_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 huangjixin
* @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 huangjixin
* @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 --> <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> <!-- 配置自动生成的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
/
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
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.TextElement;
import org.mybatis.generator.api.dom.xml.XmlElement;
/**
* <pre>
* add pagination using mysql limit.
* This class is only used in ibator code generator.
* </pre>
*/
public class PaginationPlugin extends PluginAdapter {
@Override
public boolean modelExampleClassGenerated(TopLevelClass topLevelClass,
IntrospectedTable introspectedTable) {
// add field, getter, setter for limit clause
addLimit(topLevelClass, introspectedTable, "limitStart");
addLimit(topLevelClass, introspectedTable, "limitEnd");
return super.modelExampleClassGenerated(topLevelClass,
introspectedTable);
}
@Override
public boolean sqlMapSelectByExampleWithoutBLOBsElementGenerated(
XmlElement element, IntrospectedTable introspectedTable) {
XmlElement isParameterPresenteElemen = (XmlElement) element
.getElements().get(element.getElements().size() - 1);
XmlElement isNotNullElement = new XmlElement("isGreaterEqual"); //$NON-NLS-1$
isNotNullElement.addAttribute(new Attribute("property", "limitStart")); //$NON-NLS-1$ //$NON-NLS-2$
isNotNullElement.addAttribute(new Attribute("compareValue", "0")); //$NON-NLS-1$ //$NON-NLS-2$
isNotNullElement.addElement(new TextElement(
"limit $limitStart$ , $limitEnd$"));
isParameterPresenteElemen.addElement(isNotNullElement);
return super.sqlMapUpdateByExampleWithoutBLOBsElementGenerated(element,
introspectedTable);
}
private void addLimit(TopLevelClass topLevelClass,
IntrospectedTable introspectedTable, String name) {
CommentGenerator commentGenerator = context.getCommentGenerator();
Field field = new Field();
field.setVisibility(JavaVisibility.PROTECTED);
field.setType(FullyQualifiedJavaType.getIntInstance());
field.setName(name);
field.setInitializationString("-1");
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(FullyQualifiedJavaType
.getIntInstance(), name));
method.addBodyLine("this." + name + "=" + name + ";");
commentGenerator.addGeneralMethodComment(method, introspectedTable);
topLevelClass.addMethod(method);
method = new Method();
method.setVisibility(JavaVisibility.PUBLIC);
method.setReturnType(FullyQualifiedJavaType.getIntInstance());
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();
}
}
//
<?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">
<!-- 黄记新,mybatis底层生成器配置文件 -->
<generatorConfiguration>
<classPathEntry
location="C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.18\mysql-connector-java-5.1.18.jar" />
<!-- MyBatis3,Ibatis2Java5 -->
<context id="DB2Tables" targetRuntime="MyBatis3">
<plugin type="org.mybatis.generator.plugins.CaseInsensitiveLikePlugin"></plugin>
<plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin>
<!-- 想要mybaits代码生成器生成Example类换个名字,是通过插件实现的,即下面的这个插件配置参数很关键。 -->
<!-- <plugin type="org.mybatis.generator.plugins.RenameExampleClassPlugin"> -->
<!-- <property name="searchString" value="Example$" /> -->
<!-- <property name="replaceString" value="Criteria" /> -->
<!-- </plugin> -->
<plugin type="org.mybatis.plugin.PaginationPlugin" />
<commentGenerator>
<property name="suppressDate" value="false" />
<property name="suppressAllComments" value="true" />
</commentGenerator>
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://192.168.15.5:3307/nagios_itm" userId="e3itm"
password="e3itm">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<javaModelGenerator targetPackage="com.hnisi.e3itm.domain.config"
targetProject="src/main/java">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<sqlMapGenerator targetPackage="com.hnisi.e3itm.dao.config"
targetProject="src/main/java">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.hnisi.e3itm.dao.config" targetProject="src/main/java">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!-- <table tableName="res_host" domainObjectName="ResHost" /> -->
<!-- <table tableName="res_instance" domainObjectName="ResInstance"/> -->
<!-- <table tableName="config_index" domainObjectName="ConfigIndex"/> -->
<table tableName="config_type" domainObjectName="ConfigType"/>
</context>
</generatorConfiguration>
package org.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 黄记新
* @date 2012-11-30 下午03:36:11
*/
public class PaginationPlugin 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.start} and rownum_ <= #{page.limit} ]]>"));
//----- 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();
}
}
package com.hnisi.e3itm.base.util;
import java.util.ArrayList;
import java.util.List;
/**
* 分页查询时的辅助工具类
* @author linzongxue
*
*/
@SuppressWarnings("rawtypes")
public class Page{
protected int limit = 20; //每页显示条数
protected int start = 0; //起始行号
protected long total = -1; //总数
protected List result = new ArrayList(); //结果集
protected int no = 1; //第几页
protected int pages = 0; //总页面码, jqGrid要求给出总页数,超级无聊
//-- 访问查询结果函数 --//
/**
* 获得页内的记录列表.
*/
public List getResult() {
return result;
}
/**
* 设置页内的记录列表.
*/
public void setResult(final List result) {
this.result = result;
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
this.limit = limit;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
this.start = (no - 1) * limit;
}
public int getPages() {
return (int)((total / limit) + (total % limit > 0 ? 1 : 0));
}
}