Java Code Examples for com.zaxxer.hikari.HikariDataSource#setConnectionTimeout()

/**
 * 测试数据源
 *
 * @param dataSource 数据源
 * @return true 成功 false 失败
 */
public boolean testConnection(DataSource dataSource) {
    HikariDataSource hikariDataSource = new HikariDataSource();
    hikariDataSource.setUsername(dataSource.getUser());
    hikariDataSource.setPassword(dataSource.getPassword());
    hikariDataSource.setJdbcUrl("jdbc:mysql://" + dataSource.getHost() + ":" + dataSource.getPort() + "/" + dataSource.getDatabase() + "?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false&serverTimezone=CTT");
    hikariDataSource.setDriverClassName(dataSource.getDriveName());
    hikariDataSource.setConnectionTimeout(250);
    JdbcTemplate jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
    jdbcTemplate.setDataSource(hikariDataSource);

    try {
        jdbcTemplate.query("SELECT 1", (rs, rowNum) -> rs.getString(1));
    } catch (Exception e) {
        return false;
    }
    return true;
}
 

@Bean
public HikariDataSource dataSource(DataSourceProperties properties) {
    HikariDataSource hikariDataSource = (HikariDataSource) properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    hikariDataSource.setConnectionTimeout(DB_CONN_TIMEOUT);
    hikariDataSource.setMaximumPoolSize(DB_MAX_POOL_SIZE);
    hikariDataSource.setMaxLifetime(DB_MAX_LIFE_TIME);
    hikariDataSource.setAutoCommit(DB_AUTOCOMMIT);
    hikariDataSource.setConnectionTestQuery("SELECT 1");
    hikariDataSource.addDataSourceProperty("cachePrepStmts", DB_CACHE_PREP_STMTS);
    hikariDataSource.addDataSourceProperty("prepStmtCacheSize", DB_PREP_STMT_CACHE_SIZE);
    hikariDataSource.addDataSourceProperty("prepStmtCacheSqlLimit", DB_PREP_STMT_CACHE_SQL_LIMIT);
    hikariDataSource.addDataSourceProperty("useServerPrepStmts", DB_USE_SERVER_PREP_STMTS);
    hikariDataSource.addDataSourceProperty("useLocalSessionState", DB_USE_LOCAL_SESSION_STATE);
    hikariDataSource.addDataSourceProperty("rewriteBatchedStatements", DB_REWRITE_BATCHED_STATEMENTS);
    hikariDataSource.addDataSourceProperty("cacheResultSetMetadata", DB_CACHE_RESULT_SET_METADATA);
    hikariDataSource.addDataSourceProperty("cacheServerConfiguration", DB_CACHE_SERVER_CONFIGURATION);
    hikariDataSource.addDataSourceProperty("elideSetAutoCommits", DB_ELIDE_SET_AUTO_COMMITS);
    hikariDataSource.addDataSourceProperty("maintainTimeStats", DB_MAINTAIN_TIME_STATS);

    return hikariDataSource;
}
 

private HikariDataSource createHikariDataSource(JdbcProperties jdbcProperties) {
  HikariDataSource hikariDataSource = new HikariDataSource();
  hikariDataSource.setJdbcUrl(jdbcProperties.getJdbcUrl());
  hikariDataSource.setUsername(jdbcProperties.getUsername());
  hikariDataSource.setPassword(jdbcProperties.getPassword());

  JdbcPoolProperties jdbcPoolProperties = jdbcProperties.getJdbcPool();
  hikariDataSource.setAutoCommit(jdbcPoolProperties.isAutoCommit());
  hikariDataSource.setConnectionTimeout(jdbcPoolProperties.getConnectionTimeout());
  hikariDataSource.setIdleTimeout(jdbcPoolProperties.getIdleTimeout());
  hikariDataSource.setMaxLifetime(jdbcPoolProperties.getMaxLifetime());
  hikariDataSource.setMaximumPoolSize(jdbcPoolProperties.getMaximumPoolSize());
  hikariDataSource.setMinimumIdle(jdbcPoolProperties.getMinimumIdle());
  hikariDataSource
      .setInitializationFailTimeout(jdbcPoolProperties.getInitializationFailTimeout());
  hikariDataSource.setIsolateInternalQueries(jdbcPoolProperties.isIsolateInternalQueries());
  hikariDataSource.setReadOnly(jdbcPoolProperties.isReadOnly());
  hikariDataSource.setRegisterMbeans(jdbcPoolProperties.isRegisterMbeans());
  Optional.ofNullable(jdbcPoolProperties.getDriverClassName())
      .ifPresent(hikariDataSource::setDriverClassName);
  hikariDataSource.setValidationTimeout(jdbcPoolProperties.getValidationTimeout());
  hikariDataSource.setLeakDetectionThreshold(jdbcPoolProperties.getLeakDetectionThreshold());
  return hikariDataSource;
}
 

