Java Code Examples for com.zaxxer.hikari.HikariConfig#setConnectionTestQuery()

public static DataSource dataSource(Config config) {
  HikariConfig hikariConfig = new HikariConfig();
  hikariConfig.setDriverClassName(config.getString("datasource.driverClassName"));
  hikariConfig.setJdbcUrl(config.getString("datasource.jdbcUrl"));
  hikariConfig.setUsername(config.getString("datasource.username"));
  hikariConfig.setPassword(config.getString("datasource.password"));

  hikariConfig.setMinimumIdle(config.getInt("datasource.minimum-idle"));
  hikariConfig.setMaximumPoolSize(config.getInt("datasource.maximum-pool-size"));
  hikariConfig.setConnectionTestQuery("SELECT 1");
  hikariConfig.setPoolName(config.getString("datasource.poolName"));

  hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
  hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
  hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
  hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

  hikariConfig.setAutoCommit(true);

  return new HikariDataSource(hikariConfig);
}
 

@Bean
public DataSource dataSource() {
    try {
        final HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(driverClassName);
        hikariConfig.setJdbcUrl(url);
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);

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

        return new HikariDataSource(hikariConfig);
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}
 

@Bean
public DataSource dataSource() {
    try {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(driverClassName);
        hikariConfig.setJdbcUrl(url);
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);

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

        return new HikariDataSource(hikariConfig);
    } catch (Exception e) {
        return null;
    }
}
 

@Bean(name = "primaryDataSource")
    @Primary
//    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariDataSource dataSource() {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(hikariDattaSourceConfig.getDriverClassName());
        hikariConfig.setJdbcUrl(hikariDattaSourceConfig.getJdbcUrl());
        hikariConfig.setUsername(hikariDattaSourceConfig.getUsername());
        hikariConfig.setPassword(hikariDattaSourceConfig.getPassword());
        hikariConfig.setMaxLifetime(hikariDattaSourceConfig.getMaxlifetime());
        hikariConfig.setConnectionTestQuery(hikariDattaSourceConfig.getConnectionTestQuery());
        hikariConfig.setPoolName(hikariDattaSourceConfig.getPoolName());
        hikariConfig.setIdleTimeout(hikariDattaSourceConfig.getIdleTimeout());
        hikariConfig.setAutoCommit(true);
        hikariConfig.setConnectionTimeout(hikariDattaSourceConfig.getConnectionTimeout());
        hikariConfig.setMinimumIdle(hikariDattaSourceConfig.getMinimumTdle());
        hikariConfig.setMaximumPoolSize(hikariDattaSourceConfig.getMaximumPoolSize());
        hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
        hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");
        return new HikariDataSource(hikariConfig);
    }
 

@Before
public void initializeConnectionAndTable() throws SQLException {
    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
    config.setConnectionTestQuery("VALUES 1");
    config.addDataSourceProperty("URL", "jdbc:h2:mem:test;ignorecase=true");
    config.addDataSourceProperty("user", "sa");
    config.addDataSourceProperty("password", "sa");
    HikariDataSource ds = new HikariDataSource(config);
    Connection connection = ds.getConnection();

    try (Statement st = connection.createStatement()) {
        st.execute("DROP TABLE IF EXISTS authme");
        st.execute(sqlInitialize);
    }
    hikariSource = ds;
}
 

@Override
    public boolean start() {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(maxPoolSize);
//        config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
        config.setDriverClassName(driverClass);
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(user);
        config.setPassword(password);
        
        //防止中文乱码
        config.addDataSourceProperty("useUnicode", "true");
        config.addDataSourceProperty("characterEncoding", "utf8");
        
        config.setConnectionTestQuery("SELECT 1");

        this.dataSource = new HikariDataSource(config);
        
        return true;
    }
 

private Connection initializeMySqlTable() throws IOException, SQLException {
    File sqlInitFile = TestHelper.getJarFile(TestHelper.PROJECT_ROOT + "datasource/converter/loginsecurity.sql");
    String initStatement = new String(Files.readAllBytes(sqlInitFile.toPath()));

    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
    config.setConnectionTestQuery("VALUES 1");
    config.addDataSourceProperty("URL", "jdbc:h2:mem:test");
    config.addDataSourceProperty("user", "sa");
    config.addDataSourceProperty("password", "sa");
    HikariDataSource ds = new HikariDataSource(config);
    Connection connection = ds.getConnection();

    try (Statement st = connection.createStatement()) {
        st.execute("DROP TABLE IF EXISTS authme");
        st.execute(initStatement);
    }
    return connection;
}
 

