拦截器分页的原理
分页拦截器，用于拦截需要进行分页查询的操作，然后对其进行分页处理。 利用拦截器实现Mybatis分页的原理： 要利用JDBC对数据库进行操作就必须要有一个对应的Statement对象 ，Mybatis在执行Sql语句前就会产生一个包含Sql语句的Statement对象，而且对应的Sql语句 是在Statement之前产生的，所以我们就可以在它生成Statement之前对用来生成Statement的Sql语句下手。在Mybatis中Statement语句是通过RoutingStatementHandler对象的prepare方法生成的。所以利用拦截器实现Mybatis分页的一个思路就是拦截StatementHandler接口的prepare方法，然后在拦截器方法中把Sql语句改成对应的分页查询Sql语句，之后再调用StatementHandler对象的prepare方法，即调用invocation.proceed()。对于分页而言，在拦截器里面我们还需要做的一个操作就是统计满足当前条件的记录一共有多少，这是通过获取到了原始的Sql语句后，把它改为对应的统计语句再利用Mybatis封装好的参数和设置参数的功能把Sql语句中的参数进行替换，之后再执行查询记录数的Sql语句进行总记录数的统计。
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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0
</modelVersion>
<groupId>cn.wangh
</groupId>
<artifactId>page
</artifactId>
<version>0.0.1-SNAPSHOT
</version>
<packaging>war
</packaging>
<name>page
</name>
<description/>
<properties>
<webVersion>3.0
</webVersion>
<project.build.sourceEncoding>UTF-8
</project.build.sourceEncoding>
<spring.version>4.1.0.RELEASE
</spring.version>
<slf4j.version>1.6.6
</slf4j.version>
<log4j.version>1.2.12
</log4j.version>
<junit.version>4.10
</junit.version>
<mybatis.version>3.2.1
</mybatis.version>
<mybatis.generator.generatorConfig.xml>${basedir}/src/test/resources/generatorConfig.xml
</mybatis.generator.generatorConfig.xml>
<mybatis.generator.generatorConfig.properties>file:///${basedir}/src/test/resources/generatorConfig.properties
</mybatis.generator.generatorConfig.properties>
<plugin.mybatis.generator>1.3.1
</plugin.mybatis.generator>
<plugin.maven-compiler>3.1
</plugin.maven-compiler>
<plugin.maven-surefire>2.18.1
</plugin.maven-surefire>
<skipTests>true
</skipTests>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-core
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-webmvc
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-context
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-context-support
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-aop
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-aspects
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-tx
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-jdbc
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-web
</artifactId>
<version>${spring.version}
</version>
</dependency>
<dependency>
<groupId>junit
</groupId>
<artifactId>junit
</artifactId>
<version>${junit.version}
</version>
<scope>test
</scope>
</dependency>
<dependency>
<groupId>log4j
</groupId>
<artifactId>log4j
</artifactId>
<version>${log4j.version}
</version>
</dependency>
<dependency>
<groupId>org.slf4j
</groupId>
<artifactId>slf4j-api
</artifactId>
<version>${slf4j.version}
</version>
</dependency>
<dependency>
<groupId>org.slf4j
</groupId>
<artifactId>slf4j-log4j12
</artifactId>
<version>${slf4j.version}
</version>
</dependency>
<dependency>
<groupId>org.springframework
</groupId>
<artifactId>spring-test
</artifactId>
<version>${spring.version}
</version>
<scope>test
</scope>
</dependency>
<dependency>
<groupId>org.mybatis
</groupId>
<artifactId>mybatis
</artifactId>
<version>${mybatis.version}
</version>
</dependency>
<dependency>
<groupId>org.mybatis
</groupId>
<artifactId>mybatis-spring
</artifactId>
<version>1.2.0
</version>
</dependency>
<dependency>
<groupId>mysql
</groupId>
<artifactId>mysql-connector-java
</artifactId>
<version>5.1.29
</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator
</groupId>
<artifactId>mybatis-generator-core
</artifactId>
<version>1.3.2
</version>
<type>jar
</type>
<scope>test
</scope>
</dependency>
<dependency>
<groupId>org.apache.ibatis
</groupId>
<artifactId>ibatis-sqlmap
</artifactId>
<version>3.0-beta-10
</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator
</groupId>
<artifactId>mybatis-generator-maven-plugin
</artifactId>
<version>${plugin.mybatis.generator}
</version>
<dependencies>
<dependency>
<groupId>cn.easted.edm
</groupId>
<artifactId>pagingPlugin
</artifactId>
<version>0.0.1-SNAPSHOT
</version>
</dependency>
</dependencies>
<configuration>
<configurationFile>${mybatis.generator.generatorConfig.xml}
</configurationFile>
<overwrite>true
</overwrite>
<verbose>true
</verbose>
</configuration>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin
</artifactId>
<version>2.3.2
</version>
<configuration>
<source>1.8
</source>
<target>1.8
</target>
</configuration>
</plugin>
<plugin>
<artifactId>maven-war-plugin
</artifactId>
<version>2.6
</version>
<configuration>
<failOnMissingWebXml>false
</failOnMissingWebXml>
</configuration>
</plugin>
</plugins>
</build>
</project>
create table person
(
id integer not null,
name varchar(20),
sex varchar(20),
primary key (id)
);
spring-mvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties
</value>
</list>
</property>
</bean>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="cn/wangh/mapping/*.xml"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="cn.wangh.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean>
<context:component-scan base-package="cn.wangh"/>
</beans>
mybatis-config
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<plugins>
<plugin interceptor="cn.wangh.page.PageInterceptor">
<property name="databaseType" value="mysql"/>
</plugin>
</plugins>
</configuration>
jdbc.properties
jdbc
.driverClassName=
com.mysql.jdbc.Driver
jdbc
.url=jdbc:mysql://localhost:
3306/dbgirl
jdbc
.username=root
jdbc
.password=qsqqsq
PageInterceptor.java
package cn.wangh.page;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import cn.wangh.model.Page;
/**
* 分页拦截器
*/
@Intercepts({
@Signature(type=StatementHandler.class,method=
"prepare",args={Connection.class})})
public class PageInterceptor implements Interceptor {
private String databaseType;
@Override
public Object
intercept(Invocation invocation)
throws Throwable {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY);
MappedStatement mappedStatement = (MappedStatement)metaObject.getValue(
"delegate.mappedStatement");
String id = mappedStatement.getId();
if(id.matches(
".+ByPage$")) {
BoundSql boundSql = statementHandler.getBoundSql();
String sql = boundSql.getSql();
String countSql =
"select count(*) from (" + sql +
") table_count";
Connection connection = (Connection)invocation.getArgs()[
0];
PreparedStatement countStatement = connection.prepareStatement(countSql);
ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue(
"delegate.parameterHandler");
parameterHandler.setParameters(countStatement);
ResultSet rs = countStatement.executeQuery();
Map<?,?> parameter = (Map<?,?>)boundSql.getParameterObject();
Page page = (Page)parameter.get(
"page");
if(rs.next()) {
page.setTotalNumber(rs.getInt(
1));
}
String pageSql = getPageSql(page, sql);
metaObject.setValue(
"delegate.boundSql.sql", pageSql);
}
return invocation.proceed();
}
@Override
public Object
plugin(Object target) {
return Plugin.wrap(target,
this);
}
@Override
public void setProperties(Properties properties) {
this.databaseType = properties.getProperty(
"databaseType");
}
/**
* 根据page对象获取对应的分页查询Sql语句，这里只做了两种数据库类型，Mysql和Oracle 其它的数据库都没有进行分页
*/
private String
getPageSql(Page page, String sql) {
final StringBuffer sqlBuffer =
new StringBuffer(sql);
if (
"mysql".equalsIgnoreCase(databaseType)) {
return getMysqlPageSql(page, sqlBuffer);
}
else if (
"oracle".equalsIgnoreCase(databaseType)) {
return getOraclePageSql(page, sqlBuffer);
}
return sqlBuffer.toString();
}
/**
* 获取Oracle数据库的分页查询语句
*/
private String
getOraclePageSql(Page page, StringBuffer sqlBuffer) {
final int offset = (page.getCurrentPage() -
1) * page.getDbNumber() +
1;
sqlBuffer.insert(
0,
"select u.*, rownum r from (").append(
") u where rownum < ")
.append(offset + page.getDbNumber());
sqlBuffer.insert(
0,
"select * from (").append(
") where r >= ").append(offset);
return sqlBuffer.toString();
}
/**
* 获取Mysql数据库的分页查询语句
*/
private String
getMysqlPageSql(Page page, StringBuffer sqlBuffer) {
sqlBuffer.append(
" limit ").append(page.getDbIndex()).append(
",").append(page.getDbNumber());
return sqlBuffer.toString();
}
}
TPerson实体类
package cn.wangh.model;
public class TPerson {
private Integer id;
private String name;
private String sex;
public Integer
getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String
getName() {
return name;
}
public void setName(String name) {
this.name = name ==
null ?
null : name.trim();
}
public String
getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex ==
null ?
null : sex.trim();
}
@Override
public String
toString() {
return "TPerson [id=" + id +
", name=" + name +
", sex=" + sex +
"]";
}
}
Page.java
package cn.wangh.model;
public class Page {
/**
* 总条数
*/
private int totalNumber;
/**
* 当前第几页
*/
private int currentPage;
/**
* 总页数
*/
private int totalPage;
/**
* 每页显示条数
*/
private int pageNumber =
5;
/**
* 数据库中limit的参数，从第几条开始取
*/
private int dbIndex;
/**
* 数据库中limit的参数，一共取多少条
*/
private int dbNumber;
/**
* 根据当前对象中属性值计算并设置相关属性值
*/
public void count() {
int totalPageTemp =
this.totalNumber /
this.pageNumber;
int plus = (
this.totalNumber %
this.pageNumber) ==
0 ?
0 :
1;
totalPageTemp = totalPageTemp + plus;
if(totalPageTemp <=
0) {
totalPageTemp =
1;
}
this.totalPage = totalPageTemp;
if(
this.totalPage <
this.currentPage) {
this.currentPage =
this.totalPage;
}
if(
this.currentPage <
1) {
this.currentPage =
1;
}
this.dbIndex = (
this.currentPage -
1) *
this.pageNumber;
this.dbNumber =
this.pageNumber;
}
public int getTotalNumber() {
return totalNumber;
}
public void setTotalNumber(
int totalNumber) {
this.totalNumber = totalNumber;
this.count();
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(
int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(
int totalPage) {
this.totalPage = totalPage;
}
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(
int pageNumber) {
this.pageNumber = pageNumber;
this.count();
}
public int getDbIndex() {
return dbIndex;
}
public void setDbIndex(
int dbIndex) {
this.dbIndex = dbIndex;
}
public int getDbNumber() {
return dbNumber;
}
public void setDbNumber(
int dbNumber) {
this.dbNumber = dbNumber;
}
}
TPersonMapper.java
package cn.wangh.dao;
import java.util.List;
import java.util.Map;
import cn.wangh.model.TPerson;
public interface TPersonMapper {
/**
* 根据查询条件分页查询消息列表
*/
public List<TPerson>
queryMessageListByPage(Map<String,Object> parameter);
public Integer
insertSelective(TPerson person);
}
TPersonMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.wangh.dao.TPersonMapper" >
<resultMap id="BaseResultMap" type="cn.wangh.model.TPerson" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="VARCHAR" />
</resultMap>
<sql id="columns">id,name,sex
</sql>
<select id="queryMessageListByPage" parameterType="java.util.Map" resultMap="BaseResultMap">
select
<include refid="columns"/> from person
order by id
</select>
<insert id="insertSelective" parameterType="cn.wangh.model.TPerson" useGeneratedKeys="true" keyProperty="id" >
insert into person
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="sex != null" >
sex,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="sex != null" >
#{sex,jdbcType=VARCHAR},
</if>
</trim>
</insert>
</mapper>
PersonService.java
package cn.wangh.service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import cn.wangh.dao.TPersonMapper;
import cn.wangh.model.Page;
import cn.wangh.model.TPerson;
@Service
public class PersonService {
@Resource
private TPersonMapper tPersonMapper;
public List<TPerson>
queryMessageListByPage(Page page){
Map<String,Object> parameter =
new HashMap<String, Object>();
parameter.put(
"page", page);
return tPersonMapper.queryMessageListByPage(parameter);
}
public Integer
insert(TPerson person){
return tPersonMapper.insertSelective(person);
}
}
测试类
package cn
.wangh.page
import java
.util.List
import javax
.annotation.Resource
import org
.junit.Test
import org
.junit.runner.RunWith
import org
.springframework.test.context.ContextConfiguration
import org
.springframework.test.context.junit4
.SpringJUnit4ClassRunner
import cn
.wangh.model.Page
import cn
.wangh.model.TPerson
import cn
.wangh.service.PersonService
@RunWith(SpringJUnit4ClassRunner
.class) // 表示继承了SpringJUnit4ClassRunner类
@ContextConfiguration(locations = {
"classpath:mybatis-config.xml",
"classpath:spring-mvc.xml"})
public class ServiceTest {
@Resource
private PersonService personService
@Test
public void test1(){
for(int i=
0
TPerson p = new TPerson()
p
.setId(i+
1)
p
.setName(
"wh"+i)
p
.setSex(i/
2==
0?
"m":
"f")
personService
.insert(p)
}
}
@Test
public void test2(){
Page page = new Page()
page
.setDbIndex(
0)
page
.setPageNumber(
3)
List<TPerson> persons = personService
.queryMessageListByPage(page)
for(TPerson person :persons){
System
.out.println(person
.toString())
}
}
}
