Mysql利用Druid配置多数据源

  Druid 是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池。是 Java 的数据库连接池组件。Druid 能够提供强大的监控和扩展功能。比如可以监控 SQL ，在监控业务可以查询慢查询 SQL 列表等。Druid 核心主要包括三部分：

Druid 0.1.18 之后版本都发布到maven中央仓库中，所以你只需要在项目的pom.xml中加上dependency就可以了。例如：

    <dependency>

        <groupId>com.alibaba</groupId>

        <artifactId>druid</artifactId>

        <version>${druid-version}</version>

</dependency>

运行Tomcat，浏览器输入 http://IP:PROT/druid就可以打开Druid的监控页面了.

Druid提供了WallFilter，它是基于SQL语义分析来实现防御SQL注入攻击的。

Druid提供了Log4jFilter、CommonsLogFilter和Slf4jFilter

Druid提供了多种监测连接泄漏的手段

在StatFilter配置中有慢SQL执行日志记录

下面例子：

@Configuration

@MapperScan(basePackages = DruidConfig1.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")

public class DruidConfig1 {

    private Logger logger = LoggerFactory.getLogger(DruidConfig1.class);

    static final String PACKAGE = "com.oppo.bot.common.dal.mapper";  //需要扫描的mapper文件

    static final String MAPPER_LOCATION = "classpath*:mybatis/mapper/*.xml"; //需要扫描的mybaties的xml文件

//    @HeraclesDynamicConfig(key = "spring.datasource.url")

//    @Value("${spring.datasource.url}")

//    private String dbUrl ;

    private String dbUrl = "jdbc:mysql://172.17.161.208:3306/bot_dialog?useUnicode=true&characterEncoding=UTF-8";

 

    @HeraclesDynamicConfig(key = "spring.datasource.username")

    @Value("${spring.datasource.username}")

    private String username;

 

    @HeraclesDynamicConfig(key = "spring.datasource.password")

    @Value("${spring.datasource.password}")

    private String password;

 

    @Value("${spring.datasource.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.filters}")

    private String filters;

 

    @Value("${spring.datasource.logSlowSql}")

    private String logSlowSql;

 

    @Bean(name = "masterDruidServlet)")

    public ServletRegistrationBean druidServlet() {

        ServletRegistrationBean reg = new ServletRegistrationBean();

        reg.setServlet(new StatViewServlet());

        reg.addUrlMappings("/druid/*");

        reg.addInitParameter("loginUsername", username);

        reg.addInitParameter("loginPassword", password);

        reg.addInitParameter("logSlowSql", logSlowSql);

        return reg;

    }

 

    @Bean(name = "masterFilterRegistrationBean")

    public FilterRegistrationBean filterRegistrationBean() {

        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();

        filterRegistrationBean.setFilter(new WebStatFilter());

        filterRegistrationBean.addUrlPatterns("/*");

        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

        filterRegistrationBean.addInitParameter("profileEnable", "true");

        return filterRegistrationBean;

    }

 

    @Bean(name="masterDataSource")

    @Primary

    public DataSource druidDataSource() {

        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(dbUrl);

        datasource.setUsername(username);

        datasource.setPassword(password);

        datasource.setDriverClassName(driverClassName);

        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);

        try {

            datasource.setFilters(filters);

        } catch (SQLException e) {

            logger.error("DruidConfig->druidDataSource druid configuration initialization filter:{}", e);

        }

        return datasource;

    }

 

    @Bean(name = "masterSqlSessionFactory")

    @Primary

    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)

            throws Exception {

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

        sessionFactory.setDataSource(masterDataSource);

        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()

                .getResources(DruidConfig1.MAPPER_LOCATION));//区分扫描的mapper文件接口位置

        return sessionFactory.getObject();

    }

}

其中ServletRegistrationBean就是为了能够在浏览器中访问druid控制台，basePackages = DruidConfig1.PACKAGE,为了标明该配置所关联的mapper接口，sqlSessionFactoryRef为下面SqlSessionFactory对象。

# 默认的数据源 （主库）master.datasource.url=jdbc:mysql://localhost:3306/springBoot?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true 

master.datasource.username=root 

master.datasource.password=123456

 master.datasource.driverClassName=com.mysql.jdbc.Driver 

# 另一个的数据源 （从库）

cluster.datasource.url=jdbc:mysql://localhost:3306/springBoot_testuseUnicode=true&characterEncoding=utf8 

cluster.datasource.username=root 

cluster.datasource.password=123456 

cluster.datasource.driverClassName=com.mysql.jdbc.Driver # 

连接池的配置信息 # 初始化大小，最小，最大 

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource 

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 FROM DUAL 

spring.datasource.testWhileIdle=true 

spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false 

# 打开PSCache，并且指定每个连接上PSCache的大小 spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.filters=stat,wall,log4j 

# 通过connectProperties属性来打开mergeSql功能；慢SQL记录 超过5000毫秒的都需要记录spring.datasource.connectionProperties=druid.stat.mergeSql=true;

druid.stat.slowSqlMillis=5000

但是在springboot1.0和2.0中Druid的版本兼容不一样如果还是按照1.0的话会报错，所以需要使用如下配置：

 

@Configuration
@MapperScan(basePackages = CommonDruidConfig.PACKAGE, sqlSessionFactoryRef = "commonSqlSessionFactory")
public class CommonDruidConfig {
    private Logger logger = LoggerFactory.getLogger(CommonDruidConfig.class);
    static final String PACKAGE = "com.oppo.bot.common.dal.mapper";
    static final String MAPPER_LOCATION = "classpath*:mybatis/mapper/*.xml";

    @Value("${spring.datasource.url}")
    private String dbUrl ;
//  private String dbUrl = "jdbc:mysql://172.17.161.209:3306/zpf-bot-common?useUnicode=true&characterEncoding=UTF-8";

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.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.filters}")
    private String filters;

    @Value("${spring.datasource.logSlowSql}")
    private String logSlowSql;

    @Bean("commonServletRegistrationBean")
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", username);
        reg.addInitParameter("loginPassword", password);
        reg.addInitParameter("logSlowSql", logSlowSql);
        return reg;
    }

    @Bean("commonFilterRegistrationBean")
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }
    @Bean(name = "commonTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("commonDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "commonSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("commonSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "commonDataSource")
    public DataSource druidDataSource() {
        logger.info("共用数据库连接地址{}",dbUrl);
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        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);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("DruidConfig->druidDataSource druid configuration initialization filter:{}", e);
        }
        return datasource;
    }

    @Bean(name = "commonSqlSessionFactory")
    public SqlSessionFactory commonSqlSessionFactory(@Qualifier("commonDataSource") DataSource commonDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(commonDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(CommonDruidConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}