Java Code Examples for org.apache.commons.dbcp2.BasicDataSource#setUrl()

private static void internal_dbcp2() throws Exception {

		for (Entry<String, DataServer> entry : Config.nodes().dataServers().entrySet()) {

			BasicDataSource dataSource = new BasicDataSource();

			String url = "jdbc:h2:tcp://" + entry.getKey() + ":" + entry.getValue().getTcpPort() + "/X;JMX="
					+ (entry.getValue().getJmxEnable() ? "TRUE" : "FALSE") + ";CACHE_SIZE="
					+ (entry.getValue().getCacheSize() * 1024);
			dataSource.setDriverClassName(SlicePropertiesBuilder.driver_h2);
			dataSource.setUrl(url);
			dataSource.setInitialSize(0);
			dataSource.setMinIdle(0);
			dataSource.setMaxTotal(50);
			dataSource.setMaxIdle(50);
			dataSource.setUsername("sa");
			dataSource.setTestOnCreate(false);
			dataSource.setTestWhileIdle(false);
			dataSource.setTestOnReturn(false);
			dataSource.setTestOnBorrow(false);
			dataSource.setPassword(Config.token().getPassword());
			String name = Config.nodes().dataServers().name(entry.getValue());
			new Resource(Config.RESOURCE_JDBC_PREFIX + name, dataSource);

		}
	}
 

private static Connection getConnection(BasicDataSource dataSource) {
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    //注意，替换成自己本地的 mysql 数据库地址和用户名、密码
    dataSource.setUrl("jdbc:mysql://localhost:3306/test");
    dataSource.setUsername("root");
    dataSource.setPassword("root123456");
    //设置连接池的一些参数
    dataSource.setInitialSize(10);
    dataSource.setMaxTotal(50);
    dataSource.setMinIdle(2);

    Connection con = null;
    try {
        con = dataSource.getConnection();
        log.info("创建连接池：{}", con);
    } catch (Exception e) {
        log.error("-----------mysql get connection has exception , msg = {}", e.getMessage());
    }
    return con;
}
 

@SuppressWarnings("unchecked")
@Test
public void assertGetDataSourceConfigurationWithConnectionInitSqls() {
    BasicDataSource actualDataSource = new BasicDataSource();
    actualDataSource.setDriverClassName("org.h2.Driver");
    actualDataSource.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL");
    actualDataSource.setUsername("root");
    actualDataSource.setPassword("root");
    actualDataSource.setConnectionInitSqls(Arrays.asList("set names utf8mb4;", "set names utf8;"));
    DataSourceConfiguration actual = DataSourceConfiguration.getDataSourceConfiguration(actualDataSource);
    assertThat(actual.getDataSourceClassName(), is(BasicDataSource.class.getName()));
    assertThat(actual.getProps().get("driverClassName").toString(), is("org.h2.Driver"));
    assertThat(actual.getProps().get("url").toString(), is("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL"));
    assertThat(actual.getProps().get("username").toString(), is("root"));
    assertThat(actual.getProps().get("password").toString(), is("root"));
    assertNull(actual.getProps().get("loginTimeout"));
    assertThat(actual.getProps().get("connectionInitSqls"), instanceOf(List.class));
    List<String> actualConnectionInitSql = (List<String>) actual.getProps().get("connectionInitSqls");
    assertThat(actualConnectionInitSql, hasItem("set names utf8mb4;"));
    assertThat(actualConnectionInitSql, hasItem("set names utf8;"));
}
 

private Map<String, DataSourceConfiguration> getDataSourceConfigurations() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MySQL");
    dataSource.setUsername("sa");
    dataSource.setPassword("");
    Map<String, DataSourceConfiguration> result = new LinkedHashMap<>();
    result.put("ds_m", DataSourceConfiguration.getDataSourceConfiguration(dataSource));
    result.put("ds_s", DataSourceConfiguration.getDataSourceConfiguration(dataSource));
    result.put("ds_0", DataSourceConfiguration.getDataSourceConfiguration(dataSource));
    return result;
}
 

@Bean
@Scope(value = "prototype", proxyMode = ScopedProxyMode.TARGET_CLASS)
public DataSource dataSource() {
    BasicDataSource pool = new BasicDataSource();
    pool.setDriverClassName("org.hsqldb.jdbcDriver");
    pool.setUrl("jdbc:hsqldb:target/overridedb");
    pool.setUsername("sa");
    return pool;
}
 

