private void init(AccessConfiguration configuration) throws DAOException {
if (cpds != null && isCompatible(currentConfiguration, configuration)) {
return;
}
try {
if (cpds != null) {
closeFactory();
}
cpds = new ComboPooledDataSource();
cpds.setDriverClass(configuration.getDriver());
cpds.setJdbcUrl(configuration.getUri());
cpds.setUser(configuration.getLogin());
cpds.setPassword(configuration.getPassword());
// Pool configuration
// cpds.setMaxStatements(180); //If you want to turn on PreparedStatement pooling,
// you must also set maxStatements and/or maxStatementsPerConnection
// cpds.setMaxStatementsPerConnection(180); //If you want to turn on
// PreparedStatement pooling, you must also set maxStatements and/or
// maxStatementsPerConnection
// cpds.setMinPoolSize(8);
//// cpds.setAcquireIncrement(5);
// cpds.setMaxPoolSize(20);
// cpds.setNumHelperThreads(8);
if (logger.isDebugEnabled()) logger.debug("Pool initialized: " + cpds.toString());
currentConfiguration = configuration;
} catch (Exception e) {
logger.error(" Wrong parameter in driver configuration: " + e);
logger.error("Requested driver: " + configuration.getDriver());
throw new DAOException(e.getMessage());
}
}
@Bean(name = "mainDataSource")
public DataSource dataSource() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
try {
dataSource.setDriverClass(env.getProperty("jdbc.driverClassName"));
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
logger.info("PropertyVetoException : {}", e.getMessage());
}
dataSource.setJdbcUrl(env.getProperty("jdbc.url"));
dataSource.setUser(env.getProperty("jdbc.username"));
dataSource.setPassword(env.getProperty("jdbc.password"));
dataSource.setAcquireIncrement(20);
dataSource.setAcquireRetryAttempts(30);
dataSource.setAcquireRetryDelay(1000);
dataSource.setAutoCommitOnClose(false);
dataSource.setDebugUnreturnedConnectionStackTraces(true);
dataSource.setIdleConnectionTestPeriod(100);
dataSource.setInitialPoolSize(10);
dataSource.setMaxConnectionAge(1000);
dataSource.setMaxIdleTime(200);
dataSource.setMaxIdleTimeExcessConnections(3600);
dataSource.setMaxPoolSize(10);
dataSource.setMinPoolSize(2);
dataSource.setPreferredTestQuery("select 1");
dataSource.setTestConnectionOnCheckin(false);
dataSource.setUnreturnedConnectionTimeout(1000);
return dataSource;
}
private DataSource buildDefaultDataSource() throws PropertyVetoException {
ComboPooledDataSource rst = new ComboPooledDataSource();
rst.setDriverClass("com.mysql.jdbc.Driver");
rst.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
rst.setUser("root");
rst.setPassword("root");
rst.setMinPoolSize(10);
rst.setMaxPoolSize(100);
return rst;
}
protected void changeToOracle() {
ComboPooledDataSource dataSource = (ComboPooledDataSource) jdbcTemplate.getDataSource();
try {
dataSource.setDriverClass("oracle.jdbc.driver.OracleDriver");
dataSource.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:XE");
dataSource.setUser("javashop");
dataSource.setPassword("752513");
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@PostConstruct
public void setupConfiguration() {
List<AppConfig> appConfigs = appConfiguration.getAppConfigs();
for (AppConfig appConfig : appConfigs) {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setJdbcUrl(appConfig.getDbUrl());
cpds.setUser(appConfig.getDbUser());
cpds.setPassword(appConfig.getDbPassword());
cpds.setAutoCommitOnClose(true);
datasources.put(appConfig.getAppName(), cpds);
}
}
public static ComboPooledDataSource getC3p0() {
ComboPooledDataSource cpds = new ComboPooledDataSource();
// cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl(urlHC);
cpds.setUser(userHC);
cpds.setPassword(passwordHC);
cpds.setMinPoolSize(3);
System.out.println("Returning ComboPooledDataSource \n");
return cpds;
}
/**
* Create DataSource
*
* @param connection connection
* @return data dource
*/
@Override
public DataSource getDataSource(CConnection connection) {
if (m_ds != null) return m_ds;
try {
System.setProperty("com.mchange.v2.log.MLog", "com.mchange.v2.log.FallbackMLog");
// System.setProperty("com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL", "ALL");
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDataSourceName("AdempiereDS");
cpds.setDriverClass(DRIVER);
// loads the jdbc driver
cpds.setJdbcUrl(getConnectionURL(connection));
cpds.setUser(connection.getDbUid());
cpds.setPassword(connection.getDbPwd());
cpds.setPreferredTestQuery(DEFAULT_CONN_TEST_SQL);
cpds.setIdleConnectionTestPeriod(1200);
cpds.setAcquireRetryAttempts(2);
// cpds.setTestConnectionOnCheckin(true);
// cpds.setTestConnectionOnCheckout(true);
// cpds.setCheckoutTimeout(60);
if (Ini.isClient()) {
cpds.setInitialPoolSize(1);
cpds.setMinPoolSize(1);
cpds.setMaxPoolSize(15);
cpds.setMaxIdleTimeExcessConnections(1200);
cpds.setMaxIdleTime(900);
m_maxbusyconnections = 10;
} else {
cpds.setInitialPoolSize(10);
cpds.setMinPoolSize(5);
cpds.setMaxPoolSize(150);
cpds.setMaxIdleTimeExcessConnections(1200);
cpds.setMaxIdleTime(1200);
m_maxbusyconnections = 120;
}
// the following sometimes kill active connection!
// cpds.setUnreturnedConnectionTimeout(1200);
// cpds.setDebugUnreturnedConnectionStackTraces(true);
m_ds = cpds;
} catch (Exception ex) {
m_ds = null;
// log might cause infinite loop since it will try to acquire database connection again
// log.log(Level.SEVERE, "Could not initialise C3P0 Datasource", ex);
System.err.println("Could not initialise C3P0 Datasource: " + ex.getLocalizedMessage());
}
return m_ds;
} // getDataSource
private static synchronized void initConnectionPool() {
loadDbPropertiesFile();
if (pool != null) {
ComboPooledDataSource tmp = pool;
pool = null;
tmp.resetPoolManager(true);
tmp.hardReset();
tmp.close();
}
pool = new ComboPooledDataSource();
pool.setUser(username);
pool.setPassword(password);
}
public static ComboPooledDataSource dameDataSource() {
if (cpds == null) {
cpds = new ComboPooledDataSource();
try {
cpds.setDriverClass("org.gjt.mm.mysql.Driver");
} catch (PropertyVetoException e) {
e.printStackTrace();
}
cpds.setJdbcUrl("jdbc:mysql://localhost/practica1");
cpds.setUser("UsuarioP1");
cpds.setPassword("pass");
}
return cpds;
}
private static DataSource setupDataSource() throws PropertyVetoException {
comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(DRIVER_NAME);
comboPooledDataSource.setJdbcUrl(URL);
comboPooledDataSource.setUser(UNAME);
comboPooledDataSource.setPassword(PWD);
comboPooledDataSource.setMinPoolSize(5);
comboPooledDataSource.setAcquireIncrement(5);
comboPooledDataSource.setMaxPoolSize(20);
return comboPooledDataSource;
}
private DBConnectionMgmt(DAODef daoDef) throws PropertyVetoException {
this.log = Logger.getLogger(DAO.class);
cds = new ComboPooledDataSource();
cds.setDriverClass(daoDef.getDriver());
cds.setUser(daoDef.getDBUser());
cds.setPassword(daoDef.getDBPassword());
cds.setJdbcUrl(daoDef.getDBURL());
cds.setInitialPoolSize(daoDef.get_initialSize()); // 初始的连接数；
cds.setMinPoolSize(daoDef.get_minPoolSize()); // 池最小连接数
cds.setMaxPoolSize(daoDef.get_maxPoolSize());
cds.setMaxStatements(daoDef.get_maxStatements());
cds.setMaxIdleTime(daoDef.get_maxIdleTime());
cds.setTestConnectionOnCheckout(true);
cds.setMaxStatements(0);
}
public static DataSource getDataSource() {
ComboPooledDataSource ds = null;
try {
// init pool data source
ds = new ComboPooledDataSource();
ds.setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");
ds.setJdbcUrl("jdbc:sqlserver://192.168.50.108:1433;DatabaseName=xeeng;");
ds.setUser("xeeng");
ds.setPassword("gamexeeng");
ds.setMinPoolSize(5);
ds.setMaxPoolSize(30);
ds.setAcquireIncrement(8);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
return ds;
}
/**
* Manages the JDBC connection
*
* @return comboPooledDataSource
*/
@Bean(name = "dataSource")
public DataSource dataSource() {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
try {
comboPooledDataSource.setDriverClass(env.getRequiredProperty(PROPERTY_NAME_DATA_SOURCE));
comboPooledDataSource.setJdbcUrl(env.getRequiredProperty(PROPERTY_NAME_URL));
comboPooledDataSource.setUser(env.getRequiredProperty(PROPERTY_NAME_USERNAME));
comboPooledDataSource.setPassword(env.getRequiredProperty(PROPERTY_NAME_PASSWORD));
} catch (PropertyVetoException e) {
e.printStackTrace();
} catch (IllegalStateException e) {
e.printStackTrace();
}
return comboPooledDataSource;
}
/**
* 设置数据源参数。<br>
* <B>注：</B>该方法必须在使用数据库之前调用,当连接池无法加载驱动时，程序已经没有必要再继续运行了，所以<br>
* ； 这里采用强制退出的方式来关闭程序。
*
* @throws SQLException
* @throws PropertyVetoException
*/
public ConnectionPool() {
dataSource = new ComboPooledDataSource();
try {
dataSource.setDriverClass("com.mysql.jdbc.Driver");
} catch (PropertyVetoException e) {
System.exit(0);
}
dataSource.setJdbcUrl(
"jdbc:mysql://localhost:3306/bbsdb?autoReconnect=true&characterEncoding=gbk");
dataSource.setUser("root");
dataSource.setPassword("111");
dataSource.setInitialPoolSize(10);
dataSource.setMinPoolSize(5);
dataSource.setMaxStatements(50);
dataSource.setMaxPoolSize(50);
dataSource.setMaxIdleTime(0);
dataSource.setAcquireIncrement(5);
}
@Override
public void start(ConnectionFactoryConfig config) throws CacheLoaderException {
logFileOverride();
pooledDataSource = new ComboPooledDataSource();
pooledDataSource.setProperties(new Properties());
try {
pooledDataSource.setDriverClass(config.getDriverClass()); // loads the jdbc driver
} catch (PropertyVetoException e) {
String message = "Error while instatianting JDBC driver: '" + config.getDriverClass();
log.error(message, e);
throw new CacheLoaderException(message, e);
}
pooledDataSource.setJdbcUrl(config.getConnectionUrl());
pooledDataSource.setUser(config.getUserName());
pooledDataSource.setPassword(config.getPassword());
if (log.isTraceEnabled()) {
log.trace("Started connection factory with config: " + config);
}
}
/**
* 資料庫連結的設定與配置
*
* @throws SQLException
*/
public L1DatabaseFactory() throws SQLException {
try {
// DatabaseFactoryをL2Jから一部を除いて拝借
_source = new ComboPooledDataSource();
_source.setDriverClass(_driver);
_source.setJdbcUrl(_url);
_source.setUser(_user);
_source.setPassword(_password);
/* Test the connection */
_source.getConnection().close();
} catch (SQLException x) {
_log.fine("Database Connection FAILED");
// rethrow the exception
throw x;
} catch (Exception e) {
_log.fine("Database Connection FAILED");
throw new SQLException("could not init DB connection:" + e);
}
}
private ComboPooledDataSource initPoolConnection(String tenant) throws PropertyVetoException {
ComboPooledDataSource cpds = new ComboPooledDataSource(tenant);
cpds.setDriverClass(
(String) props.get(tenant + "." + basePropertiesName + ".jdbc.driver.driverName"));
cpds.setJdbcUrl((String) props.get(tenant + "." + basePropertiesName + ".jdbc.driver.url"));
cpds.setUser((String) props.get(tenant + "." + basePropertiesName + ".jdbc.username"));
cpds.setPassword((String) props.get(tenant + "." + basePropertiesName + ".jdbc.password"));
final Integer minPoolSize =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.min_size");
if (minPoolSize != null) {
cpds.setMinPoolSize(minPoolSize);
}
final Integer maxPoolSize =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.max_size");
if (maxPoolSize != null) {
cpds.setMaxPoolSize(maxPoolSize);
}
final Integer maxIdleTime =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.timeout");
if (maxIdleTime != null) {
cpds.setMaxIdleTime(maxIdleTime);
}
final Integer maxStatements =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.max_statements");
if (maxIdleTime != null) {
cpds.setMaxStatements(maxStatements);
}
final Integer acquireIncrement =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.acquire_increment");
if (acquireIncrement != null) {
cpds.setAcquireIncrement(acquireIncrement);
}
final Integer idleTestPeriod =
(Integer) props.get(tenant + "." + basePropertiesName + ".c3p0.idle_test_period");
if (idleTestPeriod != null) {
cpds.setIdleConnectionTestPeriod(idleTestPeriod);
}
return cpds;
}
@Override
public DataSource createDataSource(
final String url, final String username, final String password) {
try {
final ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass(driverClass);
cpds.setJdbcUrl(url);
cpds.setUser(username);
cpds.setPassword(password);
cpds.setMinPoolSize(0);
cpds.setMaxPoolSize(20);
cpds.setMaxIdleTime(7200);
cpds.setPreferredTestQuery("SELECT 1");
cpds.setIdleConnectionTestPeriod(15);
return cpds;
} catch (PropertyVetoException e) {
throw new IllegalArgumentException(e);
}
}
/** {@inheritDoc} */
public void init() throws ServerInitException {
try {
LOGGER.info("正在初始化数据库连接池...");
Properties prop = loadJdbcProperties();
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass(prop.getProperty(DRIVER_PROP));
dataSource.setJdbcUrl(prop.getProperty(URL_PROP));
dataSource.setUser(prop.getProperty(USERNAME_PROP));
dataSource.setPassword(prop.getProperty(PASSWORD_PROP));
dataSource.setCheckoutTimeout(
StringUtils.parseInt(prop.getProperty(MAXWAIT_PROP), DEFAULT_MAXWAIT_TIME));
dataSource.setMaxPoolSize(30);
dataSource.setInitialPoolSize(10);
testConnection(dataSource);
LOGGER.info("初始化数据库连接池成功！");
} catch (IOException e) {
throw new ServerInitException("初始化数据库连接池失败！", e);
} catch (PropertyVetoException e) {
throw new ServerInitException("初始化数据库连接池失败！", e);
}
}
@Test
public void testComboPooledDataSource() throws Exception {
String url = "jdbc:mysql://10.112.1.110:3306/test_mysql";
String driver = "com.mysql.jdbc.Driver";
String user = "******";
String passwd = "111111";
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass(driver);
cpds.setJdbcUrl(url);
cpds.setUser(user);
cpds.setPassword(passwd);
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(30);
cpds.setMaxIdleTime(60);
Connection con = cpds.getConnection();
System.out.println("Get Connection Success!!! " + con);
}
/** 构造. */
public DataBaseHandle(
String driver, String url, String user, String password, Integer maxPoolSize) {
try {
// 创建连接池
cpds = new ComboPooledDataSource();
// 用户设置
cpds.setDriverClass(driver); // 驱动
cpds.setJdbcUrl(url); // 连接字符串
cpds.setUser(user); // 用户名
cpds.setPassword(password); // 密码
cpds.setMaxPoolSize(maxPoolSize); // 连接池中保留的最大连接数（默认：15）
// 默认设置
cpds.setMinPoolSize(1); // 连接池中保留的最小连接数（默认：0）
cpds.setInitialPoolSize(1); // 初始化时获取几个连接，取值应在minPoolSize与maxPoolSize之间（默认：3）
cpds.setAcquireIncrement(1); // 当连接池中的连接耗尽的时候c3p0一次同时获取的连接数（默认：3）
cpds.setCheckoutTimeout(
1000); // 当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出SQLException，如设为0则无限期等待，单位毫秒（默认：0）
cpds.setMaxIdleTime(25000); // 最大空闲时间，定义多少秒内未使用则连接被丢弃，若为0则永不丢弃（默认：0）
cpds.setIdleConnectionTestPeriod(18000); // 隔多少秒检查所有连接池中的空闲连接，0表示不检查（默认：0）
cpds.setDebugUnreturnedConnectionStackTraces(
true); // 启用之后(true)，对于每个从连接池拿出去的数据库连接，如果一段时间(unreturnedConnectionTimeout)内没有归还，C3P0就会强制关闭这个连接，并将获取连接时的stack trace，以抛出异常的方式显示出来（默认：false）
cpds.setUnreturnedConnectionTimeout(
600); // 用于设置开启debugUnreturnedConnectionStackTraces后的超时时间（单位：秒）
cpds.setTestConnectionOnCheckin(true); // 如果设为true那么在取得连接的同时将校验连接的有效性（默认：false）
cpds.setMaxStatements(
100); // JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量，但由于预缓存的statements属于单个connection而不是整个连接池，所以设置这个参数需要考虑到多方面的因素。如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭（默认：0)
cpds.setAutomaticTestTable(
"T_TEST_C3P0"); // c3p0将建一张名为T_TEST_C3P0的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试使用（默认: null）
// 取连接(测试)
cpds.getConnection().close();
} catch (Throwable e) {
throw new RuntimeException("连接池创建失败", e);
}
}
/**
* Detects changes and reconfigures this dbConfig. Returns true if the database was configured
* (Config info present. An exception is throwns if the config process fails.
*/
protected boolean configure() {
// prefix used before all properties when loafing config. default is 'db'
String propsPrefix;
if (defaultDbConfigName.equals(dbConfigName)) {
propsPrefix = "db";
} else {
propsPrefix = "db_" + dbConfigName;
}
boolean dbConfigured = false;
if (changed(propsPrefix)) {
try {
// We now know that we will either config the db, or fail with exception
dbConfigured = true;
Properties p = Play.configuration;
if (datasource != null) {
destroy();
}
if (p.getProperty(propsPrefix, "").startsWith("java:")) {
Context ctx = new InitialContext();
datasource = (DataSource) ctx.lookup(p.getProperty(propsPrefix));
} else {
// Try the driver
String driver = p.getProperty(propsPrefix + ".driver");
try {
Driver d = (Driver) Class.forName(driver, true, Play.classloader).newInstance();
DriverManager.registerDriver(new ProxyDriver(d));
} catch (Exception e) {
throw new Exception("Driver not found (" + driver + ")");
}
// Try the connection
Connection fake = null;
try {
if (p.getProperty(propsPrefix + ".user") == null) {
fake = DriverManager.getConnection(p.getProperty(propsPrefix + ".url"));
} else {
fake =
DriverManager.getConnection(
p.getProperty(propsPrefix + ".url"),
p.getProperty(propsPrefix + ".user"),
p.getProperty(propsPrefix + ".pass"));
}
} finally {
if (fake != null) {
fake.close();
}
}
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass(p.getProperty(propsPrefix + ".driver"));
ds.setJdbcUrl(p.getProperty(propsPrefix + ".url"));
ds.setUser(p.getProperty(propsPrefix + ".user"));
ds.setPassword(p.getProperty(propsPrefix + ".pass"));
ds.setAcquireRetryAttempts(10);
ds.setCheckoutTimeout(
Integer.parseInt(p.getProperty(propsPrefix + ".pool.timeout", "5000")));
ds.setBreakAfterAcquireFailure(false);
ds.setMaxPoolSize(Integer.parseInt(p.getProperty(propsPrefix + ".pool.maxSize", "30")));
ds.setMinPoolSize(Integer.parseInt(p.getProperty(propsPrefix + ".pool.minSize", "1")));
ds.setMaxIdleTimeExcessConnections(
Integer.parseInt(
p.getProperty(propsPrefix + ".pool.maxIdleTimeExcessConnections", "0")));
ds.setIdleConnectionTestPeriod(10);
ds.setTestConnectionOnCheckin(true);
datasource = ds;
url = ds.getJdbcUrl();
Connection c = null;
try {
c = ds.getConnection();
} finally {
if (c != null) {
c.close();
}
}
Logger.info("Connected to %s", ds.getJdbcUrl());
}
destroyMethod = p.getProperty(propsPrefix + ".destroyMethod", "");
} catch (Exception e) {
datasource = null;
Logger.error(
e,
"Cannot connected to the database" + getConfigInfoString() + " : %s",
e.getMessage());
if (e.getCause() instanceof InterruptedException) {
throw new DatabaseException(
"Cannot connected to the database"
+ getConfigInfoString()
+ ". Check the configuration.",
e);
}
throw new DatabaseException(
"Cannot connected to the database" + getConfigInfoString() + ", " + e.getMessage(), e);
}
}
return dbConfigured;
}
public static void main(String[] argv) {
try {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setJdbcUrl(argv[0]);
cpds.setUser(argv[1]);
cpds.setPassword(argv[2]);
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);
System.err.println("Initial...");
display(cpds);
Thread.sleep(2000);
HashSet hs = new HashSet();
for (int i = 0; i < 20; ++i) {
Connection c = cpds.getConnection();
hs.add(c);
System.err.println("Adding (" + (i + 1) + ") " + c);
display(cpds);
Thread.sleep(1000);
// if (i == 9)
// {
// //System.err.println("hardReset()ing");
// //cpds.hardReset();
// System.err.println("softReset()ing");
// cpds.softReset();
// }
}
int count = 0;
for (Iterator ii = hs.iterator(); ii.hasNext(); ) {
Connection c = ((Connection) ii.next());
System.err.println("Removing " + ++count);
ii.remove();
try {
c.getMetaData().getTables(null, null, "PROBABLYNOT", new String[] {"TABLE"});
} catch (Exception e) {
System.err.println(e);
System.err.println();
continue;
} finally {
c.close();
}
Thread.sleep(2000);
display(cpds);
}
System.err.println(
"Closing data source, \"forcing\" garbage collection, and sleeping for 5 seconds...");
cpds.close();
System.gc();
System.err.println("Main Thread: Sleeping for five seconds!");
Thread.sleep(5000);
// System.gc();
// Thread.sleep(5000);
System.err.println("Bye!");
} catch (Exception e) {
e.printStackTrace();
}
}
public void setPassword(String password) throws NamingException {
combods.setPassword(password);
rebind();
}
