package com.yxy.ssm.utils;
import com.baomidou.mybatisplus.entity.CountOptimize;
import com.baomidou.mybatisplus.toolkit.PluginUtils;
import com.baomidou.mybatisplus.toolkit.SqlUtils;
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.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
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;
@Intercepts({
@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})})
public class PageInterceptor implements Interceptor {
private String databaseType;//数据库类型,不同的数据库有不同的分页方法
/**
* 拦截后要执行的方法
*/
public Object intercept(Invocation invocation) throws Throwable {
//对于StatementHandler其实只有两个实现类,一个是RoutingStatementHandler,另一个是抽象类BaseStatementHandler,
//BaseStatementHandler有三个子类,分别是SimpleStatementHandler,PreparedStatementHandler和CallableStatementHandler,
//SimpleStatementHandler是用于处理Statement的,PreparedStatementHandler是处理PreparedStatement的,而CallableStatementHandler是
//处理CallableStatement的。Mybatis在进行Sql语句处理的时候都是建立的RoutingStatementHandler,而在RoutingStatementHandler里面拥有一个
//StatementHandler类型的delegate属性,RoutingStatementHandler会依据Statement的不同建立对应的BaseStatementHandler,即SimpleStatementHandler、
//PreparedStatementHandler或CallableStatementHandler,在RoutingStatementHandler里面所有StatementHandler接口方法的实现都是调用的delegate对应的方法。
//我们在PageInterceptor类上已经用@Signature标记了该Interceptor只拦截StatementHandler接口的prepare方法,又因为Mybatis只有在建立RoutingStatementHandler的时候
//是通过Interceptor的plugin方法进行包裹的,所以我们这里拦截到的目标对象肯定是RoutingStatementHandler对象。
StatementHandler delegate = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
MetaObject metaStatementHandler = SystemMetaObject.forObject(delegate);
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
//通过反射获取到当前RoutingStatementHandler对象的delegate属性
// StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
//获取到当前StatementHandler的 boundSql,这里不管是调用handler.getBoundSql()还是直接调用delegate.getBoundSql()结果是一样的,因为之前已经说过了
//RoutingStatementHandler实现的所有StatementHandler接口方法里面都是调用的delegate对应的方法。
BoundSql boundSql = delegate.getBoundSql();
//拿到当前绑定Sql的参数对象,就是我们在调用对应的Mapper映射语句时所传入的参数对象
Object obj = boundSql.getParameterObject();
//这里我们简单的通过传入的是Page对象就认定它是需要进行分页操作的。
System.out.println("invocation proceed");
if (obj instanceof Map) {
Map<String, Object> param = (Map<String, Object>) obj;
if (param.containsKey("pageNo") && param.containsKey("pageSize")) {
//通过反射获取delegate父类BaseStatementHandler的mappedStatement属性
//拦截到的prepare方法参数是一个Connection对象
Connection connection = (Connection) invocation.getArgs()[0];
//获取当前要执行的Sql语句,也就是我们直接在Mapper映射语句中写的Sql语句
String sql = boundSql.getSql();
//给当前的page参数对象设置总记录数
this.setTotalRecord(param,
mappedStatement, connection);
//获取分页Sql语句
String pageSql = this.getPageSql(param, 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语句,
* 其它的数据库都 没有进行分页
*
* @param param 分页对象
* @param sql 原sql语句
* @return
*/
private String getPageSql(Map<String, Object> param, String sql) {
StringBuffer sqlBuffer = new StringBuffer(sql);
// if ("mysql".equalsIgnoreCase(databaseType)) {
// return getMysqlPageSql(page, sqlBuffer);
// } else if ("oracle".equalsIgnoreCase(databaseType)) {
// return getOraclePageSql(page, sqlBuffer);
// }
if ("sqlServer".equalsIgnoreCase(databaseType)) {
return getSqlServerPageSql(param, sqlBuffer);
}
return sqlBuffer.toString();
}
private String getSqlServerPageSql(Map<String, Object> param, StringBuffer sqlBuffer) {
String originalSql = new String(sqlBuffer);
String pageSql = null;
int orderStartIndex = originalSql.replaceAll("(?i)ORDER\\s+BY", "ORDER BY").lastIndexOf("ORDER BY");
String orderStr = "ORDER BY n";
// 有排序,且是最外层的排序
if (orderStartIndex != -1 && originalSql.lastIndexOf(")") < orderStartIndex) {
orderStr = originalSql.substring(orderStartIndex);
}
pageSql = originalSql.replaceFirst("(?i)select", "select * from (select row_number() over(" + orderStr
+ ") as rownumber,* from ( select top " + MapUtil.ObjectToInteger("pageSize", param) * MapUtil.ObjectToInteger("pageNo", param) + " n=0,");
pageSql += ")t )tt where rownumber> " + MapUtil.ObjectToInteger("pageSize", param) * (MapUtil.ObjectToInteger("pageNo", param) - 1);
// int index = sqlBuffer.indexOf("select") + 7;
// sqlBuffer.insert(index, " row_number() over(order by id) as rownumber,");
// sqlBuffer.insert(0, "select top " + MapUtil.ObjectToInteger("pageSize", param) + " t.* from ( ").append(" ) t ")
// .append("where t.rownumber > " + MapUtil.ObjectToInteger("pageSize", param) * (MapUtil.ObjectToInteger("pageNo", param) - 1));
return pageSql;
}
/**
* 给当前的参数对象page设置总记录数
*
* @param page Mapper映射语句对应的参数对象
* @param mappedStatement Mapper映射语句
* @param connection 当前的数据库连接
*/
private void setTotalRecord(Map<String, Object> page,
MappedStatement mappedStatement, Connection connection) {
//获取对应的BoundSql,这个BoundSql其实跟我们利用StatementHandler获取到的BoundSql是同一个对象。
//delegate里面的boundSql也是通过mappedStatement.getBoundSql(paramObj)方法获取到的。
Bound