SpringBoot从入门到精通教程（七）- Mysql多数据源和Hikari用法详解

谁践踏了优雅 2021-12-20 02:17 675阅读 0赞

# 需求背景 #

> 在Springboot2框架中，常常需要用到以下两个功能点：
> 
> 1. Mysql多数据源集成
> 
> 2. Hikari 连接池用法详解

# 准备内容 #

1. 准备两个Mysql实例

2. [数据库初始化init.sql脚本内容][init.sql]（或[Docker安装MySQL数据库][Docker_MySQL]）

db1-init.sql

-- for db1
    
    drop table city1;
    
    CREATE TABLE `city1` (
      `id` int(11) NOT NULL primary key,
      `name` varchar(32) DEFAULT NULL,
      `state` varchar(32) DEFAULT NULL,
      `country` varchar(32) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `city1` (`id`, `name`, `state`, `country`)
    VALUES
    	(1, '广州1', 'GZ1', 'CH1'),
    	(2, '北京1', 'BJ1', 'CH1'),
    	(3, '深圳1', 'SZ1', 'CH1');

db2-init.sql

-- for db2
    
    drop table city2;
    
    CREATE TABLE `city2` (
      `id` int(11) NOT NULL primary key,
      `name` varchar(32) DEFAULT NULL,
      `state` varchar(32) DEFAULT NULL,
      `country` varchar(32) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `city2` (`id`, `name`, `state`, `country`)
    VALUES
    	(1, '广州2', 'GZ2', 'CH2'),
    	(2, '北京2', 'BJ2', 'CH2'),
    	(3, '深圳2', 'SZ2', 'CH2');

# 代码演示 #

此项目主要核心代码如下（其他代码查看文章：[Mysql和Mybatis+XML用法详解][Mysql_Mybatis_XML]，此项目是在这篇文章基础上修改而来）

**1. 配置application.yml（这里我定义了两个源master和slave）**

spring:
      datasource: 
        master: 
          jdbc-url: jdbc:mysql://192.168.1.1:33061/cfdb?useUnicode=true&autoReconnect=true&allowMultiQueries=true&useSSL=false
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          # 使用默认hikari连接池
          type: com.zaxxer.hikari.HikariDataSource
          minimum-idle: 5
          maximum-pool-size: 15
          pool-name: MyHikariCPOfMaster
          max-lifetime: 1800000
          connection-timeout: 20000
          connection-test-query: SELECT 1
          validation-timeout: 5000
        slave: 
          jdbc-url: jdbc:mysql://192.168.1.1:33062/cfdb?useUnicode=true&autoReconnect=true&allowMultiQueries=true&useSSL=false
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          # 使用默认hikari连接池
          type: com.zaxxer.hikari.HikariDataSource
          minimum-idle: 5
          maximum-pool-size: 15
          pool-name: MyHikariCPOfSlave
          max-lifetime: 1800000
          connection-timeout: 20000
          connection-test-query: SELECT 1
          validation-timeout: 5000
    server:
      port: 9090
    mybatis: 
      config-location: classpath:mybatis-config.xml

**2. CityMapper.xml文件**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.md.demo.vo.CityVo">
    
    	<select id="listCities111" resultType="com.md.demo.vo.CityVo">
    		select * from city1
    	</select>
    	
    	<select id="listCities222" resultType="com.md.demo.vo.CityVo">
    		select * from city2
    	</select>
    
    </mapper>

**3. 多数据源使用的几个类**

DataSourceConfig类

package com.md.demo.dao.base.datasource;
    
    import javax.sql.DataSource;
    
    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;
    
    @Configuration
    public class DataSourceConfig {
    
    	@Primary
    	@Qualifier("master")
    	@Bean(name = "master")
    	@ConfigurationProperties(prefix = "spring.datasource.master") // application.yml中对应属性的前缀
    	public DataSource masterDataSource() {
    		return DataSourceBuilder.create().build();
    	}
    
    	@Qualifier("slave")
    	@Bean(name = "slave")
    	@ConfigurationProperties(prefix = "spring.datasource.slave") // application.yml中对应属性的前缀
    	public DataSource slaveDataSource() {
    		return DataSourceBuilder.create().build();
    	}
    }

MybatisDbAConfig类

package com.md.demo.dao.base.datasource;
    
    import javax.sql.DataSource;
    
    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.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    
    @Configuration
    @MapperScan(basePackages = {"mybatis.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory1")
    public class MybatisDbAConfig {
    
    	private String localMapper = "classpath:mybatis/mapper/*.xml";
    	
        @Autowired
        @Qualifier("master")
        private DataSource ds1;
    
        @Bean
        public SqlSessionFactory sqlSessionFactory1() throws Exception {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(ds1); // 使用master数据源, 连接master库
            factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(localMapper));
            return factoryBean.getObject();
        }
    
        @Bean(name = "masterSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate1() throws Exception {
            SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory1()); // 使用上面配置的Factory
            return template;
        }
    }

MybatisDbBConfig类

package com.md.demo.dao.base.datasource;
    
    import javax.sql.DataSource;
    
    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.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    
    @Configuration
    @MapperScan(basePackages = { "mybatis.mapper" }, sqlSessionFactoryRef = "sqlSessionFactory2")
    public class MybatisDbBConfig {
    	
    	private String localMapper = "classpath:mybatis/mapper/*.xml";
    	
    	@Autowired
    	@Qualifier("slave")
    	private DataSource ds2;
    
    	@Bean
    	public SqlSessionFactory sqlSessionFactory2() throws Exception {
    		SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    		factoryBean.setDataSource(ds2);
    		factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(localMapper));
    		return factoryBean.getObject();
    	}
    
    	@Bean(name = "slaveSessionTemplate")
    	public SqlSessionTemplate sqlSessionTemplate2() throws Exception {
    		SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory2());
    		return template;
    	}
    }

