For a simple database application, the access to the database is not very frequent. You can simply create a new connection when you need to access the database and close it when you’re done with it without incurring any significant performance overhead. But for a complex database application, it’s a different story. Frequent establishment and closing of connections can greatly reduce the performance of the system, because the use of connections becomes the bottleneck of system performance. Connection multiplexing. By establishing a database connection pool and a set of connection usage management policies, a database connection can be reused efficiently and securely, and the overhead of frequent establishment and closure of database connections can be avoided. There is a well-known design pattern for shared resources: resource pooling. This mode is to solve the problems caused by frequent resource allocation and release. The application of this pattern to the database connection management field is to establish a database connection pool, provide a set of efficient connection allocation, use strategy, and the final goal is to realize the efficient and safe reuse of connection. The basic principle of database connection pool is to maintain a certain number of database connections in the internal object pool and expose the methods of obtaining and returning database connections. For example, the external user can obtain the connection through getConnection and return the connection through releaseConnection when the connection is finished. Note that the connection is not closed but reclaimed by the connection pool manager to prepare for the next use.
Advantages of database connection pool technology: 1. Resource reuse Because database connections are reused, you avoid the large performance overhead associated with frequently creating and releasing connections. On the basis of reducing system consumption, on the other hand, it also improves the stability of the system running environment (reducing memory fragmentation and the number of temporary processes/threads in the database). 2. Faster system response Database connection pool During initialization, several database connections are created and placed in the pool for standby use. The initialization of the connection is complete. For business request processing, direct use of existing available connections avoids the time overhead of database connection initialization and release, thus reducing overall system response time. 3. New resource allocation means for multiple applications sharing the same database system, can be in the application layer through the configuration of database connection, database connection pool technology, a few years may still be a new topic, for the current business system, if the design has not considered the application of connection pool, then…… Add this to your design document. The maximum number of available database connections for an application is limited to prevent an application from monopolizing all database resources. 4. Unified connection management to avoid database connection leakage In a more complete database connection pool implementation, according to the pre-connection occupation timeout setting, forced recall of occupied connections. This avoids resource leaks that may occur during regular database connection operations. A minimal database connection pool implementation:
1. Introduction Database application, often used in many software systems, is the development of large and medium-sized systems indispensable assistance. However, if database resources are not properly managed (for example, database resources such as resultsets, statements, and connections are not recovered in a timely manner), system stability may occur. This kind of unstable factors, not only by the database or the system itself party caused, only after the formal use of the system, with the increase of traffic, users, will gradually reveal. In the system based on Java development, JDBC is the main way for programmers to deal with the database, and provides a complete interface of database operation methods. However, considering the applicability of the specification, JDBC only provides the most direct database operation specification. For database resource management, such as physical connection management and buffering, a third-party Application Server is expected to provide. This article, based on the JDBC specification, introduces the related database connection pool mechanism, and introduces the implementation technology of how to effectively manage database resources in a simple way.
2.1 JDBC JDBC is a specification, which complies with the JDBC interface specification. Each database manufacturer implements its own Driver, as shown in the figure below:
When obtaining a database connection, an application needs to specify the type of Driver in the URL. After obtaining a specific connection, the application can operate different types of databases based on fixed interfaces, for example, obtaining a Statement and executing SQL to obtain a ResultSet. The following is an example:
import java.sql.*; … DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); The 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
rs.close();
st.close();
dbConn.close();
After completing the data operation, be sure to shut down all the database resources involved. This has no effect on the logic of the application, but it is a critical action. The above is a simple example. If there are many if-else and exception, the management of resources will be difficult to avoid. As with memory leaks in C, Java systems can also suffer from crashes. So the management of database resources depends on the application system itself, which is a hidden danger of insecurity and instability.
2.2 JDBC Connection Pool The standard JDBC application interface does not provide resource management methods. Therefore, the default resource management is up to the application itself. Although in the JDBC specification, there are many references to resource closing/recycling and other rational use. But the safest way is to provide effective management tools for the application. Therefore, JDBC provides a standard interface for third-party Application servers implemented by database vendors: Connection pooling. Introduced the concept of Connection Pool, which is a buffer Pool mechanism to manage database resources.
The most commonly used JDBC resources are as follows: – Connection: database Connection. – Statement: indicates the session Statement. – ResultSet: indicates the ResultSet cursor.
The following relationships exist respectively:
Therefore, Connection pools not only manage connections, but also statements and resultSets.
2.3 ConnectionPool and Resource Management A ConnectionPool uses the buffer pool mechanism to manage Connection, Statement, and ResultSet within a certain limit. The resources of any database are limited, and if exhausted, no more data services can be obtained. In most cases, resource depletion is not due to the normal load of the application, but rather to programmatic reasons. In practical work, data resources are often the bottleneck resources, different applications will access the same data source. If one application runs out of database resources, other applications cannot run properly. Therefore, the first task of ConnectionPool is to limit: the maximum resources that each application or system can have. That is, determine the size of the connection pool (PoolSize). The second task of ConnectionPool is to maximize the use of resources within the ConnectionPool size and shorten the lifetime of database access. In many databases, a Connection is not the smallest unit of resources. Controlling Statement resources is more important than Connection. Take Oracle as an example: Each Connection request establishes a Connection on a physical network (such as TCP/IP) for communication. A certain number of statements can be applied for this Connection. The number of active statements available for the same connection can be in the hundreds. This saves network resources and shorts the session cycle (establishing a physical connection is a time-consuming operation). However, in general applications, most of them follow the example in 2.1. In this case, 10 program calls will result in 10 physical connections. Each Statement occupies one physical connection independently, which is a great waste of resources. ConnectionPool solves this problem by allowing dozens or hundreds of statements to use the same physical connection. Through the effective management of resources by ConnectionPool, the total number of statements that an application can obtain reaches:
(Number of concurrent physical connections) x (Number of statements available per connection)
For example, a database can set up 200 physical connections at the same time, and each connection can provide 250 concurrent statements. In this case, the final number of concurrent statements provided by a ConnectionPool for an application is: 200 x 250 = 50,000. This is a concurrent number, and very few systems break this scale. So at the beginning of this section, it is pointed out that resource depletion is directly related to application management. The optimization of resource management largely depends on whether the DATABASE has JDBC Driver. The JDBC Driver of some databases, such as SQLServer, does not support the logical Connection between Connection and Statement. We have to wait for the updated version. The management of resource application, release, recovery, sharing, and synchronization is complex and sophisticated. So, another feature of ConnectionPool is to encapsulate these operations and provide a simple invocation interface for the application, even without changing the application style.
3. Implementation of simple JDBC ConnectionPool based on the principles and mechanisms described in chapter 2, snap-connectionpool (a simple and fast ConnectionPool tool that can be downloaded from the Internet) implements the effective database resource management function provided by ConnectionPool according to some JDBC specifications. 3.1 System Description In the JDBC specification, the database resources are directly applied through the Driver Interface. To effectively and reasonably manage resources, snap-ConnectionPool is added between applications and JDBC drivers. And most of the operation of connection pool is transparent through object-oriented mechanism. See below for the snap-ConnectionPool system:
As shown in the figure, three logical resource objects are generated in snap-ConnectionPool by implementing some resource object interfaces (Connection, Statement, ResultSet) of JDBC: PooledConnection, PooledStatement, and PooledResultSet. They are also the primary administrative action objects for the connection pool and inherit the corresponding affiliation from JDBC. Such a system has the following characteristics: – Transparency. Provides resource management services without changing the original JDBC driver interface. The application system, like the original JDBC, uses logical object resources provided by the connection pool. Simplifies connection pooling for applications. – Resource encapsulation. Complex resource management is encapsulated in snap-ConnectionPool without much interference from the application system. The reliability and security of management operations are ensured by connection pools. Application interference (such as actively shutting down resources) only optimizes system performance, and omission does not have a negative impact. – Use resources properly. Based on JDBC resource affiliation, snap-ConnectionPool buffers not only Connection but also Statement. As described in 2.3, proper use of the relationship between Connection and Statement can maximize the use of resources. Therefore, snap-ConnectionPool encapsulates Connection resources and provides application systems with more Statement resources through internal PooledConnection management. – Resource linkage management. Snap-connectionpool contains three types of logical objects that inherit the affiliation relationships between the corresponding objects in JDBC. In the internal management, the chain management is also carried out according to the subordinate relationship. For example, to determine whether a Connection times out, the Statement contains an active Statement. The Statement is also determined by the activity of the ResultSet.
3.2 centralized management of connection pools ConnectionManager a ConnectionPool is a snap-connectionpool ConnectionPool object. Within the snap-ConnectionPool, multiple different connection pools can be specified to serve the application. ConnectionManager manages all connection pools, each identified by a different name. Use the configuration file to adapt to different database types. As shown in the figure below:
Through ConnectionManager, you can manage multiple different connection pools at the same time, providing a unified management interface. In the application system, ConnectionManager and related configuration files can be used to gather database configuration information (including database name, user, password, etc.) scattered in each application into one file. Easy to maintain the system.
3.3 Connection Pool Usage Example
For the standard JDBC example in 2.1, use connection pooling instead, and 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 “); ResultSet rs = st.executeQuery(” select * from demo_table “); … some data source operation in herers.close(); st.close();
In this example, snap-ConnectionPool encapsulates the application’s management of Connection. As long as you change the way JDBC gets a Connection, you can leave all other data operations unchanged for the ConnectionPool (in bold). In this way, snap-ConnectionPool helps your application effectively manage database resources. If the application ignores the last resource release: rs.close() and st.close(), the connection pool is automatically refunded through a time-out mechanism.
4. Summary
A snap-connectionpool or any other database ConnectionPool should have the following basic features:
– Protects source database resources
– Make full use of the effective resources of the database
– Simplified application database interface, closed resource management.
– Automatically recycle and sort out legacy resources of applications to improve resource reuse.
This allows applications to focus more on their own business logic. Database resources are no longer the bottleneck of the system.
