How to use

BasicDataSource

in

org.apache.commons.dbcp

 BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("driverClassName");
ds.setUrl("jdbc://...");
ds.setUsername("username");
ds.setPassword("password");

/**
 * To close the adaptor, because we need to close all connections on this JDBC source.
 * @throws IOException If close failed.
 */
@Override
public void close() throws IOException {
  try {
    dataSource.close();
  } catch (SQLException e) {
    throw new IllegalStateException(e);
  }
}

 BasicDataSource dataSource = new BasicDataSource();

dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUsername("username");
dataSource.setPassword("password");
dataSource.setUrl("jdbc:mysql://<host>:<port>/<database>");
dataSource.setMaxActive(10);
dataSource.setMaxIdle(5);
dataSource.setInitialSize(5);
dataSource.setValidationQuery("SELECT 1");

private JdbcPushDownConnectionManager(KylinConfig config) throws ClassNotFoundException {
  dataSource = new BasicDataSource();
  Class.forName(config.getJdbcDriverClass());
  dataSource.setDriverClassName(config.getJdbcDriverClass());
  dataSource.setUrl(config.getJdbcUrl());
  dataSource.setUsername(config.getJdbcUsername());
  dataSource.setPassword(config.getJdbcPassword());
  dataSource.setMaxActive(config.getPoolMaxTotal());
  dataSource.setMaxIdle(config.getPoolMaxIdle());
  dataSource.setMinIdle(config.getPoolMinIdle());
  // Default settings
  dataSource.setTestOnBorrow(true);
  dataSource.setValidationQuery("select 1");
  dataSource.setRemoveAbandoned(true);
  dataSource.setRemoveAbandonedTimeout(300);
}

@Bean(destroyMethod = "close", name = "dataSource")
public DataSource dataSource(DataSourceProperties properties) {
  log.info("*************************dataSource***********************");
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName(properties.driverClassName);
  dataSource.setUrl(properties.url);
  dataSource.setUsername(properties.username);
  dataSource.setPassword(properties.password);
  dataSource.setMaxIdle(properties.maxIdle);
  dataSource.setMaxActive(properties.maxActive);
  dataSource.setMaxWait(properties.maxWait);
  dataSource.setInitialSize(properties.initialSize);
  dataSource.setValidationQuery(properties.validationQuery);
  dataSource.setRemoveAbandoned(true);
  dataSource.setTestWhileIdle(true);
  dataSource.setTimeBetweenEvictionRunsMillis(30000);
  dataSource.setNumTestsPerEvictionRun(30);
  dataSource.setMinEvictableIdleTimeMillis(1800000);
  return dataSource;
}

@PostConstruct
public void init() {
  BasicDataSource ds = new BasicDataSource();
  ds.setDriverClassName(JDBC_DRIVER_NAME);
  ds.setUrl("jdbc:derby:" + NACOS_HOME + File.separator + DERBY_BASE_DIR + ";create=true");
  ds.setUsername(USER_NAME);
  ds.setPassword(PASSWORD);
  ds.setInitialSize(20);
  ds.setMaxActive(30);
  ds.setMaxIdle(50);
  ds.setMaxWait(10000L);
  ds.setPoolPreparedStatements(true);
  ds.setTimeBetweenEvictionRunsMillis(TimeUnit.MINUTES
    .toMillis(10L));
  ds.setTestWhileIdle(true);
  jt = new JdbcTemplate();
  jt.setMaxRows(50000);
  jt.setQueryTimeout(5000);
  jt.setDataSource(ds);
  DataSourceTransactionManager tm = new DataSourceTransactionManager();
  tjt = new TransactionTemplate(tm);
  tm.setDataSource(ds);
  tjt.setTimeout(5000);
  if (STANDALONE_MODE && !propertyUtil.isStandaloneUseMysql()) {
    reload();
  }
}

/**
 * creates a new {@link BasicDataSource}
 * @param config the properties used for datasource instantiation
 * @return
 */
