Springboot多数据源配置

目前，业界流行的数据操作框架是 Mybatis，那 Druid 是什么呢？ Druid 是 Java 的数据库连接池组件。Druid 能够提供强大的监控和扩展功能。比如可以监控 SQL ，在监控业务可以查询慢查询 SQL 列表等。Druid 核心主要包括三部分： 1. DruidDriver 代理 Driver，能够提供基于 Filter－Chain 模式的插件体系。 2. DruidDataSource 高效可管理的数据库连接池 3. SQLParser 当业务数据量达到了一定程度，DBA 需要合理配置数据库资源。即配置主库的机器高配置，把核心高频的数据放在主库上；把次要的数据放在低配置的从库。开源节流嘛，把数据放在不同的数据库里，就需要通过不同的数据源进行操作数据。这里我们举个 springboot-mutil-datasource 工程案例： user 用户表在主库 master 上，地址表 city 在从库 slave上。

一、数据库准备

1、主库master

CREATE DATABASE master;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `user_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名称',
  `description` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT user VALUES (1 ,'程序员','他有一个小网站 tom.com.cn');

2、从库slave

CREATE DATABASE slave;

DROP TABLE IF EXISTS `city`;
CREATE TABLE `city`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '城市编号',
  `province_id` int(10) UNSIGNED NOT NULL COMMENT '省份编号',
  `city_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市名称',
  `description` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT city VALUES (1 ,1,'北京市','tom的家在北京。');

二、工程代码配置详解

1、项目整体结构

com.carry.springboot.config.ds 包下包含了多数据源的配置，同样可以有第三个数据源，按照前几个复制即可 resources/mapper 下面有两个模块，分别是 Mybatis 不同数据源需要扫描的 mapper xml 目录

2、pom.xml依赖

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 3     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 4     <modelVersion>4.0.0</modelVersion>
 5 
 6     <groupId>com.carry.elk</groupId>
 7     <artifactId>springboot-mutil-datasource</artifactId>
 8     <version>0.0.1-SNAPSHOT</version>
 9     <packaging>jar</packaging>
10 
11     <name>springboot-mutil-datasource</name>
12     <description>Demo project for Spring Boot</description>
13 
14     <parent>
15         <groupId>org.springframework.boot</groupId>
16         <artifactId>spring-boot-starter-parent</artifactId>
17         <version>2.0.4.RELEASE</version>
18         <relativePath/> <!-- lookup parent from repository -->
19     </parent>
20 
21     <properties>
22         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
23         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
24         <java.version>1.8</java.version>
25         <druid>1.0.31</druid>
26     </properties>
27 
28     <dependencies>
29         <dependency>
30             <groupId>org.springframework.boot</groupId>
31             <artifactId>spring-boot-starter-web</artifactId>
32         </dependency>
33         <dependency>
34             <groupId>org.mybatis.spring.boot</groupId>
35             <artifactId>mybatis-spring-boot-starter</artifactId>
36             <version>1.3.2</version>
37         </dependency>
38         <!-- Druid 数据连接池依赖 -->
39         <dependency>
40             <groupId>com.alibaba</groupId>
41             <artifactId>druid</artifactId>
42             <version>${druid}</version>
43         </dependency>
44 
45         <dependency>
46             <groupId>org.springframework.boot</groupId>
47             <artifactId>spring-boot-devtools</artifactId>
48             <scope>runtime</scope>
49         </dependency>
50         <dependency>
51             <groupId>mysql</groupId>
52             <artifactId>mysql-connector-java</artifactId>
53             <scope>runtime</scope>
54         </dependency>
55         <dependency>
56             <groupId>org.springframework.boot</groupId>
57             <artifactId>spring-boot-starter-test</artifactId>
58             <scope>test</scope>
59         </dependency>
60     </dependencies>
61 
62     <build>
63         <plugins>
64             <plugin>
65                 <groupId>org.springframework.boot</groupId>
66                 <artifactId>spring-boot-maven-plugin</artifactId>
67             </plugin>
68         </plugins>
69     </build>
70 
71 
72 </project>

View Code

3、application.properties 配置两个数据源配置

## master 数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.jdbc.Driver

## slave 数据源配置
slave.datasource.url=jdbc:mysql://localhost:3306/slave?useUnicode=true&characterEncoding=utf8
slave.datasource.username=root
slave.datasource.password=123456
slave.datasource.driverClassName=com.mysql.jdbc.Driver

#热部署
spring.devtools.restart.enabled=true

4、主数据源MasterDataSourceConfig 配置

 1 package com.carry.springboot.config.ds;
 2 
 3 import javax.sql.DataSource;
 4 
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.mybatis.spring.SqlSessionFactoryBean;
 7 import org.mybatis.spring.annotation.MapperScan;
 8 import org.springframework.beans.factory.annotation.Qualifier;
 9 import org.springframework.beans.factory.annotation.Value;
