环境:
<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() {
}
@Bean
public 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;
}
}
// 骚起来了~~~~