private DataSource createDBCPDataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setUrl("jdbc:mysql://localhost:3306/demo_ds");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    return dataSource;
}
 

@Bean
public DataSource dataSource() {
    BasicDataSource pool = new BasicDataSource();
    pool.setDriverClassName("org.hsqldb.jdbcDriver");
    pool.setUrl("jdbc:hsqldb:target/overridedb");
    pool.setUsername("sa");
    return pool;
}
 

public static void main(String[] args) throws LiquibaseException, SQLException, IOException, ParserConfigurationException {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setUrl("jdbc:mysql://localhost:3306/main?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC");
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUsername("root");

    java.sql.Connection connection = dataSource.getConnection();
    Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
    Liquibase liquibase = new Liquibase("db-changes.yml", new ClassLoaderResourceAccessor(), database);

    CatalogAndSchema catalogAndSchema = new CatalogAndSchema(null, "main");
    DiffToChangeLog changeLog = new DiffToChangeLog(new DiffOutputControl(false, false, true, null));

    liquibase.generateChangeLog(catalogAndSchema, changeLog, new PrintStream(new FileOutputStream("./change-logs.yml")), new YamlChangeLogSerializer(), snapTypes());
}
 

@Test
public void testOpenConnectionToH2DbHavingUsernameAndPasswordEncodedIntoConnectionUrl() throws ClassNotFoundException {
    // GIVEN
    final BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(H2_DRIVER_CLASS_PROP_VALUE);
    ds.setUrl(H2_CONNECTION_URL_PROP_VALUE + ";USER=" + USERNAME_PROP_VALUE + ";PASSWORD=" + PASSWORD_PROP_VALUE);

    // WHEN
    connection = DatabaseConnectionFactory.openConnection(ds);

    // THEN
    assertThat(connection, notNullValue());
    assertThat(connection, instanceOf(H2Connection.class));
}
 

private BasicDataSource getDataSource(String driverClass, String hostname, int port, String schema, String username, String password) {
    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setDriverClassName(driverClass);
    dataSource.setUrl(String.format(ActiveSchemaManager.CONNECTION_URL_FORMAT, hostname, port, schema));
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setTestOnBorrow(true);
    return dataSource;
}
 

private BasicDataSource getDataSource(String driverClass, String hostname, int port, String username, String password) {
    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setDriverClassName(driverClass);
    dataSource.setUrl(String.format(ActiveSchemaManager.BARE_CONNECTION_URL_FORMAT, hostname, port));
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setTestOnBorrow(true);
    return dataSource;
}
 

@BeforeClass
public static void init() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306?characterEncoding=UTF-8");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
    queryRunner = new DefaultQueryRunner(dataSource);
}
 

@BeforeClass
public static void setUpClass() {
    BasicDataSource bds = new BasicDataSource();
    bds.setDriverClassName("org.h2.Driver");
    bds.setUrl("jdbc:h2:mem:test;INIT=runscript from 'src/test/resources/create.sql'");
    bds.setUsername("sa");
    bds.setPassword("sa");
    dataSource = bds;
}
 

protected static DataSource getDBCP2DataSource2() {
    //创建BasicDataSource类对象
    BasicDataSource datasource = new BasicDataSource();
    //数据库连接信息（必须）
    datasource.setDriverClassName("com.mysql.jdbc.Driver");
    datasource.setUrl("jdbc:mysql://129.9.100.16:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false");
    datasource.setUsername("test");
    datasource.setPassword("123456");
    //连接池中的连接数量配置（可选）
    datasource.setInitialSize(10);//初始化的连接数
    datasource.setMaxOpenPreparedStatements(8);//最大连接数
    datasource.setMaxIdle(5);//最大空闲连接
    datasource.setMinIdle(1);//最小空闲连接
    return datasource;
}
 

private DataSource createDataSourceWithConnectionInitSqls(final String name) {
    BasicDataSource result = new BasicDataSource();
    result.setDriverClassName("com.mysql.jdbc.Driver");
    result.setUrl("jdbc:mysql://localhost:3306/" + name);
    result.setUsername("root");
    result.setPassword("root");
    result.setConnectionInitSqls(Arrays.asList("set names utf8mb4;", "set names utf8;"));
    return result;
}
 

