IT虾米网

Springboot多数据源配置详解

sanshao 2019年09月12日 编程语言 180 0

        目前,业界流行的数据操作框架是 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依赖

 <?xml version="1.0" encoding="UTF-8"?> 
 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 
     <modelVersion>4.0.0</modelVersion> 
  
     <groupId>com.carry.elk</groupId> 
     <artifactId>springboot-mutil-datasource</artifactId> 
     <version>0.0.1-SNAPSHOT</version> 
     <packaging>jar</packaging> 
  
     <name>springboot-mutil-datasource</name> 
     <description>Demo project for Spring Boot</description> 
  
     <parent> 
         <groupId>org.springframework.boot</groupId> 
         <artifactId>spring-boot-starter-parent</artifactId> 
         <version>2.0.4.RELEASE</version> 
         <relativePath/> <!-- lookup parent from repository --> 
     </parent> 
  
     <properties> 
         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 
         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> 
         <java.version>1.8</java.version> 
         <druid>1.0.31</druid> 
     </properties> 
  
     <dependencies> 
         <dependency> 
             <groupId>org.springframework.boot</groupId> 
             <artifactId>spring-boot-starter-web</artifactId> 
         </dependency> 
         <dependency> 
             <groupId>org.mybatis.spring.boot</groupId> 
             <artifactId>mybatis-spring-boot-starter</artifactId> 
             <version>1.3.2</version> 
         </dependency> 
         <!-- Druid 数据连接池依赖 --> 
         <dependency> 
             <groupId>com.alibaba</groupId> 
             <artifactId>druid</artifactId> 
             <version>${druid}</version> 
         </dependency> 
  
         <dependency> 
             <groupId>org.springframework.boot</groupId> 
             <artifactId>spring-boot-devtools</artifactId> 
             <scope>runtime</scope> 
         </dependency> 
         <dependency> 
             <groupId>mysql</groupId> 
             <artifactId>mysql-connector-java</artifactId> 
             <scope>runtime</scope> 
         </dependency> 
         <dependency> 
             <groupId>org.springframework.boot</groupId> 
             <artifactId>spring-boot-starter-test</artifactId> 
             <scope>test</scope> 
         </dependency> 
     </dependencies> 
  
     <build> 
         <plugins> 
             <plugin> 
                 <groupId>org.springframework.boot</groupId> 
                 <artifactId>spring-boot-maven-plugin</artifactId> 
             </plugin> 
         </plugins> 
     </build> 
  
  
 </project>

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 配置

 package com.carry.springboot.config.ds; 
  
 import javax.sql.DataSource; 
  
 import org.apache.ibatis.session.SqlSessionFactory; 
 import org.mybatis.spring.SqlSessionFactoryBean; 
 import org.mybatis.spring.annotation.MapperScan; 
 import org.springframework.beans.factory.annotation.Qualifier; 
 import org.springframework.beans.factory.annotation.Value; 
 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 com.alibaba.druid.pool.DruidDataSource; 
  
 @Configuration 
 // 扫描 Mapper 接口并容器管理 
 @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory") 
 public class MasterDataSourceConfig { 
  
     // 精确到 master 目录,以便跟其他数据源隔离 
     static final String PACKAGE = "com.carry.springboot.dao.master"; 
     static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml"; 
  
     @Value("${master.datasource.url}") 
     private String url; 
  
     @Value("${master.datasource.username}") 
     private String user; 
  
     @Value("${master.datasource.password}") 
     private String password; 
  
     @Value("${master.datasource.driverClassName}") 
     private String driverClass; 
  
     @Bean(name = "masterDataSource") 
     @Primary 
     public DataSource masterDataSource() { 
         DruidDataSource dataSource = new DruidDataSource(); 
         dataSource.setDriverClassName(driverClass); 
         dataSource.setUrl(url); 
         dataSource.setUsername(user); 
         dataSource.setPassword(password); 
         return dataSource; 
     } 
  
     @Bean(name = "masterTransactionManager") 
     @Primary 
     public DataSourceTransactionManager masterTransactionManager() { 
         return new DataSourceTransactionManager(masterDataSource()); 
     } 
  
     @Bean(name = "masterSqlSessionFactory") 
     @Primary 
     public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) 
             throws Exception { 
         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); 
         sessionFactory.setDataSource(masterDataSource); 
         sessionFactory.setMapperLocations( 
                 new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION)); 
         return sessionFactory.getObject(); 
     } 
 }

5、从数据源SlaveDataSourceConfig配置

 package com.carry.springboot.config.ds; 
  
 import javax.sql.DataSource; 
  
 import org.apache.ibatis.session.SqlSessionFactory; 
 import org.mybatis.spring.SqlSessionFactoryBean; 
 import org.mybatis.spring.annotation.MapperScan; 
 import org.springframework.beans.factory.annotation.Qualifier; 
 import org.springframework.beans.factory.annotation.Value; 
 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 com.alibaba.druid.pool.DruidDataSource; 
  
 @Configuration 
 // 扫描 Mapper 接口并容器管理 
 @MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "slaveSqlSessionFactory") 
 public class SlaveDataSourceConfig { 
  
     // 精确到 slave 目录,以便跟其他数据源隔离 
     static final String PACKAGE = "com.carry.springboot.dao.slave"; 
     static final String MAPPER_LOCATION = "classpath:mapper/slave/*.xml"; 
  
     @Value("${slave.datasource.url}") 
     private String url; 
  
     @Value("${slave.datasource.username}") 
     private String user; 
  
     @Value("${slave.datasource.password}") 
     private String password; 
  
     @Value("${slave.datasource.driverClassName}") 
     private String driverClass; 
  
     @Bean(name = "slaveDataSource") 
     public DataSource slaveDataSource() { 
         DruidDataSource dataSource = new DruidDataSource(); 
         dataSource.setDriverClassName(driverClass); 
         dataSource.setUrl(url); 
         dataSource.setUsername(user); 
         dataSource.setPassword(password); 
         return dataSource; 
     } 
  
     @Bean(name = "slaveTransactionManager") 
     public DataSourceTransactionManager slaveTransactionManager() { 
         return new DataSourceTransactionManager(slaveDataSource()); 
     } 
  
     @Bean(name = "slaveSqlSessionFactory") 
     public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource) 
             throws Exception { 
         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); 
         sessionFactory.setDataSource(slaveDataSource); 
         sessionFactory.setMapperLocations( 
                 new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION)); 
         return sessionFactory.getObject(); 
     } 
 }

6、业务层代码

 package com.carry.springboot.service.impl; 
  
 import org.springframework.beans.factory.annotation.Autowired; 
 import org.springframework.stereotype.Service; 
  
 import com.carry.springboot.dao.master.UserDao; 
 import com.carry.springboot.dao.slave.CityDao; 
 import com.carry.springboot.domain.City; 
 import com.carry.springboot.domain.User; 
 import com.carry.springboot.service.UserService; 
  
 @Service 
 public class UserServiceImpl implements UserService { 
  
     @Autowired 
     private UserDao userDao; // 主数据源 
  
     @Autowired 
     private CityDao cityDao; // 从数据源 
  
     @Override 
     public User findByName(String userName) { 
         User user = userDao.findByName(userName); 
         City city = cityDao.findByName("北京市"); 
         user.setCity(city); 
         return user; 
     } 
  
 }

三、测试

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

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

 

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Springboot集成mybatis通用Mapper与分页插件PageHelper详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。