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

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

public Database() {

        String version = "";
        try {
            String resourceName = "project.properties";
            ClassLoader loader = Thread.currentThread().getContextClassLoader();
            Properties props = new Properties();
            try(InputStream resourceStream = loader.getResourceAsStream(resourceName)) {
                props.load(resourceStream);
            }
            version = props.getProperty("version");
        } catch (IOException ex){
            Logs.write(Logs.Level.ERROR, "GetVersionOfSQLiteSyncCOM: " + ex.getMessage());
        }
        SQLiteSyncVersion = version;

        SQLiteSyncConfig.Load();

        try {
            cpds = new ComboPooledDataSource();
            cpds.setDriverClass(SQLiteSyncConfig.DBDRIVER); //loads the jdbc driver
            cpds.setJdbcUrl(SQLiteSyncConfig.DBURL);
            cpds.setUser(SQLiteSyncConfig.DBUSER);
            cpds.setPassword(SQLiteSyncConfig.DBPASS);

            // the settings below are optional -- c3p0 can work with defaults
            cpds.setMinPoolSize(3);
            cpds.setAcquireIncrement(3);
            cpds.setMaxPoolSize(10);
            cpds.setIdleConnectionTestPeriod(300);
            cpds.setMaxIdleTime(240);
            cpds.setTestConnectionOnCheckin(false);
            cpds.setMaxStatements(2000);
            cpds.setMaxStatementsPerConnection(100);

        } catch (PropertyVetoException e){
            Logs.write(Logs.Level.ERROR, "Database constructor: " + e.getMessage());
        }
    }
 

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

/**
 * Create the underlying C3PO ComboPooledDataSource with the 
 * default supported properties.
 * @throws SchedulerException 
 */
private void initialize(
    String dbDriver, 
    String dbURL, 
    String dbUser,
    String dbPassword, 
    int maxConnections, 
    int maxStatementsPerConnection, 
    String dbValidationQuery,
    boolean validateOnCheckout,
    int idleValidationSeconds,
    int maxIdleSeconds) throws SQLException, SchedulerException {
    if (dbURL == null) {
        throw new SQLException(
            "DBPool could not be created: DB URL cannot be null");
    }
    
    if (dbDriver == null) {
        throw new SQLException(
            "DBPool '" + dbURL + "' could not be created: " +
            "DB driver class name cannot be null!");
    }
    
    if (maxConnections < 0) {
        throw new SQLException(
            "DBPool '" + dbURL + "' could not be created: " + 
            "Max connections must be greater than zero!");
    }

    
    datasource = new ComboPooledDataSource(); 
    try {
        datasource.setDriverClass(dbDriver);
    } catch (PropertyVetoException e) {
        throw new SchedulerException("Problem setting driver class name on datasource: " + e.getMessage(), e);
    }  
    datasource.setJdbcUrl(dbURL); 
    datasource.setUser(dbUser); 
    datasource.setPassword(dbPassword);
    datasource.setMaxPoolSize(maxConnections);
    datasource.setMinPoolSize(1);
    datasource.setMaxIdleTime(maxIdleSeconds);
    datasource.setMaxStatementsPerConnection(maxStatementsPerConnection);
    
    if (dbValidationQuery != null) {
        datasource.setPreferredTestQuery(dbValidationQuery);
        if(!validateOnCheckout)
            datasource.setTestConnectionOnCheckin(true);
        else
            datasource.setTestConnectionOnCheckout(true);
        datasource.setIdleConnectionTestPeriod(idleValidationSeconds);
    }
}
 

@SuppressWarnings("unchecked")
private static void setupDataSource(Isolation isolation) {
  ds = new ComboPooledDataSource();
  Endpoint locatorEndPoint = (Endpoint) (NetworkServerHelper.getNetworkLocatorEndpoints()).get(0);
  String hostname = getHostNameFromEndpoint(locatorEndPoint);
  int port = getPortFromEndpoint(locatorEndPoint); 
  
  connProp.putAll(getExtraConnProp()); //singlehop conn properties and any additional prop
  
  try {
    ds.setProperties(connProp); 
    ds.setDriverClass(driver);
    ds.setJdbcUrl(protocol + hostname+ ":" + port);
    
    ds.setMinPoolSize(5);
    ds.setAcquireIncrement(5);
    ds.setMaxPoolSize(numOfWorkers + 100);
    ds.setMaxStatementsPerConnection(10);
    
    if (isolation == Isolation.NONE) {
      ds.setConnectionCustomizerClassName( "sql.sqlutil.MyConnectionCustomizer");
    } else if (isolation == Isolation.READ_COMMITTED) {
      ds.setConnectionCustomizerClassName("sql.sqlutil.IsolationRCConnectionCustomizer");
    } else {
      ds.setConnectionCustomizerClassName("sql.sqlutil.IsolationRRConnectionCustomizer");
    }
    
    Log.getLogWriter().info("Pooled data source url is set as " + ds.getJdbcUrl());
    
    StringBuilder sb = new StringBuilder();
    
    for (Iterator iter = connProp.entrySet().iterator(); iter.hasNext(); ) {
      Map.Entry<String, String> entry = (Map.Entry<String, String>) iter.next();
      
      sb.append(entry.getKey() + " is set to " + entry.getValue() +"\n");
    }
    
    Log.getLogWriter().info("Pooled data source setting the following connection prop: " + sb.toString());
    
  } catch (Exception e) {
    throw new TestException("could not set data source" + TestHelper.getStackTrace(e));
  }
}
 

