Java Code Examples for org.apache.commons.dbcp.BasicDataSource#getConnection()

/**
 * Creates an in-memory datasource.
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws SQLException {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa"); //$NON-NLS-1$
    ds.setPassword(""); //$NON-NLS-1$
    ds.setUrl("jdbc:h2:mem:BasicAuthJDBCTest;DB_CLOSE_DELAY=-1"); //$NON-NLS-1$
    Connection connection = ds.getConnection();
    connection.prepareStatement("CREATE TABLE users ( username varchar(255) NOT NULL, password varchar(255) NOT NULL, PRIMARY KEY (username))").executeUpdate();
    connection.prepareStatement("INSERT INTO users (username, password) VALUES ('bwayne', 'ae2efd698aefdf366736a4eda1bc5241f9fbfec7')").executeUpdate();
    connection.prepareStatement("INSERT INTO users (username, password) VALUES ('ckent', 'ea59f7ca52a2087c99374caba0ff29be1b2dcdbf')").executeUpdate();
    connection.prepareStatement("INSERT INTO users (username, password) VALUES ('ballen', 'ea59f7ca52a2087c99374caba0ff29be1b2dcdbf')").executeUpdate();
    connection.prepareStatement("CREATE TABLE roles (rolename varchar(255) NOT NULL, username varchar(255) NOT NULL)").executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('user', 'bwayne')").executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('admin', 'bwayne')").executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('ckent', 'user')").executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('ballen', 'user')").executeUpdate();
    connection.close();
    return ds;
}
 

@Test
public void test() throws Exception {
    try (Server server = new Server(new ServerConfiguration(folder.newFolder().toPath()))) {
        server.start();
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setUrl("jdbc:herddb:server:localhost:7000?");
        dataSource.setDriverClassName(Driver.class.getName());
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery("SELECT * FROM SYSTABLES")) {
            int count = 0;
            while (rs.next()) {
                System.out.println("table: " + rs.getString(1));
                count++;
            }
            assertTrue(count > 0);
        }
        dataSource.close();

    }
}
 

/**
 * Creates a datasource from a DB file found in the test resources.
 * @throws SQLException
 */
private static BasicDataSource createDatasourceFrom(String sqlFileName) throws Exception {
    // Step 1 - copy the db file from the classpath to a temp location
    URL sqlUrl = JdbcStorageUpgradeTest.class.getResource(sqlFileName);
    Assert.assertNotNull(sqlUrl);
    File tempDbFile = File.createTempFile("_apicurio_junit", ".mv.db");
    tempDbFile.deleteOnExit();

    String jdbcUrl = "jdbc:h2:file:" + tempDbFile.getAbsolutePath().replaceAll(".mv.db", "");
    
    // Step 2 - create a datasource from the file path
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("sa");
    ds.setUrl(jdbcUrl);
    
    try (Connection connection = ds.getConnection(); Reader reader = new InputStreamReader(sqlUrl.openStream())) {
        RunScript.execute(connection, reader);
    }
    
    return ds;
}
 

/**
 * <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'");
}
 

protected void initH2DB(String databaseName, String scriptPath) throws Exception {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        dataSource.setUrl("jdbc:h2:mem:test" + databaseName);
        try (Connection connection = dataSource.getConnection()) {
            connection.createStatement().executeUpdate("RUNSCRIPT FROM '" + scriptPath + "'");
        }
        dataSourceMap.put(databaseName, dataSource);
    }
 

public static Connection getConnection( LogChannelInterface log, DatabaseMeta dbMeta, String partitionId,
    int initialSize, int maximumSize ) throws Exception {
  lock.lock();
  try {
    if ( !isDataSourceRegistered( dbMeta, partitionId ) ) {
      addPoolableDataSource( log, dbMeta, partitionId, initialSize, maximumSize );
    }
  } finally {
    lock.unlock();
  }
  BasicDataSource ds = dataSources.get( getDataSourceName( dbMeta, partitionId ) );
  return ds.getConnection();
}
 

/**
 * Creates an in-memory datasource.
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws Exception {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
    Connection connection = ds.getConnection();
    connection.setAutoCommit(true);
    initDB(connection);
    connection.close();
    return ds;
}
 

/**
 * Creates an in-memory datasource.
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws Exception {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
    Connection connection = ds.getConnection();
    connection.setAutoCommit(true);
    initDB(connection);
    connection.close();
    return ds;
}
 

/**
 * Creates an h2 file based datasource.
 * @throws SQLException
 */