private void getDataSource(JobDatasource jobDatasource) throws SQLException {
    String userName = AESUtil.decrypt(jobDatasource.getJdbcUsername());

    //这里默认使用 hikari 数据源
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setUsername(userName);
    dataSource.setPassword(AESUtil.decrypt(jobDatasource.getJdbcPassword()));
    dataSource.setJdbcUrl(jobDatasource.getJdbcUrl());
    dataSource.setDriverClassName(jobDatasource.getJdbcDriverClass());
    dataSource.setMaximumPoolSize(1);
    dataSource.setMinimumIdle(0);
    dataSource.setConnectionTimeout(30000);
    this.datasource = dataSource;
    this.connection = this.datasource.getConnection();
}
 

static JdbcTemplate jdbc(MySqlConnectionConfiguration configuration, @Nullable String timezone) {
    HikariDataSource source = new HikariDataSource();

    source.setJdbcUrl(String.format("jdbc:mysql://%s:%d/%s", configuration.getDomain(), configuration.getPort(), configuration.getDatabase()));
    source.setUsername(configuration.getUser());
    source.setPassword(Optional.ofNullable(configuration.getPassword()).map(Object::toString).orElse(null));
    source.setMaximumPoolSize(1);
    source.setConnectionTimeout(Optional.ofNullable(configuration.getConnectTimeout()).map(Duration::toMillis).orElse(0L));

    if (timezone != null) {
        source.addDataSourceProperty("serverTimezone", timezone);
    }

    return new JdbcTemplate(source);
}
 

private static DataSource doGetDataSource(final H2DataSourceProperties datasourceProperties) {
    // database file name on disk is "<path>/cantor.db"
    final Path dbPath = Paths.get(String.format("%s/cantor", datasourceProperties.getPath()));

    final String jdbcUrl = String.format(
            "jdbc:h2:%s:%s;" +
                    "MODE=MYSQL;" +
                    "COMPRESS=" + String.valueOf(datasourceProperties.isCompressed()).toUpperCase() + ";" +
                    "LOCK_TIMEOUT=30000;" +
                    "DB_CLOSE_ON_EXIT=FALSE;" +
                    "TRACE_LEVEL_FILE=1;" +
                    "TRACE_MAX_FILE_SIZE=4;" +
                    "AUTOCOMMIT=TRUE;" +
                    "AUTO_SERVER=" + String.valueOf(datasourceProperties.isAutoServer()).toUpperCase() + ";" +
                    "LOCK_MODE=1;" +
                    "MAX_COMPACT_TIME=3000;",
            (datasourceProperties.isInMemory() ? "mem" : "split"),
            dbPath.toAbsolutePath().toString());
    logger.info("jdbc url for datasource is: {}", jdbcUrl);

    try {
        // force loading h2 driver
        Class.forName("org.h2.Driver");
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    }

    final HikariDataSource connectoinPoolDataSource = new HikariDataSource();
    connectoinPoolDataSource.setJdbcUrl(jdbcUrl);
    connectoinPoolDataSource.setUsername(datasourceProperties.getUsername());
    connectoinPoolDataSource.setPassword(datasourceProperties.getPassword());
    connectoinPoolDataSource.setMaximumPoolSize(datasourceProperties.getMaxPoolSize());
    connectoinPoolDataSource.setConnectionTimeout(datasourceProperties.getConnectionTimeoutMillis());

    return connectoinPoolDataSource;
}
 

private DataSource create() {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        ds.setDriverClassName(driver);

        ds.setReadOnly(false);
        // 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
        ds.setConnectionTimeout(30000);
        // 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
//        config.setIdleTimeout(configEntity.getMaxIdleTime());
        // 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒，参考MySQLwait_timeout参数（show variables like '%timeout%';）
        ds.setMaxLifetime(1800000 - 60000);
        // 连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
        ds.setMaximumPoolSize(Runtime.getRuntime().availableProcessors() * 5 + 30);
        ds.setConnectionInitSql("set names utf8mb4"); // 支持表情符
//        ds.setInitialSize(1);
//        config.setMinIdle(1);
//        ds.setMaxActive(400);
//        ds.setMaxWait(10000);
//        ds.setTimeBetweenEvictionRunsMillis(600000);
//        ds.setMinEvictableIdleTimeMillis(600000);
//        ds.setTestWhileIdle(true);
//        ds.setTestOnBorrow(true);
//        ds.setTestOnReturn(false);
//        ds.setValidationQuery("select 1");
//        ds.setConnectionInitSqls(Arrays.asList("set names utf8mb4")); // 支持表情符

//        ds.addDataSourceProperty("cachePrepStmts", "true");
//        ds.addDataSourceProperty("prepStmtCacheSize", "250");
//        ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        return ds;
    }
 

