database applications, in many software systems are often used, is indispensable for the development of supporting large-scale systems. But if not well managed database resources (such as: failure to make timely recovery of the database cursor (ResultSet), Statement, Connection (Connection), and other resources), often a direct result of the system's stability. Such uncertainties, not only by the database or the system itself is one cause, only the official use, with the flow, the increase in users, only gradually revealed.
Java-based development system, JDBC is a database programmer and the main way to deal with, providing a complete database How interfaces. However, considering the applicability of norms, JDBC provides only the most direct database operations specifications, database resource management, such as: the management of the physical connection and buffering, expect third-party application servers (Application Server) provider.
article to JDBC specification, based on related database connection pooling mechanism, and if a simple way to achieve effective management of database resources related enabling technologies.
2. Connection pooling technical background
JDBC is a standard, follow the JDBC Interface Specification, all database manufacturers achieve their own driver (Driver), as shown below:
applied to obtain the database connection, you need to specify a URL, the type of Driver, access to a specific connection, a fixed interface to operate in accordance with different types of databases, such as: are used to obtain Statement, the implementation of SQL access to ResultSet and so on, such as the following example:
import java.sql .*;
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
Connection dbConn = DriverManager.getConnection ("jdbc: oracle: thin: @ 127.0.0.1:1521: oracle", "username", "password");
Statement st = dbConn.createStatement ();
ResultSet rs = st.executeQuery ("select * from demo_table");
... some data source operation in here
After completion of data manipulation, also have to close all related to the database resources. Although this logic of the application has no effect, but the key operation. The above is a simple example, if the mix of many of the if-else, exception, the management of resources inevitably overlooked one important area. Like C, the memory leak problem, Java systems will also face the collapse of bad luck. Therefore, database resource management depends on the application system itself, is insecure and unstable of a risk.
2.2 JDBC connection pool on applications in the standard JDBC interface, and does not provide the resources management. Therefore, the default resource management by the application themselves. Although the JDBC specification, reference resources, many closed / recovery and other reasonable way. But the most conservative manner, or for application to provide an effective management tool. Therefore, JDBC for third-party application server (Application Server) provides a database of manufacturers to achieve the management standard interface: connection buffer (connection pooling). The introduction of the connection pool (Connection Pool) concept, also is a mechanism for management of the database buffer pool resources.
JDBC resources most commonly used categories:
- Connection: Database connection.
- Statement: Session statement.
- ResultSet: the result set cursor.
each of the following relationship:
This is a "God of - the parent - child" relationship, on the Connection management is the management of database resources. For example: If you want to make sure a database connection (Connection) is out, you need to determine (all) Statement whether the child out, also, need to determine whether all relevant ResultSet overtime; in close Connection, you need Close all the relevant Statement and ResultSet.
Therefore, the connection pool (Connection Pool) played the role, not only simple to manage Connection, also involves Statement and ResultSet.
2.3 connection pool (ConnectionPool) and resource management ConnectionPool mechanism to buffer pool, in a certain amount within the ceiling, control and management Connection, Statement and ResultSet. Any database resources are limited, if depleted, you can not get more data services.
In most cases, resource depletion not due to the application of the normal load is too high, but the procedure causes.
In practice, data resources are often the bottleneck of resources, different applications will access the same data source. Which an application runs out of the database of resources, mean that other applications can not function properly. Therefore, ConnectionPool first task is to limit: Each application or system can have the greatest resource. Connection pool is to determine the size of (PoolSize).
ConnectionPool the second task: In the connection pool size (PoolSize) range, the maximum use of resources, shorten the life cycle of database access. Many database connection (Connection) is not the smallest unit of resources, control of Statement of resources is more important than the Connection. With Oracle as an example:
a connection for each application (Connection) in the physical network (such as TCP / IP network) to establish a connection for communication in this connection can also apply for a certain number of Statement. Can provide the same connection number can reach hundreds of active Statement. Saving network resources, shorten the cycle for each session (to establish the physical connection is time-consuming operation). But in general applications, most examples of operations in accordance with 2.1, so there are 10 program called, will produce 10 times the physical connection, each occupying a separate physical connection Statement, which is a great waste of resources. ConnectionPool can solve this problem, so that dozens, hundreds Statement only occupy the same physical connection, to play the advantages of the original database.
ConnectionPool resources through effective management of the total number of applications available to reach the Statement:
(concurrent physical connections) × (Statement available for each connection number)
such a database can also establish physical connections to 200, each connection can provide 250 Statement, then ConnectionPool final Statement for the applications of concurrent total: 200 × 250 = 50,000 months. This is a complicated figure, there is little the system will break this order of magnitude. So in the beginning of this section, that the depletion of resources and applications directly to management.
optimal management of resources, largely dependent on whether the database has its own JDBC Driver. Some databases do not support JDBC Driver Connection and Statement logical connection between the features, such as SQLServer, we can only wait for an updated version of her own.
application of resources, release, collection, sharing and synchronization, the management is complex and sophisticated. Therefore, ConnectionPool another feature is that packaging these operations, the application provides a simple, even without changing the application-style call interface.
3. Simple JDBC connection pool implementation principle mechanism in accordance with Chapter II, Snap-ConnectionPool (a simple tool for quick connection pool can www.snapbug.net download) in accordance with the part of the JDBC specification, achieved connection pool of available resources for effective management of the database.
3.1 system described in the JDBC specification applied through the driver interface (Driver Interface) direct method of database resources. To be effective, rational management of resources between the application and the JDBC Driver to increase the connection pool: Snap-ConnectionPool. Through object-oriented mechanism for the connection pool most of the operation is transparent. See the following figure, Snap-ConnectionPool the system:
shown in the figure, some of the resources by implementing JDBC object interfaces (Connection, Statement, ResultSet), respectively, in the Snap-ConnectionPool produce three kinds of internal logic of resource objects: PooledConnection, PooledStatement and PooledResultSet. They are also the main connection pool management operations object, and inherits the appropriate JDBC affiliation. This system has the following characteristics:
- transparency. Application without changing the original premise of using the JDBC driver interface, to provide resource management services. Applications, like the original JDBC, use the connection pool resources to provide the logical object. Simplify the application of the connection pool reconstruction.
- Resource Package. Complex resource management is encapsulated within the Snap-ConnectionPool without too much interference applications. Management of operational reliability, safety guarantees from the connection pool. Application of interference (such as: Active closed resources), only play a role in optimizing system performance, missing operation will not be adversely affected.
- rational use of resources. JDBC resources in accordance with the affiliation, Snap-ConnectionPool Connection buffered not only on the Statement also have a corresponding mechanism. Has been described in 2.3, Connection, and Statement of rational use of the relationship between the use of resources available to a greater extent. Therefore, Snap-ConnectionPool encapsulates Connection resources, through internal management PooledConnection, for the application of the Statement to provide more resources.
- Resource Chain Management. Snap-ConnectionPool contains three logical object, the object inherits the corresponding JDBC affiliation between. Internal management, are also carried out in accordance with subordination chain management. For example: A Connection to judge whether the time-out, need to contain the Statement is active; judge Statement also the level of activity under the ResultSet.
3.2 connection pool centrally managed ConnectionManager
ConnectionPool is the Snap-ConnectionPool the connection pool object. In the Snap-ConnectionPool inside, you can specify a number of different connection pool (ConnectionPool) for the application services. ConnectionManager manages all of the connection pool, each connection pool with a different name difference. Through the configuration file to adapt to different database types. As shown below:
By ConnectionManager, you can also manage a number of different connection pool, to provide through a management interface. In the application system through the ConnectionManager and related configuration files, you can mess scattered in their application database configuration information (including: database name, user, password and other information) in one file. To facilitate system maintenance.
3.3 connection pool using the example of
JDBC 2.1 standard on the use of examples to use connection pooling, the results are as follows:
import java.sql .*;
import net.snapbug.util.dbtool .*;
.. ConnectionPool dbConn = ConnectionManager
. GetConnectionPool ("testOracle");
Statement st = dbConn.createStatement ();
ResultSet rs = st.executeQuery (
"Select * from demo_table");
some data source operation
in herers.close (); st.close ();
In the example, Snap-ConnectionPool package the application of the Connection Management. JDBC Connection for as long as the change in the method for obtaining the connection pool (ConnectionPool) (bold part), others do not do data operations can be modified. By this way, Snap-ConnectionPool can help applications to effectively manage database resources. If the application ignores the release of the final resource: rs.close () and st.close (), connection pool will be passed out (time-out) mechanism, automatic recovery.
Both Snap-ConnectionPool or other database connection pool, what should have basic functions:
- The protection of the source database resources
- Full use of resources to play an effective database
- Simplify the application of database interface, closed resource management.
- On the application of legacy automatic recovery and consolidation of resources and improve resource utilization again.
On this premise, the application can put more energy in their own business logic. Database resources is no longer a bottleneck in the system.