private static BasicDataSource createFileDatasource(File outputDirectory) throws SQLException {
    TestUtil.setProperty("apiman.hibernate.dialect", "org.hibernate.dialect.H2Dialect");
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setUrl("jdbc:h2:" + outputDirectory.toString() + "/apiman-manager-api;MVCC=true");
    Connection connection = ds.getConnection();
    connection.close();
    System.out.println("DataSource created and bound to JNDI.");
    return ds;
}
 

/**
 * Creates an in-memory datasource.
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws SQLException {
    TestUtil.setProperty("apiman.hibernate.dialect", "org.hibernate.dialect.H2Dialect");
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
    Connection connection = ds.getConnection();
    connection.close();
    System.out.println("DataSource created and bound to JNDI.");
    return ds;
}
 

/**
 * Creates an in-memory datasource.
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws Exception {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
    Connection connection = ds.getConnection();
    connection.setAutoCommit(true);
    initDB(connection);
    connection.close();
    return ds;
}
 

public static void initializeDataSource(String databaseName, String scriptPath) throws Exception {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        dataSource.setUrl("jdbc:h2:mem:" + databaseName);

        try (Connection connection = dataSource.getConnection()) {
            connection.createStatement().executeUpdate("RUNSCRIPT FROM '" + scriptPath + "'");
        }
        dataSourceMap.put(databaseName, dataSource);
    }
 

public static void initiateH2Base() throws Exception {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        dataSource.setUrl("jdbc:h2:mem:test" + DB_NAME);
        try (Connection connection = dataSource.getConnection()) {
            connection.createStatement().executeUpdate("RUNSCRIPT FROM '" + getFilePath(H2_SCRIPT_NAME) + "'");
        }
        dataSourceMap.put(DB_NAME, dataSource);
    }
 

public static void initiateH2Base() throws Exception {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        dataSource.setUrl("jdbc:h2:mem:test" + DB_NAME);
        try (Connection connection = dataSource.getConnection()) {
            connection.createStatement().executeUpdate("RUNSCRIPT FROM '" + getFilePath(H2_SCRIPT_NAME) + "'");
        }
        dataSourceMap.put(DB_NAME, dataSource);
    }
 

/**
 * Initiate an H2 database.
 *
 * @throws Exception
 */
public static void initiateH2Base() throws Exception {

    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUsername("username");
    dataSource.setPassword("password");
    dataSource.setUrl("jdbc:h2:mem:test" + DB_NAME);
    try (Connection connection = dataSource.getConnection()) {
        connection.createStatement().executeUpdate("RUNSCRIPT FROM '" + getFilePath(H2_SCRIPT_NAME) + "'");
    }
    dataSourceMap.put(DB_NAME, dataSource);
}
 

private static DataSource createDataSource(
    String host, int port, String user, String passwd, int poolSize,
    int maxWaitMillis, int socketTimeoutMillis) {
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl(String.format(
      "jdbc:mysql://%s:%d?"
          + "connectTimeout=5000&"
          + "socketTimeout=%d&"
          + "enableQueryTimeouts=false&"
          + "cachePrepStmts=true&"
          + "characterEncoding=UTF-8",
      host,
      port,
      socketTimeoutMillis));
  dataSource.setUsername(user);
  dataSource.setPassword(passwd);
  dataSource.setDefaultAutoCommit(true);
  dataSource.setInitialSize(poolSize);
  dataSource.setMaxActive(poolSize);
  dataSource.setMaxIdle(poolSize);
  // deal with idle connection eviction
  dataSource.setValidationQuery("SELECT 1 FROM DUAL");
  dataSource.setTestOnBorrow(false);
  dataSource.setTestOnReturn(false);
  dataSource.setTestWhileIdle(true);
  dataSource.setMinEvictableIdleTimeMillis(5 * 60 * 1000);
  dataSource.setTimeBetweenEvictionRunsMillis(3 * 60 * 1000);
  dataSource.setNumTestsPerEvictionRun(poolSize);
  // max wait in milliseconds for a connection.
  dataSource.setMaxWait(maxWaitMillis);
  // force connection pool initialization.
  Connection conn = null;
  try {
    // Here not getting the connection from ThreadLocal no need to worry about that.
    conn = dataSource.getConnection();
  } catch (SQLException e) {
    LOG.error(
        String.format("Failed to get a mysql connection when creating DataSource, "
            + "host: %s, port: %d", host, port),
        e);
  } finally {
    JdbcUtils.closeConnection(conn);
  }
  return dataSource;
}
 

