org.postgresql.ds.PGSimpleDataSource Java Examples

PostgresDAOTestUtil(String dbName) throws Exception {
    PGSimpleDataSource ds = new PGSimpleDataSource();
    ds.setServerName("localhost");
    ds.setPortNumber(54320);
    ds.setDatabaseName("postgres");
    ds.setUser("postgres");
    ds.setPassword("postgres");
    this.initializationDataSource = ds;

    createDb(ds, dbName);

    testConfiguration.setProperty("jdbc.url", JDBC_URL_PREFIX + dbName);
    testConfiguration.setProperty("jdbc.username", "postgres");
    testConfiguration.setProperty("jdbc.password", "postgres");

    this.dataSource = getDataSource(testConfiguration);
}
 

/**
 * Ctor.
 * @param hostname Server hostname or IPv4 Address
 * @param port Server port
 * @param dbname Database name
 */
public SourcePgsql(
    final String hostname,
    final int port,
    final String dbname
) {
    this.origin = new Unchecked<>(
        new Solid<>(
            () -> {
                final PGSimpleDataSource pds = new PGSimpleDataSource();
                pds.setUrl(
                    new FormattedText(
                        "jdbc:postgresql://%s:%d/%s",
                        hostname,
                        port,
                        dbname
                    ).asString()
                );
                return pds;
            }
        )
    );
}
 

@Override
public SQLDataSourceLocator getDataSourceLocator() {
    return new SQLDataSourceLocator() {
        public DataSource lookup(SQLDataSetDef def) throws Exception {
            String server = connectionSettings.getProperty("server");
            String database = connectionSettings.getProperty("database");
            String port = connectionSettings.getProperty("port");
            String user = connectionSettings.getProperty("user");
            String password = connectionSettings.getProperty("password");

            PGSimpleDataSource ds = new PGSimpleDataSource();
            ds.setServerName(server);
            ds.setDatabaseName(database);
            ds.setPortNumber(Integer.parseInt(port));
            if (!StringUtils.isBlank(user)) {
                ds.setUser(user);
            }
            if (!StringUtils.isBlank(password)) {
                ds.setPassword(password);
            }
            return ds;
        }
    };
}
 