/**
 * Loads the configuration
 */
public void loadConfiguration() {
    serverConfig = new HikariConfig();

    Path file = plugin.getDataFolder().toPath().resolve("sql.yml");
    //Check if the file exists. If so load the settings form there
    if (Files.notExists(file)) {
        //Create a new configuration based on the default settings form bukkit.yml
        plugin.saveResource("sql.yml", false);
    }

    FileConfiguration sqlConfig = YamlConfiguration.loadConfiguration(file.toFile());

    ConfigurationSection sqlSettingSection = sqlConfig.getConfigurationSection("SQL-Settings");
    serverConfig.setUsername(sqlSettingSection.getString("Username"));
    serverConfig.setPassword(sqlSettingSection.getString("Password"));
    serverConfig.setDriverClassName(sqlSettingSection.getString("Driver"));
    serverConfig.setJdbcUrl(replaceUrlString(sqlSettingSection.getString("Url")));
    if (serverConfig.getDriverClassName().contains("sqlite")) {
        serverConfig.setConnectionTestQuery("SELECT 1");
    }

    tablePrefix = sqlSettingSection.getString("tablePrefix", "");
}
 

@Before
public void initializeConnectionAndTable() throws SQLException {
    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
    config.setConnectionTestQuery("VALUES 1");
    // Note "ignorecase=true": H2 does not support `COLLATE NOCASE` for case-insensitive equals queries.
    // MySQL is by default case-insensitive so this is OK to make as an assumption.
    config.addDataSourceProperty("URL", "jdbc:h2:mem:test;ignorecase=true");
    config.addDataSourceProperty("user", "sa");
    config.addDataSourceProperty("password", "sa");
    HikariDataSource ds = new HikariDataSource(config);
    Connection connection = ds.getConnection();

    try (Statement st = connection.createStatement()) {
        st.execute("DROP TABLE IF EXISTS authme");
        st.execute(sqlInitialize);
    }
    hikariSource = ds;
}
 

@Override
HikariDataSource getDataSource() {
    try {
        // Initialize driver class so HikariCP can find it
        Class.forName("org.sqlite.JDBC");
    } catch (ClassNotFoundException e) {
        plugin.getLogger().severe("Failed to initialize SQLite driver");
        plugin.debug("Failed to initialize SQLite driver");
        plugin.debug(e);
        return null;
    }

    File folder = plugin.getDataFolder();
    File dbFile = new File(folder, "shops.db");

    if (!dbFile.exists()) {
        try {
            dbFile.createNewFile();
        } catch (IOException ex) {
            plugin.getLogger().severe("Failed to create database file");
            plugin.debug("Failed to create database file");
            plugin.debug(ex);
            return null;
        }
    }
    
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(String.format("jdbc:sqlite:" + dbFile));
    config.setConnectionTestQuery("SELECT 1");

    return new HikariDataSource(config);
}
 

@Override
HikariDataSource getDataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(String.format("jdbc:mysql://%s:%d/%s?autoReconnect=true&useSSL=false&serverTimezone=UTC",
            Config.databaseMySqlHost, Config.databaseMySqlPort, Config.databaseMySqlDatabase));
    config.setUsername(Config.databaseMySqlUsername);
    config.setPassword(Config.databaseMySqlPassword);
    config.setConnectionTestQuery("SELECT 1");

    return new HikariDataSource(config);
}
 

private HikariConfig initConfig(BaseProperties serverProperties) {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(serverProperties.getProperty("reporting.jdbc.url"));
    config.setUsername(serverProperties.getProperty("reporting.user"));
    config.setPassword(serverProperties.getProperty("reporting.password"));

    config.setAutoCommit(false);
    config.setConnectionTimeout(serverProperties.getLongProperty("reporting.connection.timeout.millis"));
    config.setMaximumPoolSize(5);
    config.setMaxLifetime(0);
    config.setConnectionTestQuery("SELECT 1");
    return config;
}
 

