Java Code Examples for com.mchange.v2.c3p0.ComboPooledDataSource#setCheckoutTimeout()

private ComboPooledDataSource getComboPooledDataSource(String host, int port, String database, String user,
                                                       String password, String applicationName, int maxPostgreSQLConnections) {
    final ComboPooledDataSource cpds = new ComboPooledDataSource();

    cpds.setJdbcUrl(
            String.format("jdbc:postgresql://%1$s:%2$d/%3$s?ApplicationName=%4$s&tcpKeepAlive=true", host, port, database, applicationName));

    cpds.setUser(user);
    cpds.setPassword(password);

    cpds.setInitialPoolSize(1);
    cpds.setMinPoolSize(1);
    cpds.setAcquireIncrement(1);
    cpds.setMaxPoolSize(maxPostgreSQLConnections);
    cpds.setCheckoutTimeout( CONNECTION_CHECKOUT_TIMEOUT_SECONDS * 1000 );
    cpds.setConnectionCustomizerClassName(DatabaseHandler.XyzConnectionCustomizer.class.getName());
    return cpds;
}
 

protected DAOFactory(String facName, String driverClassName, String jdbcURL, String userName, String userPassword,
        int initPoolSize, int MinPoolSize, int MaxPoolSize, int maxIdleTime, int idleConnectionTestPeriod,
        String testQuerySQL) {

    this.facName = facName;

    ds = new ComboPooledDataSource();
    // 设置JDBC的Driver类
    try {
        ds.setDriverClass(driverClassName);
    }
    catch (PropertyVetoException e) {
        throw new RuntimeException(e);
    }
    // 设置JDBC的URL
    ds.setJdbcUrl(jdbcURL);
    // 设置数据库的登录用户名
    ds.setUser(userName);
    // 设置数据库的登录用户密码
    ds.setPassword(userPassword);
    // 设置连接池的最大连接数
    ds.setMaxPoolSize(MaxPoolSize);
    // 设置连接池的最小连接数
    ds.setMinPoolSize(MinPoolSize);
    // 设置初始化连接数
    ds.setInitialPoolSize(initPoolSize);
    // 设置最大闲置时间
    ds.setMaxIdleTime(maxIdleTime);
    // 设置测试SQL
    ds.setPreferredTestQuery(testQuerySQL);
    // 设置闲置测试周期
    ds.setIdleConnectionTestPeriod(idleConnectionTestPeriod);
    // 增加单个连接的Statements数量
    ds.setMaxStatements(0);
    // 连接池内单个连接所拥有的最大缓存statements数
    ds.setMaxStatementsPerConnection(200);
    // 获取空闲连接超时时间
    ds.setCheckoutTimeout(10 * 1000);
}
 

private void generatePool(Properties props) throws LDAPException {
	String poolKey = (url+user).toLowerCase();

	synchronized(poolCache) {
		if (poolCache.get(poolKey) != null ) {
			logger.info(this.name + " - using existing connection pool");
			this.ds = poolCache.get(poolKey);
		} else {
			logger.info(this.name + " - creating connection pool");
			ComboPooledDataSource cpds = new ComboPooledDataSource();
			try {
				cpds.setDriverClass(driver);
			} catch (PropertyVetoException e1) {
				throw new LDAPException(LDAPException.resultCodeToString(LDAPException.OPERATIONS_ERROR), LDAPException.OPERATIONS_ERROR, "Could not load driver",e1);
			}
			cpds.setJdbcUrl(url);
			cpds.setUser(user);
			cpds.setPassword(pwd);
			cpds.setMaxPoolSize(this.maxCons);
			//cpds.setma(this.maxIdleCons);
			cpds.setPreferredTestQuery(this.valQuery);
			cpds.setTestConnectionOnCheckin(true);
			int testPeriod = Integer.parseInt(props.getProperty("idleConnectionTestPeriod","30"));
			cpds.setIdleConnectionTestPeriod(testPeriod);
			int unreturnedConnectionTimeout = Integer.parseInt(props.getProperty("unreturnedConnectionTimeout","30"));
			cpds.setUnreturnedConnectionTimeout(unreturnedConnectionTimeout);
			cpds.setDebugUnreturnedConnectionStackTraces(true);

			int checkoutTimeout = Integer.parseInt(props.getProperty("checkoutTimeout","30000"));
			cpds.setCheckoutTimeout(checkoutTimeout);


			this.ds = cpds;
			poolCache.put(poolKey, cpds);
		}
	
	}
}
 

