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

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;
}
 

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;
}
 

public List<HashMap<String,Object>> runMysqlQuery(ServicesControl mysql,
                           MySQLConfiguration configuration,
                           String query,
                           boolean runAsRoot) {
    Statement statement;
    BasicDataSource dataSource = initDatasource(mysql, configuration, Driver.class.getName(), runAsRoot);
    try (Connection connection = dataSource.getConnection()) {
        statement = connection.createStatement();
        LOG.debug("Executing query - " + query);

        if (!query.trim().equals("")) {
            statement.execute(query);
        }

        ResultSet result = statement.getResultSet();
        return convertResultSetToList(result);
    } catch (Exception exception) {
        LOG.warn(String.format("error executing query \"%s\": %s",
                query, exception.getMessage()));
        return null;
    }

}
 

public static IDatabaseConnection openConnection(final BasicDataSource ds) {
    try {
        final Connection connection = ds.getConnection();

        for (final DbUnitConnectionFactory impl : SERVICE_LOADER) {
            if (impl.supportsDriver(ds.getDriverClassName())) {
                return impl.createConnection(connection, discoverSchema(connection));
            }
        }

        // fall back if no specific implementation is available
        return new DatabaseConnection(connection);
    } catch (final DatabaseUnitException | SQLException e) {
        throw new JpaUnitException(e);
    }
}
 

/**
 * check the database url
 *
 * @param databaseUrl data bae url
 * @return connection
 */
public static Connection getDbcpConnection(String databaseUrl, String databaseType) {
    try {
        Map<String, String> requestUrlMap = uRLRequest(databaseUrl);
        // check all parameter
        if (!requestUrlMap.containsKey("user") || !requestUrlMap.containsKey("password") || StringUtils.isEmpty(urlPage(databaseUrl))) {
            return null;
        }
        // use the cache
        if (dsMap.containsKey(databaseUrl)) {
            // use the old connection
            return dsMap.get(databaseUrl).getConnection();
        } else {
            Properties properties = new Properties();
            BasicDataSource ds = BasicDataSourceFactory.createDataSource(properties);
            dsMap.put(databaseUrl, ds);
            if (DatabaseTypeEnum.H2_DATABASE.getCode().equals(databaseType)) {
                ds.setDriverClassName("org.h2.Driver");
            } else {
                ds.setDriverClassName("org.mariadb.jdbc.Driver");
            }
            ds.setUrl(urlPage(databaseUrl));
            ds.setUsername(requestUrlMap.get("user"));
            ds.setPassword(requestUrlMap.get("password"));

            ds.setInitialSize(Integer.parseInt(Objects.requireNonNull(ProcessorApplication.environment.getProperty("spring.datasource.dbcp2.initial-size"))));
            ds.setMinIdle(Integer.parseInt(Objects.requireNonNull(ProcessorApplication.environment.getProperty("spring.datasource.dbcp2.min-idle"))));
            ds.setMaxWaitMillis(Integer.parseInt(Objects.requireNonNull(ProcessorApplication.environment.getProperty("spring.datasource.dbcp2.max-wait-millis"))));
            ds.setMaxTotal(Integer.parseInt(Objects.requireNonNull(ProcessorApplication.environment.getProperty("spring.datasource.dbcp2.max-total"))));

            return ds.getConnection();
        }
    } catch (Exception e) {
        log.error("e:{}", e.toString());
        return null;
    }
}
 

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());
}
 

public boolean createDbIfNotExists(Map<String, Object> configuration) {
    Object driverClass  = configuration.getOrDefault(Configuration.MYSQL_DRIVER_CLASS, ActiveSchemaManager.DEFAULT_MYSQL_DRIVER_CLASS);
    Object hostname     = configuration.get(Configuration.MYSQL_HOSTNAME);
    Object port         = configuration.getOrDefault(Configuration.MYSQL_PORT, "3306");
    Object schema1      = configuration.get(Configuration.MYSQL_ACTIVE_SCHEMA);
    Object username     = configuration.get(Configuration.MYSQL_USERNAME);
    Object password     = configuration.get(Configuration.MYSQL_PASSWORD);

    Objects.requireNonNull(hostname, String.format("Configuration required: %s", Configuration.MYSQL_HOSTNAME));
    Objects.requireNonNull(schema1, String.format("Configuration required: %s", Configuration.MYSQL_ACTIVE_SCHEMA));
    Objects.requireNonNull(username, String.format("Configuration required: %s", Configuration.MYSQL_USERNAME));
    Objects.requireNonNull(password, String.format("Configuration required: %s", Configuration.MYSQL_PASSWORD));

    String schema = schema1.toString();
    BasicDataSource dataSource = this.getDataSource(driverClass.toString(), this.getFirst(hostname), Integer.parseInt(port.toString()), username.toString(), password.toString());
    try (Connection conn = dataSource.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement("SHOW DATABASES LIKE ?");
        stmt.setString(1, schema);
        ResultSet resultSet = stmt.executeQuery();
        if (resultSet.next()) {
            LOG.info("Database " + schema + " already exists in active schema.");
            return true;
        }

        LOG.info("Database " + schema + " doesn't exists in active schema. Creating ...");
        PreparedStatement createDb = conn.prepareStatement("CREATE DATABASE " + schema);
        return createDb.execute();
    } catch (SQLException e) {
        LOG.error("Could not establist connection to: " + hostname, e);
    }
    return false;
}
 

