MyBatisPlus SQLSERVER2012 分页

 1.工具类重写

        修改JdbcUtils工具类

package com.daogu.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;

/**
 * 标题、简要说明. <br>
 * 类详细说明:
 * <p>
 * Copyright: Copyright (c) 2023-02-22 10:17
 * <p>
 * Company:
 * <p>
 *
 * @author 顾小俊
 * @version 1.0.0
 */

public class JdbcUtils {

	private static final Log logger = LogFactory.getLog(com.baomidou.mybatisplus.extension.toolkit.JdbcUtils.class);

	public JdbcUtils() {
	}

	public static DbType getDbType(String jdbcUrl) {
		Assert.isFalse(StringUtils.isBlank(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type", new Object[0]);
		String url = jdbcUrl.toLowerCase();
		if (!url.contains(":mysql:") && !url.contains(":cobar:")) {
			if (url.contains(":mariadb:")) {
				return DbType.MARIADB;
			} else if (url.contains(":oracle:")) {
				return DbType.ORACLE;
			} else if (!url.contains(":sqlserver:") && !url.contains(":microsoft:")) {
				if (url.contains(":postgresql:")) {
					return DbType.POSTGRE_SQL;
				} else if (url.contains(":hsqldb:")) {
					return DbType.HSQL;
				} else if (url.contains(":db2:")) {
					return DbType.DB2;
				} else if (url.contains(":sqlite:")) {
					return DbType.SQLITE;
				} else if (url.contains(":h2:")) {
					return DbType.H2;
				} else if (url.contains(":dm:")) {
					return DbType.DM;
				} else if (url.contains(":xugu:")) {
					return DbType.XU_GU;
				} else if (!url.contains(":kingbase:") && !url.contains(":kingbase8:")) {
					if (url.contains(":phoenix:")) {
						return DbType.PHOENIX;
					} else if (jdbcUrl.contains(":zenith:")) {
						return DbType.GAUSS;
					} else {
						logger.warn("The jdbcUrl is " + jdbcUrl + ", Mybatis Plus Cannot Read Database type or The Database's Not Supported!");
						return DbType.OTHER;
					}
				} else {
					return DbType.KINGBASE_ES;
				}
			} else if (url.contains("sqlserver2012")) {
				return DbType.SQL_SERVER;
			} else {
				return DbType.SQL_SERVER2005;
			}
		} else {
			return DbType.MYSQL;
		}
	}
}

2.重写PaginationInterceptor

package com.daogu.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisDefaultParameterHandler;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.core.parser.SqlInfo;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectFactory;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.IDialect;
import com.baomidou.mybatisplus.extension.toolkit.SqlParserUtils;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.*;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.mapping.*;
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 org.apache.ibatis.session.Configuration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 标题、简要说明. <br>
 * 类详细说明:
 * <p>
 * Copyright: Copyright (c) 2023-02-22 10:13
 * <p>
 * Company:
 * <p>
 *
 * @author 顾小俊
 * @version 1.0.0
 */
@Intercepts({@Signature(
        type = StatementHandler.class,
        method = "prepare",
        args = {Connection.class, Integer.class}
)})
public class MyPaginationInterceptor  extends AbstractSqlParserHandler implements Interceptor {
    protected static final Log logger = LogFactory.getLog(MyPaginationInterceptor.class);
    protected ISqlParser countSqlParser;
    protected boolean overflow = false;
    protected long limit = 500L;
    private DbType dbType;
    private IDialect dialect;
    /** @deprecated */
    @Deprecated
    protected String dialectType;
    /** @deprecated */
    @Deprecated
    protected String dialectClazz;

    public MyPaginationInterceptor() {
    }

    public String concatOrderBy(String originalSql, IPage<?> page) {
        if (CollectionUtils.isNotEmpty(page.orders())) {
            try {
                List<OrderItem> orderList = page.orders();
                Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);
                List orderByElements;
                List orderByElementsReturn;
                if (selectStatement.getSelectBody() instanceof PlainSelect) {
                    PlainSelect plainSelect = (PlainSelect)selectStatement.getSelectBody();
                    orderByElements = plainSelect.getOrderByElements();
                    orderByElementsReturn = addOrderByElements(orderList, orderByElements);
                    plainSelect.setOrderByElements(orderByElementsReturn);
                    return plainSelect.toString();
                }

                if (selectStatement.getSelectBody() instanceof SetOperationList) {
                    SetOperationList setOperationList = (SetOperationList)selectStatement.getSelectBody();
                    orderByElements = setOperationList.getOrderByElements();
                    orderByElementsReturn = addOrderByElements(orderList, orderByElements);
                    setOperationList.setOrderByElements(orderByElementsReturn);
                    return setOperationList.toString();
                }

                if (selectStatement.getSelectBody() instanceof WithItem) {
                    return originalSql;
                }

                return originalSql;
            } catch (JSQLParserException var8) {
                logger.warn("failed to concat orderBy from IPage, exception=" + var8.getMessage());
            }
        }

        return originalSql;
    }

    private static List<OrderByElement> addOrderByElements(List<OrderItem> orderList, List<OrderByElement> orderByElements) {
         orderByElements = CollectionUtils.isEmpty(orderByElements) ? new ArrayList(orderList.size()) : orderByElements;
        List<OrderByElement> orderByElementList = (List)orderList.stream().filter((item) -> {
            return StringUtils.isNotBlank(item.getColumn());
        }).map((item) -> {
            OrderByElement element = new OrderByElement();
            element.setExpression(new Column(item.getColumn()));
            element.setAsc(item.isAsc());
            element.setAscDescPresent(true);
            return element;
        }).collect(Collectors.toList());
        ((List)orderByElements).addAll(orderByElementList);
        return (List)orderByElements;
    }

    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        this.sqlParser(metaObject);
        MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
        if (SqlCommandType.SELECT == mappedStatement.getSqlCommandType() && StatementType.CALLABLE != mappedStatement.getStatementType()) {
            BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");
            Object paramObj = boundSql.getParameterObject();
            IPage<?> page = (IPage) ParameterUtils.findPage(paramObj).orElse(null);
            if (null != page && page.getSize() >= 0L) {
                if (this.limit > 0L && this.limit <= page.getSize()) {
                    this.handlerLimit(page);
                }

                String originalSql = boundSql.getSql();
                Connection connection = (Connection)invocation.getArgs()[0];
                if (page.isSearchCount() && !page.isHitCount()) {
                    SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(page.optimizeCountSql(), this.countSqlParser, originalSql);
                    this.queryTotal(sqlInfo.getSql(), mappedStatement, boundSql, page, connection);
                    if (page.getTotal() <= 0L) {
                        return null;
                    }
                }

                DbType dbType = this.dbType == null ? JdbcUtils.getDbType(connection.getMetaData().getURL()) : this.dbType;
                IDialect dialect = (IDialect) Optional.ofNullable(this.dialect).orElseGet(() -> {
                    return DialectFactory.getDialect(dbType);
                });
                String buildSql = this.concatOrderBy(originalSql, page);
                DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());
                Configuration configuration = mappedStatement.getConfiguration();
                List<ParameterMapping> mappings = new ArrayList(boundSql.getParameterMappings());
                Map<String, Object> additionalParameters = (Map)metaObject.getValue("delegate.boundSql.additionalParameters");
                model.consumers(mappings, configuration, additionalParameters);
                metaObject.setValue("delegate.boundSql.sql", model.getDialectSql());
                metaObject.setValue("delegate.boundSql.parameterMappings", mappings);
                return invocation.proceed();
            } else {
                return invocation.proceed();
            }
        } else {
            return invocation.proceed();
        }
    }

    protected void handlerLimit(IPage<?> page) {
        page.setSize(this.limit);
    }

    protected void queryTotal(String sql, MappedStatement mappedStatement, BoundSql boundSql, IPage<?> page, Connection connection) {
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            Throwable var7 = null;

            try {
                DefaultParameterHandler parameterHandler = new MybatisDefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), boundSql);
                parameterHandler.setParameters(statement);
                long total = 0L;
                ResultSet resultSet = statement.executeQuery();
                Throwable var12 = null;

                try {
                    if (resultSet.next()) {
                        total = resultSet.getLong(1);
                    }
                } catch (Throwable var37) {
                    var12 = var37;
                    throw var37;
                } finally {
                    if (resultSet != null) {
                        if (var12 != null) {
                            try {
                                resultSet.close();
                            } catch (Throwable var36) {
                                var12.addSuppressed(var36);
                            }
                        } else {
                            resultSet.close();
                        }
                    }

                }

                page.setTotal(total);
                if (this.overflow && page.getCurrent() > page.getPages()) {
                    this.handlerOverflow(page);
                }
            } catch (Throwable var39) {
                var7 = var39;
                throw var39;
            } finally {
                if (statement != null) {
                    if (var7 != null) {
                        try {
                            statement.close();
                        } catch (Throwable var35) {
                            var7.addSuppressed(var35);
                        }
                    } else {
                        statement.close();
                    }
                }

            }

        } catch (Exception var41) {
            throw ExceptionUtils.mpe("Error: Method queryTotal execution error of sql : \n %s \n", var41, new Object[]{sql});
        }
    }

    protected void handlerOverflow(IPage<?> page) {
        page.setCurrent(1L);
    }

    public Object plugin(Object target) {
        return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
    }

    public void setProperties(Properties prop) {
        String countSqlParser = prop.getProperty("countSqlParser");
        String overflow = prop.getProperty("overflow");
        String limit = prop.getProperty("limit");
        String dialectType = prop.getProperty("dialectType");
        String dialectClazz = prop.getProperty("dialectClazz");
        this.setOverflow(Boolean.parseBoolean(overflow));
        if (StringUtils.isNotBlank(countSqlParser)) {
            this.setCountSqlParser((ISqlParser)ClassUtils.newInstance(countSqlParser));
        }

        if (StringUtils.isNotBlank(dialectType)) {
            this.setDialectType(dialectType);
        }

        if (StringUtils.isNotBlank(dialectClazz)) {
            this.setDialectClazz(dialectClazz);
        }

        if (StringUtils.isNotBlank(limit)) {
            this.setLimit(Long.parseLong(limit));
        }

    }

    /** @deprecated */
    @Deprecated
    public void setDialectType(String dialectType) {
        this.setDbType(DbType.getDbType(dialectType));
    }

    /** @deprecated */
    @Deprecated
    public void setDialectClazz(String dialectClazz) {
        this.setDialect(DialectFactory.getDialect(dialectClazz));
    }

    public MyPaginationInterceptor setCountSqlParser(final ISqlParser countSqlParser) {
        this.countSqlParser = countSqlParser;
        return this;
    }

    public MyPaginationInterceptor setOverflow(final boolean overflow) {
        this.overflow = overflow;
        return this;
    }

    public MyPaginationInterceptor setLimit(final long limit) {
        this.limit = limit;
        return this;
    }

    public MyPaginationInterceptor setDbType(final DbType dbType) {
        this.dbType = dbType;
        return this;
    }

    public MyPaginationInterceptor setDialect(final IDialect dialect) {
        this.dialect = dialect;
        return this;
    }

}

其实只要将工具类的引入，引入自己重写的类，其他都不用变

3.拦截器注册配置

        拦截器注册是的时候，注册自己重写的拦截器

@Bean
    public MyPaginationInterceptor paginationInterceptor() {
        return new MyPaginationInterceptor();
    }

4:数据源配置

        jdbc的url配置加上applicationname 并取名为sqlserver2012

完美解决。

注意：使用新的分页方式,调用必须要排序。