个人博客

当业务数据量达到了一定程度，DBA需要合理配置数据库资源。即配置主库的机器高配置，把核心高频的数据放在主库上；把次要的数据放在从库，低配置。开源节流嘛，就这个意思。把数据放在不同的数据库里，就需要通过不同的数据源进行操作数据。

1、Maven导包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>

2、主库配置

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
@Configuration
@MapperScan(
        basePackages = "net.zhaoxiaobin.mybatis.dao.master", // Mapper映射接口，多数据源需要分开
        sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    /**
     * 数据源配置
     * 多数据源配置的时候必须要有一个主数据源，用@Primary标志该Bean
     *
     * @return
     */
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "master.datasource")
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        return dataSource;
    }

    /**
     * 事务管理器
     * 使用代理对象获取数据源创建事务管理器
     *
     * @return
     */
    @Bean
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    /**
     * 创建mybatis会话工厂
     * 指定数据源和mybatis映射文件(多数据源需要分开)
     *
     * @return
     * @throws Exception
     */
    @Bean
    @Primary
    public SqlSessionFactory masterSqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource());
        sessionFactory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
        return sessionFactory.getObject();
    }
}
1
2
3
4
5
6
7
8
9
10
11
master:
  datasource:
    url: jdbc:mysql://148.70.153.63:3306/ttms?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
    username: root
    password: password
    driverClassName: com.mysql.cj.jdbc.Driver
    initialSize: 5
    maxActive: 50
    minIdle: 0
    maxWait: 60000
    useUnfairLock: true # 禁用公平锁

3、从库配置

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
@Configuration
@MapperScan(
        basePackages = "net.zhaoxiaobin.mybatis.dao.cluster",
        sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {

    /**
     * 数据源配置
     *
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "cluster.datasource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        return dataSource;
    }

    /**
     * 事务管理器
     * 使用代理对象获取数据源创建事务管理器
     *
     * @return
     */
    @Bean
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    /**
     * 创建mybatis会话工厂
     * 指定数据源和mybatis映射文件(多数据源需要分开)
     *
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory clusterSqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource());
        sessionFactory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/cluster/*.xml"));
        return sessionFactory.getObject();
    }
}
1
2
3
4
5
6
7
8
9
10
11
cluster:
  datasource:
    url: jdbc:mysql://148.70.153.63:3306/ttms?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
    username: root
    password: password
    driverClassName: com.mysql.cj.jdbc.Driver
    initialSize: 1
    maxActive: 10
    minIdle: 0
    maxWait: 60000
    useUnfairLock: true # 禁用公平锁

4、工程结构

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
├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── zxb
        │           └── mybatis
        │               ├── MybatisMutilDataSourceApplication.java
        │               ├── config
        │               │   ├── ClusterDataSourceConfig.java
        │               │   └── MasterDataSourceConfig.java
        │               ├── dao
        │               │   ├── cluster
        │               │   │   └── AuthClientServiceDao.java
        │               │   └── master
        │               │       └── BaseUserDao.java
        │               ├── domain
        │               │   └── bo
        │               │       ├── AuthClientServiceBO.java
        │               │       └── BaseUserBO.java
        │               ├── service
        │               │   ├── MutilDatasourceService.java
        │               │   └── impl
        │               │       └── MutilDatasourceServiceImpl.java
        │               └── web
        │                   └── MutilDatasourceController.java
        └── resources
            ├── application.yml
            ├── logback-spring.xml
            ├── mapper
            │   ├── cluster
            │   │   └── authMapper.xml
            │   └── master
            │       └── userMapper.xml
            ├── mybatis-config.xml
            └── sql
                └── sql.sql

参考链接

代码地址