Java Code Examples for org.apache.commons.dbcp2.PoolableConnectionFactory#setValidationQuery()

private ScipioErrorDbConnectionFactory() {
    Properties properties = new Properties();
    properties.setProperty("user", DB_USERNAME);
    properties.setProperty("password", DB_PASSWORD);
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(DB_URL, properties);
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    GenericObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool); // TODO: REVIEW: circular?
    poolableConnectionFactory.setValidationQuery("SELECT 1");
    poolableConnectionFactory.setValidationQueryTimeout(3);
    poolableConnectionFactory.setDefaultReadOnly(false);
    poolableConnectionFactory.setDefaultAutoCommit(false);
    poolableConnectionFactory.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // Original (log4j manual, dbcp lib):
    //PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
    //        connectionFactory, connectionPool, null, "SELECT 1", 3, false, false, Connection.TRANSACTION_READ_COMMITTED
    //);
    this.dataSource = new PoolingDataSource<>(connectionPool);
}
 

@Before
public void setUp() throws Exception {
	org.hsqldb.jdbc.JDBCDriver.class.newInstance();
	Properties connectionProps=new Properties();
	connectionProps.put("user", "SA");
	connectionProps.put("password", "");
	connectionProps.put("create", "true");
	
	ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
			"jdbc:hsqldb:mem:my-sample",connectionProps);
	
	ObjectName poolName= poolName=new ObjectName("org.moneta", "connectionPool", "TestPool");
	poolableConnectionFactory=
			new PoolableConnectionFactory(connectionFactory,poolName);
	poolableConnectionFactory.setDefaultCatalog("PUBLIC");
	poolableConnectionFactory.setValidationQuery(VALIDATION_SQL);
	
	connectionPool = 
			new GenericObjectPool<PoolableConnection>(poolableConnectionFactory);
	poolableConnectionFactory.setPool(connectionPool);
	connectionPool.setMaxTotal(2);
	connectionPool.setMaxWaitMillis(400);
	
	healthCheck = new DbcpConnectionPoolHealthCheck(connectionPool, "mine");
}
 

/**
 * Creates a data source
 */
private static DataSource createDataSource(String uri, String username, String password,
                                           Integer maxActive, Integer maxIdle, Long maxWait,
                                           Long timeBtwnEvictionRuns, Long minEvictableIdleTime,
                                           Boolean testWhileIdle, Boolean testOnBorrow,
                                           String validationQuery, Integer isolationLevel) {
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(uri, username, password);
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    GenericObjectPoolConfig config = createPoolConfig(maxActive, maxIdle, maxWait, timeBtwnEvictionRuns, minEvictableIdleTime, testWhileIdle, testOnBorrow);
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory, config);
    poolableConnectionFactory.setPool(connectionPool);
    if (validationQuery != null) {
        poolableConnectionFactory.setValidationQuery(validationQuery);
    }
    if (isolationLevel != null) {
        poolableConnectionFactory.setDefaultTransactionIsolation(isolationLevel);
    }
    return new PoolingDataSource<>(connectionPool);
}
 

public void init(final PoolableConnectionFactory poolableConnectionFactory) {
    if (poolableConnectionFactory != null) {
        StatusLogger.getLogger().debug("Initializing PoolableConnectionFactory {} with {}",
                poolableConnectionFactory, this);
        poolableConnectionFactory.setCacheState(cacheState);
        poolableConnectionFactory.setConnectionInitSql(connectionInitSqls);
        poolableConnectionFactory.setDefaultAutoCommit(defaultAutoCommit);
        poolableConnectionFactory.setDefaultCatalog(defaultCatalog);
        poolableConnectionFactory.setDefaultQueryTimeout(defaultQueryTimeoutSeconds);
        poolableConnectionFactory.setDefaultReadOnly(defaultReadOnly);
        poolableConnectionFactory.setDefaultTransactionIsolation(defaultTransactionIsolation);
        poolableConnectionFactory.setDisconnectionSqlCodes(disconnectionSqlCodes);
        poolableConnectionFactory.setEnableAutoCommitOnReturn(autoCommitOnReturn);
        poolableConnectionFactory.setFastFailValidation(fastFailValidation);
        poolableConnectionFactory.setMaxConnLifetimeMillis(maxConnLifetimeMillis);
        poolableConnectionFactory.setMaxOpenPreparedStatements(maxOpenPreparedStatements);
        poolableConnectionFactory.setPoolStatements(poolStatements);
        poolableConnectionFactory.setRollbackOnReturn(rollbackOnReturn);
        poolableConnectionFactory.setValidationQuery(validationQuery);
        poolableConnectionFactory.setValidationQueryTimeout(validationQueryTimeoutSeconds);
    }

}
 