public boolean runMysqlScript(ServicesControl mysql,
                              MySQLConfiguration configuration,
                              String scriptFilePath,
                              Map<String, String> scriptParams,
                              boolean runAsRoot) {
    Statement statement;
    BasicDataSource dataSource = initDatasource(mysql, configuration, Driver.class.getName(), runAsRoot);
    try (Connection connection = dataSource.getConnection()) {
        statement = connection.createStatement();
        LOG.info("Executing query from " + scriptFilePath);
        String s;
        StringBuilder sb = new StringBuilder();
        File filehandle = new File(scriptFilePath);
        FileReader fr = new FileReader(filehandle);
        BufferedReader br = new BufferedReader(fr);
        while ((s = br.readLine()) != null) {
            sb.append(s);
        }
        br.close();

        StrSubstitutor sub = new StrSubstitutor(scriptParams, "{", "}");
        String subSb = sub.replace(sb);

        String[] inst = subSb.split(";");
        for (String query : inst) {
            if (!query.trim().equals("")) {
                statement.execute(query);
                LOG.debug("Query executed - " + query);
            }
        }
        return true;
    } catch (Exception exception) {
        throw new RuntimeException(String.format("error executing query \"%s\": %s",
                scriptFilePath, exception.getMessage()));
    }

}
 

@Bean(name = "goDataSource")
public BasicDataSource getDataSource() throws SQLException {
    BasicDataSource dataSource = connectionManager.getDataSourceInstance();
    try (Connection connection = dataSource.getConnection()) {
        new DbDeploySchemaVerifier().verify(connection, systemEnvironment.getConfigDir());
    }

    try (Connection connection = dataSource.getConnection()) {
        new DatabaseMigrator().migrate(connection);
    }

    return dataSource;
}
 

/**
 * Creates the internal data source. This method also initializes the
 * database.
 *
 * @return the data source
 * @throws Exception if an error occurs
 */
private DataSource setUpDataSource() throws Exception
{
    final BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(DATABASE_DRIVER);
    ds.setUrl(DATABASE_URL);
    ds.setUsername(DATABASE_USERNAME);
    ds.setPassword(DATABASE_PASSWORD);
    ds.setDefaultAutoCommit(!isAutoCommit());

    // prepare the database
    final Connection conn = ds.getConnection();
    final IDatabaseConnection connection = new DatabaseConnection(conn);
    final IDataSet dataSet = new XmlDataSet(new FileInputStream(
            ConfigurationAssert.getTestFile("dataset.xml")));

    try
    {
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
    }
    finally
    {
        if (!isAutoCommit())
        {
            conn.commit();
        }
        connection.close();
    }

    return ds;
}
 