public static BasicDataSource newDataSource(Config config) {
 BasicDataSource basicDataSource = new BasicDataSource();
 PasswordManager passwordManager = PasswordManager.getInstance(ConfigUtils.configToProperties(config));
 basicDataSource.setDriverClassName(ConfigUtils.getString(config, ConfigurationKeys.STATE_STORE_DB_JDBC_DRIVER_KEY,
   ConfigurationKeys.DEFAULT_STATE_STORE_DB_JDBC_DRIVER));
 // MySQL server can timeout a connection so need to validate connections before use
 basicDataSource.setValidationQuery("select 1");
 basicDataSource.setTestOnBorrow(true);
 basicDataSource.setDefaultAutoCommit(false);
 basicDataSource.setTimeBetweenEvictionRunsMillis(60000);
 basicDataSource.setUrl(config.getString(ConfigurationKeys.STATE_STORE_DB_URL_KEY));
 basicDataSource.setUsername(passwordManager.readPassword(
   config.getString(ConfigurationKeys.STATE_STORE_DB_USER_KEY)));
 basicDataSource.setPassword(passwordManager.readPassword(
   config.getString(ConfigurationKeys.STATE_STORE_DB_PASSWORD_KEY)));
 basicDataSource.setMinEvictableIdleTimeMillis(
   ConfigUtils.getLong(config, ConfigurationKeys.STATE_STORE_DB_CONN_MIN_EVICTABLE_IDLE_TIME_KEY,
     ConfigurationKeys.DEFAULT_STATE_STORE_DB_CONN_MIN_EVICTABLE_IDLE_TIME));
 return basicDataSource;
}

