mybatisplus3.5多数据源多数据库动态切换方言分页插件

环境：

<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version>
</dependency>

<!--多数据源--><dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId>      <version>3.4.0</version> </dependency>

 官方提供的分页插件使用：

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));return interceptor;
}

 问题：mysql中支持limit ?,? ,而postgresql不支持limit ?,?。

解决方案：

1、默认使用

DbType.POSTGRE_SQL 可以解决。因为mysql兼容。

2、最佳方案： 使用空参PaginationInnerInterceptor插件类。如下：

public class DynamicPaginationInnerInterceptor extends PaginationInnerInterceptor {}public class MybatisPlusDynamicAutoConfig {public MybatisPlusDynamicAutoConfig() {}@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new DynamicPaginationInnerInterceptor());return interceptor;}
}// 使用：
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;@Configuration
@MapperScan("com.xxx.xxx.mapper")
public class MyBatisPlusConfig extends MybatisPlusDynamicAutoConfig {
}

牛逼

源码核心：

/*** 获取分页方言类的逻辑** @param executor Executor* @return 分页方言类*/protected IDialect findIDialect(Executor executor) {if (dialect != null) {return dialect;}if (dbType != null) {dialect = DialectFactory.getDialect(dbType);return dialect;}return DialectFactory.getDialect(JdbcUtils.getDbType(executor));}/*** 不关闭 Connection,因为是从事务里获取的,sqlSession会负责关闭** @param executor Executor* @return DbType*/public static DbType getDbType(Executor executor) {try {Connection conn = executor.getTransaction().getConnection();return JDBC_DB_TYPE_CACHE.computeIfAbsent(conn.getMetaData().getURL(), JdbcUtils::getDbType);} catch (SQLException e) {throw ExceptionUtils.mpe(e);}}/*** 根据连接地址判断数据库类型** @param jdbcUrl 连接地址* @return ignore*/public static DbType getDbType(String jdbcUrl) {Assert.isFalse(StringUtils.isBlank(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type");String url = jdbcUrl.toLowerCase();if (url.contains(":mysql:") || url.contains(":cobar:")) {return DbType.MYSQL;} else if (url.contains(":mariadb:")) {return DbType.MARIADB;} else if (url.contains(":oracle:")) {return DbType.ORACLE;} else if (url.contains(":sqlserver:") || url.contains(":microsoft:")) {return DbType.SQL_SERVER2005;} else if (url.contains(":sqlserver2012:")) {return DbType.SQL_SERVER;} else 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 (regexFind(":dm\\\\d*:", url)) {return DbType.DM;} else if (url.contains(":xugu:")) {return DbType.XU_GU;} else if (regexFind(":kingbase\\\\d*:", url)) {return DbType.KINGBASE_ES;} else if (url.contains(":phoenix:")) {return DbType.PHOENIX;} else if (url.contains(":zenith:")) {return DbType.GAUSS;} else if (url.contains(":gbase:")) {return DbType.GBASE;} else if (url.contains(":gbasedbt-sqli:") || url.contains(":informix-sqli:")) {return DbType.GBASE_8S;} else if (url.contains(":clickhouse:")) {return DbType.CLICK_HOUSE;} else if (url.contains(":oscar:")) {return DbType.OSCAR;} else if (url.contains(":sybase:")) {return DbType.SYBASE;} else if (url.contains(":oceanbase:")) {return DbType.OCEAN_BASE;} else if (url.contains(":highgo:")) {return DbType.HIGH_GO;} else if (url.contains(":cubrid:")) {return DbType.CUBRID;} else if (url.contains(":goldilocks:")) {return DbType.GOLDILOCKS;} else if (url.contains(":csiidb:")) {return DbType.CSIIDB;} else if (url.contains(":sap:")) {return DbType.SAP_HANA;} else if (url.contains(":impala:")) {return DbType.IMPALA;} else if (url.contains(":vertica:")) {return DbType.VERTICA;} else if (url.contains(":xcloud:")) {return DbType.XCloud;} else if (url.contains(":firebirdsql:")) {return DbType.FIREBIRD;} else {logger.warn("The jdbcUrl is " + jdbcUrl + ", Mybatis Plus Cannot Read Database type or The Database's Not Supported!");return DbType.OTHER;}}// 骚起来了～～～～