Springboot Druid dynamic (multiple data sources)

Time:2022-5-24

Druid: Based on Apache 2 0 protocol Alibaba open source database connection pool
Dynamic: springboot based initiator for fast integration of multiple data sources

  • What can Druid do

Druid is more than just a database connection pool
1. Monitor database access performance. A statfilter plug-in is built-in, which can make detailed statistics on SQL execution performance.
2. Database password encryption
3. SQL execution log supports common logging, log4 and jdklog, and can be switched on demand
4. Extend JDBC. If there are requirements for JDBC layer, you can easily write extension plug-ins through the filter mechanism provided by Druid.

Druid monitoring configuration

  • Modify application
#####Connection pool configuration#######
#Filter settings (the first stat is very important. If not, SQL will not be monitored)
spring.datasource.druid.filters=stat,wall,log4j2

#####Webstatfilter configuration#######
#Enable statfilter
spring.datasource.druid.web-stat-filter.enabled=true
#Add filter rule
spring.datasource.druid.web-stat-filter.url-pattern=/*
#Exclude some unnecessary URLs
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
#Enable session statistics
spring.datasource.druid.web-stat-filter.session-stat-enable=true
#The default sessionstatmaxcount is 1000
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
#spring.datasource.druid.web-stat-filter.principal-session-name=
#spring.datasource.druid.web-stat-filter.principal-cookie-name=
#spring.datasource.druid.web-stat-filter.profile-enable=

#####Statviewservlet configuration#######
#Enable built-in monitoring page
spring.datasource.druid.stat-view-servlet.enabled=true
#Address of built-in monitoring page
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#Turn off the reset all function
spring.datasource.druid.stat-view-servlet.reset-enable=false
#Set login user name
spring.datasource.druid.stat-view-servlet.login-username=admin
#Set login password
spring.datasource.druid.stat-view-servlet.login-password=123
#White list (if allow is not configured or empty, all accesses are allowed)
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
#Blacklist (deny takes precedence over allow. If it is in deny list, it will be rejected even if it is in allow list)
spring.datasource.druid.stat-view-servlet.deny=
  • After restarting the project, access and change the address, and enter the user name and password
http://ip:port/druid/login.html
  • What can dynamic do

It is a springboot based launcher for rapid integration of multiple data sources
1. Support data source grouping, read-write separation, one master and multiple slaves, etc.
2. Database sensitive information encryption (customizable)
3. Support startup without data source and lazy loading of data source (create connection when necessary)
4. Provide and simplify rapid integration of Druid, etc

How dynamic switches data sources

Use @ ds annotation. This annotation can be used on methods or classes. At the same time, the principle of proximity shall be adopted. Method annotations take precedence over class annotations
(you can use it on the service implementation class, and you don’t need to use annotations if you use the default data source)

@Service
@DS("slave_1")
public class ShengshiStudentManagementServiceImpl extends SysServiceImpl<ShengshiStuManagementMapper, ShengshiStuManagement> implements ShengshiStuManagementService {

    @Override
    public ShengshiStuManagement getById(Serializable id) {
        return baseMapper.selectById(id);
    }

}

————————————Experiment————————————

1. Maven configuration

<!-- Database connection pool -- > 
      <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid-version}</version>
        </dependency>

<!--         Multi database connection -- >
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>${dynamic-version}</version>
        </dependency>

2. Data source configuration

spring:
  datasource:
    druid:
      initial-size: 10
      max-active: 100
      min-idle: 3
      max-wait: 5000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 100
    dynamic:
      Primary: Master # sets the default data source or data source group. The default value is master
      Strict: false # sets the strict mode. False does not start by default After startup, an exception will be thrown when the specified data source is not matched. If it is not started, the default data source will be used
      datasource:
        master:
          url: jdbc:mysql://127.0.0.1:3306/edu_test
          username: root
          password: 123456
          driver-class-name: com. mysql. cj. jdbc. Driver # 3.2.0 supports SPI. This configuration can be omitted
        slave_1:
          url: jdbc:mysql://127.0.0.1:3306/shengshi
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
#        slave_2:
#URL: enc (xxxxx) # built in encryption, please check the detailed document
#          username: ENC(xxxxx)
#          password: ENC(xxxxx)
#          driver-class-name: com.mysql.jdbc.Driver
#          schema: db/schema. The SQL # configuration takes effect and automatically initializes the table structure
#          data: db/data. SQL # configuration takes effect and automatically initializes data
#Continue on error: true # defaults to true. Initialization fails. Continue
#          separator: ";" #  SQL default semicolon separator

Write the Dao layer according to the business. Secive and controller are OK. Use @ ds annotation on the service implementation class to switch the data source. The default data source does not need annotation.

Example code git address

reference
Druid GitHub address
Dynamic GitHub address

Recommended Today

Modul of fastems

Each module of fastems is implemented from the abstract class Fastems.Mms.Client.Infrastructure.UiModuleBase; public class DataManagerModule : UiModuleBase { public override void Initialize() { AddResourceDictionary(“/Resources/DataManagerResources.xaml”, typeof(DataManagerModule)); RegisterViewWithRegion(“DialogRegion”, typeof(DialogView)); RegisterViewWithRegion(“BusyIndicatorRegion”, typeof(BusyIndicatorView)); } } And Fastems.Mms.Client.Infrastructure.UiModuleBase inherits from Fastems.Mms.Client.Infrastructure.ModuleBase public abstract class UiModuleBase : ModuleBase { [Import] public IRegionManager RegionManager { get; set; } [Import] public IMergedDictionaryRegistry MergedDictionaryRegistry { […]