static JdbcTemplate jdbc(MySqlConnectionConfiguration configuration, @Nullable String timezone) {
    HikariDataSource source = new HikariDataSource();

    source.setJdbcUrl(String.format("jdbc:mysql://%s:%d/%s", configuration.getDomain(), configuration.getPort(), configuration.getDatabase()));
    source.setUsername(configuration.getUser());
    source.setPassword(Optional.ofNullable(configuration.getPassword()).map(Object::toString).orElse(null));
    source.setMaximumPoolSize(1);
    source.setConnectionTimeout(Optional.ofNullable(configuration.getConnectTimeout()).map(Duration::toMillis).orElse(0L));

    if (timezone != null) {
        source.addDataSourceProperty("serverTimezone", timezone);
    }

    return new JdbcTemplate(source);
}
 

@Override
public void init(final String modelName, final HmilyConfig txConfig) {
    try {
        final HmilyDbConfig hmilyDbConfig = txConfig.getHmilyDbConfig();
        if (hmilyDbConfig.getDataSource() != null && StringUtils.isBlank(hmilyDbConfig.getUrl())) {
            dataSource = hmilyDbConfig.getDataSource();
        } else {
            HikariDataSource hikariDataSource = new HikariDataSource();
            hikariDataSource.setJdbcUrl(hmilyDbConfig.getUrl());
            hikariDataSource.setDriverClassName(hmilyDbConfig.getDriverClassName());
            hikariDataSource.setUsername(hmilyDbConfig.getUsername());
            hikariDataSource.setPassword(hmilyDbConfig.getPassword());
            hikariDataSource.setMaximumPoolSize(hmilyDbConfig.getMaxActive());
            hikariDataSource.setMinimumIdle(hmilyDbConfig.getMinIdle());
            hikariDataSource.setConnectionTimeout(hmilyDbConfig.getConnectionTimeout());
            hikariDataSource.setIdleTimeout(hmilyDbConfig.getIdleTimeout());
            hikariDataSource.setMaxLifetime(hmilyDbConfig.getMaxLifetime());
            hikariDataSource.setConnectionTestQuery(hmilyDbConfig.getConnectionTestQuery());
            if (hmilyDbConfig.getDataSourcePropertyMap() != null && !hmilyDbConfig.getDataSourcePropertyMap().isEmpty()) {
                hmilyDbConfig.getDataSourcePropertyMap().forEach(hikariDataSource::addDataSourceProperty);
            }
            dataSource = hikariDataSource;
        }
        this.tableName = RepositoryPathUtils.buildDbTableName(modelName);
        //save current database type
        this.currentDBType = DbTypeUtils.buildByDriverClassName(hmilyDbConfig.getDriverClassName());
        executeUpdate(SqlHelper.buildCreateTableSql(hmilyDbConfig.getDriverClassName(), tableName));
    } catch (Exception e) {
        LogUtil.error(LOGGER, "hmily jdbc log init exception please check config:{}", e::getMessage);
        throw new HmilyRuntimeException(e);
    }
}
 

protected HikariDataSource createDataSource() {
    // get the configured datasources
    Map<String, Object> dataSourceMap = Config.getInstance().getJsonMapConfig(DATASOURCE);

    // get the decrypted secret file
    Map<String, Object> secret = Config.getInstance().getJsonMapConfig(SECRET);

    // get the requested datasource
    Map<String, Object> mainParams = (Map<String, Object>) dataSourceMap.get(getDsName());
    Map<String, String> configParams = (Map<String, String>)mainParams.get("parameters");

    // create the DataSource
    ds = new HikariDataSource();
    ds.setJdbcUrl((String)mainParams.get("jdbcUrl"));
    ds.setUsername((String)mainParams.get("username"));

    // use encrypted password
    String password = (String)mainParams.get(DB_PASSWORD);
    if (password==null) {
        password = (String)secret.get(getDbPassKey());
    }
    ds.setPassword(password);

    // set datasource paramters
    ds.setMaximumPoolSize((Integer)mainParams.get("maximumPoolSize"));
    ds.setConnectionTimeout((Integer)mainParams.get("connectionTimeout"));

    // add datasource specific connection parameters
    if(configParams != null) configParams.forEach((k, v) -> ds.addDataSourceProperty(k, v));

    return ds;
}
 