private DataSource createDataSource(final String dataSetFile) {
    BasicDataSource result = new BasicDataSource();
    result.setDriverClassName(org.h2.Driver.class.getName());
    result.setUrl(String.format("jdbc:h2:mem:%s;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL", getFileName(dataSetFile)));
    result.setUsername("sa");
    result.setPassword("");
    result.setMaxTotal(100);
    return result;
}
 

@Bean
@Profile("legacy")
public DataSource legacyDataSource() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
    dataSource.setUrl(SettingsService.getDefaultJDBCUrl());
    dataSource.setUsername(SettingsService.getDefaultJDBCUsername());
    dataSource.setPassword(SettingsService.getDefaultJDBCPassword());
    return dataSource;
}
 

public boolean register(JdbcVo conf) {
    boolean isOk = true;
    try {
        BasicDataSource bds = new BasicDataSource();
        bds.setDriverClassName(conf.getDriverClass());
        bds.setUrl(conf.getUrl());
        bds.setUsername(conf.getUserName());
        bds.setPassword(conf.getPassword());
        bds.setInitialSize(conf.getInitialSize());
        bds.setMaxTotal(conf.getMaxActive());
        bds.setMaxIdle(conf.getMaxIdle());
        bds.setMinIdle(conf.getMinIdle());
        // 设置等待获取连接的最长时间
        // bds.setMaxWaitMillis(20 * 1000);
        // validQuery 只给test idle 使用，不给 test Borrow使用， 为什么？
        // 发现 oracle版本 insert心跳被block， 下面的link 有人说 可以通过设置TestOnBorrow=false 解决。
        // https://stackoverflow.com/questions/4853732/blocking-on-dbcp-connection-pool-open-and-close-connnection-is-database-conne
        bds.setTestOnBorrow(true);
        bds.setTestWhileIdle(false);

        if (StringUtils.equalsIgnoreCase(Constants.CONFIG_DB_TYPE_ORA, conf.getType())) {
            bds.setValidationQuery("select 1 from dual");
            bds.setValidationQueryTimeout(5);
        } else if (StringUtils.equalsIgnoreCase(Constants.CONFIG_DB_TYPE_MYSQL, conf.getType())) {
            bds.setValidationQuery("select 1");
            bds.setValidationQueryTimeout(5);
        }

        /*bds.setTimeBetweenEvictionRunsMillis(1000 * 300);
        if (org.apache.commons.lang.StringUtils.equals(Constants.CONFIG_DB_TYPE_ORA, conf.getType())) {
            bds.setValidationQuery("select 1 from dual");
            bds.setValidationQueryTimeout(1);
        } else if (org.apache.commons.lang.StringUtils.equals(Constants.CONFIG_DB_TYPE_MYSQL, conf.getType())) {
            bds.setValidationQuery("select 1");
            bds.setValidationQueryTimeout(1);
        }*/
        LoggerFactory.getLogger().info("create datasource key:" + conf.getKey() + " url:" + conf.getUrl());
        cmap.put(conf.getKey(), bds);

        // 为了支持查询主库和被库的延时，一个ds需要同时建立同主库和被库的连接
        if (conf instanceof DsVo) {
            DsVo ds = (DsVo) conf;
            if (StringUtils.isNotBlank(ds.getSlvaeUrl())) {
                BasicDataSource slaveBds = new BasicDataSource();
                slaveBds.setDriverClassName(ds.getDriverClass());
                slaveBds.setUrl(ds.getSlvaeUrl());
                slaveBds.setUsername(ds.getUserName());
                slaveBds.setPassword(ds.getPassword());
                slaveBds.setInitialSize(ds.getInitialSize());
                slaveBds.setMaxTotal(ds.getMaxActive());
                slaveBds.setMaxIdle(ds.getMaxIdle());
                slaveBds.setMinIdle(ds.getMinIdle());
                slaveBds.setTestOnBorrow(true);
                slaveBds.setTestWhileIdle(false);

                if (StringUtils.equalsIgnoreCase(Constants.CONFIG_DB_TYPE_ORA, conf.getType())) {
                    slaveBds.setValidationQuery("select 1 from dual");
                    slaveBds.setValidationQueryTimeout(5);
                } else if (StringUtils.equalsIgnoreCase(Constants.CONFIG_DB_TYPE_MYSQL, conf.getType())) {
                    slaveBds.setValidationQuery("select 1");
                    slaveBds.setValidationQueryTimeout(5);
                }

                // 设置等待获取连接的最长时间
                // slaveBds.setMaxWaitMillis(20 * 1000);
                String key = StringUtils.join(new String[]{ds.getKey(), "slave"}, "_");
                LoggerFactory.getLogger().info("create datasource key:" + key + " url:" + ds.getSlvaeUrl());
                cmap.put(key, slaveBds);
            } else {
                LoggerFactory.getLogger().warn("db container initDsPool key " + ds.getKey() + " of slave url is empty.");
            }
        }

    } catch (Exception e) {
        LoggerFactory.getLogger().error("[db container initDsPool key " + conf.getKey() + " datasource error!]", e);
        isOk = false;
    }
    return isOk;
}
 