10 import org.springframework.context.annotation.Bean;
11 import org.springframework.context.annotation.Configuration;
12 import org.springframework.context.annotation.Primary;
13 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
14 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
15 
16 import com.alibaba.druid.pool.DruidDataSource;
17 
18 @Configuration
19 // 扫描 Mapper 接口并容器管理
20 @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
21 public class MasterDataSourceConfig {
22 
23     // 精确到 master 目录，以便跟其他数据源隔离
24     static final String PACKAGE = "com.carry.springboot.dao.master";
25     static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
26 
27     @Value("${master.datasource.url}")
28     private String url;
29 
30     @Value("${master.datasource.username}")
31     private String user;
32 
33     @Value("${master.datasource.password}")
34     private String password;
35 
36     @Value("${master.datasource.driverClassName}")
37     private String driverClass;
38 
39     @Bean(name = "masterDataSource")
40     @Primary
41     public DataSource masterDataSource() {
42         DruidDataSource dataSource = new DruidDataSource();
43         dataSource.setDriverClassName(driverClass);
44         dataSource.setUrl(url);
45         dataSource.setUsername(user);
46         dataSource.setPassword(password);
47         return dataSource;
48     }
49 
50     @Bean(name = "masterTransactionManager")
51     @Primary
52     public DataSourceTransactionManager masterTransactionManager() {
53         return new DataSourceTransactionManager(masterDataSource());
54     }
55 
56     @Bean(name = "masterSqlSessionFactory")
57     @Primary
58     public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
59             throws Exception {
60         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
61         sessionFactory.setDataSource(masterDataSource);
62         sessionFactory.setMapperLocations(
63                 new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
64         return sessionFactory.getObject();
65     }
66 }

View Code

5、从数据源SlaveDataSourceConfig配置

 1 package com.carry.springboot.config.ds;
 2 
 3 import javax.sql.DataSource;
 4 
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.mybatis.spring.SqlSessionFactoryBean;
 7 import org.mybatis.spring.annotation.MapperScan;
 8 import org.springframework.beans.factory.annotation.Qualifier;
 9 import org.springframework.beans.factory.annotation.Value;
10 import org.springframework.context.annotation.Bean;
11 import org.springframework.context.annotation.Configuration;
12 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
13 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
14 
15 import com.alibaba.druid.pool.DruidDataSource;
16 
17 @Configuration
18 // 扫描 Mapper 接口并容器管理
19 @MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "slaveSqlSessionFactory")
20 public class SlaveDataSourceConfig {
21 
22     // 精确到 slave 目录，以便跟其他数据源隔离
23     static final String PACKAGE = "com.carry.springboot.dao.slave";
24     static final String MAPPER_LOCATION = "classpath:mapper/slave/*.xml";
25 
26     @Value("${slave.datasource.url}")
27     private String url;
28 
29     @Value("${slave.datasource.username}")
30     private String user;
31 
32     @Value("${slave.datasource.password}")
33     private String password;
34 
35     @Value("${slave.datasource.driverClassName}")
36     private String driverClass;
37 
38     @Bean(name = "slaveDataSource")
39     public DataSource slaveDataSource() {
40         DruidDataSource dataSource = new DruidDataSource();
41         dataSource.setDriverClassName(driverClass);
42         dataSource.setUrl(url);
43         dataSource.setUsername(user);
44         dataSource.setPassword(password);
45         return dataSource;
46     }
47 
48     @Bean(name = "slaveTransactionManager")
49     public DataSourceTransactionManager slaveTransactionManager() {
50         return new DataSourceTransactionManager(slaveDataSource());
51     }
52 
53     @Bean(name = "slaveSqlSessionFactory")
54     public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource)
55             throws Exception {
56         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
57         sessionFactory.setDataSource(slaveDataSource);
58         sessionFactory.setMapperLocations(
59                 new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION));
60         return sessionFactory.getObject();
61     }
62 }

View Code

6、业务层代码

 1 package com.carry.springboot.service.impl;
 2 
 3 import org.springframework.beans.factory.annotation.Autowired;
 4 import org.springframework.stereotype.Service;
 5 
 6 import com.carry.springboot.dao.master.UserDao;
 7 import com.carry.springboot.dao.slave.CityDao;
 8 import com.carry.springboot.domain.City;
 9 import com.carry.springboot.domain.User;
10 import com.carry.springboot.service.UserService;
11 
12 @Service
13 public class UserServiceImpl implements UserService {
14 
15     @Autowired
16     private UserDao userDao; // 主数据源
17 
18     @Autowired
19     private CityDao cityDao; // 从数据源
20 
21     @Override
22     public User findByName(String userName) {
23         User user = userDao.findByName(userName);
24         City city = cityDao.findByName("北京市");
25         user.setCity(city);
26         return user;
27     }
28 
29 }

View Code

三、测试

写个controller和request方法，我这里的是http://localhost:8080/api/user

在浏览器中输入http://localhost:8080/api/user?userName=程序员得到结果

posted @ 2018-08-01 15:23 CarryChan 阅读(36498) 评论(2) 编辑收藏举报

刷新页面返回顶部

CarryChan

Springboot多数据源配置

一、数据库准备

二、工程代码配置详解

三、测试

公告