static PoolableDataSource create(ConnectionFactory connectionFactory) {

        // setup our pool config object
        
        GenericObjectPoolConfig config = setupPoolConfig();
        
        PoolableConnectionFactory poolableConnectionFactory =
            new PoolableConnectionFactory(connectionFactory, null);
         
        // Set max lifetime of a connection in milli-secs, after which it will
        // always fail activation, passivation, and validation.
        // Value of -1 means infinite life time. The default value
        // defined in this class is 10 minutes.
        long connTtlMillis = retrieveConfigSetting(ATHENZ_PROP_DBPOOL_MAX_TTL, MAX_TTL_CONN_MS);
        poolableConnectionFactory.setMaxConnLifetimeMillis(connTtlMillis);
        if (LOG.isInfoEnabled()) {
            LOG.info("Setting Time-To-Live interval for live connections ({}) msecs", connTtlMillis);
        }

        // set the validation query for our jdbc connector
        final String validationQuery = System.getProperty(ATHENZ_PROP_DBPOOL_VALIDATION_QUERY, MYSQL_VALIDATION_QUERY);
        poolableConnectionFactory.setValidationQuery(validationQuery);

        ObjectPool<PoolableConnection> connectionPool =
                new GenericObjectPool<>(poolableConnectionFactory, config);
        poolableConnectionFactory.setPool(connectionPool);

        return new AthenzDataSource(connectionPool);
    }
 

private void createConnectionPool(String url, String user, String dbPrefix,
    Properties properties) throws SQLException, ClassNotFoundException {

  String driverClass = properties.getProperty(DRIVER_KEY);
  if (driverClass != null && (driverClass.equals("com.facebook.presto.jdbc.PrestoDriver")
          || driverClass.equals("io.prestosql.jdbc.PrestoDriver"))) {
    // Only add valid properties otherwise presto won't work.
    for (String key : properties.stringPropertyNames()) {
      if (!PRESTO_PROPERTIES.contains(key)) {
        properties.remove(key);
      }
    }
  }

  ConnectionFactory connectionFactory =
          new DriverManagerConnectionFactory(url, properties);

  PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
          connectionFactory, null);
  final String maxConnectionLifetime =
      StringUtils.defaultIfEmpty(getProperty("zeppelin.jdbc.maxConnLifetime"), "-1");
  poolableConnectionFactory.setMaxConnLifetimeMillis(Long.parseLong(maxConnectionLifetime));
  poolableConnectionFactory.setValidationQuery("show databases");
  ObjectPool connectionPool = new GenericObjectPool(poolableConnectionFactory);

  poolableConnectionFactory.setPool(connectionPool);
  Class.forName(driverClass);
  PoolingDriver driver = new PoolingDriver();
  driver.registerPool(dbPrefix + user, connectionPool);
  getJDBCConfiguration(user).saveDBDriverPool(dbPrefix, driver);
}
 

@Override
public void start() {

    try {
        Class.forName(driver);
    } catch (final ClassNotFoundException e) {
        throw new RuntimeException("Unable to find JDBC driver " + driver, e);
    }

    final ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(serverURL, username, password);
    final PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    poolableConnectionFactory.setValidationQuery(testSQL);
    poolableConnectionFactory.setValidationQueryTimeout(testTimeout);
    poolableConnectionFactory.setMaxConnLifetimeMillis((long) (connectionTimeout * JiveConstants.DAY));

    final GenericObjectPoolConfig poolConfig = new GenericObjectPoolConfig();
    poolConfig.setTestOnBorrow(testBeforeUse);
    poolConfig.setTestOnReturn(testAfterUse);
    poolConfig.setMinIdle(minConnections);
    if( minConnections > GenericObjectPoolConfig.DEFAULT_MAX_IDLE )
    {
        poolConfig.setMaxIdle(minConnections);
    }
    poolConfig.setMaxTotal(maxConnections);
    poolConfig.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRuns);
    poolConfig.setSoftMinEvictableIdleTimeMillis(minIdleTime);
    poolConfig.setMaxWaitMillis(maxWaitTime);
    connectionPool = new GenericObjectPool<>(poolableConnectionFactory, poolConfig);
    poolableConnectionFactory.setPool(connectionPool);
    dataSource = new PoolingDataSource<>(connectionPool);
}
 