public static DataSource loadDB(String dbID, Settings settings) throws Throwable {
    // 1.获取数据库连接配置信息
    String driverString = settings.getString(dbID + ".driver");
    String urlString = settings.getString(dbID + ".url");
    String userString = settings.getString(dbID + ".user");
    String pwdString = settings.getString(dbID + ".password");
    // 2.创建数据库连接池
    int poolMaxSize = 40;
    logger.info("C3p0 Pool Info maxSize is ‘%s’ driver is ‘%s’ jdbcUrl is‘%s’", poolMaxSize, driverString, urlString);
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass(driverString);
    dataSource.setJdbcUrl(urlString);
    dataSource.setUser(userString);
    dataSource.setPassword(pwdString);
    dataSource.setMaxPoolSize(poolMaxSize);
    dataSource.setInitialPoolSize(1);
    // dataSource.setAutomaticTestTable("DB_TEST_ATest001");
    dataSource.setIdleConnectionTestPeriod(18000);
    dataSource.setCheckoutTimeout(3000);
    dataSource.setTestConnectionOnCheckin(true);
    dataSource.setAcquireRetryDelay(1000);
    dataSource.setAcquireRetryAttempts(30);
    dataSource.setAcquireIncrement(1);
    dataSource.setMaxIdleTime(25000);
    // 3.启用默认事务拦截器
    return dataSource;
}
 

private DataSource createDataSource(String driverClass, String url, String account, String password) {
	ComboPooledDataSource dataSource = new ComboPooledDataSource();
	try {
		dataSource.setDriverClass(driverClass);
		dataSource.setJdbcUrl(url);
		dataSource.setUser(account);
		dataSource.setPassword(password);

		dataSource.setMinPoolSize(10);
		dataSource.setMaxPoolSize(35);
		dataSource.setAcquireIncrement(0);
		dataSource.setMaxStatements(0);
		
		/** 最大允許的閒置時間(秒) */
		dataSource.setMaxIdleTime(300);
		/** 對閒置的連線進行Query測試設置(秒) */
		dataSource.setIdleConnectionTestPeriod(1800);
		
		/** Checkin connection時不檢查連線是否有效 */
		dataSource.setTestConnectionOnCheckin(false);
		/** Checkout connection時檢查連線的有效性*/
		dataSource.setTestConnectionOnCheckout(true);
		/** 進行test時使用的 Query設定*/
		dataSource.setPreferredTestQuery("SELECT 1;");
		/** Connection的最大有效時數(秒)*/
		dataSource.setMaxConnectionAge(28800);
		/** Connection checkout 之後的有效時數(毫秒)*/
		dataSource.setCheckoutTimeout(28800000);
	} catch (PropertyVetoException e) {
		e.printStackTrace();
	}
	mPoolMap.put(url, dataSource);
	return dataSource;
}
 

private IdService constructDbIdService(String dbUrl, String dbName,
                                       String dbUser, String dbPassword) {
    log.info(
            "Construct Db IdService dbUrl {} dbName {} dbUser {} dbPassword {}",
            dbUrl, dbName, dbUser, dbPassword);

    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

    String jdbcDriver = "com.mysql.jdbc.Driver";
    try {
        comboPooledDataSource.setDriverClass(jdbcDriver);
    } catch (PropertyVetoException e) {
        log.error("Wrong JDBC driver {}", jdbcDriver);
        log.error("Wrong JDBC driver error: ", e);
        throw new IllegalStateException("Wrong JDBC driver ", e);
    }

    comboPooledDataSource.setMinPoolSize(5);
    comboPooledDataSource.setMaxPoolSize(30);
    comboPooledDataSource.setIdleConnectionTestPeriod(20);
    comboPooledDataSource.setMaxIdleTime(25);
    comboPooledDataSource.setBreakAfterAcquireFailure(false);
    comboPooledDataSource.setCheckoutTimeout(3000);
    comboPooledDataSource.setAcquireRetryAttempts(50);
    comboPooledDataSource.setAcquireRetryDelay(1000);

    String url = String
            .format("jdbc:mysql://%s/%s?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
                    dbUrl, dbName);

    comboPooledDataSource.setJdbcUrl(url);
    comboPooledDataSource.setUser(dbUser);
    comboPooledDataSource.setPassword(dbPassword);

    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setLazyInit(false);
    jdbcTemplate.setDataSource(comboPooledDataSource);

    DbMachineIdProvider dbMachineIdProvider = new DbMachineIdProvider();
    dbMachineIdProvider.setJdbcTemplate(jdbcTemplate);
    dbMachineIdProvider.init();

    IdServiceImpl idServiceImpl = new IdServiceImpl();
    idServiceImpl.setMachineIdProvider(dbMachineIdProvider);
    if (genMethod != -1)
        idServiceImpl.setGenMethod(genMethod);
    if (type != -1)
        idServiceImpl.setType(type);
    if (version != -1)
        idServiceImpl.setVersion(version);
    idServiceImpl.init();

    return idServiceImpl;
}
 

