二、springBoot+gradle+postgresql运行环境配置以及集成druid连接池（application.yml）

「爱情、让人受尽委屈。」 2022-03-11 16:23 1313阅读 0赞

一、没有连接池的配置

server:
      port: 4567
    
    spring:
      datasource:
        username: postgres
        password: 123456
        url: jdbc:postgresql://localhost:5432/myjpaproject
        driver-class-name: org.postgresql.Driver
      jpa:
        database-platform: org.hibernate.dialect.PostgreSQL95Dialect  #设置方言
        properties:
          hibernate:
            jdbc.lob.non_contextual_creation: true #防止postgresql数据库因未实现CLOB格式而造成的数据检查出错

或者多环境配置(此处仅添加了开发环境) ，测试与生产环境往后添加即可

server:
      port: 4567
    
    spring:
      profiles:
          active: dev #默认激活的profiles
      jpa:
        database-platform: org.hibernate.dialect.PostgreSQL95Dialect  #设置方言
        properties:
          hibernate:
            jdbc.lob.non_contextual_creation: true #防止postgresql数据库因未实现CLOB格式而造成的数据检查出错
    
    
    ---
    spring:
      profiles: dev
      datasource:
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/myjpaproject
        username: postgres
        password: 123456

二、有连接池的配置

build.gradle中添加包

// druid数据库连接池
    implementation 'com.alibaba:druid-spring-boot-starter:1.1.10'

配置：

server:
      port: 4567
    
    spring:
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://localhost:5432/myjpaproject
          username: postgres
          password: 123456
          filters: stat #别名方式，扩展插件，监控统计用的filter:stat，日志用的filter:log4j，防御sql注入的filter:wall

或者

server:
      port: 4567
    
    spring:
      application:
        name: firstproject
      profiles:
          active: dev #默认激活的profiles
      jpa:
        database-platform: org.hibernate.dialect.PostgreSQL95Dialect  #设置方言
        generate-ddl: true  #允许生成数据库表结构
        open-in-view: true
        hibernate:
          ddl-auto: update  #设置ddl自动操作的类型
        properties:
          hibernate:
            jdbc.lob.non_contextual_creation: true #防止postgresql数据库因未实现CLOB格式而造成的数据检查出错
      data:
        jpa:
          repositories:
            enabled: true  #是否开启JPA支持
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          driver-class-name: org.postgresql.Driver
          # 下面为连接池的补充设置，应用到上面所有数据源中
          # 初始化大小，最小，最大
          initialSize: 1
          minIdle: 3
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 30000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，#别名方式，扩展插件，监控统计用的filter:stat，日志用的filter:log4j，防御sql注入的filter:wall
          filters: stat,slf4j,wall
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    
    
    ---
    spring:
      profiles: dev
      datasource:
        druid:
          url: jdbc:postgresql://localhost:5432/firstproject
          username: postgres
          password: 123456
          filters: stat #别名方式，扩展插件，监控统计用的filter:stat，日志用的filter:log4j，防御sql注入的filter:wall

springBoot配置druid方式(使用java文件加载配置)

1、application.yml配置(确保druid包已经加载进来)

spring:
      datasource:
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/firstproject
        username: postgres
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
        # 下面为连接池的补充设置，应用到上面所有数据源中
        # 初始化大小，最小，最大
        initialSize: 1
        minIdle: 3
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 30000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,slf4j
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        platform: postgresql
        # 合并多个DruidDataSource的监控数据
        #useGlobalDataSourceStat: true
        packages.to.scan: com.trgis.firstproject

2、创建配置文件加载