public DataSource getDatabase(String userName, String dbName, Map<String, String> properties) {
    final PGSimpleDataSource ds = new PGSimpleDataSource();
    ds.setServerName("localhost");
    ds.setPortNumber(port);
    ds.setDatabaseName(dbName);
    ds.setUser(userName);

    properties.forEach((propertyKey, propertyValue) -> {
        try {
            ds.setProperty(propertyKey, propertyValue);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    });
    return ds;
}
 

private DataSource getDatabase(String dbName) throws SQLException {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();

    dataSource.setServerName(container.getContainerIpAddress());
    dataSource.setPortNumber(container.getMappedPort(POSTGRESQL_PORT));
    dataSource.setDatabaseName(dbName);

    dataSource.setUser(container.getUsername());
    dataSource.setPassword(container.getPassword());

    for (Map.Entry<String, String> entry : config.connectProperties.entrySet()) {
        dataSource.setProperty(entry.getKey(), entry.getValue());
    }

    return new BlockingDataSourceWrapper(dataSource, semaphore);
}
 

private DataSource getDatabase(String dbName) throws SQLException {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();

    dataSource.setServerName(DEFAULT_HOST);
    dataSource.setPortNumber(postgres.getConfig().map(config -> config.net().port()).orElse(-1));
    dataSource.setDatabaseName(dbName);

    dataSource.setUser(POSTGRES_USERNAME);
    dataSource.setPassword(POSTGRES_PASSWORD);

    for (Map.Entry<String, String> entry : config.connectProperties.entrySet()) {
        dataSource.setProperty(entry.getKey(), entry.getValue());
    }

    return new BlockingDataSourceWrapper(dataSource, semaphore);
}
 

private void addTenantConnectionProvider(String tenantId) {
    PGSimpleDataSource defaultDataSource = (PGSimpleDataSource) database().dataSourceProvider().dataSource();

    Properties properties = properties();

    PGSimpleDataSource tenantDataSource = new PGSimpleDataSource();
    tenantDataSource.setDatabaseName(defaultDataSource.getDatabaseName());
    tenantDataSource.setCurrentSchema(tenantId);
    tenantDataSource.setServerName(defaultDataSource.getServerName());
    tenantDataSource.setUser(defaultDataSource.getUser());
    tenantDataSource.setPassword(defaultDataSource.getPassword());

    properties.put(
            Environment.DATASOURCE,
            dataSourceProxyType().dataSource(tenantDataSource)
    );

    addTenantConnectionProvider(tenantId, tenantDataSource, properties);
}
 

@Test
public void testConfigurationProperties() throws Exception {
    assertThat(dataSource.unwrap(PGSimpleDataSource.class).getProperty("stringtype")).isEqualTo("unspecified");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String collate = jdbcTemplate.queryForObject("show lc_collate", String.class);
    assertThat(collate).isEqualTo("cs_CZ.UTF-8");

    String maxConnections = jdbcTemplate.queryForObject("show max_connections", String.class);
    assertThat(maxConnections).isEqualTo("100");

    String sharedBuffers = jdbcTemplate.queryForObject("show shared_buffers", String.class);
    assertThat(sharedBuffers).isEqualTo("64MB");
}
 

protected void setUp() throws Exception {
	super.setUp();
	PGSimpleDataSource postgresDS = new PGSimpleDataSource();
	postgresDS.setUser("postgres");
	postgresDS.setPassword("");
	postgresDS.setDatabaseName("nosco_test_jpetstore");
	ds = postgresDS;
	Context vmContext = Context.getVMContext();
	vmContext.setDataSource(ds).setAutoUndo(false);
	//vmContext.overrideSchema(ds, "public", "nosco_test_jpetstore").setAutoUndo(false);
	ccds = new ConnectionCountingDataSource(ds);
	//vmContext.overrideSchema(ccds, "public", "nosco_test_jpetstore").setAutoUndo(false);
}
 

@Test
@FlywayTest(flywayName = "flyway1", locationsForMigrate = "db/test_migration/appendable")
@FlywayTest(flywayName = "flyway2", locationsForMigrate = "db/test_migration/dependent")
@FlywayTest(flywayName = "flyway3", overrideLocations = true, locationsForMigrate = "db/test_migration/separated")
public void loadDefaultMigrations() throws SQLException {
    assertThat(dataSource1).isNotNull();
    assertThat(dataSource2).isNotNull();
    assertThat(dataSource3).isNotNull();

    assertThat(getPort(dataSource1)).isEqualTo(getPort(dataSource3));
    assertThat(getPort(dataSource1)).isNotEqualTo(getPort(dataSource2));
    assertThat(getDatabaseName(dataSource1)).isNotEqualTo(getDatabaseName(dataSource3));

    assertThat(dataSource1.unwrap(PGSimpleDataSource.class).getPassword()).isNotEqualTo("docker");
    assertThat(dataSource2.unwrap(PGSimpleDataSource.class).getPassword()).isEqualTo("docker");
    assertThat(dataSource3.unwrap(PGSimpleDataSource.class).getPassword()).isNotEqualTo("docker");

    assertThat(jdbcTemplate1.queryForList(SQL_SELECT_PERSONS)).extracting("id", "first_name", "last_name").containsExactlyInAnyOrder(
            tuple(1L, "Dave", "Syer"),
            tuple(2L, "Tom", "Hanks"));

    assertThat(jdbcTemplate2.queryForList(SQL_SELECT_PERSONS)).extracting("id", "first_name", "last_name", "full_name").containsExactlyInAnyOrder(
            tuple(1L, "Dave", "Syer", "Dave Syer"),
            tuple(3L, "Will", "Smith", "Will Smith"));

    List<Map<String, Object>> persons = jdbcTemplate3.queryForList(SQL_SELECT_PERSONS);
    assertThat(persons).isNotNull().hasSize(1);

    Map<String, Object> person = persons.get(0);
    assertThat(person).containsExactly(
            entry("id", 1L),
            entry("first_name", "Tom"),
            entry("last_name", "Hanks"));
}
 

private PGSimpleDataSource extractDataSource(CfCredentials databaseServiceCredentials) {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setServerName(databaseServiceCredentials.getHost());
    dataSource.setUser(databaseServiceCredentials.getUsername());
    dataSource.setPassword(databaseServiceCredentials.getPassword());
    dataSource.setDatabaseName(databaseServiceCredentials.getString("dbname"));
    dataSource.setPortNumber(getPort(databaseServiceCredentials));
    dataSource.setSsl(false);
    return dataSource;
}
 

@Bean(name = "dataSource")
public DataSource getDataSource() {
  PGSimpleDataSource ret = new PGSimpleDataSource();
  //
  ret.setDatabaseName(NetworkProperties.getDatabaseName());
  ret.setPortNumber(5432);
  ret.setUser(NetworkProperties.getDatabaseUser());

  return ret;
}
 

@Override
protected DataSource createDataSource() {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setUrl(POSTGRESQL_CONTAINER.getJdbcUrl());
    dataSource.setUser(POSTGRESQL_CONTAINER.getUsername());
    dataSource.setPassword(POSTGRESQL_CONTAINER.getPassword());
    return dataSource;
}
 

@Override
protected DataSource createDataSource() {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setUrl(POSTGRESQL_CONTAINER.getJdbcUrl());
    dataSource.setUser(POSTGRESQL_CONTAINER.getUsername());
    dataSource.setPassword(POSTGRESQL_CONTAINER.getPassword());
    return dataSource;
}
 

public static PGSimpleDataSource getPostgresDataSource(PostgresIOTestPipelineOptions options) {
  PGSimpleDataSource dataSource = new PGSimpleDataSource();
  dataSource.setDatabaseName(options.getPostgresDatabaseName());
  dataSource.setServerName(options.getPostgresServerName());
  dataSource.setPortNumber(options.getPostgresPort());
  dataSource.setUser(options.getPostgresUsername());
  dataSource.setPassword(options.getPostgresPassword());
  dataSource.setSsl(options.getPostgresSsl());
  return dataSource;
}
 

@Override
protected void configure() {
  final Map<String, String> properties = new HashMap<>();
  properties.put(TRANSACTION_TYPE, PersistenceUnitTransactionType.RESOURCE_LOCAL.name());

  final String dbUrl = System.getProperty("jdbc.url");
  final String dbUser = System.getProperty("jdbc.user");
  final String dbPassword = System.getProperty("jdbc.password");

  waitConnectionIsEstablished(dbUrl, dbUser, dbPassword);

  properties.put(JDBC_URL, dbUrl);
  properties.put(JDBC_USER, dbUser);
  properties.put(JDBC_PASSWORD, dbPassword);
  properties.put(JDBC_DRIVER, System.getProperty("jdbc.driver"));

  JpaPersistModule main = new JpaPersistModule("main");
  main.properties(properties);
  install(main);
  final PGSimpleDataSource dataSource = new PGSimpleDataSource();
  dataSource.setUser(dbUser);
  dataSource.setPassword(dbPassword);
  dataSource.setUrl(dbUrl);
  bind(SchemaInitializer.class)
      .toInstance(new FlywaySchemaInitializer(dataSource, "che-schema", "codenvy-schema"));
  bind(DBInitializer.class).asEagerSingleton();
  bind(TckResourcesCleaner.class).to(JpaCleaner.class);

  bind(new TypeLiteral<TckRepository<InviteImpl>>() {})
      .toInstance(new JpaTckRepository<>(InviteImpl.class));
  bind(new TypeLiteral<TckRepository<OrganizationImpl>>() {})
      .toInstance(new JpaTckRepository<>(OrganizationImpl.class));
  bind(new TypeLiteral<TckRepository<WorkspaceImpl>>() {})
      .toInstance(new JpaTckRepository<>(WorkspaceImpl.class));

  bind(InviteDao.class).to(JpaInviteDao.class);
}
 

@Bean
public DataSource readWriteDataSource() {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setURL(primaryUrl);
    dataSource.setUser(username);
    dataSource.setPassword(password);
    return connectionPoolDataSource(dataSource);
}
 

@Bean
public DataSource readOnlyDataSource() {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setURL(replicaUrl);
    dataSource.setUser(username);
    dataSource.setPassword(password);
    return connectionPoolDataSource(dataSource);
}
 

@Override
public void afterInit() {
    PGSimpleDataSource defaultDataSource = (PGSimpleDataSource) database().dataSourceProvider().dataSource();
    addTenantConnectionProvider(TenantContext.DEFAULT_TENANT_IDENTIFIER, defaultDataSource, properties());

    createSchema("europe");
    createSchema("asia");
}
 

@Override
public DataSource actualDataSource() {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setDatabaseName(jdbcDatabase);
    dataSource.setUser(jdbcUser);
    dataSource.setPassword(jdbcPassword);
    dataSource.setServerName(jdbcHost);
    dataSource.setPortNumber(Integer.valueOf(jdbcPort));
    return dataSource;
}
 

public static synchronized void INIT() {

        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setUrl(PG_URL);

        try (final Connection cnx = ds.getConnection(); final Statement st = cnx.createStatement()) {
            st.execute(TestSuite.getResourceAsString(PG_SQL));
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }

        TestSuite.setupSessionFactoryBuilder(ds);
    }
 

/**
 * Create a new Datasource given DBInfo.
 * More common usage is to call createDatasource().
 */
public DataSource createDataSourceFromConnectionInfo(final ConnectionInfo connectionInfo) throws SQLException
{
    final PGSimpleDataSource ds = new PGSimpleDataSource();
    ds.setPortNumber(connectionInfo.getPort());
    ds.setDatabaseName(connectionInfo.getDbName());
    ds.setUser(connectionInfo.getUser());
    return ds;
}
 

public ConnectionHandler(String host, int port, String dbName, String dbUser, String dbPassword) {
    PGSimpleDataSource dataSource = new PGSimpleDataSource();
    dataSource.setPortNumbers(new int[] {port});
    dataSource.setServerNames(new String[] {host});
    dataSource.setDatabaseName(dbName);
    dataSource.setPassword(dbPassword);
    dataSource.setUser(dbUser);
    jdbcTemplate = new JdbcTemplate(dataSource);
}
 

public synchronized Connection getConnection() throws SQLException {
    if (source == null) { // New data source
        PGSimpleDataSource postgresqlSource = new PGSimpleDataSource();

        // Set credentials
        postgresqlSource.setServerNames(addresses);
        postgresqlSource.setPortNumbers(ports);
        postgresqlSource.setUser(username);
        postgresqlSource.setPassword(password);
        postgresqlSource.setDatabaseName(database);

        // Try using HikariCP
        source = postgresqlSource;

        //noinspection Duplicates
        try {
            Class.forName("com.zaxxer.hikari.HikariDataSource");
            hikari = true; // Used for shutdown. Not instanceof because of possible classpath error

            // Set HikariCP pool
            HikariDataSource hikariSource = new HikariDataSource();
            hikariSource.setDataSource(source);

            // Set pool settings
            hikariSource.setPoolName(poolName);
            hikariSource.setMinimumIdle(0);
            hikariSource.setMaximumPoolSize(MAX_POOL_SIZE);
            hikariSource.setIdleTimeout(TIMEOUT * 1000L);

            // Replace source with hds
            source = hikariSource;
            LogHelper.info("HikariCP pooling enabled for '%s'", poolName);
        } catch (ClassNotFoundException ignored) {
            LogHelper.warning("HikariCP isn't in classpath for '%s'", poolName);
        }
    }
    return source.getConnection();
}
 

@Test
public void testDataSource() throws SQLException {
    assertThat(dataSource.unwrap(PGSimpleDataSource.class).getPassword()).isEqualTo("docker-postgres");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String version = jdbcTemplate.queryForObject("show server_version", String.class);
    assertThat(version).startsWith("9.6.");
}
 

private static void dataSourceSimple() {
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("learnjava");
    source.setUser("student");
    //source.setPassword("password");
    source.setLoginTimeout(10);
    try {
        Connection conn = source.getConnection();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}
 

private static Connection getDbConnection2(){
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("cookbook");
    source.setLoginTimeout(10);
    try {
        return source.getConnection();
    }
    catch(Exception ex) {
        ex.printStackTrace();
        return null;
    }
}
 

private static Connection getDbConnection2(){
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("cookbook");
    source.setLoginTimeout(10);
    try {
        return source.getConnection();
    }
    catch(Exception ex) {
        ex.printStackTrace();
        return null;
    }
}
 

private static Connection getDbConnection2(){
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("cookbook");
    source.setLoginTimeout(10);
    try {
        return source.getConnection();
    }
    catch(Exception ex) {
        ex.printStackTrace();
        return null;
    }
}
 

private static Connection getDbConnection2(){
    PGSimpleDataSource source = new PGSimpleDataSource();
    source.setServerName("localhost");
    source.setDatabaseName("cookbook");
    source.setLoginTimeout(10);
    try {
        return source.getConnection();
    }
    catch(Exception ex) {
        ex.printStackTrace();
        return null;
    }
}