Research and application of database connection pool technology based on JDBC

Data | database | database connection SummaryThis paper introduces the principle of Java Access database and its existing problems, puts forward the solution-database connection pool, analyzes its key problems, constructs a simple and easy-to-use connection pool and describes how it can be used in development with the current hot technology servlet.

Key WordsJdbc,jsp/servlet, database connection pool, multiple database server and multi-user, multithreading

　　 Introduction

In recent years, with the rapid development of internet/intranet network technology and the rapid popularization in the world, computer

Applications have been transferred from traditional desktop applications to Web applications. The 3-tier development model based on B/s (browser/server) architecture gradually replaces the development mode of C/s (client/server) architecture, and becomes the common technology used in the development of enterprise application and electronic commerce. In the early days of web application development, the main technology used was cgi﹑asp﹑php. After that, Sun introduced Servlet+jsp+javabean technology based on the Java language. Compared with traditional development technology, it has the features of Cross-platform ﹑ security ﹑ effective ﹑ portability, which makes it easier to use and develop.

　　 Fundamentals of Java application access to databases

In the Java language, JDBC (Java database Connection) is a bridge between applications and databases.

That is, the Java language accesses the database through JDBC technology. JDBC is an "open" scenario that provides a standard application design interface for database application developers ﹑ Database foreground tool developers, enabling developers to write complete database applications in a pure Java language. JDBC provides two APIs, the developer-oriented API and the underlying JDBC driver API, which are connected to the database primarily through a direct JDBC Drive and Jdbc-odbc Bridge drive.

In general, the process of accessing a database by a Java application (as shown in Figure 1) is:

① load Database driver;

② establishes a database connection through JDBC;

③ access to the database, execute the SQL statement;

④ Disconnect the database.


Figure 1 Java Database access mechanism

JDBC, as a database access technology, has the advantages of simplicity and ease of use. However, there are many problems with using this pattern for Web application

Program development: First, the database connection is established once for each Web request. Establishing a connection is a time-consuming activity that takes 0.05s~1s time each time and allocates memory resources to the system. This time for one or several database operations, may not feel the system has much overhead. But for today's web apps, especially large e-commerce sites, it's normal to have hundreds of or even thousands of people online. In this case, frequent database connection operation is bound to occupy a lot of system resources, the response speed of the site must drop, serious even will cause the crash of the server. is not alarmist, this is to restrict the development of some E-commerce Web site technology bottlenecks. Second, for each database connection, you have to disconnect after use. Otherwise, if the program fails to shut down with an exception, it will cause memory leaks in the database system, which will eventually have to restart the database. Also, this development does not control the number of connection objects created, and system resources are allocated without consideration, such as too many connections, which can lead to memory leaks and server crashes.

　　How the database connection pool (connection pool) works

1. Basic concepts and principles

As can be seen from the above analysis, the root of the problem lies in the inefficient management of the database connection resources. We know

For shared resources, there is a well-known design pattern: resource pools (Resource pool). The model is to solve the problems caused by the frequent distribution of resources and ﹑ release. In order to solve the above problems, we can use database connection pool technology. The basic idea of a database connection pool is to create a "buffer pool" for database connections. Put a certain number of connections in the buffer pool beforehand, and when you need to establish a database connection, simply remove one from the buffer pool, and then put it back when you have finished using it. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool. More importantly, we can monitor the number of database connections through the connection pool management mechanism, and provide the basis for system development ﹑ testing and performance ﹑. The basic working principle of connection pool is shown in Figure 2 below.


Fig. 2 Basic working principle of connection pool
2, the server from the connection pool with

There is no method in the JDBC API to provide connection pooling. Some large Web application servers, such as Bea's WebLogic and IBM's WebSphere, provide a mechanism for connection pooling, but there must be a Third-party dedicated class method that supports the use of connection pooling.

　　Analysis of key problems of connection pool

1, concurrency problems

In order to make the connection Management Service have the most universality, it is necessary to consider the multithread environment, that is concurrency problem. This is a relatively good solution because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to directly precede the class method with the Synchronized keyword, such as:

Public synchronized Connection getconnection ()

2. Multi-database server and multiuser