private HikariConfig initConfig(BaseProperties serverProperties) {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(serverProperties.getProperty("jdbc.url"));
    config.setUsername(serverProperties.getProperty("user"));
    config.setPassword(serverProperties.getProperty("password"));

    config.setAutoCommit(false);
    config.setConnectionTimeout(serverProperties.getLongProperty("connection.timeout.millis"));
    config.setMaximumPoolSize(3);
    config.setMaxLifetime(0);
    config.setConnectionTestQuery("SELECT 1");
    return config;
}
 

private HikariDataSource getDataSource(String jdbcUrl, int poolSize) {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setJdbcUrl(jdbcUrl);
    hikariConfig.setConnectionTestQuery("SELECT 1");
    hikariConfig.setMinimumIdle(1);
    hikariConfig.setMaximumPoolSize(poolSize);

    return new HikariDataSource(hikariConfig);
}
 

private static DataSource createHikariCP(final DatabaseType databaseType, final String dataSourceName) {
    HikariConfig result = new HikariConfig();
    DatabaseEnvironment databaseEnvironment = IntegrateTestEnvironment.getInstance().getDatabaseEnvironments().get(databaseType);
    result.setDriverClassName(databaseEnvironment.getDriverClassName());
    result.setJdbcUrl(getURL(databaseType, dataSourceName));
    result.setUsername("root");
    result.setPassword("root");
    result.setMaximumPoolSize(2);
    result.setTransactionIsolation("TRANSACTION_READ_COMMITTED");
    result.setConnectionTestQuery("SELECT 1");
    if ("Oracle".equals(databaseType.getName())) {
        result.setConnectionInitSql("ALTER SESSION SET CURRENT_SCHEMA = " + dataSourceName);
    }
    return new HikariDataSource(result);
}
 

private static DataSource createHikariCP(final DatabaseType databaseType, final String dataSourceName) {
    HikariConfig result = new HikariConfig();
    DatabaseEnvironment databaseEnvironment = IntegrateTestEnvironment.getInstance().getDatabaseEnvironments().get(databaseType);
    result.setDriverClassName(databaseEnvironment.getDriverClassName());
    result.setJdbcUrl(null == dataSourceName ? databaseEnvironment.getURL() : databaseEnvironment.getURL(dataSourceName));
    result.setUsername(databaseEnvironment.getUsername());
    result.setPassword(databaseEnvironment.getPassword());
    result.setMaximumPoolSize(2);
    result.setTransactionIsolation("TRANSACTION_READ_COMMITTED");
    result.setConnectionTestQuery("SELECT 1");
    if ("Oracle".equals(databaseType.getName())) {
        result.setConnectionInitSql("ALTER SESSION SET CURRENT_SCHEMA = " + dataSourceName);
    }
    return new HikariDataSource(result);
}
 

public static HikariConfig createConfig(IHost host) {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(host.getConnectionUrl());
    if (host instanceof SQLHost) {
        config.setDriverClassName(settings.getString("DefaultSettings.DriverClassName", "com.mysql.jdbc.Driver"));
        config.setUsername(((SQLHost) host).getUser());
        config.setPassword(((SQLHost) host).getPassword());
    } else if (host instanceof SQLiteHost) {
        config.setDriverClassName("org.sqlite.JDBC");
    } else {
        throw new IllegalArgumentException("Invalid host: " + host.getClass().getName());
    }
    config.setAutoCommit(settings.getBoolean("DefaultSettings.AutoCommit", true));
    config.setMinimumIdle(settings.getInt("DefaultSettings.MinimumIdle", 1));
    config.setMaximumPoolSize(settings.getInt("DefaultSettings.MaximumPoolSize", 10));
    config.setValidationTimeout(settings.getInt("DefaultSettings.ValidationTimeout", 5000));
    config.setConnectionTimeout(settings.getInt("DefaultSettings.ConnectionTimeout", 30000));
    config.setIdleTimeout(settings.getInt("DefaultSettings.IdleTimeout", 600000));
    config.setMaxLifetime(settings.getInt("DefaultSettings.MaxLifetime", 1800000));
    if (settings.contains("DefaultSettings.ConnectionTestQuery")) {
        config.setConnectionTestQuery(settings.getString("DefaultSettings.ConnectionTestQuery"));
    }
    if (settings.contains("DefaultSettings.DataSourceProperty")) {
        settings.getConfigurationSection("DefaultSettings.DataSourceProperty").getKeys(false).forEach(key -> config.addDataSourceProperty(key, settings.getString("DefaultSettings.DataSourceProperty." + key)));
    }
    return config;
}
 

