Spring Boot JDBC + Mybatis 配置多数据源 以及 采用Durid 作为连接池

多数据源最简单配置,多数据源对应的mapper和xml分开注入即可，简单方便，缺点不能多个db以及主从配合等。

1 配置文件

在配置文件中配置两个数据源配置，以及mybatis xml配置文件路径

# mybatis  多数据源配置
mybatis.config-location = classpath:mapper/config/mybatis-config.xml

#################  mysql  数据源1 #################
spring.datasource.one.jdbc-url=jdbc:mysql://localhost:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.one.username=root
spring.datasource.one.password=root
#spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.one.driver-class-name=com.mysql.jdbc.Driver
#################  mysql  数据源1 #################

#################  mysql  数据源2 ################
spring.datasource.second.jdbc-url=jdbc:mysql://xxxxxxxxxx:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.second.username=root
spring.datasource.second.password=root
#spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.second.driver-class-name=com.mysql.jdbc.Driver
#################  mysql  数据源1 #################

2 数据库配置代码：

1 步骤

1 首先加载配置的数据源：手动将数据配置文件信息注入到数据源实例对象中。
2 根据创建的数据源，配置数据库实例对象注入到SqlSessionFactory 中，构建对应的 SqlSessionFactory。
3 配置数据库事务：将数据源添加到事务中。
4 将SqlSessionFactory 注入到SqlSessionTemplate 模板中
5 最后将上面创建的 SqlSessionTemplate 注入到对应的 Mapper 包路径下，这样这个包下面的 Mapper 都会使用第一个数据源来进行数据库操作。

basePackages   指明 Mapper 地址。
sqlSessionTemplateRef    指定 Mapper 路径下注入的 sqlSessionTemplate。

在多数据源的情况下，不需要在启动类添加：@MapperScan(“com.xxx.mapper”) 的注解。

2 项目结构：

3 第一个数据源

@Api("SqlSessionTemplate 注入到对应的 Mapper 包路径下")@Configuration@MapperScan(basePackages="com.example.demo.mapper.one", 
sqlSessionFactoryRef="oneSqlSessionTemplate")//与当前sqlSessionFactory 保持一致publicclassOneDataSourceConfig{//------------------                 1 加载配置的数据源：   -------------------------------@Bean("oneDatasource")@ConfigurationProperties(prefix="spring.datasource.one")@Primary//默认是这个库publicDataSourceDataSource1Config(){returnDataSourceBuilder.create().build();}//---------------------- 2 创建的数据源 构建对应的 SqlSessionFactory。  ----------------------@Bean(name="oneSqlSessionFactory")@PrimarypublicSqlSessionFactoryoneSqlSessionFactory(@Qualifier("oneDatasource")DataSource dataSource)throwsException{SqlSessionFactoryBean bean=newSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(newPathMatchingResourcePatternResolver().getResources("classpath:/mapper/one/*.xml"));return bean.getObject();}//------------------------3  配置事务 --------------------------@Bean(name="oneTransactionManager")@PrimarypublicDataSourceTransactionManageroneTransactionManager(@Qualifier("oneDatasource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}//------------------------------- 4 注入 SqlSessionFactory 到 SqlSessionTemplate 中---------------------------------@Bean(name="oneSqlSessionTemplate")@PrimarypublicSqlSessionTemplateoneSqlSessionTemplate(@Qualifier("oneSqlSessionFactory")SqlSessionFactory sqlSessionFactory)throwsException{returnnewSqlSessionTemplate(sqlSessionFactory);}}

第二个数据源

@Api("SqlSessionTemplate 注入到对应的 Mapper 包路径下")@Configuration@MapperScan(basePackages="com.example.demo.mapper.second", sqlSessionFactoryRef="secondSqlSessionFactory")//与当前sqlSessionFactory 保持一致publicclassSecondDataSourceConfig{//------------------                  加载配置的数据源：   -------------------------------@Bean("secondDatasource")@ConfigurationProperties(prefix="spring.datasource.second")publicDataSourceDataSource2Config(){returnDataSourceBuilder.create().build();}//---------------------- 创建的数据源 构建对应的 SqlSessionFactory。  ----------------------@Bean(name="secondSqlSessionFactory")publicSqlSessionFactorysecondSqlSessionFactory(@Qualifier("secondDatasource")DataSource dataSource)throwsException{SqlSessionFactoryBean bean=newSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(newPathMatchingResourcePatternResolver().getResources("classpath:/mapper/second/*.xml"));return bean.getObject();}//------------------------ 配置事务 --------------------------@Bean(name="secondTransactionManager")publicDataSourceTransactionManagersecondTransactionManager(@Qualifier("secondDatasource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}//------------------------------- 注入 SqlSessionFactory 到 SqlSessionTemplate 中---------------------------------@Bean(name="secondSqlSessionTemplate")publicSqlSessionTemplatesecondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory")SqlSessionFactory sqlSessionFactory)throwsException{returnnewSqlSessionTemplate(sqlSessionFactory);}}

