初始化数据与连接池

初始化数据 

sql:
  init:
    schema-locations:
      - classpath:department.sql
    mode: always

hikari

https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby

spring:
  datasource:
    hikari:
      auto-commit: true
      data-source-properties:
        # 缓存 SQL 语句
        cachePrepStmts: true
        # 缓存大小
        prepStmtCacheSize: 250
        # 缓存 SQL 的长度限制
        prepStmtCacheSqlLimit: 2048
        # 服务器预编译
        useServerPrepStmts: true
      pool-name: Hikari
#      最小连接数
      minimum-idle: 20
      maximum-pool-size: 500
      # 等待连接池分配连接的时间 毫秒 默认 30
      connection-timeout: 500
#      连接超时的最大 时常 毫秒 默认10分钟
      idle-timeout: 60000
#      连接的生命时常 超时没有被使用 则回收 毫秒 默认30分钟
      max-lifetime: 30000000
      connection-test-query: select 1

 druids

https://github.com/alibaba/druid/wiki/Druid%E8%BF%9E%E6%8E%A5%E6%B1%A0%E4%BB%8B%E7%BB%8D

Pom

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>${druids.version}</version>
</dependency>

配置

datasource
  type: com.alibaba.druid.pool.DruidDataSource
druid
  initial-size: 200
  validation-query: select 1
  test-while-idle: true
  min-idle: 50
  max-active: 600
  max-wait: 600

spring:
 datasource:
   username: root
   password: root
   url: jdbc:mysql://127.0.0.1:3306/sql?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
   driver-class-name: com.mysql.cj.jdbc.Driver
   type: com.alibaba.druid.pool.DruidDataSource
   druid:
     filter:
       slf4j:
         enabled: true
     initial-size: 10
     validation-query: select 1
     test-while-idle: true
     min-idle: 12
     max-active: 50
     max-wait: 600
     web-stat-filter:
       enabled: true
       url-pattern: "/*"
       exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
     stat-view-servlet:
       enabled: true
#        默认允许所有
       allow: ""
       deny: 192.168.163.8,192.168.168.5
       url-pattern: "/druid/*"
#        禁用HTML页面上的“Reset All”功能
       reset-enable: false
       login-username: root
       login-password: root

 web 监视

@Configuration
public class DruidConfig {
  /**
     * 配置Druid的监控
     * 配置一个管理后台的Servlet
     *
     * @return ServletRegistrationBean
     */
  @Bean
  public ServletRegistrationBean statViewServlet() {
    ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    Map<String, String> initParams = new HashMap<>(4);

    initParams.put("loginUsername", "admin");
    initParams.put("loginPassword", "123456");
    //默认就是允许所有访问
    initParams.put("allow", "");
    initParams.put("deny", "192.168.15.21");
    bean.setInitParameters(initParams);
    return bean;
  }


  /**
     * 配置一个web监控的filter
     *
     * @return FilterRegistrationBean
     */
  @Bean
  public FilterRegistrationBean webStatFilter() {
    FilterRegistrationBean bean = new FilterRegistrationBean();
    bean.setFilter(new WebStatFilter());
    Map<String, String> initParams = new HashMap<>(1);
    initParams.put("exclusions", "*.js,*.css,/druid/*");
    bean.setInitParameters(initParams);
    bean.setUrlPatterns(Arrays.asList("/*"));
    return bean;
  }
}

validationQuery

Mysql:SELECT 1;
SQLSERVER:SELECT 1;
ORACLE:SELECT 'x' FROM DUAL;
PostGresql:SELECT 'x';

如果是Mysql数据库，则validationQuery不会起作用，Mysql会使用ping的方式验证

获取连接

@ExtendWith(SpringExtension.class)
@SpringBootTest
public class ConnectionTest {

  @Autowired
  DataSource dataSource;

  @Test
  public void testDataSourceAttribute() throws SQLException {

    //System.out.println(dataSource.getClass());
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
  }
}