SpringBoot_连接池_多数据源_Druid

超、凢脫俗 2021-10-06 03:26 545阅读 0赞

### SpringBoot\_连接池\_多数据源\_Druid ###

*  多数据源实现
 *   *  依赖pom.xml
     *  配置文件bootstrap.yml
     *  配置源码
 *  starter实现
 *   *  依赖pom.xml
     *  配置文件bootstrap.yml
     *  配置源码
 *  自定义实现
 *   *  依赖pom.xml
     *  配置文件bootstrap.yml
     *  配置源码

# 多数据源实现 #

## 依赖pom.xml ##

## 配置文件bootstrap.yml ##

spring:
        datasource:
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            druid:
                first:  #数据源1
                    url: jdbc:mysql://192.168.164.100:3306/renren_fast?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                    username: root
                    password: qwe123
                second:  #数据源2
                    url: jdbc:mysql://192.168.164.100:3306/kettle?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                    username: root
                    password: qwe123
                initial-size: 10
                max-active: 100
                min-idle: 10
                max-wait: 60000
                pool-prepared-statements: true
                max-pool-prepared-statement-per-connection-size: 20
                time-between-eviction-runs-millis: 60000
                min-evictable-idle-time-millis: 300000
                validation-query: SELECT 1 FROM DUAL
                test-while-idle: true
                test-on-borrow: false
                test-on-return: false
                stat-view-servlet:
                    enabled: true
                    url-pattern: /druid/*
                    #login-username: admin
                    #login-password: admin
                filter:
                    stat:
                        log-slow-sql: true
                        slow-sql-millis: 1000
                        merge-sql: true
                    wall:
                        config:
                            multi-statement-allow: true

## 配置源码 ##

/**
         * 添加数据源
         * 
         * 
         * @author vander
         * @date 2019年3月29日
         */
        public interface DataSourceNames {
            String FIRST = "first";
            String SECOND = "second";
        }
        
        /**
         * 多数据源注解
         */
        @Target(ElementType.METHOD)
        @Retention(RetentionPolicy.RUNTIME)
        @Documented
        public @interface DataSource {
            String name() default "";
        }
        
        /**
         * 多数据源，切面处理类
         */
        @Aspect
        @Component
        public class DataSourceAspect implements Ordered {
            protected Logger logger = LoggerFactory.getLogger(getClass());
        
            @Pointcut("@annotation(com.plxc.springboot.datasources.annotation.DataSource)")
            public void dataSourcePointCut() {
            }
        
            @Around("dataSourcePointCut()")
            public Object around(ProceedingJoinPoint point) throws Throwable {
                MethodSignature signature = (MethodSignature) point.getSignature();
                Method method = signature.getMethod();
        
                DataSource ds = method.getAnnotation(DataSource.class);
                if(ds == null){
                    DynamicDataSource.setDataSource(DataSourceNames.FIRST);
                    logger.debug("set datasource is " + DataSourceNames.FIRST);
                }else {
                    DynamicDataSource.setDataSource(ds.name());
                    logger.debug("set datasource is " + ds.name());
                }
        
                try {
                    return point.proceed();
                } finally {
                    DynamicDataSource.clearDataSource();
                    logger.debug("clean datasource");
                }
            }
        
            @Override
            public int getOrder() {
                return 1;
            }
        }
        
        /**
         * 配置多数据源
         */
        @Configuration
        public class DynamicDataSourceConfig {
        
            @Bean
            @ConfigurationProperties("spring.datasource.druid.first")
            public DataSource firstDataSource(){
                return DruidDataSourceBuilder.create().build();
            }
        
            @Bean
            @ConfigurationProperties("spring.datasource.druid.second")
            public DataSource secondDataSource(){
                return DruidDataSourceBuilder.create().build();
            }
        
            @Bean(name = "kettleJdbcTemplate")
            public JdbcTemplate kettleJdbcTemplate(@Qualifier("secondDataSource") DataSource kettleSource) {
                return new JdbcTemplate(kettleSource);
            }
        
        
            @Bean
            @Primary
            public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) {
                Map<String, DataSource> targetDataSources = new HashMap<>();
                targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
                targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
                return new DynamicDataSource(firstDataSource, targetDataSources);
            }
        }
        
        /**
         * 动态数据源
         */
        public class DynamicDataSource extends AbstractRoutingDataSource {
            private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
        
            public DynamicDataSource(DataSource defaultTargetDataSource, Map<String, DataSource> targetDataSources) {
                super.setDefaultTargetDataSource(defaultTargetDataSource);
                super.setTargetDataSources(new HashMap<>(targetDataSources));
                super.afterPropertiesSet();
            }
        
            @Override
            protected Object determineCurrentLookupKey() {
                return getDataSource();
            }
        
            public static void setDataSource(String dataSource) {
                contextHolder.set(dataSource);
            }
        
            public static String getDataSource() {
                return contextHolder.get();
            }
        
            public static void clearDataSource() {
                contextHolder.remove();
            }
        
        }
        
        /**
         * 测试
         */
        @Service
        public class DataSourceTestService {
            @Autowired
            private UserService userService;
        
            public UserEntity queryObject(Long userId){
                return userService.queryObject(userId);
            }
        
            @DataSource(name = DataSourceNames.SECOND)
            public UserEntity queryObject2(Long userId){
                return userService.queryObject(userId);
            }
        }
    
    @SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
    @Import({DynamicDataSourceConfig.class})
    public class AppApplication extends SpringBootServletInitializer {
    
    	public static void main(String[] args) {
    		SpringApplication.run(AppApplication.class, args);
    	}
    
    	@Override
    	protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
    		return application.sources(AppApplication.class);
    	}
    }

# starter实现 #

## 依赖pom.xml ##