**4. 编写基类：BaseDao类**

package com.md.demo.dao.base;
    
    import javax.annotation.Resource;
    
    import org.mybatis.spring.SqlSessionTemplate;
    import org.springframework.beans.factory.annotation.Autowired;
    
    public abstract class BaseDao {
    	
    	/**
    	 * 默认库-主库
    	 */
    	@Resource(name = "masterSessionTemplate")
    	@Autowired
    	public SqlSessionTemplate sqlSessionTemplateOfMaster;
    	
    	/**
    	 * 灾备库-用作读库
    	 */
    	@Resource(name = "slaveSessionTemplate")
    	@Autowired
    	public SqlSessionTemplate sqlSessionTemplateOfSlave;
    }

**5. 编写Dao类**

接口

package com.md.demo.dao;
    
    import java.util.List;
    
    import com.md.demo.vo.CityVo;
    
    public interface CityDao {
    
    	public List<CityVo> listCities111();
    
    	public List<CityVo> listCities222();
    
    }

实现类（继承了BaseDao）

package com.md.demo.dao.impl;
    
    import java.util.List;
    
    import org.springframework.stereotype.Component;
    
    import com.md.demo.dao.CityDao;
    import com.md.demo.dao.base.BaseDao;
    import com.md.demo.vo.CityVo;
    
    @Component
    public class CityDaoImpl extends BaseDao implements CityDao {
    
    	@Override
    	public List<CityVo> listCities111() {
    		return this.sqlSessionTemplateOfMaster.selectList("listCities111");
    	}
    
    	@Override
    	public List<CityVo> listCities222() {
    		return this.sqlSessionTemplateOfSlave.selectList("listCities222");
    	}
    
    }

**6. 编写Service类**

接口

package com.md.demo.service;
    
    import java.util.List;
    import com.md.demo.vo.CityVo;
    
    public interface CityService {
    
    	public List<CityVo> listCities111();
    
    	public List<CityVo> listCities222();
    }

实现类

package com.md.demo.service.impl;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.md.demo.dao.CityDao;
    import com.md.demo.service.CityService;
    import com.md.demo.vo.CityVo;
    
    @Service
    public class CityServiceImpl implements CityService {
    
    	@Autowired
    	private CityDao cityDao;
    
    	/**
    	 * 获得城市1列表
    	 */
    	@Override
    	public List<CityVo> listCities111() {
    		return this.cityDao.listCities111();
    	}
    
    	/**
    	 * 获得城市2列表
    	 */
    	@Override
    	public List<CityVo> listCities222() {
    		return this.cityDao.listCities222();
    	}
    
    }

**7. 编写接口访问层**

