mybatis 显示自定义多个统计_Mybatis分页-利用Mybatis Generator插件生成基于数据库方言的分页语句，统计记录总数...

众所周知，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(

" #{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 warnings) {

return true;

}

}

二、增加插件到Mybatis Generator配置文件中

三、示例

/**

* 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 oredCriteria;

protected Page page;

...

2、mapper.xml

...

select

distinct

from CTSPMT.MT_V_MSG_ITEM

order by ${orderByClause}

...

select * from ( select row_.*, rownum rownum_ from (

#{page.begin} and rownum_ <= #{page.end} ]]>

...

附件是Mybatis Generatorjar包。

其他数据库的方言可以按照Oracle的去改写，有写好的希望能共享下。

-------------------------------------------------------------------------------------------------------

maven管理：

1、pom.xml

org.mybatis.generator

mybatis-generator-maven-plugin

1.3.1

Generate MyBatis Artifacts

generate

com.oracle

ojdbc14

10.2.0.4.0

2、generatorConfig.xml

/p>

PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

type="com.tgwoo.test.core.dao.mybatis.generator.plugin.pagination.OraclePaginationPlugin">

connectionURL="jdbc:oracle:thin:@192.168.0.2:1521:test" userId="test"

password="test123" />

targetProject=".\src\main\java">

targetProject=".\src\main\resources">

targetPackage="com.tgwoo.test.dao" targetProject=".\src\main\java">

3、run

Goals:mybatis-generator:generate

4、注意事项

报插件无法找到或者无法实例化的一般是分页插件和maven插件不在同一classloader下引起的，需要在mybatis-generator-maven-plugin的dependencies中增加dependency。