在开发中,有时我们需要使用多个数据源来访问不同的数据库。而在分页查询时,我们希望能够方便地使用PageHelper
插件来处理结果集的分页逻辑。通过结合Spring Boot的双数据源功能和PageHelper的Spring Boot Starter,我们可以实现简单且高效的分页查询。
在这个功能组合中,Spring Boot的双数据源功能允许我们配置和管理多个数据源,使得我们可以轻松地访问多个数据库。而PageHelper的Spring Boot Starter则提供了与Spring Boot集成的便捷方式,自动配置了PageHelper插件,并与双数据源功能无缝集成。
通过使用这个组合,我们可以使用PageHelper提供的分页插件来处理双数据源的查询结果,以实现分页功能。我们只需要在相应的数据源上配置PageHelper的属性,然后在查询方法中使用PageHelper提供的分页方法,即可轻松地进行分页查询操作。
【重点】在多个数据源中不同的数据库分页的sql语句不一样,MySQL使用limit,Oracle使用rownum,而PageHelper为我们提供了方便,只要配置属性autoRuntimeDialect
为true
就可以完全支持不同类型数据库数据源分页了,为了实现该功能有三种方式
1、添加依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.11</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.6.11</version>
</dependency>
<!-- pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.2</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.3.0</version>
</dependency>
<!-- oracle-->
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.3.0.0</version>
</dependency>
2、不同类型数据库多数据源处理方式
SqlSessionFactoryBean时使用了PageInterceptor
插件,并设置"helperDialect"
,值为不同数据库方言:properties.setProperty("helperDialect", "数据库方言");
这样的方式能达到不同数据源分页的效果
2.1、不同的数据源使用不同的PageInterceptor过滤
2.1.1、主数据源配置
package com.hssa.shrimppaste.config;
import com.github.pagehelper.PageInterceptor;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.primary", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Primary
@Bean(name = "primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
PageInterceptor pageInterceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("helperDialect", "oracle");
pageInterceptor.setProperties(properties);
sqlSessionFactoryBean.setPlugins(pageInterceptor);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/primary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Primary
@Bean(name = "primaryTransactionManager")
public DataSourceTransactionManager primaryTransactionManager(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Primary
@Bean(name = "primarySqlSessionTemplate")
public SqlSessionTemplate primarySqlSessionTemplate(
@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.1.2、辅助数据源配置
package com.hssa.shrimppaste.config;
import com.github.pagehelper.PageInterceptor;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
/**
* 辅助数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.secondary", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
PageInterceptor pageInterceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("helperDialect", "mysql");
pageInterceptor.setProperties(properties);
sqlSessionFactoryBean.setPlugins(pageInterceptor);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/secondary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Bean(name = "secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(
@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.2、统一配置PageInterceptor
2.2.1、统一配置
@Configuration
public class PageHelperConfig {
@Bean
PageInterceptor pageInterceptor() {
PageInterceptor pageInterceptor = new PageInterceptor();
Properties properties = new Properties();
// 处理多数据源
properties.setProperty("autoRuntimeDialect", "true");
pageInterceptor.setProperties(properties);
return pageInterceptor;
}
}
2.2.2、主数据源配置
package com.hssa.shrimppaste.config;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.primary", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Primary
@Bean(name = "primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/primary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Primary
@Bean(name = "primaryTransactionManager")
public DataSourceTransactionManager primaryTransactionManager(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Primary
@Bean(name = "primarySqlSessionTemplate")
public SqlSessionTemplate primarySqlSessionTemplate(
@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.2.3、辅助数据源配置
package com.hssa.shrimppaste.config;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* 辅助数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.secondary", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/secondary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Bean(name = "secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(
@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.3、配置PageHelper支持多数据源
2.3.1、配置开启支持多数据源
pagehelper:
autoRuntimeDialect: true
2.3.2、主数据源配置
package com.hssa.shrimppaste.config;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.primary", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Primary
@Bean(name = "primarySqlSessionFactory")
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/primary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Primary
@Bean(name = "primaryTransactionManager")
public DataSourceTransactionManager primaryTransactionManager(
@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Primary
@Bean(name = "primarySqlSessionTemplate")
public SqlSessionTemplate primarySqlSessionTemplate(
@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.3.3、辅助数据源配置
package com.hssa.shrimppaste.config;
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.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* 辅助数据源配置
*
* @author 虾酱
* @since 2024/4/24
*/
@Configuration
@MapperScan(basePackages = "com.hssa.shrimppaste.mapper.secondary", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDataSourceConfig {
/**
* 主数据源配置
*
* @author 虾酱
* @since 2024/4/24
* @return 主数据源
*/
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建SqlSessionFactory数据库会话对象
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return SqlSessionFactory对象
*/
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置Mapper文件的位置
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResource("classpath:config/mappers/secondary/**/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 创建DataSourceTransactionManager数据源事务管理器
*
* @author 虾酱
* @since 2024/4/24
* @param dataSource 数据源
* @return DataSourceTransactionManager对象
*/
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager(
@Qualifier("secondaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 创建SqlSessionTemplate数据库会话模板
*
* @author 虾酱
* @since 2024/4/24
* @param sqlSessionFactory sqlSessionFactory
* @return SqlSessionTemplate对象
*/
@Bean(name = "secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(
@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
【总结】:Spring Boot的双数据源功能和PageHelper的Spring Boot Starter来实现多数据源的分页查询,需要配置PageHelper的"autoRuntimeDialect"
属性为true
在类PageAutoDialect
方法setProperties
中动态多数据源分支明确表示需要配置autoRuntimeDialect
,如未配置则不会认为是多数据源,HelperDialect 方言永远是第一次查询时的方言,当第一次查询MySQL再次查询Oracle分页时候则会使用MySQL分页方式给Oracle分页,这显然不是我们想要的。
public void setProperties(Properties properties) {
//初始化自定义AutoDialect
initAutoDialectClass(properties);
//使用 sqlserver2012 作为默认分页方式,这种情况在动态数据源时方便使用
String useSqlserver2012 = properties.getProperty("useSqlserver2012");
if (StringUtil.isNotEmpty(useSqlserver2012) && Boolean.parseBoolean(useSqlserver2012)) {
registerDialectAlias("sqlserver", SqlServer2012Dialect.class);
registerDialectAlias("sqlserver2008", SqlServerDialect.class);
}
initDialectAlias(properties);
//指定的 Helper 数据库方言,和 不同
String dialect = properties.getProperty("helperDialect");
//运行时获取数据源
String runtimeDialect = properties.getProperty("autoRuntimeDialect");
//1.动态多数据源
if (StringUtil.isNotEmpty(runtimeDialect) && "TRUE".equalsIgnoreCase(runtimeDialect)) {
this.autoDialect = false;
this.properties = properties;
}
//2.动态获取方言
else if (StringUtil.isEmpty(dialect)) {
autoDialect = true;
this.properties = properties;
}
//3.指定方言
else {
autoDialect = false;
this.delegate = instanceDialect(dialect, properties);
}
}