springcloud整合druid连接池或者HikariCP连接池

太过爱你忘了你带给我的痛 2022-05-12 12:54 427阅读 0赞

#### Druid连接池 ####

**1.添加配置**

spring:
        # 数据库配置
        datasource:
          # 数据源
          type: ${db-type}
          driverClassName: ${db-driverClass}
          url: ${db-url}
          username: ${db-username}
          password: ${db-password}
    #      自定义连接
    #      type: com.alibaba.druid.pool.DruidDataSource
    #      driverClassName: com.mysql.jdbc.Driver
    #      url: jdbc:mysql://127.0.0.1:3306/dev_xinqiuboche_local2?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8
    #      password:
    #      username: root
    
          # 连接池配置
          initialSize: ${db-initial-size}
          minIdle: ${db-min-idle}
          maxActive: ${db-max-active}
          # 连接等待超时时间
          maxWait: ${db-max-wait}
          # 配置隔多久进行一次检测(检测可以关闭的空闲连接)
          timeBetweenEvictionRunsMillis: ${db-time-between-eviction-runs-millis}
          minEvictableIdleTimeMillis: ${db-min-evictable-idle-time-millis}
          validationQuery: ${db-validation-query}
          testWhileIdle: ${db-test-while-idle}
          testOnBorrow: ${db-test-on-borrow}
          testOnReturn: ${db-test-on-return}
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: ${db-pool-prepared-statements}
          maxPoolPreparedStatementPerConnectionSize: ${db-max-pool-prepared-statement-per-connection-size}
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
          filters: ${db-filters}
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: ${db-connection-properties}

**2.Bean配置**

import com.alibaba.druid.pool.DruidDataSource;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    
    @Configuration
    @Primary // 在同样的DataSource中，首先使用被标注的DataSource
    @Slf4j
    public class DruidDataSourceConfig extends DataSourceProperties {
    
        @org.springframework.beans.factory.annotation.Value("${spring.datasource.url}")
        private String dbUrl;
    
        @Value("${spring.datasource.username}")
        private String username;
    
        @Value("${spring.datasource.password}")
        private String password;
    
        @Value("${spring.datasource.driverClassName}")
        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实例
        @Bean
        public DataSource dataSource(){
            DruidDataSource datasource = new DruidDataSource();
    
            datasource.setUrl(this.dbUrl);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);
    
            // 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);
            } catch (SQLException e) {
                log.error("druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
    
            return datasource;
        }
    
    }

#### HIkaciCP连接池 ####

**1.配置同上**

**2.添加依赖**

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.1</version>
                
                <exclusions>
                    <exclusion>
                        <groupId>org.apache.tomcat</groupId>
                        <artifactId>tomcat-jdbc</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
    
    <dependency>
                <groupId>com.zaxxer</groupId>
                <artifactId>HikariCP</artifactId>
                <version>2.7.4</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.39</version>
            </dependency>

**3.Bean配置**

@Bean(destroyMethod = "close")
    public DataSource dataSource(){
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("com.mysql.jdbc.Driver");
        hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/spring-test"); 
        hikariConfig.setUsername("root");
        hikariConfig.setPassword("admin");
    
        hikariConfig.setMaximumPoolSize(5);
        hikariConfig.setConnectionTestQuery("SELECT 1");
        hikariConfig.setPoolName("springHikariCP");
    
        hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
        hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");
    
        HikariDataSource dataSource = new HikariDataSource(hikariConfig);
    
        return dataSource;
    }