<dependency>
    		<groupId>com.alibaba</groupId>
    		<artifactId>druid</artifactId>
    	</dependency>
    	<dependency>
    		<groupId>com.alibaba</groupId>
    		<artifactId>druid-spring-boot-starter</artifactId>
    	</dependency>
    	<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>
       <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

## 配置文件bootstrap.yml ##

spring:
          datasource:
            druid:
              url: ${MYSQL_URL:jdbc:mysql://localhost:3306/tb_user?characterEncoding=utf-8}
              username: root
              password: mysql
              driverClassName: com.mysql.jdbc.Driver
              initialSize: 5  #初始建立连接数量
              minIdle: 5  #最小连接数量
              maxActive: 20 #最大连接数量
              maxWait: 10000  #获取连接最大等待时间，毫秒
              testOnBorrow: true #申请连接时检测连接是否有效
              testOnReturn: false #归还连接时检测连接是否有效
              timeBetweenEvictionRunsMillis: 60000 #配置间隔检测连接是否有效的时间（单位是毫秒）
              minEvictableIdleTimeMillis: 300000  #连接在连接池的最小生存时间（毫秒）
    mybatis:
        mapperLocations: classpath:mapper/**/*.xml
        configuration:
            map-underscore-to-camel-case: true

## 配置源码 ##

@MapperScan("com.plxc.ucenter.dao")
    @EntityScan("com.plxc.framework.domain.ucenter")//扫描实体类
    @SpringBootApplication
    public class UcenterApplication {
        public static void main(String[] args) throws Exception {
            SpringApplication.run(UcenterApplication.class, args);
        }
    }

# 自定义实现 #

## 依赖pom.xml ##

<dependency>
    		<groupId>com.alibaba</groupId>
    		<artifactId>druid</artifactId>
    	</dependency>

## 配置文件bootstrap.yml ##

//spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    //spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    //spring.datasource.url=jdbc:mysql://192.168.164.100:3306/renren_fast?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    //spring.datasource.username=root
    //spring.datasource.password=qwe123
    //spring.datasource.initialSize=5
    //spring.datasource.minIdle=5
    //spring.datasource.maxActive=20
    //spring.datasource.maxWait=60000
    //spring.datasource.timeBetweenEvictionRunsMillis=60000
    //spring.datasource.minEvictableIdleTimeMillis=300000
    //spring.datasource.validationQuery=SELECT 1
    //spring.datasource.testWhileIdle=true
    //spring.datasource.testOnBorrow=false
    //spring.datasource.testOnReturn=false
    //spring.datasource.poolPreparedStatements=true
    //spring.datasource.filters=stat
    //spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    //spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    //spring.datasource.useGlobalDataSourceStat=true
    
    //spring: 
    //	  datasource: 
    //	    type: com.alibaba.druid.pool.DruidDataSource
    //	    driver-class-name: com.mysql.jdbc.Driver
    //	    url: jdbc:mysql://192.168.164.100:3306/renren_fast?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    //	    username: root
    //	    password: qwe123
    //	    initial-size: 5
    //	    min-idle: 5
    //	    max-active: 20
    //	    max-wait: 60000
    //	    time-between-eviction-runs-millis: 60000
    //	    min-evictable-idle-time-millis: 300000
    //	    validation-query: SELECT 1
    //	    test-while-idle: true
    //	    test-on-borrow: false
    //	    test-on-return: false
    //	    pool-prepared-statements: true
    //	    filters: stat
    //	    max-pool-prepared-statement-per-connection-size: 20
    //	    connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    //	    use-global-data-source-stat: true

## 配置源码 ##

/**
     * Druid配置中心
     *
     *
     * @author vander
     * @date 2018年11月20日
     */
    @SuppressWarnings("all")
    @Configuration
    @ConditionalOnProperty(value="pingruan.base.enable-druid",havingValue="true")
    public class DruidConfig {
    	
    	@Autowired
    	BProperties bProperties;
    	
    	
        /**
         * @function 添加
         * @return
         */
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource druid(){
        	DruidDataSource datasource = new DruidDataSource();
        	openMultiStatement(datasource);
            return datasource;
        }
    
        //配置多sql执行  filters: stat
    	private void openMultiStatement(DruidDataSource datasource) {
    		WallConfig wc = new WallConfig();
        	wc.setMultiStatementAllow(true);
        	WallFilter wf = new WallFilter();
        	wf.setConfig(wc);
        	List<Filter> filters = new ArrayList<Filter>();
        	filters.add(wf);
        	datasource.setProxyFilters(filters);
    	}
    
        //配置Druid的监控
        //1、配置一个管理后台的Servlet
        @Bean
        public ServletRegistrationBean statViewServlet(){
    		ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            Map<String,String> initParams = new HashMap<>();
            initParams.put("loginUsername",bProperties.getDruidUserName());
            initParams.put("loginPassword",bProperties.getDruidPassword());
            initParams.put("allow",bProperties.getDruidAllowIps());//默认就是允许所有访问
            initParams.put("deny",bProperties.getDruidDenyIps());
            bean.setInitParameters(initParams);
            return bean;
        }
    
        //2、配置一个web监控的filter
        @Bean
        public FilterRegistrationBean webStatFilter(){
            FilterRegistrationBean bean = new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
            Map<String,String> initParams = new HashMap<>();
            initParams.put("exclusions","*.js,*.gif,*.png,*.css,*.ico,/druid/*");
            bean.setInitParameters(initParams);
            bean.setUrlPatterns(Arrays.asList("/*"));
            return  bean;
        }
    }
    
    @Component
    @ConfigurationProperties(prefix="spring.datasource")
    @Data
    public class DruidProperties {
    	
    	private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;
        private boolean useGlobalDataSourceStat;
    
    }