private static HikariDataSource createHikariDataSource(final DatabaseType databaseType, final String databaseName) {
    HikariDataSource result = new HikariDataSource();
    result.setJdbcUrl(getURL(databaseType, databaseName));
    result.setUsername("root");
    result.setPassword("root");
    result.setMaximumPoolSize(10);
    result.setMinimumIdle(2);
    result.setConnectionTimeout(15 * 1000);
    result.setIdleTimeout(40 * 1000);
    return result;
}
 

@Bean
@Profile({"!"+Initializer.PROFILE_INTEGRATION_TEST, "travis"})
public DataSource getDataSource(Environment env, PlatformProvider platform) {
    if(platform == PlatformProvider.CLOUD_FOUNDRY) {
        return new FakeCFDataSource();
    } else {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(platform.getUrl(env));
        dataSource.setUsername(platform.getUsername(env));
        dataSource.setPassword(platform.getPassword(env));
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setMaximumPoolSize(platform.getMaxActive(env));
        dataSource.setMinimumIdle(platform.getMinIdle(env));
        dataSource.setConnectionTimeout(1000L);

        log.debug("Connection pool properties: max active {}, initial size {}", dataSource.getMaximumPoolSize(), dataSource.getMinimumIdle());

        // check
        boolean isSuperAdmin = Boolean.TRUE.equals(new NamedParameterJdbcTemplate(dataSource)
            .queryForObject("select usesuper from pg_user where usename = CURRENT_USER",
                new EmptySqlParameterSource(),
                Boolean.class));

        if (isSuperAdmin) {
            log.warn("You're accessing the database using a superuser. This is highly discouraged since it will disable the row security policy checks.");
        }

        //
        return dataSource;
    }
}
 

@Override
public boolean register() {
    if (IsRegistered) {
        return HAS_REGISTERED;
    }

    HikariDataSource ds = new HikariDataSource();

    //basic config
    ds.setJdbcUrl(jdbcURL);
    ds.setDriverClassName(jdbcDriver);
    ds.setUsername(jdbcUsername);
    ds.setPassword(jdbcPassword);

    //custom config
    ds.setAutoCommit(autoCommit);
    ds.setConnectionTimeout(connectionTimeout);
    ds.setIdleTimeout(idleTimeout);
    ds.setMaxLifetime(maxLifetime);
    ds.setMaximumPoolSize(maximumPoolSize);
    ds.setValidationTimeout(validationTimeout);
    ds.setLeakDetectionThreshold(leakDetectionThreshold);

    if (!StrUtil.isBlank(poolName)) {
        ds.setPoolName(poolName);
    }

    if (!StrUtil.isBlank(catalog)) {
        ds.setCatalog(catalog);
    }

    if (!StrUtil.isBlank(connectionInitSql)) {
        ds.setConnectionInitSql(connectionInitSql);
    }

    if (!StrUtil.isBlank(transactionIsolation)) {
        ds.setTransactionIsolation(transactionIsolation);
    }

    if (jdbcURL.contains(":mysql:")) {
        ds.addDataSourceProperty("cachePrepStmts", "true");
        ds.addDataSourceProperty("prepStmtCacheSize", "250");
        ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        ds.addDataSourceProperty("useServerPrepStmts", "true");
    }

    setDataSource(ds);
    setIsRegistered(HAS_REGISTERED);
    return HAS_REGISTERED;
}
 

/**
 * Returns a new pooled data source based on jdbc url.
 * 
 * @param url
 *            jdbc url
 * @param username
 *            login username
 * @param password
 *            login password
 * @param minPoolSize
 *            minimum database connection pool size
 * @param maxPoolSize
 *            maximum database connection pool size
 * @param connectionTimeoutMs
 * @return
 */
private static HikariDataSource createPool(String url, String username, String password,
        int minPoolSize, int maxPoolSize, long connectionTimeoutMs) {

    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl(url);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setMinimumIdle(minPoolSize);
    ds.setMaximumPoolSize(maxPoolSize);
    ds.setConnectionTimeout(connectionTimeoutMs);
    return ds;
}