@SuppressWarnings("unchecked")
private static void setupDataSource(Isolation isolation) {
  ds = new ComboPooledDataSource();
  Endpoint locatorEndPoint = (Endpoint) (NetworkServerHelper.getNetworkLocatorEndpoints()).get(0);
  String hostname = getHostNameFromEndpoint(locatorEndPoint);
  int port = getPortFromEndpoint(locatorEndPoint); 
  
  connProp.putAll(getExtraConnProp()); //singlehop conn properties and any additional prop
  
  try {
    ds.setProperties(connProp); 
    ds.setDriverClass(driver);
    ds.setJdbcUrl(protocol + hostname+ ":" + port);
    
    ds.setMinPoolSize(5);
    ds.setAcquireIncrement(5);
    ds.setMaxPoolSize(numOfWorkers + 100);
    ds.setMaxStatementsPerConnection(10);
    
    if (isolation == Isolation.NONE) {
      ds.setConnectionCustomizerClassName( "sql.sqlutil.MyConnectionCustomizer");
    } else if (isolation == Isolation.READ_COMMITTED) {
      ds.setConnectionCustomizerClassName("sql.sqlutil.IsolationRCConnectionCustomizer");
    } else {
      ds.setConnectionCustomizerClassName("sql.sqlutil.IsolationRRConnectionCustomizer");
    }
    
    Log.getLogWriter().info("Pooled data source url is set as " + ds.getJdbcUrl());
    
    StringBuilder sb = new StringBuilder();
    
    for (Iterator iter = connProp.entrySet().iterator(); iter.hasNext(); ) {
      Map.Entry<String, String> entry = (Map.Entry<String, String>) iter.next();
      
      sb.append(entry.getKey() + " is set to " + entry.getValue() +"\n");
    }
    
    Log.getLogWriter().info("Pooled data source setting the following connection prop: " + sb.toString());
    
  } catch (Exception e) {
    throw new TestException("could not set data source" + TestHelper.getStackTrace(e));
  }
}
 

@Override
public DataSource getDataSource(JsonObject config) throws SQLException {
  String url = config.getString("url");
  if (url == null) throw new NullPointerException("url cannot be null");
  String driverClass = config.getString("driver_class");
  String user = config.getString("user");
  String password = config.getString("password");
  Integer maxPoolSize = config.getInteger("max_pool_size");
  Integer initialPoolSize = config.getInteger("initial_pool_size");
  Integer minPoolSize = config.getInteger("min_pool_size");
  Integer maxStatements = config.getInteger("max_statements");
  Integer maxStatementsPerConnection = config.getInteger("max_statements_per_connection");
  Integer maxIdleTime = config.getInteger("max_idle_time");
  Integer acquireRetryAttempts = config.getInteger("acquire_retry_attempts");
  Integer acquireRetryDelay = config.getInteger("acquire_retry_delay");
  Boolean breakAfterAcquireFailure = config.getBoolean("break_after_acquire_failure");

  // If you want to configure any other C3P0 properties you can add a file c3p0.properties to the classpath
  ComboPooledDataSource cpds = new ComboPooledDataSource();
  cpds.setJdbcUrl(url);
  if (driverClass != null) {
    try {
      cpds.setDriverClass(driverClass);
    } catch (PropertyVetoException e) {
      throw new IllegalArgumentException(e);
    }
  }
  if (user != null) {
    cpds.setUser(user);
  }
  if (password != null) {
    cpds.setPassword(password);
  }
  if (maxPoolSize != null) {
    cpds.setMaxPoolSize(maxPoolSize);
  }
  if (minPoolSize != null) {
    cpds.setMinPoolSize(minPoolSize);
  }
  if (initialPoolSize != null) {
    cpds.setInitialPoolSize(initialPoolSize);
  }
  if (maxStatements != null) {
    cpds.setMaxStatements(maxStatements);
  }
  if (maxStatementsPerConnection != null) {
    cpds.setMaxStatementsPerConnection(maxStatementsPerConnection);
  }
  if (maxIdleTime != null) {
    cpds.setMaxIdleTime(maxIdleTime);
  }
  if(acquireRetryAttempts != null){
    cpds.setAcquireRetryAttempts(acquireRetryAttempts);
  }
  if(acquireRetryDelay != null){
    cpds.setAcquireRetryDelay(acquireRetryDelay);
  }
  if(breakAfterAcquireFailure != null){
    cpds.setBreakAfterAcquireFailure(breakAfterAcquireFailure);
  }
  return cpds;
}