@Override
public DataSource build(DataSourceConfig config, boolean withDefaultValue) {
	BasicDataSource dbcp2DataSource = new BasicDataSource();

	dbcp2DataSource.setUrl(config.getJdbcUrl());
	dbcp2DataSource.setUsername(config.getUsername());
	dbcp2DataSource.setPassword(config.getPassword());
	dbcp2DataSource.setDriverClassName(StringUtils.isNotBlank(config.getDriverClass()) ? config.getDriverClass()
	      : JdbcDriverClassHelper.getDriverClassNameByJdbcUrl(config.getJdbcUrl()));

	if (withDefaultValue) {
		dbcp2DataSource.setInitialSize(getIntProperty(config, "initialPoolSize", 5));
		dbcp2DataSource.setMaxTotal(getIntProperty(config, "maxPoolSize", 30));
		dbcp2DataSource.setMinIdle(getIntProperty(config, "minPoolSize", 5));
		dbcp2DataSource.setMaxIdle(getIntProperty(config, "maxPoolSize", 20));
		dbcp2DataSource.setMaxWaitMillis(getIntProperty(config, "checkoutTimeout", 1000));
		dbcp2DataSource.setValidationQuery(getStringProperty(config, "preferredTestQuery", "SELECT 1"));
		dbcp2DataSource.setMinEvictableIdleTimeMillis(getIntProperty(config, "minEvictableIdleTimeMillis", 1800000));// 30min
		dbcp2DataSource
		      .setTimeBetweenEvictionRunsMillis(getIntProperty(config, "timeBetweenEvictionRunsMillis", 30000)); // 30s
		dbcp2DataSource.setRemoveAbandonedTimeout(getIntProperty(config, "removeAbandonedTimeout", 300)); // 30s
		dbcp2DataSource.setNumTestsPerEvictionRun(getIntProperty(config, "numTestsPerEvictionRun", 6)); // 30s
		dbcp2DataSource.setValidationQueryTimeout(getIntProperty(config, "validationQueryTimeout", 0));
		if (StringUtils.isNotBlank(getStringProperty(config, "connectionInitSql", null))) {
			List<String> initSqls = new ArrayList<String>();
			initSqls.add(getStringProperty(config, "connectionInitSql", null));
			dbcp2DataSource.setConnectionInitSqls(initSqls);
		}

		dbcp2DataSource.setTestWhileIdle(true);
		dbcp2DataSource.setTestOnBorrow(false);
		dbcp2DataSource.setTestOnReturn(false);
		dbcp2DataSource.setRemoveAbandonedOnBorrow(true);
		dbcp2DataSource.setRemoveAbandonedOnMaintenance(true);
	} else {
		try {
			PropertiesInit<BasicDataSource> propertiesInit = new PropertiesInit<BasicDataSource>(dbcp2DataSource);
			propertiesInit.initPoolProperties(config);
		} catch (Exception e) {
			throw new ZebraConfigException(String.format("dbcp2 dataSource [%s] created error : ", config.getId()), e);
		}
	}

	this.pool = dbcp2DataSource;
	LOGGER.info(String.format("New dataSource [%s] created.", config.getId()));

	return this.pool;
}
 

/**
 * Configures connection pool by creating an instance of the
 * {@link BasicDataSource} based on configuration provided with
 * {@link ConfigurationContext}.
 *
 * This operation makes no guarantees that the actual connection could be
 * made since the underlying system may still go off-line during normal
 * operation of the connection pool.
 *
 * @param context
 *            the configuration context
 * @throws InitializationException
 *             if unable to create a database connection
 */