import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    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;
    
    /**
     * Druid数据源连接池配置
     */
    @Configuration
    public class DruidConfig {
    
        private static final String DB_PREFIX = "spring.datasource";
    
        @ConfigurationProperties(prefix = DB_PREFIX)
        class IDataSourceProperties {
            private String url;
            private String username;
            private String password;
            private String driverClassName;
            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;
    
            @Bean     //声明其为Bean实例
            @Primary  //在同样的DataSource中，首先使用被标注的DataSource
            public DataSource dataSource() {
                DruidDataSource datasource = new DruidDataSource();
                datasource.setUrl(url);
                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) {
                    System.err.println("druid configuration initialization filter: " + e);
                }
                datasource.setConnectionProperties(connectionProperties);
                return datasource;
            }
    
            public String getUrl() {
                return url;
            }
    
            public void setUrl(String url) {
                this.url = url;
            }
    
            public String getUsername() {
                return username;
            }
    
            public void setUsername(String username) {
                this.username = username;
            }
    
            public String getPassword() {
                return password;
            }
    
            public void setPassword(String password) {
                this.password = password;
            }
    
            public String getDriverClassName() {
                return driverClassName;
            }
    
            public void setDriverClassName(String driverClassName) {
                this.driverClassName = driverClassName;
            }
    
            public int getInitialSize() {
                return initialSize;
            }
    
            public void setInitialSize(int initialSize) {
                this.initialSize = initialSize;
            }
    
            public int getMinIdle() {
                return minIdle;
            }
    
            public void setMinIdle(int minIdle) {
                this.minIdle = minIdle;
            }
    
            public int getMaxActive() {
                return maxActive;
            }
    
            public void setMaxActive(int maxActive) {
                this.maxActive = maxActive;
            }
    
            public int getMaxWait() {
                return maxWait;
            }
    
            public void setMaxWait(int maxWait) {
                this.maxWait = maxWait;
            }
    
            public int getTimeBetweenEvictionRunsMillis() {
                return timeBetweenEvictionRunsMillis;
            }
    
            public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
                this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
            }
    
            public int getMinEvictableIdleTimeMillis() {
                return minEvictableIdleTimeMillis;
            }
    
            public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
                this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
            }
    
            public String getValidationQuery() {
                return validationQuery;
            }
    
            public void setValidationQuery(String validationQuery) {
                this.validationQuery = validationQuery;
            }
    
            public boolean isTestWhileIdle() {
                return testWhileIdle;
            }
    
            public void setTestWhileIdle(boolean testWhileIdle) {
                this.testWhileIdle = testWhileIdle;
            }
    
            public boolean isTestOnBorrow() {
                return testOnBorrow;
            }
    
            public void setTestOnBorrow(boolean testOnBorrow) {
                this.testOnBorrow = testOnBorrow;
            }
    
            public boolean isTestOnReturn() {
                return testOnReturn;
            }
    
            public void setTestOnReturn(boolean testOnReturn) {
                this.testOnReturn = testOnReturn;
            }
    
            public boolean isPoolPreparedStatements() {
                return poolPreparedStatements;
            }
    
            public void setPoolPreparedStatements(boolean poolPreparedStatements) {
                this.poolPreparedStatements = poolPreparedStatements;
            }
    
            public int getMaxPoolPreparedStatementPerConnectionSize() {
                return maxPoolPreparedStatementPerConnectionSize;
            }
    
            public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
                this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
            }
    
            public String getFilters() {
                return filters;
            }
    
            public void setFilters(String filters) {
                this.filters = filters;
            }
    
            public String getConnectionProperties() {
                return connectionProperties;
            }
    
            public void setConnectionProperties(String connectionProperties) {
                this.connectionProperties = connectionProperties;
            }
        }

扩展：若是需要druid监控，则添加一下代码到项目，访问[http://localhost:端口号/druid/login.html][http_localhost_druid_login.html]输入下面配置的账密即可进入监控

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    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;
    
    import javax.sql.DataSource;
    
    @Configuration
    public class DruidConfig {
    
        @Bean
        public ServletRegistrationBean druidServlet() {// 主要实现web监控的配置处理
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
                    new StatViewServlet(), "/druid/*");//表示进行druid监控的配置处理操作
            servletRegistrationBean.addInitParameter("allow", "127.0.0.1,192.168.0.12");//白名单
            servletRegistrationBean.addInitParameter("deny", "129.168.1.12");//黑名单
            servletRegistrationBean.addInitParameter("loginUsername", "root");//用户名
            servletRegistrationBean.addInitParameter("loginPassword", "123456");//密码
            servletRegistrationBean.addInitParameter("resetEnable", "false");//是否可以重置数据源
            return servletRegistrationBean;
    
        }
    
        @Bean    //监控
        public FilterRegistrationBean filterRegistrationBean(){
            FilterRegistrationBean filterRegistrationBean=new FilterRegistrationBean();
            filterRegistrationBean.setFilter(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");//所有请求进行监控处理
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");//排除
            return filterRegistrationBean;
        }
    
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource druidDataSource() {
            return new DruidDataSource();
        }
    
    }

[http_localhost_druid_login.html]: http://localhost/druid/login.html