Downloaded 41 times
Uploaded byKasun Madusanke
Database Connection Pooling With c3p0
This document discusses connection pooling in Java using c3p0. It defines what a database connection is and explains how connection pooling works to improve performance by maintaining a pool of ready-to-use connections rather than continuously opening and closing connections. The document introduces c3p0 as a connection pooler, describes how to configure and use c3p0, and outlines some of the benefits of connection pooling such as improved performance, easier diagnostics, and increased maintainability.
Database Connection Pooling With c3p0
- 1. hSenid Lanka: ConnectionPooling With c3p0
- 2. hSenid Lanka: c3p0 What is a Connection? What is a Connection Pool? How Connection Pool Works? Connection Life Cycle Connection Poolers 2
- 3. hSenid Lanka: c3p0 3 Introduction to c3p0 Using c3p0 Configuring c3p0 Benefits of Connection Pooling
- 4. hSenid Lanka: c3p0 •In computer science, a database connection is the means by which a database server and its client software communicate with each other. • The term is used whether or not the client and the server are on different machines. • Connections are built by supplying an underlying driver or provider with a connection string. 4
- 5. hSenid Lanka: c3p0 •Connection string is used to address a specific database or server and to provide instance and user authentication credentials. • Connections are a key concept in data-centric programming. • Since some DBMSs require considerable time to connect, connection pooling is used to improve performance. 5
- 6. hSenid Lanka: c3p0 •Database connections are finite and expensive and can take a long time to create relative to the operations performed on them. • It is very inefficient for an application to create and close a database connection whenever it needs to update a database. 6
- 7. hSenid Lanka: c3p0 •The application server maintains a pool of ready to use connections to a data store. o Application client requests a connections using a data source or a connection factory object. o Connection is retrieved from the pool. • Opening a connection only when needed, and closing it as soon as the work is done, rather than holding a connection open for the entire life of the application. 7
- 8. hSenid Lanka: c3p0 •In this manner, a relatively small number of connections can service a large number of requests. • Using too many connections may just cause thrashing rather than get more useful work done. • It is desirable to set some limit on the number of connections in the pool. • In case all the connections are in use, the operation can block until a connection is returned to the pool, or an error may be returned. 8
- 9.
- 10. hSenid Lanka: c3p0 •A managed connection object is always in one of three states. DoesNotExist, InFreePool or InUse • After a connection is created, it can be in either, o InUse (if allocated to an application) o InFreePool state 10
- 11. hSenid Lanka: c3p0 •Between these three states are transitions. Examaple: If the application has called the data source or connection factory getConnection() method: FreePoolState -> InUse state • After a connection is closed with the close() method, it is either, o Returned to the free pool o Destroyed 11
- 12. hSenid Lanka: c3p0 •Here are some of the connection poolers that we can use to create a pool of connections, o Apache DBCP - Java Database Connection Pooling library o BoneCP - The JDBC Connection Pool o MongoPool - Manages MongoDB Connections o SQL Relay - Database Connection Pool library with API available in all programming languages o c3p0 - JDBC3 Connection and Statement Pooling 12
- 13. hSenid Lanka: c3p0 •c3p0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers, including DataSources that implement Connection and Statement Pooling. • In particular, c3p0 provides several useful services: • A class which adapt traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections. • Transparent pooling of Connection and PreparedStatements behind DataSources. 13
- 14. hSenid Lanka: c3p0 14 •There are three ways to create a c3p0 connection pool. i. Using a ComboPooledDataSource. ii. DataSources factory class. iii.Build our own pool-backed DataSource by directly instantiating PoolBackedDataSource and setting its ConectionPoolDataSource.
- 15. hSenid Lanka: c3p0 15 Mostuser prefer using this method than the other methods.
- 16. hSenid Lanka: c3p0 16 •We can use the static factory class com.mchange.v2.c3p0.DataSources to build unpooled DataSources from traditional JDBC drivers, and to build pooled DataSources from unpooled DataSources.
- 17. hSenid Lanka: c3p0 17 Wecan programmatically set the configuration parameters and override the default parameters using map like in the code above.
- 18. hSenid Lanka: c3p0 18 •Here are some of the methods that we can use to query/get the information of the current status of the data sources. public int getNumConnectionsDefaultUser() public int getNumIdleConnectionsDefaultUser() public int getNumBusyConnectionsDefaultUser() public int getNumConnections(String username, String password) public int getNumConnectionsAllUsers() c3p0 maintains separate pools for Connections with distinct authentications.
- 19. hSenid Lanka: c3p0 19 oUsing destroy method
- 20. hSenid Lanka: c3p0 20 oUsing close method Unreferenced instances of PooledDataSource that are not closed by clients close() themselves prior to garbage collection in their finalize() methods.
- 21. hSenid Lanka: c3p0 21 •There are several ways to modify c3p0 properties: By changing the property values associated with a particular DataSource in your code, or you can configure c3p0 externally, i. via a simple Java properties file ii. via HOCON (typesafe-config) files (if and only if you bundle the typesafe-config library with your application) iii.via an XML configuration file iv. via System properties
- 22. hSenid Lanka: c3p0 22 •DataSources must be configured before they are used (during or after the construction), c3p0 does support property modifications midstream. However if you obtain a DataSource by instantiating a ComboPooledDataSource, configure it by simply calling appropriate setter methods offered by that class before attempting a call to getConnection().
- 23. hSenid Lanka: c3p0 23 If you obtain a DataSource by using factory methods of the utility class com.mchange.v2.c3p0.DataSources, and wish to use a non-default configuration, you can supply a Map of property names.
- 24. hSenid Lanka: c3p0 24 •To override the library's built-in defaults, create a file called c3p0.properties and place it at the "root" of your classpath or classloader. • The file must be available as a classloader resource under the name /c3p0.properties, in the classloader that loaded c3p0's jar file. • The format of c3p0.properties should be a normal Java Properties file format, whose keys are c3p0 configurable properties.An example c3p0.properties file is given in the next slide.
- 25.
- 26. hSenid Lanka: c3p0 26 •HOCON (Human-Optimized Config Object Notation) is a format for human-readable data, and a superset of JSON. • You must specify ordinary config params explicitly inside a c3p0 scope one way or another, even in a c3p0.conf file. "Dot notation" can be used equivalently to scopes.
- 27.
- 28. hSenid Lanka: c3p0 28 •You can use the XML config file for all c3p0 configuration, including configuration of defaults, named configurations, per- user overrides, and configuration extensions. • By default, c3p0 will look for an XML configuration file in its classloader's resource path under the name "/c3p0-config.xml".
- 29.
- 30.
- 31. hSenid Lanka: c3p0 31 •c3p0 properties can also be defined as System properties, using the same "c3p0." prefix for properties specified in a c3p0.properties file.
- 32. hSenid Lanka: c3p0 32 •Under some circumstances, statement pooling can dramatically improve application performance. • Whether and how much statement pooling will help depends on how much parsing, planning, and optimizing of queries your databases does when the statements are prepared.
- 33. hSenid Lanka: c3p0 33 •We can configure statement pooling in c3p0 via the following configuration parameters: • maxStatements-Defines the total number PreparedStatements a DataSource will cache. • maxStatementsPerConnection-Defines how many statements each pooled Connection is allowed to own. • statementCacheNumDeferredCloseThreads-If greater than zero, the Statement pool will defer physically closing cached Statements until its parent Connection is not in use by any client or internally by the pool itself.
- 34. hSenid Lanka: c3p0 34 •c3p0 DataSources are designed (and configured by default) to recover from temporary database outages, such as those which occur during a database restart or brief loss of network connectivity. • You can affect how c3p0 handles errors in acquiring Connections via the configurable properties described in the next slide.
- 35. hSenid Lanka: c3p0 35 •acquireRetryAttempts-How much attempts to get a connection, default is infinite. • acquireRetryDelay-Delay between retry attempts. • breakAfterAcquireFailure-If 'true', DataSource will consider itself broken after a failed round of Connection attempts, and future client requests will fail immediately.
- 36. hSenid Lanka: c3p0 36 •Performance-The connection cost is paid for once and amortized across all the consuming components. • Diagnostics-If you have one sub-system responsible for connecting to the database, it becomes easier to diagnose and analyze database connection usage. • Maintainability-Again, if you have one sub-system responsible for handing out database connections, your code will be easier to maintain than if each component connected to the database itself.
- 37. hSenid Lanka: ConnectionPooling With c3p0