SpringBoot配置多数据源数据库

**

一、SpringBoot + Druid + Mybatisplus 使用注解整合

**
主要依赖包：

1. spring-boot-starter-web
2. mybatis-plus-boot-starter
3. dynamic-datasource-spring-boot-starter # 配置动态数据源
4. druid-spring-boot-starter # 阿里的数据库连接池
5. 数据库驱动 myql —》mysql-connector-java ，pgsql—》taos-jdbcdriver，postgresql

pom.xml 如下：

 <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
   </dependency>
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter</artifactId>
       <version>3.2.0</version>
   </dependency>
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
       <version>2.5.6</version>
   </dependency>
   <!--数据库-->
   <!--pg-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>com.taosdata.jdbc</groupId>
        <artifactId>taos-jdbcdriver</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>
    <!--mysql-->
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <scope>runtime</scope>
   </dependency>
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid-spring-boot-starter</artifactId>
       <version>1.1.20</version>
   </dependency>

application.yml 配置文件

1)mysql数据源
server:
port: 8080
spring:
datasource:
dynamic:
primary: db1 # 配置默认数据库
datasource:
db1: # 数据源1配置
url: jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
db2: # 数据源2配置
url: jdbc:mysql://localhost:3306/db2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
durid:
initial-size: 1
max-active: 20
min-idle: 1
max-wait: 60000
autoconfigure:
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # 去除druid配置
2)pgsql

datasource:
dynamic:
primary: db1
datasource:
db1:
url: jdbc:postgresql://localhost:5432/db1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&&nullCatalogMeansCurrent=true
username: postgres
password: ******
# 使用druid数据源
platform: mysql
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
filters: stat,wall
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select ‘x’
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
maxPoolPreparedStatementPerConnectionSize: 20
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
useGlobalDataSourceStat: true
db2:
name: cfzx-auth-center
url: jdbc:postgresql://localhost:5432/*****?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&&nullCatalogMeansCurrent=true
username: postgres
password: ********
# 使用druid数据源
platform: mysql
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: org.postgresql.Driver
filters: stat,wall
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select ‘x’
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
maxPoolPreparedStatementPerConnectionSize: 20
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
useGlobalDataSourceStat: true
durid:
initial-size: 1
max-active: 20
min-idle: 1
max-wait: 60000
autoconfigure:
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # 去除druid配置

DruidDataSourceAutoConfigure会注入一个DataSourceWrapper，其会在原生的spring.datasource下找 url, username, password 等。动态数据源 URL 等配置是在 dynamic 下，因此需要排除，否则会报错。排除方式有两种，一种是上述配置文件排除，还有一种可以在项目启动类排除：
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}

代码应用：

给使用非默认数据源添加注解@DS
@DS 可以注解在方法上和类上，同时存在方法注解优先于类上注解。
注解在 service 实现或 mapper 接口方法上，不要同时在 service 和 mapper 注解。

@DS(“db2”)
public interface CountMapper extends BaseMapper {
}

@Service
@DS(“db2”)
public class CountServiceImpl extends ServiceImpl<CountMapper, Count> implements ICountService {}

@Select(“SELECT * FROM COUNT”)
@DS(“db2”)
List selectAll();

本人习惯写在方法上：
public interface CountMapper {
String test();
@DS(“db2”)
String test1();
}

**

二、springboot+mybatis使用分包方式整合

**

主要依赖包
spring-boot-starter-web
mybatis-spring-boot-starter
mysql-connector-java

pom.xml：

<dependencies>
    <!-- spring 依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
     <!-- mysql 依赖 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

application.yml 配置文件：
server:
port: 8080 # 启动端口
spring:
datasource:
db1: # 数据源1
jdbc-url: jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
db2: # 数据源2
jdbc-url: jdbc:mysql://localhost:3306/db2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver

注意事项

各个版本的 springboot 配置 datasource 时参数有所变化，例如低版本配置数据库 url 时使用 url 属性，高版本使用 jdbc-url 属性，请注意区分。

建立连接数据源的配置文件：
one:
@Configuration
@MapperScan(basePackages = “com.example.multipledatasource.mapper.db1”, sqlSessionFactoryRef = “db1SqlSessionFactory”)
public class DataSourceConfig1 {

@Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
@Bean("db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
public DataSource getDb1DataSource(){
    return DataSourceBuilder.create().build();
}

@Primary
@Bean("db1SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db1/*.xml"));
    return bean.getObject();
}

@Primary
@Bean("db1SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
    return new SqlSessionTemplate(sqlSessionFactory);
}

}

two:
@Configuration
@MapperScan(basePackages = “com.example.multipledatasource.mapper.db2”, sqlSessionFactoryRef = “db2SqlSessionFactory”)
public class DataSourceConfig2 {

@Bean("db2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource getDb1DataSource(){
    return DataSourceBuilder.create().build();
}

@Bean("db2SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db2/*.xml"));
    return bean.getObject();
}

@Bean("db2SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
    return new SqlSessionTemplate(sqlSessionFactory);
}

}

注意事项

在 service 层中根据不同的业务注入不同的 dao 层
如果是主从复制- -读写分离：比如 db1 中负责增删改，db2 中负责查询。但是需要注意的是负责增删改的数据库必须是主库（master）