For large enterprise applications, it is often necessary to simultaneously connect different databases, such as Oracle and Sybase. How do I connect to a different database? The strategy we adopt is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when a unique instance of the connection pool management class is created, where the URL address (<poolName.url>) of multiple databases is stored in the resource file ﹑ user name (< poolname.user>) ﹑ Password (<poolName.password>) and other information. such as tx.url=172.21.15.123:5000/tx_it,tx.user=yang,tx.password=yang321. Create multiple instances of the connection pool class based on the information provided by the resource file, each of which is a connection pool for a particular database. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools by different names.

With multiple users accessing different names and passwords for the same database, you can also use resource files to set up multiple database connection information with the same URL address, but with different user names and passwords, in the resource file.

3. Transaction processing

We know that a transaction is atomic, requiring that the operation of the database conform to the "all-all-nothing" principle, that is, to a set of SQL statements, either full or none at all.

In the Java language, the connection class itself provides support for transactions by setting the connection Autocommit property to False and then explicitly invoking the commit or Rollback method. However, in order to efficiently perform connection reuse, the corresponding transaction support mechanism must be provided. Each transaction can be implemented exclusively with one connection, which can greatly reduce the complexity of transaction management.

4. Distribution and release of connection pool

The allocation and release of the connection pool have a great effect on the performance of the system. Reasonable allocation and release can increase the reuse of the connection, reduce the cost of establishing new connection, and speed up the user's access.