public void testPooling(CloudSpannerConnection original)
    throws SQLException, PropertyVetoException, InterruptedException {
  log.info("Starting connection pooling tests");
  ComboPooledDataSource cpds = new ComboPooledDataSource();
  cpds.setDriverClass("nl.topicus.jdbc.CloudSpannerDriver");
  cpds.setJdbcUrl(original.getUrl());
  cpds.setProperties(original.getSuppliedProperties());

  cpds.setInitialPoolSize(5);
  cpds.setMinPoolSize(5);
  cpds.setAcquireIncrement(5);
  cpds.setMaxPoolSize(20);
  cpds.setCheckoutTimeout(1000);

  log.info("Connection pool created and configured. Acquiring connection from pool");
  Assert.assertEquals(0, cpds.getNumBusyConnectionsDefaultUser());
  Connection connection = cpds.getConnection();
  Assert.assertNotNull(connection);
  Assert.assertEquals(1, cpds.getNumBusyConnectionsDefaultUser());
  connection.close();
  while (cpds.getNumBusyConnections() == 1) {
    TimeUnit.MILLISECONDS.sleep(100L);
  }
  Assert.assertEquals(0, cpds.getNumBusyConnectionsDefaultUser());

  log.info("About to acquire 10 connections");
  Connection[] connections = new Connection[10];
  for (int i = 0; i < connections.length; i++) {
    connections[i] = cpds.getConnection();
    Assert.assertEquals(i + 1, cpds.getNumBusyConnectionsDefaultUser());
  }
  log.info("10 connections acquired, closing connections...");
  for (int i = 0; i < connections.length; i++) {
    connections[i].close();
  }
  log.info("10 connections closed");
  log.info("Acquiring 20 connections");
  // Check that we can get 20 connections
  connections = new Connection[20];
  for (int i = 0; i < connections.length; i++) {
    connections[i] = cpds.getConnection();
    connections[i].prepareStatement("SELECT 1").executeQuery();
  }
  log.info("20 connections acquired, trying to get one more");
  // Verify that we can't get a connection now
  connection = null;
  try {
    connection = cpds.getConnection();
  } catch (SQLException e) {
    // timeout exception
    log.info("Exception when trying to get one more connection (this is expected)");
  }
  log.info("Closing 20 connections");
  Assert.assertNull(connection);
  for (int i = 0; i < connections.length; i++) {
    connections[i].close();
  }
  log.info("Closing connection pool");
  cpds.close();
  log.info("Finished tests");
}
 

public L2DatabaseFactory() throws SQLException
{
	try
	{
		if (Config.DATABASE_MAX_CONNECTIONS < 2)
           {
               Config.DATABASE_MAX_CONNECTIONS = 2;
               _log.warning("at least " + Config.DATABASE_MAX_CONNECTIONS + " db connections are required.");
           }

		_source = new ComboPooledDataSource();
		_source.setAutoCommitOnClose(true);

		_source.setInitialPoolSize(10);
		_source.setMinPoolSize(10);
		_source.setMaxPoolSize(Config.DATABASE_MAX_CONNECTIONS);

		_source.setAcquireRetryAttempts(0); // try to obtain connections indefinitely (0 = never quit)
		_source.setAcquireRetryDelay(500);  // 500 miliseconds wait before try to acquire connection again
		_source.setCheckoutTimeout(0);      // 0 = wait indefinitely for new connection
		// if pool is exhausted
		_source.setAcquireIncrement(5);     // if pool is exhausted, get 5 more connections at a time
		// cause there is a "long" delay on acquire connection
		// so taking more than one connection at once will make connection pooling
		// more effective.

		// this "connection_test_table" is automatically created if not already there
		_source.setAutomaticTestTable("connection_test_table");
		_source.setTestConnectionOnCheckin(false);

		// testing OnCheckin used with IdleConnectionTestPeriod is faster than  testing on checkout

		_source.setIdleConnectionTestPeriod(3600); // test idle connection every 60 sec
		_source.setMaxIdleTime(0); // 0 = idle connections never expire
		// *THANKS* to connection testing configured above
		// but I prefer to disconnect all connections not used
		// for more than 1 hour

		// enables statement caching,  there is a "semi-bug" in c3p0 0.9.0 but in 0.9.0.2 and later it's fixed
		_source.setMaxStatementsPerConnection(100);

		_source.setBreakAfterAcquireFailure(false);  // never fail if any way possible
		// setting this to true will make
		// c3p0 "crash" and refuse to work
		// till restart thus making acquire
		// errors "FATAL" ... we don't want that
		// it should be possible to recover
		_source.setDriverClass(Config.DATABASE_DRIVER);
		_source.setJdbcUrl(Config.DATABASE_URL);
		_source.setUser(Config.DATABASE_LOGIN);
		_source.setPassword(Config.DATABASE_PASSWORD);

		/* Test the connection */
		_source.getConnection().close();

		if (Config.DEBUG) _log.fine("Database Connection Working");

		if (Config.DATABASE_DRIVER.toLowerCase().contains("microsoft"))
               _providerType = ProviderType.MsSql;
           else
               _providerType = ProviderType.MySql;
	}
	catch (SQLException x)
	{
		if (Config.DEBUG) _log.fine("Database Connection FAILED");
		// rethrow the exception
		throw x;
	}
	catch (Exception e)
	{
		if (Config.DEBUG) _log.fine("Database Connection FAILED");
		throw new SQLException("could not init DB connection:"+e);
	}
}
 