@Override
public DataSource getDataSource(JsonObject json) throws SQLException {

  final HikariConfig config = new HikariConfig();

  for (Map.Entry<String, Object> entry : json) {
    switch (entry.getKey()) {
      case "dataSourceClassName":
        config.setDataSourceClassName((String) entry.getValue());
        break;
      case "jdbcUrl":
        config.setJdbcUrl((String) entry.getValue());
        break;
      case "username":
        config.setUsername((String) entry.getValue());
        break;
      case "password":
        config.setPassword((String) entry.getValue());
        break;
      case "autoCommit":
        config.setAutoCommit((Boolean) entry.getValue());
        break;
      case "connectionTimeout":
        config.setConnectionTimeout(getLong(entry.getValue()));
        break;
      case "idleTimeout":
        config.setIdleTimeout(getLong(entry.getValue()));
        break;
      case "maxLifetime":
        config.setMaxLifetime(getLong(entry.getValue()));
        break;
      case "connectionTestQuery":
        config.setConnectionTestQuery((String) entry.getValue());
        break;
      case "minimumIdle":
        config.setMinimumIdle((Integer) entry.getValue());
        break;
      case "maximumPoolSize":
        config.setMaximumPoolSize((Integer) entry.getValue());
        break;
      case "metricRegistry":
        throw new UnsupportedOperationException(entry.getKey());
      case "healthCheckRegistry":
        throw new UnsupportedOperationException(entry.getKey());
      case "poolName":
        config.setPoolName((String) entry.getValue());
        break;
      case "isolationInternalQueries":
        config.setIsolateInternalQueries((Boolean) entry.getValue());
        break;
      case "allowPoolSuspension":
        config.setAllowPoolSuspension((Boolean) entry.getValue());
        break;
      case "readOnly":
        config.setReadOnly((Boolean) entry.getValue());
        break;
      case "registerMBeans":
        config.setRegisterMbeans((Boolean) entry.getValue());
        break;
      case "catalog":
        config.setCatalog((String) entry.getValue());
        break;
      case "connectionInitSql":
        config.setConnectionInitSql((String) entry.getValue());
        break;
      case "driverClassName":
        config.setDriverClassName((String) entry.getValue());
        break;
      case "transactionIsolation":
        config.setTransactionIsolation((String) entry.getValue());
        break;
      case "validationTimeout":
        config.setValidationTimeout(getLong(entry.getValue()));
        break;
      case "leakDetectionThreshold":
        config.setLeakDetectionThreshold(getLong(entry.getValue()));
        break;
      case "dataSource":
        throw new UnsupportedOperationException(entry.getKey());
      case "threadFactory":
        throw new UnsupportedOperationException(entry.getKey());
      case "datasource":
        // extension to support configuring datasource.* properties
        for (Map.Entry<String, Object> key : ((JsonObject) entry.getValue())) {
          config.addDataSourceProperty(key.getKey(), key.getValue());
        }
        break;
    }
  }

  return new HikariDataSource(config);
}
 