package com.md.demo.rest;
    
    import java.util.List;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.md.demo.service.CityService;
    import com.md.demo.util.JsonResult;
    import com.md.demo.util.ResultCode;
    import com.md.demo.vo.CityVo;
    
    /**
     * @author Minbo
     */
    @RestController
    public class InitRest {
    
    	protected static Logger logger = LoggerFactory.getLogger(InitRest.class);
    
    	@Autowired
    	private CityService cityService;
    
    	/**
    	 * http://localhost:9090/hello
    	 * 
    	 * @return
    	 */
    	@GetMapping("/hello")
    	public String hello() {
    		return "Hello greetings from spring-boot2-mysql-multi-datasource";
    	}
    
    	/**
    	 * http://localhost:9090/listCities111
    	 * 
    	 * @return
    	 */
    	@GetMapping("/listCities111")
    	public JsonResult listCities111() {
    		List<CityVo> list = this.cityService.listCities111();
    		return new JsonResult(ResultCode.SUCCESS, list);
    	}
    
    	/**
    	 * http://localhost:9090/listCities222
    	 * 
    	 * @return
    	 */
    	@GetMapping("/listCities222")
    	public JsonResult listCities222() {
    		List<CityVo> list = this.cityService.listCities222();
    		return new JsonResult(ResultCode.SUCCESS, list);
    	}
    }

# 完整源码下载 #

我的[Github源码地址：][Github]

[https://github.com/hemin1003/spring-boot-study/tree/master/spring-boot2-study/spring-boot2-parent][Github]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70][]

# 官方资料 #

[SpringBoot配置多数据源][SpringBoot]

[HikariCP源码项目][HikariCP]

# 附加说明 #

**问题1：多数据源配置，项目加载是否生效？**

答案1：在项目启动加载时，可以查看日志是否已生效

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1][]

**问题2：hikari连接池配置，如何验证是否生效？**

答案2：在访问各接口时，可以查看日志是否已生效

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 3][]

**问题3：hikari连接池配置，常用参数的含义是？**

## 最小空闲连接数量
    minimum-idle=5
    ## 连接池最大连接数，默认是10
    maximum-pool-size=15
    ## 连接池名称
    pool-name=MyHikariCPOfMaster
    ## 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
    max-lifetime=1800000
    ## 数据库连接超时时间,默认30秒，即30000
    connection-timeout=20000
    ## 空闲连接存活最大时间，默认600000（10分钟）
    idle-timeout=180000
    ## 此属性控制从池返回的连接的默认自动提交行为,默认值：true
    auto-commit=true

# 下一章教程 #

[SpringBoot从入门到精通教程（八）- 多环境配置文件用法][SpringBoot_-]

# 该系列教程 #

[SpringBoot从入门到精通教程][SpringBoot 1]

至此，全部介绍就结束了

\------------------------------------------------------

[关于我][Link 1]（个人域名）

[我的开源项目集Github][Github 1]

期望和大家***一起学习，一起成长***，共勉，O(∩\_∩)O谢谢

欢迎交流问题，可加个人QQ 469580884，

或者，加我的群号 **751925591**，一起探讨交流问题

不讲虚的，只做实干家

Talk is cheap，show me the code

![20180411181545874][]

如果觉得内容赞，您可以请我喝杯咖啡：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 4][]        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 5][]

[init.sql]: https://github.com/hemin1003/spring-boot-study/tree/master/spring-boot2-study/spring-boot2-parent/spring-boot2-mysql-multi-datasource
[Docker_MySQL]: https://blog.csdn.net/hemin1003/article/details/52808736
[Mysql_Mybatis_XML]: https://blog.csdn.net/hemin1003/article/details/93123524
[Github]: https://github.com/hemin1003/spring-boot-study/tree/master/spring-boot2-study/spring-boot2-parent
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: /images/20211220/c6602408441840b1b2daef02703981c2.png
[SpringBoot]: https://docs.spring.io/spring-boot/docs/2.0.9.RELEASE/reference/htmlsingle/#howto-two-datasources
[HikariCP]: https://github.com/brettwooldridge/HikariCP
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]: /images/20211220/17b0e1ccfa2140a39445081e24c349c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019062920063081.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 3]: /images/20211220/99f130c559a142609db7d3960fd95119.png
[SpringBoot_-]: https://blog.csdn.net/hemin1003/article/details/96483484
[SpringBoot 1]: https://hemin.blog.csdn.net/column/info/40170
[Link 1]: http://heminit.com/about/
[Github 1]: https://github.com/hemin1003
[20180411181545874]: /images/20211220/37763d8d571a4193908ee0bae12c246d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190816110426562.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190816110923699.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70