@OnEnabled
public void onConfigured(final ConfigurationContext context) throws InitializationException {

    final String drv = context.getProperty(DB_DRIVERNAME).evaluateAttributeExpressions().getValue();
    final String user = context.getProperty(DB_USER).evaluateAttributeExpressions().getValue();
    final String passw = context.getProperty(DB_PASSWORD).evaluateAttributeExpressions().getValue();
    final Integer maxTotal = context.getProperty(MAX_TOTAL_CONNECTIONS).evaluateAttributeExpressions().asInteger();
    final String validationQuery = context.getProperty(VALIDATION_QUERY).evaluateAttributeExpressions().getValue();
    final Long maxWaitMillis = extractMillisWithInfinite(context.getProperty(MAX_WAIT_TIME).evaluateAttributeExpressions());
    final Integer minIdle = context.getProperty(MIN_IDLE).evaluateAttributeExpressions().asInteger();
    final Integer maxIdle = context.getProperty(MAX_IDLE).evaluateAttributeExpressions().asInteger();
    final Long maxConnLifetimeMillis = extractMillisWithInfinite(context.getProperty(MAX_CONN_LIFETIME).evaluateAttributeExpressions());
    final Long timeBetweenEvictionRunsMillis = extractMillisWithInfinite(context.getProperty(EVICTION_RUN_PERIOD).evaluateAttributeExpressions());
    final Long minEvictableIdleTimeMillis = extractMillisWithInfinite(context.getProperty(MIN_EVICTABLE_IDLE_TIME).evaluateAttributeExpressions());
    final Long softMinEvictableIdleTimeMillis = extractMillisWithInfinite(context.getProperty(SOFT_MIN_EVICTABLE_IDLE_TIME).evaluateAttributeExpressions());
    final KerberosCredentialsService kerberosCredentialsService = context.getProperty(KERBEROS_CREDENTIALS_SERVICE).asControllerService(KerberosCredentialsService.class);
    final String kerberosPrincipal = context.getProperty(KERBEROS_PRINCIPAL).evaluateAttributeExpressions().getValue();
    final String kerberosPassword = context.getProperty(KERBEROS_PASSWORD).getValue();

    if (kerberosCredentialsService != null) {
        kerberosUser = new KerberosKeytabUser(kerberosCredentialsService.getPrincipal(), kerberosCredentialsService.getKeytab());
    } else if (!StringUtils.isBlank(kerberosPrincipal) && !StringUtils.isBlank(kerberosPassword)) {
        kerberosUser = new KerberosPasswordUser(kerberosPrincipal, kerberosPassword);
    }

    if (kerberosUser != null) {
        try {
            kerberosUser.login();
        } catch (LoginException e) {
            throw new InitializationException("Unable to authenticate Kerberos principal", e);
        }
    }

    dataSource = new BasicDataSource();
    dataSource.setDriverClassName(drv);

    // Optional driver URL, when exist, this URL will be used to locate driver jar file location
    final String urlString = context.getProperty(DB_DRIVER_LOCATION).evaluateAttributeExpressions().getValue();
    dataSource.setDriverClassLoader(getDriverClassLoader(urlString, drv));

    final String dburl = context.getProperty(DATABASE_URL).evaluateAttributeExpressions().getValue();

    dataSource.setMaxWaitMillis(maxWaitMillis);
    dataSource.setMaxTotal(maxTotal);
    dataSource.setMinIdle(minIdle);
    dataSource.setMaxIdle(maxIdle);
    dataSource.setMaxConnLifetimeMillis(maxConnLifetimeMillis);
    dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    dataSource.setSoftMinEvictableIdleTimeMillis(softMinEvictableIdleTimeMillis);

    if (validationQuery!=null && !validationQuery.isEmpty()) {
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestOnBorrow(true);
    }

    dataSource.setUrl(dburl);
    dataSource.setUsername(user);
    dataSource.setPassword(passw);

    context.getProperties().keySet().stream().filter(PropertyDescriptor::isDynamic)
            .forEach((dynamicPropDescriptor) -> dataSource.addConnectionProperty(dynamicPropDescriptor.getName(),
                    context.getProperty(dynamicPropDescriptor).evaluateAttributeExpressions().getValue()));

}