public ManagedDataSource create(DataSourceConfiguration config) throws SQLMissingDependencyException, SQLConfigurationException {

        //
        // http://www.mchange.com/projects/c3p0/index.html#configuration_properties
        //

        //
        // these system properties need turned off prior to creating our first
        // instance of the ComboPooledDataSource, otherwise, they are ignored

        // turn off VMID stuff (causes long ugly names for datasources)
        System.setProperty("com.mchange.v2.c3p0.VMID", "NONE");

        // jmx is off by default
        if (!config.getJmx()) {
            // apparently, c3p0 does this with a system-wide property
            // com.mchange.v2.c3p0.management.ManagementCoordinator=com.mchange.v2.c3p0.management.NullManagementCoordinator
            System.setProperty("com.mchange.v2.c3p0.management.ManagementCoordinator", "com.mchange.v2.c3p0.management.NullManagementCoordinator");
        } else {
            System.setProperty("com.mchange.v2.c3p0.management.ManagementCoordinator", "com.cloudhopper.commons.sql.c3p0.C3P0CustomManagementCoordinator");
        }

        // set the JMX domain for the C3P0
        C3P0CustomManagementCoordinator.setJmxDomainOnce(config.getJmxDomain());

        // create a new instance of the c3p0 datasource
        ComboPooledDataSource cpds = new ComboPooledDataSource(true);

        // set properties
        try {
            // set required properties
            cpds.setDriverClass(config.getDriver());
            cpds.setUser(config.getUsername());
            cpds.setPassword(config.getPassword());
            cpds.setJdbcUrl(config.getUrl());

            // set optional properties
            cpds.setDataSourceName(config.getName());
            cpds.setMinPoolSize(config.getMinPoolSize());
            cpds.setMaxPoolSize(config.getMaxPoolSize());
            // we'll set the initial pool size to the minimum size
            cpds.setInitialPoolSize(config.getMinPoolSize());

            // set the validation query
            cpds.setPreferredTestQuery(config.getValidationQuery());

            // amount of time (in ms) to wait for getConnection() to succeed
            cpds.setCheckoutTimeout((int)config.getCheckoutTimeout());

            // checkin validation
            cpds.setTestConnectionOnCheckin(config.getValidateOnCheckin());

            // checkout validation
            cpds.setTestConnectionOnCheckout(config.getValidateOnCheckout());

            // amount of time to wait to validate connections
            // NOTE: in seconds
            int seconds = (int)(config.getValidateIdleConnectionTimeout()/1000);
            cpds.setIdleConnectionTestPeriod(seconds);

            // set idleConnectionTimeout
            // NOTE: in seconds
            seconds = (int)(config.getIdleConnectionTimeout()/1000);
            cpds.setMaxIdleTimeExcessConnections(seconds);

            // set activeConnectionTimeout
            seconds = (int)(config.getActiveConnectionTimeout()/1000);
            cpds.setUnreturnedConnectionTimeout(seconds);

            if (config.getDebug()) {
                cpds.setDebugUnreturnedConnectionStackTraces(true);
            } else {
                cpds.setDebugUnreturnedConnectionStackTraces(false);
            }

            // properties I think aren't valid for c3p0
            // defines how many times c3p0 will try to acquire a new Connection from the database before giving up.
            cpds.setAcquireRetryAttempts(10);

        } catch (PropertyVetoException e) {
            throw new SQLConfigurationException("Property was vetoed during configuration", e);
        }

        /**
        // configure c3p0 defaults that seem to make more sense
        /**
         *  c3p0.acquireIncrement	hibernate.c3p0.acquire_increment
            c3p0.idleConnectionTestPeriod	hibernate.c3p0.idle_test_period
            c3p0.initialPoolSize	not available -- uses minimum size
            c3p0.maxIdleTime	hibernate.c3p0.timeout
            c3p0.maxPoolSize	hibernate.c3p0.max_size
            c3p0.maxStatements	hibernate.c3p0.max_statements
            c3p0.minPoolSize	hibernate.c3p0.min_size
            c3p0.testConnectionsOnCheckout 	hibernate.c3p0.validate hibernate 2.x only!
         */
        
        return new C3P0ManagedDataSource(this, config, cpds);
    }