@Override
public DataSource build(DataSourceConfig config, boolean withDefaultValue) {
  BasicDataSource dbcpDataSource = new BasicDataSource();
  dbcpDataSource.setUrl(config.getJdbcUrl());
  dbcpDataSource.setUsername(config.getUsername());
  dbcpDataSource.setPassword(config.getPassword());
  dbcpDataSource.setDriverClassName(StringUtils.isNotBlank(config.getDriverClass()) ? config.getDriverClass() : JdbcDriverClassHelper.getDriverClassNameByJdbcUrl(config.getJdbcUrl()));
    dbcpDataSource.setInitialSize(getIntProperty(config, "initialPoolSize", 5));
    dbcpDataSource.setMaxActive(getIntProperty(config, "maxPoolSize", 30));
    dbcpDataSource.setMinIdle(getIntProperty(config, "minPoolSize", 5));
    dbcpDataSource.setMaxIdle(getIntProperty(config, "maxPoolSize", 20));
    dbcpDataSource.setMaxWait(getIntProperty(config, "checkoutTimeout", 1000));
    dbcpDataSource.setValidationQuery(getStringProperty(config, "preferredTestQuery", "SELECT 1"));
    dbcpDataSource.setMinEvictableIdleTimeMillis(getIntProperty(config, "minEvictableIdleTimeMillis", 1800000));// 30min
    dbcpDataSource.setTimeBetweenEvictionRunsMillis(getIntProperty(config, "timeBetweenEvictionRunsMillis", 30000)); // 30s
    dbcpDataSource.setRemoveAbandonedTimeout(getIntProperty(config, "removeAbandonedTimeout", 300)); // 30s
    dbcpDataSource.setNumTestsPerEvictionRun(getIntProperty(config, "numTestsPerEvictionRun", 6)); // 30s
    dbcpDataSource.setValidationQueryTimeout(getIntProperty(config, "validationQueryTimeout", 0));
    if (StringUtils.isNotBlank(getStringProperty(config, "connectionInitSql", null))) {
      List<String> initSqls = new ArrayList<String>();
      initSqls.add(getStringProperty(config, "connectionInitSql", null));
      dbcpDataSource.setConnectionInitSqls(initSqls);
    dbcpDataSource.setTestWhileIdle(true);
    dbcpDataSource.setTestOnBorrow(false);
    dbcpDataSource.setTestOnReturn(false);
    dbcpDataSource.setRemoveAbandoned(true);

 BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(DATABASE_DRIVER_CLASS);
ds.setUsername(DATABASE_USERNAME);
ds.setPassword(DATABASE_PASSWORD);
ds.setUrl(DATABASE_URL);
ds.setInitialSize(1);
ds.setMaxActive(50);
ds.setDefaultAutoCommit(false);

  throws DataSourceException {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(driverName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setAccessToUnderlyingConnectionAllowed(true);
dataSource.setMaxActive(maxActive);
dataSource.setMinIdle(minIdle);
dataSource.setMinEvictableIdleTimeMillis(1000 * 20);
dataSource.setTimeBetweenEvictionRunsMillis(1000 * 10);
  dataSource.setTestOnBorrow(true);
  dataSource.setValidationQuery(validationQuery);
  dataSource.setPoolPreparedStatements(true);
  dataSource.setMaxOpenPreparedStatements(10);
  dataSource.setRemoveAbandoned(true);
  dataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
  dataSource.setLogAbandoned(true);
  conn = dataSource.getConnection();
} catch (Exception e) {
  throw new DataSourceException("Connection test failed ", e);

BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(getDriverClassName());
dataSource.setUrl(getJDBCUrl(params));
  dataSource.setUsername(user);
  dataSource.setPassword(passwd);
  dataSource.setMaxWait(maxWait * 1000);
  dataSource.setMinIdle(minConn);
  dataSource.setMaxActive(maxConn);
  dataSource.setTestOnBorrow(true);
  dataSource.setValidationQuery(getValidationQuery());
  dataSource.setTestWhileIdle(testWhileIdle);
  dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictorRuns * 1000l);
  dataSource.setMinEvictableIdleTimeMillis(minEvictableTime * 1000l);
  dataSource.setNumTestsPerEvictionRun(evictorTestsPerRun);
dataSource.setAccessToUnderlyingConnectionAllowed(true);
return dataSource;

 private static DataSource setupDataSource() {
  BasicDataSource ds = new BasicDataSource();
  ds.setDriverClassName(getDriver());
  ds.setUsername(getUser());
  ds.setPassword(getPassword());
  ds.setUrl(getConnectionString());

  ds.setDefaultAutoCommit(false);
  ds.setInitialSize(4);
  ds.setMaxActive(60);
  ds.setMaxIdle(10);

  ds.setValidationQuery("/* ping */ SELECT 1");//config to validate against mysql
  ds.setValidationQueryTimeout(3);
  ds.setTestOnBorrow(true);
  ds.setTestOnReturn(true);

  return ds;
}

private void setupDataSource() {
  setDerbyPaths();
  dataSource = new BasicDataSource();
  dataSource.setDriverClassName(databaseConfiguration.getDriverClassName());
  System.setProperty("hibernate.dialect", databaseConfiguration.getHibernateDialect());
  dataSource.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
  dataSource.setMaxActive(databaseConfiguration.getPoolMaxActive());
  dataSource.setMinIdle(databaseConfiguration.getPoolMinIdle());
  dataSource.setMaxIdle(databaseConfiguration.getPoolMaxIdle());
  dataSource.setDefaultAutoCommit(true);
  dataSource.setInitialSize(databaseConfiguration.getPoolMinIdle());
  //Derby blows up if the username of password is empty (even an empty string thats not null).
  if (databaseConfiguration.getUsername()!=null && databaseConfiguration.getUsername().length()>=1) dataSource.setUsername(databaseConfiguration.getUsername());
  if (databaseConfiguration.getPassword()!=null && databaseConfiguration.getPassword().length()>=1) dataSource.setPassword(databaseConfiguration.getPassword());
  dataSource.setUrl(databaseConfiguration.getJDBCUri());
}

public DataSource createNewDataSource(Map params) throws IOException {
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName((String) DRIVERCLASS.lookUp(params));
  dataSource.setUrl((String) JDBC_URL.lookUp(params));
  dataSource.setUsername((String) USERNAME.lookUp(params));
  dataSource.setPassword((String) PASSWORD.lookUp(params));
  dataSource.setAccessToUnderlyingConnectionAllowed(true);
  dataSource.setMaxActive(((Integer) MAXACTIVE.lookUp(params)).intValue());
  dataSource.setMaxIdle(((Integer) MAXIDLE.lookUp(params)).intValue());
  // check the data source is properly setup by trying to gather a connection out of it
  Connection conn = null;
  try {
    conn = dataSource.getConnection();
  } catch (SQLException e) {
    throw new DataSourceException(
        "Connection pool improperly set up: " + e.getMessage(), e);
  } finally {
    // close the connection at once
    if (conn != null)
      try {
        conn.close();
      } catch (SQLException e) {
      }
  }
  return dataSource;
}

/**
 * Creates a data source by reading properties from a file called 'db.properties', located
 * paralell to the test setup instance.
 */
protected DataSource createDataSource() throws IOException {
  Properties db = fixture;
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName(db.getProperty("driver"));
  dataSource.setUrl(db.getProperty("url"));
  if (db.containsKey("user")) {
    dataSource.setUsername(db.getProperty("user"));
  } else if (db.containsKey("username")) {
    dataSource.setUsername(db.getProperty("username"));
  }
  if (db.containsKey("password")) {
    dataSource.setPassword(db.getProperty("password"));
  }
  dataSource.setPoolPreparedStatements(true);
  dataSource.setAccessToUnderlyingConnectionAllowed(true);
  dataSource.setMinIdle(1);
  dataSource.setMaxActive(4);
  // if we cannot get a connection within 5 seconds give up
  dataSource.setMaxWait(5000);
  initializeDataSource(dataSource, db);
  // return a closeable data source (DisposableDataSource interface)
  // so that the connection pool will be tore down on datastore dispose
  return new DBCPDataSource(dataSource);
}

    bds.setDefaultReadOnly(true);
    bds.setDriverClassName(olap4jDriverClassName);
    bds.setPassword(pwd);
    bds.setUsername(user);
    bds.setUrl(olap4jDriverConnectionString);
    bds.setPoolPreparedStatements(false);
    bds.setMaxIdle(maxPerUserConnectionCount);
    bds.setMaxActive(maxPerUserConnectionCount);
    bds.setMinEvictableIdleTimeMillis(
      idleConnectionsCleanupTimeoutMs);
    bds.setAccessToUnderlyingConnectionAllowed(true);
    bds.setInitialSize(1);
    bds.setTimeBetweenEvictionRunsMillis(60000);
    if (catalog != null) {
      bds.setDefaultCatalog(catalog);
Connection connection = bds.getConnection();
DelegatingConnection dc = (DelegatingConnection) connection;
Connection underlyingOlapConnection = dc.getInnermostDelegate();

protected void setupDbcp()
{
  org.apache.commons.dbcp.BasicDataSource ds = new org.apache.commons.dbcp.BasicDataSource();
  ds.setUrl(jdbcUrl);
  ds.setUsername("brettw");
  ds.setPassword("");
  ds.setInitialSize(MIN_POOL_SIZE);
  ds.setMinIdle(MIN_POOL_SIZE);
  ds.setMaxIdle(maxPoolSize);
  ds.setMaxActive(maxPoolSize);
  ds.setDefaultAutoCommit(false);
  ds.setTestOnBorrow(true);
  ds.setValidationQuery("SELECT 1");
  DS = ds;
}

 BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(JDBCDriver);
ds.setUrl(JDBCUrl);
ds.setUsername(JDBCUser);
ds.setPassword(JDBCPassword);
ds.setInitialSize(initSize);
ds.setMaxTotal(maxTotal);//negative for no limit
ds.setTestOnBorrow(false);
ds.setTestWhileIdle(true);

Connection con = ds.getConnection();

@SuppressWarnings( "deprecation" )
private static void setPoolProperties( BasicDataSource ds, Properties properties, int initialSize, int maxSize ) {
 ds.setInitialSize( initialSize );
 ds.setMaxActive( maxSize );
  ds.setDefaultAutoCommit( Boolean.valueOf( value ) );
  ds.setDefaultReadOnly( Boolean.valueOf( value ) );
  ds.setDefaultTransactionIsolation( Integer.valueOf( value ) );
  ds.setDefaultCatalog( value );
  ds.setInitialSize( Integer.valueOf( value ) );
  ds.setMaxActive( Integer.valueOf( value ) );
  ds.setMaxIdle( Integer.valueOf( value ) );
  ds.setMinIdle( Integer.valueOf( value ) );
  ds.setMaxWait( Long.valueOf( value ) );
  ds.setValidationQuery( value );
  ds.setTestOnBorrow( Boolean.valueOf( value ) );
  ds.setTestOnReturn( Boolean.valueOf( value ) );
  ds.setTestWhileIdle( Boolean.valueOf( value ) );

/**
 * <ul>
 * <li>Create the in-memory database and connect
 * <li>Create tables for snapshots and daily_paritions
 * <li>Attach all user defined functions from {@link SqlUdfs}
 * </ul>
 *
 */
@BeforeClass
public void setup() throws SQLException {
 basicDataSource = new BasicDataSource();
 basicDataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
 basicDataSource.setUrl("jdbc:derby:memory:derbypoc;create=true");
 Connection connection = basicDataSource.getConnection();
 connection.setAutoCommit(false);
 this.connection = connection;
 execute("CREATE TABLE Snapshots (dataset_path VARCHAR(255), name VARCHAR(255), path VARCHAR(255), ts TIMESTAMP, row_count bigint)");
 execute("CREATE TABLE Daily_Partitions (dataset_path VARCHAR(255), path VARCHAR(255), ts TIMESTAMP)");
 // Register UDFs
 execute("CREATE FUNCTION TIMESTAMP_DIFF(timestamp1 TIMESTAMP, timestamp2 TIMESTAMP, unitString VARCHAR(50)) RETURNS BIGINT PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.apache.gobblin.data.management.retention.sql.SqlUdfs.timestamp_diff'");
}