Druid实现Mybatis多数据源和可视化监控

作者: 十二找十三 | 来源:发表于2022-06-16 15:26 被阅读0次

springboot+mybatis-plus+druid 动态
Druid实现Mybatis多数据源和可视化监控
微架构 springcloud-06. springboot-监
SpringBoot + Mybatis + Druid + P
SpringBoot+Mybatis+ Druid+PageHe
SpringBoot + Mybatis + Druid + P
SpringBoot整合ElasticSearch实现多版本的兼
Spring boot 2.0整合mybatis和druid数据
spring-boot整合mybatis和druid连接池（动态
微服务架构实战篇（四）：Spring boot2.0 + Myb

application.properties 核心配置

################ 数据库1 ##############
spring.datasource.db1.name=db1
spring.datasource.db1.jdbc-url=jdbc:mysql://172.16.1.70:3306/bcss20220118?characterEncoding=utf8&allowMultiQueries=true&noAccessToProcedureBodies=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.db1.username=root
spring.datasource.db1.password=root
spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver

################ 数据库2 ##############
spring.datasource.db2.name=db2
spring.datasource.db2.jdbc-url=jdbc:mysql://172.16.1.63:3306/bcss_release?characterEncoding=utf8&allowMultiQueries=true&noAccessToProcedureBodies=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.db2.username=root
spring.datasource.db2.password=1qaz@WSX
spring.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver


################ druid ###############
#spring.druid.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.filters=stat
spring.datasource.maxActive=20
spring.datasource.initialSize=10
spring.datasource.maxWait=60000
spring.datasource.minIdle=5
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=select 'X'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.maxOpenPreparedStatements=20
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

第一个数据源配置相关信息

package com.ccbckj.config;
import com.alibaba.druid.pool.DruidDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;


@Configuration
@MapperScan(basePackages = "com.study.mapper.mapper1", sqlSessionFactoryRef = "sqlSessionFactoryBeanDB1")
public class MybatisConfigByDB1 {

    @Value("${spring.datasource.db1.name}")
    private String name;
    @Value("${spring.datasource.db1.jdbc-url}")
    private String url;
    @Value("${spring.datasource.db1.username}")
    private String username;
    @Value("${spring.datasource.db1.password}")
    private String password;
    @Value("${spring.datasource.db1.driver-class-name}")
    private String driverClassName;


    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;

    @Bean
    DataSource db1(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setName(name);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        // Druid  Configuration
        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {
            dataSource.setFilters(filters); // druid 监控sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        dataSource.setConnectionProperties(connectionProperties);
        return dataSource;
    }

    @Autowired
    @Qualifier("db1")
    DataSource db1;

    @Bean(name = "db1TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("db1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


    @Bean
    SqlSessionFactory sqlSessionFactoryBeanDB1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db1);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/db1/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanDB1());
    }
}

第二个数据源配置相关信息类似第一个

package com.study.config;
import com.alibaba.druid.pool.DruidDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;


@Configuration
@MapperScan(basePackages = "com.study.mapper.mapper2", sqlSessionFactoryRef = "sqlSessionFactoryBeanDB2")
public class MybatisConfigByDB2 {

//    @Bean
//    @ConfigurationProperties("spring.datasource.db2")
//    DataSource db2(){
//        return DataSourceBuilder.create().build();
//    }

    @Value("${spring.datasource.db2.name}")
    private String name;
    @Value("${spring.datasource.db2.jdbc-url}")
    private String url;
    @Value("${spring.datasource.db2.username}")
    private String username;
    @Value("${spring.datasource.db2.password}")
    private String password;
    @Value("${spring.datasource.db2.driver-class-name}")
    private String driverClassName;


    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;

    @Bean
    DataSource db2(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setName(name);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        // Druid  Configuration
        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {
            dataSource.setFilters(filters); // druid 监控sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        dataSource.setConnectionProperties(connectionProperties);
        return dataSource;
    }

    @Autowired
    @Qualifier("db2")
    DataSource db2;

    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("db2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    SqlSessionFactory sqlSessionFactoryBeanDB2() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db2);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/db2/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate2() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanDB2());
    }
}

druid可视化页面自己的配置

package com.study.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DruidConfiguration {

    @Bean
    public ServletRegistrationBean druidStatViewServle() {
        // 注册服务
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
                new StatViewServlet(), "/druid/*");

        // 白名单->(为空表示所有的都可以访问,多个IP的时候用逗号隔开 例127.0.0.1, 127.0.0.1)
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        // IP黑名单 (存在共同时，deny优先于allow) （如果是黑名单，那么该ip无法登陆druid的可视化界面）
        servletRegistrationBean.addInitParameter("deny", "127.0.0.12");

        // 设置登录的用户名和密码
        servletRegistrationBean.addInitParameter("loginUsername", "root");
        servletRegistrationBean.addInitParameter("loginPassword", "root");

        // druid页面配置 是否能够重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(
                new WebStatFilter());
        // 添加过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        // 添加不需要的->忽略
        filterRegistrationBean.addInitParameter("exclusions", "*.js, *.gif, *.jpg, *.png, *.css, *.ico, /druid/*");
        return filterRegistrationBean;

    }
}

spring boot 启动类

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MainStarter {
    public static void main(String[] args) {
        SpringApplication.run(MainStarter.class, args);
    }
}

springboot+mybatis-plus+druid 动态
一. springboot+mybatis-plus+druid实现动态数据源以及监控 1. 创建一个spring...
Druid实现Mybatis多数据源和可视化监控
application.properties 核心配置第一个数据源配置相关信息第二个数据源配置相关信息类似...
微架构 springcloud-06. springboot-监
监控druid 数据源、异常全局捕获、打包监控druid 数据源从第[4]节开始，开始使用到druid连接池，...
SpringBoot + Mybatis + Druid + P
本篇文章主要讲述的是SpringBoot整合Mybatis、Druid和PageHelper 并实现多数据源和分页...
SpringBoot+Mybatis+ Druid+PageHe
前言本篇文章主要讲述的是SpringBoot整合Mybatis、Druid和PageHelper 并实现多数据源...
SpringBoot + Mybatis + Druid + P
前言本篇文章主要讲述的是SpringBoot整合Mybatis、Druid和PageHelper 并实现多数据源...
SpringBoot整合ElasticSearch实现多版本的兼
前言在上一篇学习SpringBoot中，整合了Mybatis、Druid和PageHelper并实现了多数据源的...
Spring boot 2.0整合mybatis和druid数据
要点简介快速搭建基于mybatis和druid的spring boot 2.0工程聊聊druid数据源和2.0...
spring-boot整合mybatis和druid连接池（动态
springboot+mybatis+druid 动态数据源切换参考1 springboot+mybatis+dr...
微服务架构实战篇（四）：Spring boot2.0 + Myb
简介该项目主要利用Spring boot2.0 + Mybatis +Druid 实现监控数据库访问性能。 Dr...