MyIbatis学習(二)--Generatorプラグインを使用してデータベース方言ベースのページング文を生成
21518 ワード
周知のように、Mybatis自体はデータベース方言ベースのページング機能を提供していないが、JDBCベースのカーソルページングであり、パフォーマンスの問題が発生しやすい.ネット上にはページ分けのソリューションがたくさんありますが、Mybatis本体のプラグインメカニズムに基づいて、Sqlをブロックすることでページ分けをします.しかし、Oracleのようなデータベースでは、ブロッカーによって生成されたSql文には変数バインドがなく、文ごとにブロックされ、パフォーマンスが少し浪費されています.
Mybatis GeneratorはMybatisのコード生成ツールであり、ほとんどのクエリー文を生成することができます.
本明細書で提供するページングソリューションは、Mybatis Generatorプラグインを追加し、Mybatis GeneratorでMybatisコードを生成する際に、データベース方言ベースのSql文を直接生成し、Oralceなどのデータベースの変数バインドを解決し、Mybatisブロッカーを使用して文判定ページングをブロックする必要がない.
------------------------------------------------------------------------------------------------------------------------------------------------------------------
maven管理pom.xml:
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>