private BasicDataSource createDataSource(Map<String, String> properties, String urlPattern) {
	BasicDataSource dsPool = new BasicDataSource();

	dsPool.setDriverClassName(DSPropertyUtil.getPropertyStringValue("driver", properties, null, null, true));
	dsPool.setUsername(DSPropertyUtil.getPropertyStringValue("username", properties, null, null, true));
	dsPool.setPassword(DSPropertyUtil.getPropertyStringValue("password", properties, null, null, true));

	String url = DSPropertyUtil.getPropertyStringValue("url", properties, null, null, true);
	if (null != urlPattern) {
		url = DSPropertyUtil.replace(url, "{}", urlPattern);
	}
	dsPool.setUrl(url);
	dsPool.setPoolPreparedStatements(DSPropertyUtil.getPropertyBooleanValue("poolingStatements", properties, null, true, true)); // 开启池的prepared
																																	// 池功能
	dsPool.setRemoveAbandoned(DSPropertyUtil.getPropertyBooleanValue("removeAbandoned", properties, null, true, true));
	dsPool.setRemoveAbandonedTimeout(DSPropertyUtil.getPropertyIntegerValue("removeAbandonedTimeout", properties, null, 1000, true));
	dsPool.setLogAbandoned(DSPropertyUtil.getPropertyBooleanValue("logAbandoned", properties, null, true, true));

	dsPool.setInitialSize(DSPropertyUtil.getPropertyIntegerValue("initialSize", properties, null, 2, true));
	dsPool.setMaxActive(DSPropertyUtil.getPropertyIntegerValue("maxActive", properties, null, 8, true));
	dsPool.setMaxIdle(DSPropertyUtil.getPropertyIntegerValue("maxIdle", properties, null, 8, true));
	dsPool.setMinIdle(DSPropertyUtil.getPropertyIntegerValue("minIdle", properties, null, 0, true));
	dsPool.setMaxWait(DSPropertyUtil.getPropertyIntegerValue("maxWait", properties, null, 10000, true));

	dsPool.setTimeBetweenEvictionRunsMillis(DSPropertyUtil.getPropertyIntegerValue("timeBetweenEvictionRunsMillis", properties, null, -1, true));

	dsPool.setTestOnBorrow(DSPropertyUtil.getPropertyBooleanValue("testOnBorrow", properties, null, false, true));
	dsPool.setTestOnReturn(DSPropertyUtil.getPropertyBooleanValue("testOnReturn", properties, null, false, true));
	dsPool.setTestWhileIdle(DSPropertyUtil.getPropertyBooleanValue("testWhileIdle", properties, null, false, true));

	String validationQuery = DSPropertyUtil.getPropertyStringValue("validationQuery", properties, null, null, false);
	if (null != validationQuery) {
		dsPool.setValidationQuery(validationQuery);
	}
	int timeout = DSPropertyUtil.getPropertyIntegerValue("", properties, null, -1, false);
	if (timeout > -1) {
		dsPool.setValidationQueryTimeout(timeout);
	}

	dsPool.setNumTestsPerEvictionRun(DSPropertyUtil.getPropertyIntegerValue("numTestsPerEvictionRun", properties, null, 10, true));
	dsPool.setMinEvictableIdleTimeMillis(DSPropertyUtil.getPropertyIntegerValue("minEvictableIdleTimeMillis", properties, null, 60000, true));

	// mysql:select 1
	// oracle:select 1 from dual
	// sqlserver:select 1
	// jtds:select 1

	boolean openTest = DSPropertyUtil.getPropertyBooleanValue("openTest", properties, null, false, false);
	if (openTest) {
		try {
			Connection conn = dsPool.getConnection();
			conn.close();
			log.info("test open database success.");
		} catch (Exception e) {
			throw new DataSourceException("test open database error: " + e.getMessage(), e);
		}
	}
	return dsPool;
}