@BeforeEach
public void setUp() throws Exception {
    // create a GeronimoTransactionManager for testing
    transactionManager = new TransactionManagerImpl();

    // create a driver connection factory
    final Properties properties = new Properties();
    properties.setProperty("user", "userName");
    properties.setProperty("password", "password");
    final ConnectionFactory connectionFactory = new DriverConnectionFactory(new TesterDriver(), "jdbc:apache:commons:testdriver", properties);

    // wrap it with a LocalXAConnectionFactory
    final XAConnectionFactory xaConnectionFactory = new LocalXAConnectionFactory(transactionManager, connectionFactory);

    // create the pool object factory
    final PoolableConnectionFactory factory =
        new PoolableConnectionFactory(xaConnectionFactory, null);
    factory.setValidationQuery("SELECT DUMMY FROM DUAL");
    factory.setDefaultReadOnly(Boolean.TRUE);
    factory.setDefaultAutoCommit(Boolean.TRUE);

    // create the pool
    pool = new GenericObjectPool<>(factory);
    factory.setPool(pool);
    pool.setMaxTotal(getMaxTotal());
    pool.setMaxWaitMillis(getMaxWaitMillis());

    // finally create the datasource
    ds = new ManagedDataSource<>(pool, xaConnectionFactory.getTransactionRegistry());
    ds.setAccessToUnderlyingConnectionAllowed(true);
}
 

@BeforeEach
public void setUp()
    throws Exception {
    // create a GeronimoTransactionManager for testing
    transactionManager = new TransactionManagerImpl();

    // create a driver connection factory
    final Properties properties = new Properties();
    properties.setProperty("user", "userName");
    properties.setProperty("password", "password");
    final ConnectionFactory connectionFactory = new DriverConnectionFactory(new TesterDriver(), "jdbc:apache:commons:testdriver", properties);

    // wrap it with a LocalXAConnectionFactory
    final XAConnectionFactory xaConnectionFactory = new UncooperativeLocalXAConnectionFactory(transactionManager, connectionFactory);

    // create the pool object factory
    final PoolableConnectionFactory factory = new PoolableConnectionFactory(xaConnectionFactory, null);
    factory.setValidationQuery("SELECT DUMMY FROM DUAL");
    factory.setDefaultReadOnly(Boolean.TRUE);
    factory.setDefaultAutoCommit(Boolean.TRUE);

    // create the pool
    pool = new GenericObjectPool<>(factory);
    factory.setPool(pool);
    pool.setMaxTotal(10);
    pool.setMaxWaitMillis(100);

    // finally create the datasource
    ds = new ManagedDataSource<>(pool, xaConnectionFactory.getTransactionRegistry());
    ds.setAccessToUnderlyingConnectionAllowed(true);
}
 

@BeforeEach
public void setUp() throws Exception {
    // create a GeronimoTransactionManager for testing
    transactionManager = new TransactionManagerImpl();

    // create a driver connection factory
    final Properties properties = new Properties();
    properties.setProperty("user", "userName");
    properties.setProperty("password", "password");
    final ConnectionFactory connectionFactory = new DriverConnectionFactory(new TesterDriver(), "jdbc:apache:commons:testdriver", properties);

    // wrap it with a LocalXAConnectionFactory
    final XAConnectionFactory xaConnectionFactory = new LocalXAConnectionFactory(transactionManager, connectionFactory);

    // create transaction registry
    transactionRegistry = xaConnectionFactory.getTransactionRegistry();

    // create the pool object factory
    final PoolableConnectionFactory factory = new PoolableConnectionFactory(xaConnectionFactory, null);
    factory.setValidationQuery("SELECT DUMMY FROM DUAL");
    factory.setDefaultReadOnly(Boolean.TRUE);
    factory.setDefaultAutoCommit(Boolean.TRUE);

    // create the pool
    pool = new GenericObjectPool<>(factory);
    factory.setPool(pool);
    pool.setMaxTotal(10);
    pool.setMaxWaitMillis(100);
}
 

