mybatis中通过 SqlSessionFactory 来创建 SqlSession会话对象，一个会话commit一次，也即一个会话就是一个事务

动态数据源构建原理就是根据需要，添加需要的datasource来定制化 SqlSessionFactory，springboot中集成通过切面的形式来进行配置

mybatisplus 动态数据源整合

第一种部署

添加依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-web</artifactId>
     <version>2.2.2.RELEASE</version>
</dependency>
<!-- mybatisplus相关依赖 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1.tmp</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    <version>8.0.13</version>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
<!-- AOP -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
    <version>2.2.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.9.2</version>
</dependency>

配置mybatisplus相关信息

pojo

1
2
3
4
5
6
7
@Data
@TableName(value = "dept")
public class DeptPojo {
    Integer deptno;
    String deptname;
    String deptsource;
}

mapper

1
2
3
4
//mybatisplus通过BaseMapper来自动配置mapper
@Mapper
public interface DeptMapper extends BaseMapper<DeptPojo> {
}

service

1
2
3
public interface TestService {
    List<DeptPojo> selectALL();
}

serviceimpl

1
2
3
4
5
6
7
8
9
10
@Service
public class TestServiceimpl implements TestService {
    @Autowired
    private DeptMapper deptMapper;

    public List<DeptPojo> selectALL() {
        QueryWrapper<DeptPojo> queryWrapper = new QueryWrapper<DeptPojo>();
        return deptMapper.selectList(queryWrapper);
    }
}

controller

1
2
3
4
5
6
7
8
9
10
11
@RestController
public class TestController {

    @Autowired
    private TestService testService;

    @GetMapping("list_my")
    public List<DeptPojo> selectAll(){
        return testService.selectALL();
    }
}

数据源的核心配置

数据源操作类

通过继承 AbstractRoutingDataSource类来构建多数据源然后放入线程中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public class DynamicDataSource extends AbstractRoutingDataSource {

    //创建线程对象
    private static final ThreadLocal<DataSource> CONTEXT_HOLDER = new ThreadLocal<>();

    //利用构造方法将默认数据源以及多数据源放入
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        //设置默认数据源
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        //设置所有数据源
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

    //数据源放入线程
    public static void setDataSource(DataSource dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    //数据源拿出线程
    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }

    //获取数据源
    public static DataSource getDataSource() {
        return CONTEXT_HOLDER.get();
    }

}

application.yml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#datasource配置
spring:
  datasource:
    primary:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/cloud_data?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456
    secondary:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/cloud_data?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456

#mybatis配置
mybatis-plus:
  global-config:
    db-config:
      id-type: auto
      field-strategy: not_empty
      table-underline: true
      db-type: mysql
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)

server:
  port: 9669

读取配置文件信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
@Configuration
@MapperScan(basePackages = "com.simplemw.mapper", sqlSessionFactoryRef = "SqlSessionFactory") //basePackages 我们接口文件的地址
public class DynamicDataSourceConfig {

    //设置为默认数据源
    @Primary
    @Bean(name = "PrimaryDataSource")
    //获取application中的配置
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public static DataSource getDateSource1() {
        //通过DataSourceBuilder类来生成datasource对象
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "SecondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public static DataSource getDateSource2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dynamicDataSource")
    //使用获取的配置对象
    public DynamicDataSource DataSource(@Qualifier("PrimaryDataSource") DataSource primaryDataSource,@Qualifier("SecondaryDataSource") DataSource secondaryDataSource) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put("primary", primaryDataSource);
        targetDataSource.put("secondary", secondaryDataSource);
        return new DynamicDataSource(primaryDataSource,targetDataSource);
    }

    //生成SqlSessionFactory
    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory SqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {
        //mybatisplus生成SqlSessionFactory
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //传入数据源
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());

        MybatisConfiguration configuration = new MybatisConfiguration();
        //传入泛型 statement和mapper
        configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        //手动指定传入数据为空的数据类型
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        //传入configuration配置
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

}

配置启动类

1
2
3
4
5
6
//此处需要关闭springboot的自动配置datasource
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class TestApplication {
    public static void main(String[] args) {
    	SpringApplication.run(TestApplication.class,args);
	}                                                                                     }

xml形式配置

注：若mapper以xml的形式实现则使用下面的方式

1
2
3
4
5
6
7
8
9
10
11
12
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)throws Exception{
    //mybatisplus生成SqlSessionFactory
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    //传入数据源
    sqlSessionFactoryBean.setDataSource(dynamicDataSource);
    //传入mapper.xml文件
    sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                                             .getResources("classpath*:/mapper/*.xml"));
    sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
    return sqlSessionFactoryBean.getObject();
}

第二种部署

添加依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 实现对 dynamic-datasource 的自动化配置 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>2.5.7</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-actuator</artifactId>
</dependency>
<!-- mybatisplus相关依赖 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1.tmp</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    <version>8.0.13</version>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.8</version>
</dependency>

yml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
spring:
  datasource:
    # dynamic-datasource-spring-boot-starter 动态数据源的配置内容
    dynamic:
      primary: master # 设置默认的数据源或者数据源组，默认值即为 master
      datasource:
        # 主数据源配置
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/ces?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
          username: root
          password: 123456
        secondary:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/ces2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
          username: root
          password: 123456

使用方式

1
2
@DS("secondary")
List<CesPojo> selectAll();

方法上加注解

补充：再整合druid

添加依赖

1
2
3
4
5
6
<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

yml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
spring:
  autoconfigure:
    #自动化配置，若不配置需启动类配置  @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # dynamic-datasource-spring-boot-starter 动态数据源的配置内容
    dynamic:
      # 设置默认的数据源或者数据源组，默认值即为 master
      primary: master
      datasource:
        # 订单 orders 数据源配置
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/ces?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
          username: root
          password: 123456
        secondary:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/ces2?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
          username: root
          password: 123456
      #druid配置
      druid:
        # 初始化连接池个数
        initialSize: 5
        # 最小连接池个数——>已经不再使用，配置了也没效果
        minIdle: 5
        # 最大连接池个数
        maxActive: 30
        # 配置获取连接等待超时的时间，单位毫秒，缺省启用公平锁，并发效率会有所下降
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        # 用来检测连接是否有效的sql，要求是一个查询语句。
        # 如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用
        validationQuery: SELECT 'x'
        # 建议配置为true，不影响性能，并且保证安全性。
        # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        testWhileIdle: true
        # 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        testOnBorrow: false
        # 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 通过别名的方式配置扩展插件，多个英文逗号分隔，常用的插件有：
        # 监控统计用的filter:stat
        # 日志用的filter:log4j
        # 防御sql注入的filter:wall
        filters: stat,wall,slf4j,config
        useGlobalDataSourceStat: true
        stat:
          log-slow-sql: true
          merge-sql: true
          slow-sql-millis: 10000

配置类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@Configuration
public class DruidConfig {

    //配置Druid的监控
    //1、配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名单
        servletRegistrationBean.addInitParameter("allow", "172.16.106.156");
        // IP黑名单(共同存在时，deny优先于allow)
//        servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }


    //创建一个新的过滤器，将原来的filter进行改写 WebStatFilter，对druid.*的url进行拦截
    @Bean
    public FilterRegistrationBean webStatFilter() {

        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,*/druid/*");
        return filterRegistrationBean;
    }

}

druid监控页面url

http://localhost:8080/druid/login.html