从零搭建自己的SpringBoot后台框架(十一)

Hello大家好，本章我们配置多数据源功能 。有问题可以联系我mr_beany@163.com。另求各路大神指点，感谢

一：配置数据源

修改application.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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
1spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver

2spring.datasource.db1.url=jdbc:mysql://localhost:3333/demo?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false

3spring.datasource.db1.username=root

4spring.datasource.db1.password=123456

5spring.datasource.db1.initialSize=5

6spring.datasource.db1.minIdle=5

7spring.datasource.db1.maxActive=20

8#连接等待超时时间

9spring.datasource.db1.maxWait=60000

10#配置隔多久进行一次检测(检测可以关闭的空闲连接)

11spring.datasource.db1.timeBetweenEvictionRunsMillis=60000

12#配置连接在池中的最小生存时间

13spring.datasource.db1.minEvictableIdleTimeMillis=300000

14spring.datasource.db1.validationQuery=SELECT 1 FROM DUAL

15spring.datasource.db1.testWhileIdle=true

16spring.datasource.db1.testOnBorrow=false

17spring.datasource.db1.testOnReturn=false

18# 打开PSCache，并且指定每个连接上PSCache的大小

19spring.datasource.db1.poolPreparedStatements=true

20spring.datasource.db1.maxPoolPreparedStatementPerConnectionSize=20

21# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

22spring.datasource.db1.filters=stat,wall,slf4j

23# 通过connectProperties属性来打开mergeSql功能；慢SQL记录

24spring.datasource.db1.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

25

26

27spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver

28spring.datasource.db2.url=jdbc:mysql://localhost:3306/chuchen?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false

29spring.datasource.db2.username=root

30spring.datasource.db2.password=123456

31spring.datasource.db2.initialSize=5

32spring.datasource.db2.minIdle=5

33spring.datasource.db2.maxActive=20

34#连接等待超时时间

35spring.datasource.db2.maxWait=60000

36#配置隔多久进行一次检测(检测可以关闭的空闲连接)

37spring.datasource.db2.timeBetweenEvictionRunsMillis=60000

38#配置连接在池中的最小生存时间

39spring.datasource.db2.minEvictableIdleTimeMillis=300000

40spring.datasource.db2.validationQuery=SELECT 1 FROM DUAL

41spring.datasource.db2.testWhileIdle=true

42spring.datasource.db2.testOnBorrow=false

43spring.datasource.db2.testOnReturn=false

44# 打开PSCache，并且指定每个连接上PSCache的大小

45spring.datasource.db2.poolPreparedStatements=true

46spring.datasource.db2.maxPoolPreparedStatementPerConnectionSize=20

47# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

48spring.datasource.db2.filters=stat,wall,slf4j

49# 通过connectProperties属性来打开mergeSql功能；慢SQL记录

50spring.datasource.db2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000复制代码

51

注意修改为自己的数据库连接

二：创建数据源配置

MysqlDataSource1Config

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
1package com.example.demo.core.configurer.dataSource;

2

3import com.alibaba.druid.pool.DruidDataSource;

4import org.apache.ibatis.session.SqlSessionFactory;

5import org.mybatis.spring.SqlSessionFactoryBean;

6import org.springframework.beans.factory.annotation.Qualifier;

7import org.springframework.boot.context.properties.ConfigurationProperties;

8import org.springframework.context.annotation.Bean;

9import org.springframework.context.annotation.Configuration;

10import org.springframework.context.annotation.Primary;

11import org.springframework.core.io.Resource;

12import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

13import org.springframework.jdbc.datasource.DataSourceTransactionManager;

14import org.springframework.transaction.annotation.EnableTransactionManagement;

15import tk.mybatis.spring.annotation.MapperScan;

16

17import javax.sql.DataSource;

18

19@Configuration

20@EnableTransactionManagement

21@MapperScan(basePackages = {"com.example.demo.dao.db1"})

22public class MysqlDataSource1Config {

23

24    @Bean(name = "primaryDataSource")

25    //需设置主数据源

26    @Primary

27    @ConfigurationProperties(prefix="spring.datasource.db1")

28    public DataSource dataSource(){

29        //跟之前不一样了

30        return new DruidDataSource();

31    }

32

33    @Bean(name = "primaryTransactionManager")

34    @Primary

35    public DataSourceTransactionManager masterTransactionManager() {

36        return new DataSourceTransactionManager(dataSource());

37

38    }

39

40    @Bean(name="primarySqlSessionFactory")

41    @Primary

42    public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource")DataSource primaryDataSource) throws Exception {

43        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

44        sessionFactory.setDataSource(primaryDataSource);

45        Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db1/*.xml");

46        sessionFactory.setMapperLocations(mapperLocations);

47        return sessionFactory.getObject();

48    }

49}

50复制代码

51

MysqlDataSource2Config

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
1package com.example.demo.core.configurer.dataSource;

2

3

4import com.alibaba.druid.pool.DruidDataSource;

5import org.apache.ibatis.session.SqlSessionFactory;

6import org.mybatis.spring.SqlSessionFactoryBean;

7import org.springframework.boot.context.properties.ConfigurationProperties;

8import org.springframework.context.annotation.Bean;

9import org.springframework.context.annotation.Configuration;

10import org.springframework.core.io.Resource;

11import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

12import tk.mybatis.spring.annotation.MapperScan;

13

14import javax.sql.DataSource;

15

16@Configuration

17@MapperScan(basePackages = {"com.example.demo.dao.db2"}, sqlSessionFactoryRef = "secondSqlSessionFactory")

18public class MysqlDataSource2Config {

19

20    @Bean(name = "secondDataSource")

21    @ConfigurationProperties(prefix = "spring.datasource.db2")

22    public DataSource dataSource() {

23        return new DruidDataSource();

24    }

25

26    @Bean(name = "secondSqlSessionFactory")

27    public SqlSessionFactory sqlSessionFactory() throws Exception {

28        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

29        sessionFactory.setDataSource(dataSource());

30        Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml");

31        sessionFactory.setMapperLocations(mapperLocations);

32        return sessionFactory.getObject();

33    }

34}复制代码

35

三：修改目录结构

目录结构如下图，db1为主数据源，db2为从数据源，这样根据访问dao层不同，会自动进行数据源切换

四：功能测试

输入http://localhost:8080/userInfo/selectAll

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1{

2    "code": 200,

3    "data": {

4        ....省略

5        "list": [

6            {

7                "id": "1",

8                "userName": "Mr_初晨"

9            },

10            {

11                "id": "2",

12                "userName": "Mr_初晨"

13            }

14        ],

15        .....省略

16    },

17    "msg": "success"

18}复制代码

19

输入http://localhost:8080/userLabel/list

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1{

2    "code": 200,

3    "data": {

4        ....省略

5        "list": [

6             {

7                "createTime": 1523283547000,

8                "id": "24084baed3c6409a8619a635bf4428a1",

9                "labelId": "4",

10                "userId": "1"

11            }

12        ],

13        .....省略

14    },

15    "msg": "success"

16}复制代码

17

输入http://localhost:8080/druid/datasource.html

也可以看见有两个数据源

项目地址

码云地址： gitee.com/beany/mySpr…

GitHub地址： github.com/MyBeany/myS…

写文章不易，如对您有帮助，请帮忙点下star

结尾

配置多数据源功能已完成，后续功能接下来陆续更新，有问题可以联系我mr_beany@163.com。另求各路大神指点，感谢大家。