@Test
public void testSessionSynchronization() throws Exception {
    final DataSourceXAConnectionFactory xaConnectionFactory = new DataSourceXAConnectionFactory(transactionManager,
            xads);

    final PoolableConnectionFactory factory = new PoolableConnectionFactory(xaConnectionFactory, null);
    factory.setValidationQuery("SELECT DUMMY FROM DUAL");
    factory.setDefaultReadOnly(Boolean.TRUE);
    factory.setDefaultAutoCommit(Boolean.TRUE);

    // create the pool
    try (final GenericObjectPool pool = new GenericObjectPool<>(factory)) {
        factory.setPool(pool);
        pool.setMaxTotal(10);
        pool.setMaxWaitMillis(1000);

        // finally create the datasource
        try (final ManagedDataSource ds = new ManagedDataSource<>(pool,
                xaConnectionFactory.getTransactionRegistry())) {
            ds.setAccessToUnderlyingConnectionAllowed(true);

            transactionManager.begin();
            try (final DelegatingConnection<?> connectionA = (DelegatingConnection<?>) ds.getConnection()) {
                // close right away.
            }
            transactionManager.commit();
            assertTrue(transactionManagerRegistered);
            assertFalse(transactionSynchronizationRegistryRegistered);
        }
    }
}
 

@Test
public void testInterposedSynchronization() throws Exception {
    final DataSourceXAConnectionFactory xaConnectionFactory = new DataSourceXAConnectionFactory(transactionManager,
            xads, transactionSynchronizationRegistry);

    final PoolableConnectionFactory factory = new PoolableConnectionFactory(xaConnectionFactory, null);
    factory.setValidationQuery("SELECT DUMMY FROM DUAL");
    factory.setDefaultReadOnly(Boolean.TRUE);
    factory.setDefaultAutoCommit(Boolean.TRUE);

    // create the pool
    try (final GenericObjectPool pool = new GenericObjectPool<>(factory)) {
        factory.setPool(pool);
        pool.setMaxTotal(10);
        pool.setMaxWaitMillis(1000);

        // finally create the datasource
        try (final ManagedDataSource ds = new ManagedDataSource<>(pool,
                xaConnectionFactory.getTransactionRegistry())) {
            ds.setAccessToUnderlyingConnectionAllowed(true);

            transactionManager.begin();
            try (final DelegatingConnection<?> connectionA = (DelegatingConnection<?>) ds.getConnection()) {
                // Close right away.
            }
            transactionManager.commit();
            assertFalse(transactionManagerRegistered);
            assertTrue(transactionSynchronizationRegistryRegistered);
        }
    }
}
 

private void createDbcp(DbcpConfig conf)
{
	if (!dataSources.containsKey(conf.name))
	{
		try
	    {
			
			Class.forName(conf.driverClassName);
		    
		    DriverManagerConnectionFactory cf =  new DriverManagerConnectionFactory(conf.jdbc,conf.user,conf.password);
		    
		    PoolableConnectionFactory pcf  =  new PoolableConnectionFactory(cf,null);
		    pcf.setValidationQuery(conf.validationQuery);
		    //, pool, null, conf.validationQuery, false, true,abandondedConfig);
			
		    logger.info("Creating pool "+conf.toString());
		    // create a generic pool
		    GenericObjectPool<PoolableConnection> pool = new GenericObjectPool<PoolableConnection>(pcf);
		    pool.setMaxTotal(conf.maxTotal);
		    pool.setMaxIdle(conf.maxIdle);
		    pool.setMinIdle(conf.minIdle);
		    pool.setMaxWaitMillis(conf.maxWait);
		    pool.setTimeBetweenEvictionRunsMillis(conf.timeBetweenEvictionRunsMillis);
		    pool.setMinEvictableIdleTimeMillis(conf.minEvictableIdleTimeMillis);
		    pool.setTestWhileIdle(conf.testWhileIdle);
		    pool.setTestOnBorrow(conf.testOnBorrow);
	    
		    AbandonedConfig abandonedConfig = new AbandonedConfig();
		    abandonedConfig.setRemoveAbandonedOnMaintenance(conf.removeAbanadoned);
		    abandonedConfig.setRemoveAbandonedTimeout(conf.removeAbandonedTimeout);
		    abandonedConfig.setLogAbandoned(conf.logAbandonded);
	    
		    pool.setAbandonedConfig(abandonedConfig);
	    
		    pcf.setPool(pool);
		    DataSource ds = new PoolingDataSource(pool);
		    dataSources.put(conf.name, ds);

	    } catch (ClassNotFoundException e) {
			logger.error("Failed to create datasource for "+conf.name+ " with class "+conf.driverClassName);
		}
	   
	}
	else
	{
		logger.error("Pool "+conf.name+" already exists. Can't change existing datasource at present.");
	}
}