You can use a free pool for connection management. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Each time a user requests a connection, the system first checks to see if there is an idle connection in the free pool. If there is a connection to the longest established (by the order of the container to be implemented) assigned to him (actually the first to do the connection is valid judgment, if available, assign to the user, if not available, delete the connection from the free pool, and re-detect if there is a connection to the free pool. If not, check whether the current open connection pool reaches the maximum number of connections allowed by the connection pool (maxconn), and if not, create a new connection and wait a certain amount of time (timeout) if it has been reached. If a connection is released within the waiting time, the connection can be assigned to the waiting user, or null if the wait time exceeds the scheduled time timeout. The system counts only the connections that are already being used, and returns them to the free pool when it is used. For the state of the idle connection, a dedicated thread timing detection can be developed, which will cost a certain amount of overhead, but can ensure a faster response speed. It can also take a method that does not open up specialized threads, just before the distribution is detected.

5, the connection pool configuration and maintenance

How many connections should be placed in the connection pool to make the system performance best? The system can take the setting of the minimum number of connections (minconn) and the maximum number of connections (maxconn) to control connections in the connection pool. The minimum number of connections is the number of connections created by the connection pool at system startup. If you create too many, the system starts slowly, but the system responds quickly when it is created, and if you create too little, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections at development time, and it will be faster to develop, and larger when the system is actually in use, because it will be faster for the client to access. Maximum connection number is the maximum number of connections allowed in the connection pool, the specific settings, depending on the amount of access to the system, through repeated testing, to find the best point.

How do I make sure that the minimum number of connections in the connection pool? There are both dynamic and static strategies. Dynamic that is, every time the connection pool is detected, if the number of connections is found to be less than the minimum number of connections, then add a corresponding number of new connections to ensure the normal operation of the connection pool. Static is found when the idle connection is not enough to check again.

　　Implementation of connection pooling

1. Connection Pool Model

The connection pool discussed in this article includes a connection pool class (Dbconnectionpool) and a connection pooling management class (Dbconnetionpoolmanager). A connection pool class is a "buffer pool" for all connections to a database, which mainly implements the following functions: ① Gets or creates an available connection from the connection pool; ② the connection to the connection pool after use; ③ disconnect all connections and release system resources for connection before system shutdown ④ can also handle invalid connections (connections that were originally registered as available, are no longer available for some reason, such as timeouts, communication problems), and can limit the total number of connections in the connection pool to no less than a predetermined value and no more than a predetermined value.

The connection pool management class is the outer covering class (wrapper) of the connection pool class, which conforms to the singleton pattern, where only one instance of the connection pool management class can be in the system. It is mainly used for the management of multiple connection pool objects and has the following functions: ① load and register the JDBC driver for a particular database; ② create a connection pool object based on the information given by the property file; ③ for the convenience of managing multiple connection pool objects, take a name for each connection pool object, The mapping between the connection pool name and its instance is implemented, and ④ tracks the customer's use of the connection so that the connection is closed to release resources. The introduction of connection pool management classes is designed to facilitate the use and management of multiple connection pools, such as the need for a system to connect to different databases, or to connect to the same database, but because of security issues, different users are required to use different names and passwords.

2, Connection Pool implementation

The main attributes of the connection pool class and connection pool management class and the basic interfaces to implement are given below:

public class Dbconnectionpool implements timerlistener{
private int checkedout;//Number of connections that have been allocated
Private ArrayList freeconnections = new ArrayList ()//container, free pool, storing created but not allocated connections according to//creation time order
Minimum number of connections in private int minconn;//connection pool
Maximum number of connections allowed in private int maxconn;//connection pool
Private String name;//A name for this connection pool for easy administration
Private String password;//The password required to connect to the database
Private String url;//The address of the database to which you want to create a connection
Private String user;//The user name required to connect to the database
Public Timer timer;//Timer
Public Dbconnectionpool (string name, string URL, string user, String
password, int maxconn)//exposed constructors
Public synchronized void freeconnection (Connection con)//after use,//return connection to free pool
Public synchronized Connection getconnection (long timeout)//Get a connection,//timeout is wait time
Public synchronized void Release ()//disconnect all connections, releasing occupied system resources
Private Connection newconnection ()/Create a new database connection
Public synchronized void TimerEvent ()//Timer event handler

}

public class Dbconnectionmanager {
Unique instance of the static private Dbconnectionmanager instance;//Connection pool Management class
static private int clients;//number of customers
Private ArrayList drivers = new ArrayList ()//container, storing database driver

Private HASHMAP pools = new HashMap ()//access to Connection pool//object name and Connection pool object in name/value form
Static synchronized public Dbconnectionmanager getinstance ()//if the unique//instance instance has been created, return this instance directly; otherwise, call the private constructor, create// Unique instance of building a connection pool management class

Private Dbconnectionmanager ()//proprietary constructor, in which initialization function init () is invoked

public void Freeconnection (String name, Connection con)/free a connection,//name is the name of a connection pool object

Public Connection getconnection (String name)//Gets a connection from the connection pool object///

Public Connection getconnection (String name, long time)//from Name

Connection pool object in a connection, time is waiting

Public synchronized void release ()/Free all resources

private void Createpools (properties props)//Create one or more connection pools based on the information provided by the property file

private void init ()//Initializes a unique instance of the connection pool management class, which is called by the private constructor

private void Loaddrivers (Properties props)/Load Database driver

}

3, Connection pool use

How does the connection pool implemented above apply to the system during the development of the program? The following is an example of a servlet to illustrate the use of connection pooling.

The lifecycle of the servlet is to invoke its initialization (init) method when the servlet is started. Each user request then causes a thread to invoke the service method of the previously established instance. Finally, when the server decides to uninstall a servlet, it first invokes the Destroy method of the servlet.

Depending on the nature of the servlet, we can generate a unique instance of the connection pool management class in the initialization function (which includes creating one or more connection pools). Such as:

public void Init () throws Servletexception
{
Connmgr = Dbconnectionmanager.getinstance ();
}

You can then use connection pooling in the service method through the connection pool name to perform database operations. Finally, release the occupied system resources in the Destroy method, such as:

public void Destroy () {
Connmgr.release (); Super.destroy ();
}

　　 Concluding remarks

Database connection management is a difficult problem in the development of database-related applications using JDBC. In many cases, the system resource overhead caused by the chaotic management of the connection becomes the bottleneck that restricts the application efficiency of the large enterprise level. For a wide range of user-accessible Web applications, systems with database connectivity technologies are much better at efficiency and stability than in traditional systems. This paper describes the technology of accessing the database using JDBC ﹑ discusses the key problems of database connection management based on connection pool technology and gives an implementation model. This paper presents a basic mode of connection pool management program, which can be used to improve the overall performance of the system, and also make a lot of meaningful extensions.