众所周知,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>
- <!-- 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"/>
- --><!--
- <