public AuthStorage(FastLoginCore<?, ?, ?> core, String host, int port, String databasePath,
                   HikariConfig config, boolean useSSL) {
    this.core = core;
    config.setPoolName(core.getPlugin().getName());

    ThreadFactory platformThreadFactory = core.getPlugin().getThreadFactory();
    if (platformThreadFactory != null) {
        config.setThreadFactory(platformThreadFactory);
    }

    String jdbcUrl = "jdbc:";
    if (config.getDriverClassName().contains("sqlite")) {
        String pluginFolder = core.getPlugin().getPluginFolder().toAbsolutePath().toString();
        databasePath = databasePath.replace("{pluginDir}", pluginFolder);

        jdbcUrl += "sqlite://" + databasePath;
        config.setConnectionTestQuery("SELECT 1");
        config.setMaximumPoolSize(1);

        //a try to fix https://www.spigotmc.org/threads/fastlogin.101192/page-26#post-1874647
        // format strings retrieved by the timestamp column to match them from MySQL
        config.addDataSourceProperty("date_string_format", "yyyy-MM-dd HH:mm:ss");

        // TODO: test first for compatibility
        // config.addDataSourceProperty("date_precision", "seconds");
    } else {
        jdbcUrl += "mysql://" + host + ':' + port + '/' + databasePath;

        // Require SSL on the server if requested in config - this will also verify certificate
        // Those values are deprecated in favor of sslMode
        config.addDataSourceProperty("useSSL", useSSL);
        config.addDataSourceProperty("requireSSL", useSSL);

        // prefer encrypted if possible
        config.addDataSourceProperty("sslMode", "PREFERRED");

        // adding paranoid hides hostname, username, version and so
        // could be useful for hiding server details
        config.addDataSourceProperty("paranoid", true);

        // enable MySQL specific optimizations
        // disabled by default - will return the same prepared statement instance
        config.addDataSourceProperty("cachePrepStmts", true);
        // default prepStmtCacheSize 25 - amount of cached statements
        config.addDataSourceProperty("prepStmtCacheSize", 250);
        // default prepStmtCacheSqlLimit 256 - length of SQL
        config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
        // default false - available in newer versions caches the statements server-side
        config.addDataSourceProperty("useServerPrepStmts", true);
        // default false - prefer use of local values for autocommit and
        // transaction isolation (alwaysSendSetIsolation) should only be enabled if always use the set* methods
        // instead of raw SQL
        // https://forums.mysql.com/read.php?39,626495,626512
        config.addDataSourceProperty("useLocalSessionState", true);
        // rewrite batched statements to a single statement, adding them behind each other
        // only useful for addBatch statements and inserts
        config.addDataSourceProperty("rewriteBatchedStatements", true);
        // cache result metadata
        config.addDataSourceProperty("cacheResultSetMetadata", true);
        // cache results of show variables and collation per URL
        config.addDataSourceProperty("cacheServerConfiguration", true);
        // default false - set auto commit only if not matching
        config.addDataSourceProperty("elideSetAutoCommits", true);

        // default true - internal timers for idle calculation -> removes System.getCurrentTimeMillis call per query
        // Some platforms are slow on this and it could affect the throughput about 3% according to MySQL
        // performance gems presentation
        // In our case it can be useful to see the time in error messages
        // config.addDataSourceProperty("maintainTimeStats", false);
    }

    config.setJdbcUrl(jdbcUrl);
    this.dataSource = new HikariDataSource(config);
}
 

public SkinStorage(ChangeSkinCore core, String driver, String host, int port, String database
        , String user, String pass, boolean useSSL) {
    this.logger = core.getLogger();

    HikariConfig config = new HikariConfig();
    config.setPoolName(core.getPlugin().getName());

    config.setUsername(user);
    config.setPassword(pass);
    config.setDriverClassName(driver);

    ThreadFactory threadFactory = core.getPlugin().getThreadFactory();
    if (threadFactory != null) {
        config.setThreadFactory(threadFactory);
    }

    Properties properties = new Properties();

    String jdbcUrl = "jdbc:";
    if (driver.contains("sqlite")) {
        String folderPath = core.getPlugin().getPluginFolder().toAbsolutePath().toString();
        database = database.replace("{pluginDir}", folderPath);

        jdbcUrl += "sqlite://" + database;
        config.setConnectionTestQuery("SELECT 1");
        config.setMaximumPoolSize(1);

        //a try to fix https://www.spigotmc.org/threads/fastlogin.101192/page-26#post-1874647
        properties.setProperty("date_string_format", "yyyy-MM-dd HH:mm:ss");
    } else {
        jdbcUrl += "mysql://" + host + ':' + port + '/' + database;
        properties.setProperty("useSSL", String.valueOf(useSSL));
        // enable MySQL specific optimizations
        // default prepStmtCacheSize 25 - amount of cached statements - enough for us
        // default prepStmtCacheSqlLimit 256 - length of SQL - our queries are not longer
        // disabled by default - will return the same prepared statement instance
        config.addDataSourceProperty("cachePrepStmts", true);
        // default false - available in newer versions caches the statements server-side
        config.addDataSourceProperty("useServerPrepStmts", true);
    }

    config.setJdbcUrl(jdbcUrl);
    config.setDataSourceProperties(properties);
    this.dataSource = new HikariDataSource(config);
}