分页拦截器PageInterceptor
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
* 分页拦截器,用于拦截需要进行分页查询的操作,然后对其进行分页处理
* 注意MyBatis 3.4.0 之后,StatementHandler的prepare方法做了修改,如下:
*@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class }) })
*/
@Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
private String databaseType;// 数据库类型,不同的数据库有不同的分页方法
/**
* 拦截后要执行的方法
*/
public Object intercept(Invocation invocation) throws Throwable {
RoutingStatementHandler handler = (RoutingStatementHandler) invocation
.getTarget();
StatementHandler delegate = (StatementHandler) ReflectUtil
.getFieldValue(handler, "delegate");
BoundSql boundSql = delegate.getBoundSql();
Object paramObj = boundSql.getParameterObject();
// 判断参数里是否有page对象
Pagination page = null;
if (paramObj instanceof Pagination) {
page = (Pagination) paramObj;
} else if (paramObj instanceof Map) {
for (Object arg : ((Map) paramObj).values()) {
if (arg instanceof Page<?>) {
page = (Pagination) arg;
break;
}
}
}
if (page != null) {
MappedStatement mappedStatement = (MappedStatement) ReflectUtil
.getFieldValue(delegate, "mappedStatement");
Connection connection = (Connection) invocation.getArgs()[0];
String sql = boundSql.getSql();
if (page.getTotalCount() < 0) { // 如果总数为负数表需要设置
this.setTotalRecord(paramObj, mappedStatement, connection, page);
}
// 获取分页Sql语句
String pageSql = this.getPageSql(page, sql);
// 利用反射设置当前BoundSql对应的sql属性为我们建立好的分页Sql语句
ReflectUtil.setFieldValue(boundSql, "sql", pageSql);
}
return invocation.proceed();
}
/**
* 拦截器对应的封装原始对象的方法
*/
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* 设置注册拦截器时设定的属性
*/
public void setProperties(Properties properties) {
this.databaseType = properties.getProperty("databaseType");
}
/**
* 根据page对象获取对应的分页查询Sql语句,这里只做了两种数据库类型,Mysql和Oracle 其它的数据库都 没有进行分页
*
* @param page
* 分页对象
* @param sql
* 原sql语句
* @return
*/
private String getPageSql(Pagination page, String sql) {
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();
}
/**
* 获取Mysql数据库的分页查询语句
*
* @param page
* 分页对象
* @param sqlBuffer
* 包含原sql语句的StringBuffer对象
* @return Mysql数据库分页语句
*/
private String getMysqlPageSql(Pagination page, StringBuffer sqlBuffer) {
// 计算第一条记录的位置,Mysql中记录的位置是从0开始的。
int offset = (page.getPageNo() - 1) * page.getPageCount();
sqlBuffer.append(" limit ").append(offset).append(",")
.append(page.getPageCount());
return sqlBuffer.toString();
}
/**
* 获取Oracle数据库的分页查询语句
*
* @param page
* 分页对象
* @param sqlBuffer
* 包含原sql语句的StringBuffer对象
* @return Oracle数据库的分页查询语句
*/
private String getOraclePageSql(Pagination page, StringBuffer sqlBuffer) {
// 计算第一条记录的位置,Oracle分页是通过rownum进行的,而rownum是从1开始的
int offset = (page.getPageNo() - 1) * page.getPageCount() + 1;
sqlBuffer.insert(0, "select u.*, rownum _rownum from (")
.append(") u where rownum < ")
.append(offset + page.getPageCount());
sqlBuffer.insert(0, "select * from (").append(") where _rownum >= ")
.append(offset);
// 上面的Sql语句拼接之后大概是这个样子:
// select * from (select u.*, rownum r from (select * from t_user) u
// where rownum < 31) where r >= 16
return sqlBuffer.toString();
}
/**
* 给当前的参数对象page设置总记录数
*
* @param obj
* Mapper映射语句对应的参数对象
* @param mappedStatement
* Mapper映射语句
* @param connection
* 当前的数据库连接
*/
private void setTotalRecord(Object obj, MappedStatement mappedStatement,
Connection connection, Pagination page) {
BoundSql boundSql = mappedStatement.getBoundSql(obj);
String sql = boundSql.getSql();
String countSql = this.getCountSql(sql);
List<ParameterMapping> parameterMappings = boundSql
.getParameterMappings();
BoundSql countBoundSql = new BoundSql(
mappedStatement.getConfiguration(), countSql,
parameterMappings, obj);
ReflectUtil.setFieldValue(countBoundSql, "additionalParameters",
ReflectUtil.getFieldValue(boundSql, "additionalParameters"));
ReflectUtil.setFieldValue(countBoundSql, "metaParameters",
ReflectUtil.getFieldValue(boundSql, "metaParameters"));
ParameterHandler parameterHandler = new DefaultParameterHandler(
mappedStatement, obj, countBoundSql);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = connection.prepareStatement(countSql);
parameterHandler.setParameters(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
int totalRecord = rs.getInt(1);
// 给当前的参数page对象设置总记录数
page.setTotalCount(totalRecord);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 根据原Sql语句获取对应的查询总记录数的Sql语句
*
* @param sql
* @return
*/
private String getCountSql(String sql) {
return "select count(1) from (" + sql + ") _tmp";
}
/**
* 利用反射进行操作的一个工具类
*
*/
private static class ReflectUtil {
/**
* 利用反射获取指定对象的指定属性
*
* @param obj
* 目标对象
* @param fieldName
* 目标属性
* @return 目标属性的值
*/
public static Object getFieldValue(Object obj, String fieldName) {
Object result = null;
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
field.setAccessible(true);
try {
result = field.get(obj);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return result;
}
/**
* 利用反射获取指定对象里面的指定属性
*
* @param obj
* 目标对象
* @param fieldName
* 目标属性
* @return 目标字段
*/
private static Field getField(Object obj, String fieldName) {
Field field = null;
for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz
.getSuperclass()) {
try {
field = clazz.getDeclaredField(fieldName);
break;
} catch (NoSuchFieldException e) {
// 这里不用做处理,子类没有该字段可能对应的父类有,都没有就返回null。
}
}
return field;
}
/**
* 利用反射设置指定对象的指定属性为指定的值
*
* @param obj
* 目标对象
* @param fieldName
* 目标属性
* @param fieldValue
* 目标值
*/
public static void setFieldValue(Object obj, String fieldName,
String fieldValue) {
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
try {
field.setAccessible(true);
field.set(obj, fieldValue);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 利用反射设置指定对象的指定属性为指定的值
*
* @param obj
* 目标对象
* @param fieldName
* 目标属性
* @param fieldValue
* 目标值
*/
public static void setFieldValue(Object obj, String fieldName,
Object fieldValue) {
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
try {
field.setAccessible(true);
field.set(obj, fieldValue);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
public String getDatabaseType() {
return databaseType;
}
public void setDatabaseType(String databaseType) {
this.databaseType = databaseType;
}
}
配置分页拦截器
import com.yuntu.commons.mybatispage.*;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
*/
@Configuration
@MapperScan("com.yuntu.core.repository")
@EnableTransactionManagement
public class MybatisConfig {
private static final String MYBATIS_CONFIG_FILE = "mybatis-config.xml";
/**
* 注册datasource,通过@ConfigurationProperties(prefix="c3p0")将properties文件中c3p0开头的属性map到datasource相应的属性上
* @return
*/
@Bean
@ConfigurationProperties(prefix="c3p0")
public ComboPooledDataSource dataSource() {
return new ComboPooledDataSource();
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource());
sqlSessionFactoryBean.setTypeAliasesPackage("com.friday.core.model");
sqlSessionFactoryBean.setConfigLocation(new ClassPathResource(MYBATIS_CONFIG_FILE));
PageInterceptor pageInterceptor = new PageInterceptor();
pageInterceptor.setDatabaseType("mysql");
sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor});
return sqlSessionFactoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dataSource());
}
}
其他支持类:
import java.util.List;
/**
* 分页基本实现
* @param <T>
*/
public class Page<T> implements Pagination {
/** 页码 */
protected int pageNo;
/** 每页记录条数 */
protected int pageCount;
/** 总页数 */
protected int totalPage;
/** 总记录条数 */
protected int totalCount = -1;
/** 用于存放查询结果 */
protected List<T> resultList;
public Page() {}
public Page(Integer pageNo, int pageCount) {
if(pageNo==null){
pageNo=1;
}
if (pageNo <= 0) {
pageNo=1;
//throw new IllegalArgumentException("pageNo must be greater than 0.");
}
if (pageCount<=0 && pageCount>50) {
pageCount=50;
//throw new IllegalArgumentException("pageCount must be greater than 0.");
}
this.pageNo = pageNo;
this.pageCount = pageCount;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageNo() {
return pageNo;
}
public int getPageCount() {
return pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
if (totalCount < 0) { // 如果总数为负数, 表未设置
totalPage = 0;
} else { // 计算总页数
totalPage = (totalCount / pageCount) + (totalCount % pageCount == 0 ? 0 : 1);
}
}
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
public int getTotalPage() {
return totalPage;
}
}
/**
* 分页,在分页挡截中使用
*
*/
public interface Pagination {
/**
* @return 当前页码
*/
int getPageNo();
/**
* @return 每页记录数
*/
int getPageCount();
/**
* @return 总记录数: 负数表尚未设置, 挡截器会使用count语句统计总数; 0或正整数表总数已设置, 挡截器将不会统计总数.
*/
int getTotalCount();
/**
* @param totalCount 设置记录总数
*/
void setTotalCount(int totalCount);
}
使用:
在mapper接口中:
List<LibraryBookResult> getLibraryStatics(@Param("libraryBooksDto") LibraryBooksCollectDto libraryBooksDto, Pagination pagination);
在mapper.xml中不用关心分页情况
在业务代码中使用:
Page<DepositLogDto> page = new Page<DepositLogDto>(pageNo, pageCount);
List<DepositLogDto> result = accountTransLogRepository.findAccountTransLog(idCard, page);