In the previous post we saw how Spring integrated with DBCP. There is another data-source c3p0 which can also be integrated via Spring.
The XML configuration for the data-source would be
The XML configuration for the data-source would be
<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <!-- Connection properties --> <property name="driverClass" value="org.gjt.mm.mysql.Driver" /> <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/information_schema" /> <property name="user" value="root" /> <property name="password" value="root" /> <!-- Pool properties --> <!-- Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted default - 3 --> <property name="acquireIncrement" value="3" /> <!-- Default: 30 Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely. --> <property name="acquireRetryAttempts" value="3" /> <property name="acquireRetryDelay" value="4500" /> <!-- milliseconds. time c3p0 will wait between acquire attempts --> <!-- autoCommitOnClose Default: false --> <property name="autoCommitOnClose" value="false" /> <!-- Unreasonable values of initialPoolSize will be ignored, and minPoolSize will be used instead. minPoolSize <= maxPoolSize. --> <property name="initialPoolSize" value="3" /> <!-- Default: 15 Maximum number of Connections a pool will maintain at any given time. --> <property name="maxPoolSize" value="200" /> <!-- Default: 3 Minimum number of Connections a pool will maintain at any given time. --> <property name="minPoolSize" value="10" /> <!-- Default: 0 Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire --> <property name="maxIdleTime" value="3600" /> <!-- 1 hour --> <!-- Default: 0 Seconds, effectively a time to live. A Connection older than maxConnectionAge will be destroyed and purged from the pool. This differs from maxIdleTime in that it refers to absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds maxConnectionAge. Zero means no maximum absolute age is enforced. --> <property name="maxConnectionAge" value="7200" /> <!-- 2hour --> <!-- maxIdleTimeExcessConnections is about minimizing the number of Connections held by c3p0 pools when the pool is not under load. By default, c3p0 pools grow under load, but only shrink if Connections fail a Connection test or are expired away via the parameters described above. Some users want their pools to quickly release unnecessary Connections after a spike in usage that forces a large pool size. You can achieve this by setting maxIdleTimeExcessConnections to a value much shorter than maxIdleTime, forcing Connections beyond your set minimum size to be released if they sit idle for more than a short period of time. --> <property name="maxIdleTimeExcessConnections" value="600" /> <!-- 10 minutes --> <!-- The point of Connection pooling is to bear the cost of acquiring a Connection only once, and then to reuse the Connection many, many times. Most databases support Connections that remain open for hours at a time. There's no need to churn through all your Connections every few seconds or minutes. Setting maxConnectionAge or maxIdleTime to 1800 (30 minutes) is quite aggressive. For most databases, several hours may be more appropriate. You can ensure the reliability of your Connections by testing them, rather than by tossing them. (see Configuring Connection Testing.) The only one of these parameters that should generally be set to a few minutes or less is maxIdleTimeExcessConnections. --> <!-- <property name="maxStatements" value="50" /> Use only if statement caching is used --> <property name="preferredTestQuery" value="select 1 from DUAL" /> <!-- Most convenient way to speed up Connection testing is to define the parameter automaticTestTable If provided, c3p0 will create an empty table of the specified name, and use queries against that table to test the Connection. If automaticTestTable is provided, c3p0 will generate its own test query, therefore any preferredTestQuery set will be ignored. You should not work with the named table after c3p0 creates it; it should be strictly for c3p0's use in testing your Connection <property name="automaticTestTable" value ="connectionTestTable"/> --> <!-- The most reliable time to test Connections is on check-out. But this is also the most costly choice from a client-performance perspective. Most applications should work quite reliably using a combination of idleConnectionTestPeriod and testConnectionsOnCheckIn. Both the idle test and the check-in test are performed asynchronously, which leads to better performance, both perceived and actual. --> <!-- idleConnectionTestPeriod Default: 0 If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds. --> <!-- Test the idle connections every 3 minutes (180)--> <property name="idleConnectionTestPeriod" value="180" /> <!-- testConnectionOnCheckin Default: false If true, an operation will be performed asynchronously at every connection check-in to verify that the connection is valid. Use in combination with idleConnectionTestPeriod for quite reliable, always asynchronous Connection testing --> <property name="testConnectionOnCheckin" value="true" /> <!-- testConnectionOnCheckout Default: false Use only if necessary. Expensive. If true, an operation will be performed at every connection checkout to verify that the connection is valid. --> <property name="testConnectionOnCheckout" value="false" /> <!-- <property name="loginTimeout" value="30" /> --> <property name="connectionCustomizerClassName" value="com.test.ConnectionPoolLogger"/> </bean>Just like DBCP most of the configurations are configurable. I picked up the above comments from the c3p0 documentation site. Also to monitor the connection behavior, I added a connection pool monitor The class is capable of hooking into life cycle operations of the connection pool providing us details of the connection pool state.
public class ConnectionPoolLogger implements ConnectionCustomizer{ private static final Logger logger = Logger .getLogger(ConnectionPoolLogger.class); private int activeConnections = 0; private int acquiredConnections = 0; public void onAcquire(Connection c, String pdsIdt) { logger.info("onAcquire: Connection acquired from database : " + c + " [" + pdsIdt + "]"); acquiredConnections++; logger.info("onAcquire: Total Open Connections in Pool : " + acquiredConnections); } public void onDestroy(Connection c, String pdsIdt) { logger.info("onDestroy: Connection closed with database : " + c + " [" + pdsIdt + "]"); acquiredConnections--; logger.info("onDestroy: Total Open Connections in Pool : " + acquiredConnections); } public void onCheckOut(Connection c, String pdsIdt) { logger.info("onCheckOut: Connection from pool provide to application : " + c + " [" + pdsIdt + "]"); activeConnections++; logger.info("onCheckOut: Total Active Connections in Pool : " + activeConnections); } public void onCheckIn(Connection c, String pdsIdt) { logger.info("onCheckIn: Connection returned to pool from application : " + c + " [" + pdsIdt + "]"); activeConnections--; logger.info("onCheckIn: Total Active Connections in Pool : " + activeConnections); } }I tested the bean using the below code:
public static void main(String[] args) throws SQLException { final BeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("spring-c3p0.xml")); DataSource dataSource = (DataSource) beanFactory.getBean("c3p0DataSource"); System.out.println("The dataSource is created as " + dataSource); }The output indicates that the data-source was configured successfully.
0 [main] INFO com.mchange.v2.log.MLog - MLog clients using log4j logging.
609 [main] INFO com.mchange.v2.c3p0.C3P0Registry - Initializing c3p0-0.9.1.2
[built 21-May-2007 15:04:56; debug? true; trace: 10]
672 [main] DEBUG com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerM
Bean - MBean: com.mchange.v2.c3p0:type=PooledDataSource[1hge13e8l1o2l0jiffxtg8|
184ec44] registered.
1031 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - incremented p
ending_acquires: 1
1031 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - incremented p
ending_acquires: 2
...
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - incremented p
ending_acquires: 9
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - incremented p
ending_acquires: 10
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - com.mchange.v
2.resourcepool.BasicResourcePool@16dadf9 config: [start -> 3; min -> 10; max ->
200; inc -> 3; num_acq_attempts -> 3; acq_attempt_delay -> 4500; check_idle_reso
urces_delay -> 180000; mox_resource_age -> 7200000; max_idle_time -> 3600000; ex
cess_max_idle_time -> 600000; destroy_unreturned_resc_time -> 0; expiration_enfo
rcement_delay -> 150000; break_on_acquisition_failure -> false; debug_store_chec
kout_exceptions -> false]
1047 [main] INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initi
alizing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncremen
t -> 3, acquireRetryAttempts -> 3, acquireRetryDelay -> 4500, autoCommitOnClose
-> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkou
tTimeout -> 0, connectionCustomizerClassName -> com.test.ConnectionPoolLogger, c
onnectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, da
taSourceName -> 1hge13e8l1o2l0jiffxtg8|184ec44, debugUnreturnedConnectionStackTr
aces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, fact
oryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityTo
ken -> 1hge13e8l1o2l0jiffxtg8|184ec44, idleConnectionTestPeriod -> 180, initialP
oolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/information_schema, maxAdministr
ativeTaskTime -> 0, maxConnectionAge -> 7200, maxIdleTime -> 3600, maxIdleTimeEx
cessConnections -> 600, maxPoolSize -> 200, maxStatements -> 0, maxStatementsPer
Connection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingChe
ckoutDefaultUser -> 0, preferredTestQuery -> select 1 from DUAL, properties -> {
user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> tr
ue, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTra
ditionalReflectiveProxies -> false ]
The dataSource is created as com.mchange.v2.c3p0.ComboPooledDataSource [ acquire
Increment -> 3, acquireRetryAttempts -> 3, acquireRetryDelay -> 4500, autoCommit
OnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false,
checkoutTimeout -> 0, connectionCustomizerClassName -> com.test.ConnectionPoolL
ogger, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTe
ster, dataSourceName -> 1hge13e8l1o2l0jiffxtg8|184ec44, debugUnreturnedConnectio
nStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driv
er, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, id
entityToken -> 1hge13e8l1o2l0jiffxtg8|184ec44, idleConnectionTestPeriod -> 180,
initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/information_schema, maxA
dministrativeTaskTime -> 0, maxConnectionAge -> 7200, maxIdleTime -> 3600, maxId
leTimeExcessConnections -> 600, maxPoolSize -> 200, maxStatements -> 0, maxState
mentsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwa
itingCheckoutDefaultUser -> 0, preferredTestQuery -> select 1 from DUAL, propert
ies -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheck
in -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0,
usesTraditionalReflectiveProxies -> false ]
excelente, muchas gracias
ReplyDeleteexcellent info..
ReplyDeleteFantastic Info. I loved it
ReplyDeleteDude. Thank you!
ReplyDelete