3 xml文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEconfigurationPUBLIC"-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration></configuration>

4 mapper 类

publicinterfaceUser1Mapper{publicvoidinserts(User user);}

publicinterfaceUser2Mapper{publicvoidinserts(User user);}

5 mybatis mapper.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.example.demo.mapper.one.User1Mapper"><insertid="inserts"parameterType="com.example.demo.pojo.User"useGeneratedKeys="true"keyProperty="id">
        insert into user(`name`,age) VALUE (#{name},#{age})</insert></mapper>

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.example.demo.mapper.one.User2Mapper"><insertid="inserts"parameterType="com.example.demo.pojo.User"useGeneratedKeys="true"keyProperty="id">
        insert into user(`name`,age) VALUE (#{name},#{age})</insert></mapper>

3 启动成功

表示数据源创建成功，这里连接池采用springboot默认的Hikari数据库连接池(不需要配置)

6 测试

@AutowiredUser1Mapper user1Mapper;@AutowiredUser2Mapper user2Mapper;@Testpublicvoidtest(){
		user1Mapper.inserts(newUser(22L,"a123456",1));
		user1Mapper.inserts(newUser(33L,"b123456",1));
		user2Mapper.inserts(newUser(44L,"b123456",1));}

结果

7 更换数据源配置

官方文档 ： https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

增加配置文件 , 更换为durid数据源

1 配置文件增加配置属性

#  StatViewServlet 配置
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

# 配置 StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000

# Druid 数据源 1 配置
spring.datasource.druid.one.initial-size=3
spring.datasource.druid.one.min-idle=3
spring.datasource.druid.one.max-active=10
spring.datasource.druid.one.max-wait=60000

# Druid 数据源 2 配置
spring.datasource.druid.second.initial-size=6
spring.datasource.druid.second.min-idle=6
spring.datasource.druid.second.max-active=20
spring.datasource.druid.second.max-wait=120000

将上面数据库配置文件前缀加上druid

如：

#  StatViewServlet 配置
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

# 配置 StatFilter
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000

# Druid 数据源 1 配置
spring.datasource.druid.one.initial-size=3
spring.datasource.druid.one.min-idle=3
spring.datasource.druid.one.max-active=10
spring.datasource.druid.one.max-wait=60000

# Druid 数据源 2 配置
spring.datasource.druid.second.initial-size=6
spring.datasource.druid.second.min-idle=6
spring.datasource.druid.second.max-active=20
spring.datasource.druid.second.max-wait=120000

#mybatis.type-aliases-package = com.example.demo.pojo
#################  mysql  数据源1 #################
spring.datasource.druid.one.url=jdbc:mysql://localhost:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.druid.one.username=root
spring.datasource.druid.one.password=root
spring.datasource.druid.one.driver-class-name=com.mysql.jdbc.Driver
#################  mysql  数据源1 #################

#################  mysql  数据源2 #################
spring.datasource.druid.second.url=jdbc:mysql://xxxxxxxxxxx:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.druid.second.username=root
spring.datasource.druid.second.password=root
spring.datasource.druid.second.driver-class-name=com.mysql.jdbc.Driver
#################  mysql  数据源1 #################

2 引入依赖

<!--druid--><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.10</version></dependency>

然后在加载数据源配置哪儿读取配置文件注解改为

@ConfigurationProperties(prefix = "spring.datasource.druid.one")
@ConfigurationProperties(prefix = "spring.datasource.druid.second")

启动后发现配置成功

[外链图片转存失败(img-q2Xl75ep-1567998979692)(https://upload-images.jianshu.io/upload_images/7852807-0e9a1cfdeeab87bc.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)]

过程中可能会遇到问题

Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class

原因是：url链接写错了 , SpringBoot duridd无法配置到 数据源url

错误：spring.datasource.druid.one.jdbc-url=jdbc:mysql://localhost:3306/user?   这是jdbc的url链接

正确 ： spring.datasource.druid.one.url=jdbc:mysql://localhost:3306/user?   这是连接池用的url

附上yml

spring:profiles: testredis:# Redis数据库索引（默认为0）database:10# Redis服务器地址host: 127.0.0.1# Redis服务器连接端口port:6379# Redis服务器连接密码（默认为空）password:# oracle 数据库连接datasource:type: com.alibaba.druid.pool.DruidDataSourcedruid:#StatViewServlet 配置stat-view-servlet:login-username: adminlogin-password: admin# 配置 StatFilterstat:log-slow-sql:trueslow-sql-millis:2000one:url: jdbc:mysql://localhost:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=trueusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverinitial-size:3min-idle:3max-active:10max-wait:60000second:url: jdbc:mysql://localhost:3306/user?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=trueusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverinitial-size:6min-idle:6max-active:20max-wait:120000

源码地址传送门