public static TableSchema computeTableSchema(
        String schemaName,
        String tableName,
        BasicDataSource activeSchemaDataSource,
        BasicDataSource replicantDataSource,
        Boolean fallbackToReplicant) {

    try (Connection activeSchemaConnection = activeSchemaDataSource.getConnection()) {

        if ( fallbackToReplicant ) {
            createTableIfNotExists(tableName, activeSchemaConnection, replicantDataSource);
        }

        Statement statementActiveSchemaListColumns      = activeSchemaConnection.createStatement();
        Statement statementActiveSchemaShowCreateTable  = activeSchemaConnection.createStatement();

        List<ColumnSchema> columnList = new ArrayList<>();

        ResultSet resultSet;

        resultSet = statementActiveSchemaListColumns.executeQuery(
             String.format(
                     ActiveSchemaManager.LIST_COLUMNS_SQL,
                     schemaName,
                     tableName
             )
        );

        while (resultSet.next()) {

            boolean isNullable = (resultSet.getString("IS_NULLABLE").equals("NO") ? false : true);

            DataType dataType = DataType.byCode(resultSet.getString("DATA_TYPE"));

            ColumnSchema columnSchema = new ColumnSchema(
                    resultSet.getString("COLUMN_NAME"),
                    dataType,
                    resultSet.getString("COLUMN_TYPE"),
                    isNullable,
                    resultSet.getString("COLUMN_KEY"),
                    resultSet.getString("EXTRA")
            );

            columnSchema
                    .setCollation(resultSet.getString("COLLATION_NAME"))
                    .setDefaultValue(resultSet.getString("COLUMN_DEFAULT"))
                    .setDateTimePrecision(resultSet.getInt("DATETIME_PRECISION"))
                    .setCharMaxLength(resultSet.getInt("CHARACTER_MAXIMUM_LENGTH"))
                    .setCharOctetLength(resultSet.getInt("CHARACTER_OCTET_LENGTH"))
                    .setNumericPrecision(resultSet.getInt("NUMERIC_PRECISION"))
                    .setNumericScale(resultSet.getInt("NUMERIC_SCALE"));

            columnList.add(columnSchema);
        }

        DatabaseMetaData dbm = activeSchemaConnection.getMetaData();
        boolean tableExists = false;
        ResultSet tables = dbm.getTables(schemaName, null, tableName, null);
        if (tables.next()) {
            tableExists = true; // DLL statement was not table DROP
        }

        String tableCreateStatement = "";
        if (tableExists) {
            Statement createTableStatement = statementActiveSchemaShowCreateTable;
            ResultSet showCreateTableResultSet = createTableStatement.executeQuery(
                    String.format(ActiveSchemaManager.SHOW_CREATE_TABLE_SQL, tableName)
            );
            ResultSetMetaData showCreateTableResultSetMetadata = showCreateTableResultSet.getMetaData();
            tableCreateStatement = ActiveSchemaHelpers.getCreateTableStatement(tableName, showCreateTableResultSet, showCreateTableResultSetMetadata);
        }

        return new TableSchema(
                new FullTableName(schemaName, tableName),
                columnList,
                tableCreateStatement
        );

    } catch (SQLException exception) {
        throw new IllegalStateException("Could not get table schema: ", exception);
    }
}
 

public void run(AtomicBoolean bootstrapInProgress) {

        if ((boolean) configuration.get(Augmenter.Configuration.BOOTSTRAP) == false) {
            LOG.info("Skipping active schema bootstrapping");
            bootstrapInProgress.set(false);
            return;
        }
        LOG.info("Running bootstrapping");

        bootstrapInProgress.set(true);

        ActiveSchemaManager activeSchemaManager = new ActiveSchemaManager(configuration);
        boolean dbCreated = activeSchemaManager.createDbIfNotExists(configuration);
        if (!dbCreated) {
            throw new IllegalStateException("Could not create active schema.");
        }

        Object binlogSchemaObj = configuration.get(BinaryLogSupplier.Configuration.MYSQL_SCHEMA);
        Objects.requireNonNull(binlogSchemaObj);
        String binlogSchema = String.valueOf(binlogSchemaObj);

        BasicDataSource binLogDS = activeSchemaManager.initBinlogDatasource(configuration);

        try (Connection binlogConn = binLogDS.getConnection()) {
            PreparedStatement binlogShowTablesQuery = binlogConn.prepareStatement("show tables");

            ResultSet binlogTables = binlogShowTablesQuery.executeQuery();

            Object schemaRegistryUrlConfig = configuration.get(KafkaApplier.Configuration.SCHEMA_REGISTRY_URL);
            String dataFormat = configuration.get(KafkaApplier.Configuration.FORMAT) == null ? KafkaApplier.MessageFormat.AVRO : String.valueOf(configuration.get(KafkaApplier.Configuration.FORMAT));

            BCachedSchemaRegistryClient schemaRegistryClient = null;
            if (Objects.equals(dataFormat, KafkaApplier.MessageFormat.AVRO)) {
                schemaRegistryClient = new BCachedSchemaRegistryClient(String.valueOf(schemaRegistryUrlConfig), 2000);
            }

            while (binlogTables.next()) {
                String replicantTableName = binlogTables.getString(1);

                LOG.info(replicantTableName + " Recreating in active schema.");

                // Override
                activeSchemaManager.dropTable(replicantTableName);
                activeSchemaManager.copyTableSchemaFromReplicantToActiveSchema(replicantTableName);

                // Get schemas
                List<ColumnSchema> columnSchemas = activeSchemaManager.listColumns(replicantTableName);

                if (schemaRegistryClient == null) {
                    continue;
                }

                Schema avroSchema = EventDataPresenterAvro.createAvroSchema(true, true, new FullTableName(binlogSchema, replicantTableName), columnSchemas);
                String schemaKey = String.format("bigdata-%s-%s-value", binlogSchema, replicantTableName);
                LOG.info("Registering " + schemaKey + " in schemaregistry.");
                schemaRegistryClient.register(schemaKey, avroSchema);
            }

            bootstrapInProgress.set(false);

            LOG.info("Finished bootstrapping.");

        } catch (Exception e) {
            LOG.error("Error while bootstrapping", e);
        }
    }