使用Spring Boot双数据源和PageHelper实现无缝分页查询

在开发中，有时我们需要使用多个数据源来访问不同的数据库。而在分页查询时，我们希望能够方便地使用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.bootgroupId>
    <artifactId>spring-boot-starter-webartifactId>
    <version>2.6.11version>
dependency>
<dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-aopartifactId>
    <version>2.6.11version>
dependency>

<dependency>
   <groupId>com.github.pagehelpergroupId>
   <artifactId>pagehelper-spring-boot-starterartifactId>
   <version>1.4.2version>
dependency>

<dependency>
    <groupId>com.mysqlgroupId>
    <artifactId>mysql-connector-jartifactId>
    <version>8.3.0version>
dependency>

<dependency>
    <groupId>com.oracle.ojdbcgroupId>
    <artifactId>ojdbc8artifactId>
    <version>19.3.0.0version>
dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

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;
      }
  }
1
2
3
4
5
6
7
8
9
10
11
12

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

2.3、配置PageHelper支持多数据源

2.3.1、配置开启支持多数据源

  pagehelper:
    autoRuntimeDialect: true
1
2

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

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);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

【总结】：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);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30