SpringBoot_连接池_多数据源_Druid

多数据源实现

依赖pom.xml

	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
	</dependency>
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid-spring-boot-starter</artifactId>
	</dependency>

配置文件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;

}