public static Connection connectWithC3p0() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (ClassNotFoundException cnfe) {
System.err.println("Error: " + cnfe.getMessage());
} catch (InstantiationException ie) {
System.err.println("Error: " + ie.getMessage());
} catch (IllegalAccessException iae) {
System.err.println("Error: " + iae.getMessage());
}
ComboPooledDataSource cpds = getC3p0();
try {
if (con != null && !con.isClosed()) {
con.close();
con = null;
}
con = cpds.getConnection();
} catch (SQLException ex) {
ex.printStackTrace();
}
System.out.println("Returning c3p0 Connection " + con);
return con;
}
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());
}
}
static void display(ComboPooledDataSource cpds) throws Exception {
System.err.println("numConnections: " + cpds.getNumConnections());
System.err.println("numBusyConnections: " + cpds.getNumBusyConnections());
System.err.println("numIdleConnections: " + cpds.getNumIdleConnections());
System.err.println(
"numUnclosedOrphanedConnections: " + cpds.getNumUnclosedOrphanedConnections());
System.err.println();
}
@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);
}
}
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();
}
}
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;
}
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;
}
/**
* Get Status
*
* @return status info
*/
@Override
public String getStatus() {
if (m_ds == null) {
return null;
}
StringBuffer sb = new StringBuffer();
try {
sb.append("# Connections: ").append(m_ds.getNumConnections());
sb.append(" , # Busy Connections: ").append(m_ds.getNumBusyConnections());
sb.append(" , # Idle Connections: ").append(m_ds.getNumIdleConnections());
sb.append(" , # Orphaned Connections: ").append(m_ds.getNumUnclosedOrphanedConnections());
} catch (Exception e) {
}
return sb.toString();
} // getStatus
private void logBefore(boolean checkout) {
if (log.isTraceEnabled()) {
String operation = checkout ? "checkout" : "release";
try {
log.trace(
"DataSource before "
+ operation
+ " (NumBusyConnectionsAllUsers) : "
+ pooledDataSource.getNumBusyConnectionsAllUsers()
+ ", (NumConnectionsAllUsers) : "
+ pooledDataSource.getNumConnectionsAllUsers());
} catch (SQLException e) {
log.warn("Unexpected", e);
}
}
}
/** 测试数据库连接池 */
private static void testConnection(ComboPooledDataSource dataSource) throws ServerInitException {
try {
Connection connection = dataSource.getConnection();
connection.close();
} catch (SQLException e) {
throw new ServerInitException("初始化数据库连接池失败！", e);
}
}
/**
* 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;
}
/**
* 获取连接
*
* @return
*/
public final synchronized Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 从连接池中取出链接。
*
* @return
*/
public synchronized Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
System.exit(0);
}
return null;
}
/**
* 设置数据源参数。<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);
}
// 获得连接
public static Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* String Representation
*
* @return info
*/
@Override
public String toString() {
StringBuffer sb = new StringBuffer("DB_Oracle[");
sb.append(m_connectionURL);
try {
StringBuffer logBuffer = new StringBuffer(50);
logBuffer.append("# Connections: ").append(m_ds.getNumConnections());
logBuffer.append(" , # Busy Connections: ").append(m_ds.getNumBusyConnections());
logBuffer.append(" , # Idle Connections: ").append(m_ds.getNumIdleConnections());
logBuffer
.append(" , # Orphaned Connections: ")
.append(m_ds.getNumUnclosedOrphanedConnections());
} catch (Exception e) {
sb.append("=").append(e.getLocalizedMessage());
}
sb.append("]");
return sb.toString();
} // toString
/**
* Returns a new pooled SQL Connection object.
*
* @return
*/
public Connection openConnection() {
if (!initDone) {
init();
}
try {
return pool.getConnection();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
@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);
}
}
@Override
public Connection getAnyConnection() throws SQLException {
ComboPooledDataSource cpds = connProviderMap.get(CurrentTenantResolver.DEFAULT_TENANT_ID);
logger.debug(
"Get Default Connection:::Number of connections (max: busy - idle): {} : {} - {}",
new int[] {
cpds.getMaxPoolSize(),
cpds.getNumBusyConnectionsAllUsers(),
cpds.getNumIdleConnectionsAllUsers()
});
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()) {
logger.warn("Maximum number of connections opened");
}
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()
&& cpds.getNumIdleConnectionsAllUsers() == 0) {
logger.error("Connection pool empty!");
}
return cpds.getConnection();
}
public Connection getConnection(AccessConfiguration configuration) throws DAOException {
init(configuration);
Connection connection = null;
try {
if (logger.isTraceEnabled()) logger.trace("Getting connection from pool ");
if (logger.isTraceEnabled()) logger.trace(" getMaxPoolSize: " + cpds.getMaxPoolSize());
if (logger.isTraceEnabled())
logger.trace(" getNumConnections: " + cpds.getNumConnections());
if (logger.isTraceEnabled())
logger.trace(" getNumBusyConnections: " + cpds.getNumBusyConnections());
connection = cpds.getConnection();
if (logger.isTraceEnabled()) logger.trace("Opened connections: " + cpds.getNumConnections());
} catch (SQLException sqle) {
close(connection);
throw new DAOException(
" getConnection: "
+ sqle
+ "\n\ndriver: "
+ configuration.getDriver()
+ " - uri: "
+ configuration.getUri()
+ " - login: "******" - password: "******"\n");
}
if (connection == null) {
throw new DAOException(
"Connection is NULL !"
+ "\n\ndriver: "
+ configuration.getDriver()
+ " - uri: "
+ configuration.getUri()
+ " - login: "******" - password: "******"\n");
}
return connection;
}
/**
* 資料庫連結的設定與配置
*
* @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);
}
}
@Override
public Connection getConnection() throws CacheLoaderException {
try {
logBefore(true);
Connection connection = pooledDataSource.getConnection();
logAfter(connection, true);
return connection;
} catch (SQLException e) {
throw new CacheLoaderException("Failed obtaining connection from PooledDataSource", e);
}
}
/** 伺服器關閉的時候要關閉與資料庫的連結 */
public void shutdown() {
try {
_source.close();
} catch (Exception e) {
_log.log(Level.INFO, "", e);
}
try {
_source = null;
} catch (Exception e) {
_log.log(Level.INFO, "", e);
}
}
/**
* 取得資料庫連結時的連線
*
* @return Connection 連結對象
* @throws SQLException
*/
public Connection getConnection() {
Connection con = null;
while (con == null) {
try {
con = _source.getConnection();
} catch (SQLException e) {
_log.warning("L1DatabaseFactory: getConnection() failed, trying again " + e);
}
}
return Config.DETECT_DB_RESOURCE_LEAKS ? LeakCheckedConnection.create(con) : con;
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
ComboPooledDataSource cpds = connProviderMap.get(tenantIdentifier);
logger.debug(
"Get {} Connection:::Number of connections (max: busy - idle): {} : {} - {}",
new Object[] {
tenantIdentifier,
cpds.getMaxPoolSize(),
cpds.getNumBusyConnectionsAllUsers(),
cpds.getNumIdleConnectionsAllUsers()
});
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()) {
logger.warn("Maximum number of connections opened");
}
if (cpds.getNumConnectionsAllUsers() == cpds.getMaxPoolSize()
&& cpds.getNumIdleConnectionsAllUsers() == 0) {
logger.error("Connection pool empty!");
}
return cpds.getConnection();
// return cpds.getConnection(tenantIdentifier, (String) props.get(tenantIdentifier));
}
/** Close */
@Override
public void close() {
log.config(toString());
if (m_ds != null) {
try {
m_ds.close();
} catch (Exception e) {
log.log(Level.SEVERE, "Could not close Data Source");
}
}
m_ds = null;
} // close
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;
}
@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);
}
}
@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 final Connection getConnection() {
try {
// log.debug("从连接池中获取连接开始！"+connMgmt.getClass().toString()+"
// 内存块："+connMgmt.toString()+"连接数："+cds.getNumConnections());
Connection conn = cds.getConnection();
// log.debug("从连接池中获取到了连接！"+connMgmt.getClass().toString()+"
// 内存块："+connMgmt.toString()+"连接数："+cds.getNumConnections());
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
