JDBC database connection pool based on efficient management strategy Date: 2002-06-03 Author: Sun Ming Read Count: 18055 in JDBC-based database application development, database connection management is a difficult, since it is decided that the application performance An important factor. Based on the thorough analysis of database connections, this paper proposes and implements an efficient connection management policy that develops high-performance database applications relatively easy. In particular, two difficulties in connection management: transaction and multi-threaded problems have been deeply analyzed, and a solution based on design patterns is given. Introduction In the application development related to the Java language and database, JDBC is usually used to perform and interact with the database, where there is a key concept is Connection, it is a class in Java, representing a channel . By using it, the application of data can be accessed from the database. For a simple database application, because access to the database is not very frequent. At this time, you can simply create a connection when you need to access the database. After you are used, you will close it, do not bring any significant overhead. But for a complex database application, the situation is completely different. Frequent establishment, closing the connection, which will greatly reduce the performance of the system because the use of system performance bottlenecks for connections. The method given herein can effectively solve this problem. A reasonable, effective connection management strategy is proposed in this method, avoiding the randomness of the connection, no rule. The core idea of this strategy is: connection multiplexing. By establishing a database connection pool and a set of connection to use management policies, a database connection can be efficient and secure, avoiding the overhead of database connection frequently establishing, close. In addition, since the original connection in JDBC is packaged, it is convenient for database applications for connection purposes (especially for transaction), it is improving the development efficiency, which is precisely because of this encapsulation layer. Isolate the application itself. The processing logic and specific database access logic make the reuse of the application itself possible. Problems Generate I have participated in the development of a network management system, and inevitable and deal with the database. At the beginning, because the access to the database is not very frequent, the use of database connections is to establish, the shutdown policy is used, which is very compliant with the slogan of XP (EXTREME Programming): "Do The Simplest Thing That Could Possibly Work. Indeed, work very well at the beginning. With the progress of the project, the problem is frequent for the start of the database, the problem is exposed, the original method of simply acquires and closing the database connection will significantly affect the performance of the system, this impact is due to database resource management The process is frequently created and destroyed by the connection objects. At this point, it is necessary to reconstruct the database access method because we really need to improve to improve the performance of the system. The solution can be seen that the root cause of the problem is due to inefficient management of the connection resource. For shared resources, there is a very famous design pattern: resource pool. This mode is in order to solve the problem of frequent distribution of resources and release it. Apply this mode to the field of database connection management, which is to create a database connection pool, provide an efficient connection allocation, use policies, and ultimate goals are efficient and secure multiplexing. 3.1 To establish a connection pool, it is to establish a static connection pool. The so-called static means that the connection in the pool is allocated when the system is initialized, and it is not possible to close.
Many container classes can be easily used to build connect pools such as: Vector, Stack, etc. When the system is initialized, the connection is created according to the configuration and placed in the connection pool. The connection used later is acquired from the connection pool, so it can avoid the overhead of the connection, closing, and the turn off (of course, we have no way to avoid Overhead brought by Java's Garbage Collection. 3.2, allocation, release strategy has this connection pool, below we can provide a set of custom assignments, release policies. When a customer requests a database connection, first look at whether there is a free connection in the connection pool, the idleness here means that there is currently no connection. If there is an idle connection, allocate the connection to the customer and make a corresponding processing, the specific processing policy, which will be detailed in key issues, the main processing strategy is to mark the connection as allocated. If there is no idle connection in the connection pool, in the connection that has been assigned, find a suitable connection to the customer (selection policy is detailed in key issues), and this connection is multiplexed between multiple customers. When the customer releases the database connection, it can be done according to whether the connection is reused, performing different processing. If there is no user, put it into the connecting pool instead of being turned off. It can be seen that this strategy guarantees the valid multiplexing of the database connection. 3.3, how many connections should be placed in the configuration policy database connection pool? How to deal with the connection? At this time a configuration strategy. The general configuration strategy is that at the beginning, according to the specific application requirements, give the number of connections in the initial connection pool and the maximum number of connecting pools can be expanded. This program is implemented in accordance with this strategy. Key Topics This section will detail key details in the above solutions, which are these key strategies to ensure efficient and secure of database connection multiplexing. 4.1, the distribution of the record 3.2, the release strategy is very important for effective reuse connections, and the methods we use are also a very famous design mode: Reference Counting. This mode is very widely used in multiplexing resources, we use this method to release the allocation release of the connection. Each database is connected to retain a reference count, used to record the number of users of the connection. DETAILED DESCRIPTIONS, we use two pole connecting pools, idle pools, and pools. The idle pool is stored in the currently not allocated connected connections. Once a connection is assigned, it will be placed in the pool and increase the reference count. This has a great advantage that we can use the connection efficiently, because once the connection in the idle pool is all allocated, we can pick out a connection from the pool according to the corresponding strategy. Reserved, not casually, take out a connection to multiplex. Policy can be selected as needed, and we use the policy relatively simple: multiplexing the number of references minimal. Object-oriented features of Java makes our flexible selection of different policies (provide an abstract interface shared by different policies, each specific policy implements this interface, so that the processing logic of the policy is logically separated by the policy of the policy). 4.2, transaction processing previously talking about using database connections for normal database access. For transaction processing, the situation is more complicated. Because the transaction itself requires atomic guarantee, at this time, the operation of the database is required to meet the "all-all-nothing" principle, that is, all complete, either nothing. If you simply use the above-described connection multiplexing, there is a problem, because there is no way to control the action of multiple database operation methods belonging to the same transaction, and these database operations are performed on multiple connections, and these connects May be reused by other non-transaction methods.
The Connection itself has a support for transaction, which can be implemented by setting up a CONNECTION's AutoCommit property for false, explicit call commit or rollback method. But to be safe, efficiently, the Connection is reused, and the corresponding transaction support mechanism must be provided. The way we use is: use explicit transaction support methods, each of which is exclusive. This method can greatly reduce the complexity of transaction processing (if the transaction does not exclusively, the atomicity of the transaction is guaranteed, and does not hinder other other and the transaction unrelated operation, basically impossible, unless The Connection class is you developed) and does not hinder the connection multiplexing because all database operations belonging to the transaction are done through this connection, and the transaction method has multiple other database methods. In our connection management service provides an explicit transaction start, end (commit or rollback) declaration, as well as a transaction registry, the corresponding relationship for the connection of the transaction initiator and transaction, through the table, use transaction The part is separated from our connection management section, because the table is dynamically generated according to the actual call situation at runtime. The connection used by the transaction cannot be reused in this transaction. When the user needs to use the transaction method, first call the Begintrans method provided by the connection management service, the main process process is as follows (pseudo code description): public void begintrans () {... conn = getIdleConnectionFromPoll (); userid = getUserid () Registertrans (userid, conn); ...} In our implementation, the user identifier is identified by the thread where the user is located. All of the following access to the database is done by looking for the registry, using the assigned connections. When the transaction ends, remove the corresponding entry from the registry. How to deal with nested transactions? The method we use is still a reference number, but the reference number here refers to "nested hierarchy", specific details, no longer repeat it. 4.3, the encapsulation can be seen from the above discussion, the normal database method and the transaction method are different for the use (allocation, release) of the connection, in order to facilitate use, an uniform operation interface, we have packaged the connection: Ordinary connection and transaction connection. Here, we use powerful object-oriented properties in Java: polymorphism. The normal connection and transaction connections have realized a DBConnection interface, and for the methods defined in the interface, different implementations are made according to their own characteristics, so that they are very consistent on the processing of the connection. 4.4, concurrent problems In order to be our connection management service, there must be a multi-threaded environment, which is a problem. In a multi-threaded environment, we must ensure that connect management self-data consistency and internal data are consistency, and Java provides good support for this (SYNCHRONIZED keyword) so that we are easy. Make connection management to thread security. 5. Conclusion This article gives a basic connection management framework in which some widely used design modes (resource pool, reference) make it possible to efficiently and secure multiplexed database connections.
