SummaryThis paper introduces the principle and problems of Java Access database, puts forward the solution-database connection pool, analyzes its key problems, constructs a simple and easy-to-use connection pool and illustrates how it is used in development with the current hot technology servlet.
Key WordsJdbc,jsp/servlet, database connection pool, multi-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 a traditional desktop app to a web app. The 3-tier development model based on B/s (browser/server) architecture gradually replaces the development model of C/s (client/server) architecture, and becomes a popular technology for developing enterprise applications and e-commerce. In the early days of web application development, the main technologies used were cgi﹑asp﹑php and so on. After that, Sun launched the 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 accessing databases by Java applications
In the Java language, JDBC (Java database Connection) is a bridge between application and database communication,
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 developer ﹑ Database foreground tool developers, enabling developers to write complete database applications in a pure Java language. JDBC provides two APIs, namely the developer-oriented API and the JDBC driver API for the underlying, which is primarily connected to the database through direct JDBC driver and Jdbc-odbc bridge drivers.
In general, the process of accessing a database by a Java application (1) is as follows:
① loading the database driver;
② database connection through JDBC;
③ Access database, execute SQL statement;
④ Disconnect the database.
Figure 1 Java Database access mechanism |
As a database access technology, JDBC has the advantages of simple and easy to use. But using this pattern for Web applications
program development, there are many problems: first, each Web request to establish a database connection. Establishing a connection is a time-consuming activity that takes 0.05s~1s times each time, and the system allocates memory resources. This time for one or several database operations, you may not feel how much overhead the system has. But for today's web applications, especially large e-commerce sites, there are hundreds of people or even thousands of people online is very normal. In this case, the frequent database connection operation is bound to occupy a lot of system resources, the response speed of the site must be reduced, serious or even cause a server crash. is not alarmist, this is restricting the development of some e-commerce website technology bottleneck problem. Second, for each database connection, you have to disconnect after use. Otherwise, if the program fails to shut down, it will cause a memory leak in the database system and will eventually have to restart the database. Also, this development does not control the number of connection objects being created, and system resources are allocated without consideration, such as too many connections, which can lead to memory leaks and server crashes.
How database connection pooling (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 database connection resources. We know
For shared resources, there is a well-known design pattern: resource pools (Resource pool). This model is to solve the problem caused by the frequent allocation of resources ﹑ release. To solve the above problems, the database connection pooling technology can be adopted. The basic idea of a database connection pool is to establish a "buffer pool" for database connections. A certain number of connections are pre-placed in the buffer pool, and when a database connection needs to be established, simply take one out of the buffer pool and put it back when you are finished. 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 connections in the database through the connection pool management mechanism ﹑ usage, provide the basis for system development ﹑ test and performance adjustment. The basic working principle of connection pooling is shown in 2.
Figure 2 How the connection pooling works |
2, the server comes with the connection pool
There is no way to provide a connection pool in the JDBC API. Some large Web application servers, such as Bea's WebLogic and IBM's WebSphere, provide a mechanism for connection pooling, but must have their third-party dedicated class methods to support the usage of connection pooling.
analysis of key problems in connection pooling
1, concurrency problems
In order for the connection Management Service to be the most versatile, you must consider a multithreaded environment, which is a concurrency problem. This is a relatively good problem to solve because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to precede the class method with the Synchronized keyword, such as:
| Public synchronized Connection getconnection () |
2, multi-database server and multi-user
For large enterprise applications, it is often necessary to connect different databases at the same time (such as connecting Oracle and Sybase). How do I connect to different databases? Our strategy is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when the 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. Creates instances of multiple connection pool classes, each of which is a connection pool for a particular database, based on the information provided by the resource file. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools with different names.
In the case of multiple users with different names and passwords for the same database, it is also possible to process the resource file by setting up multiple database connection information in the resource file with the same URL address but with a different user name and password.
3. Transaction processing
We know that transactions are atomic, which requires that the operation of the database conform to the "all-all-nothing" principle, which is to either do the whole set of SQL statements or do nothing at all.
In the Java language, the connection class itself provides support for transactions by setting the Autocommit property of connection to false and then explicitly invoking either the commit or Rollback method. However, for efficient connection reuse, it is necessary to provide the corresponding transaction support mechanism. Each transaction can be implemented with a single connection, which can greatly reduce the complexity of transaction management.
4. Allocation and release of connection pool
The allocation and release of the connection pool has a great impact on the performance of the system. Reasonable allocation and release can increase the reusability of the connection, thus reducing the cost of establishing a new connection and speeding up the user's access speed.
You can use the free pool for management of connections. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Whenever a user requests a connection, the system first checks that there are no idle connections in the free pool. If there is the longest established (through the container in the order of the implementation) of the connection assigned to him (the actual connection is the validity of the judgment, if available to assign to the user, if it is not available to delete the connection from the free pool, re-detect whether the free pool is still connected) If not, check whether the currently 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 is reached. If a connection is freed within the waiting time, the connection can be assigned to the waiting user, and a null value (NULL) is returned if the wait time exceeds the scheduled time of timeout. The system only counts the connections that are already being used, and then returns them to the free pool when it is finished. For the state of idle connections, a dedicated thread timing detection can be created, which can cost a certain amount of overhead, but ensures a faster response time. You can also take a method that does not open up specialized threads, just before the allocation is detected.
5, the connection pool configuration and maintenance
How many connections should be placed in the connection pool to make the system perform optimally? The system can take control of connections in the connection pool by setting the minimum number of connections (minconn) and the maximum number of connections (maxconn). The minimum number of connections is the number of connections created by the connection pool when the system starts. If you create too many, the system starts slowly, but the system responds quickly after creation, 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, develop quickly, and set a larger size when the system is actually used, because it will be faster to access the customer. Maximum connections is the maximum number of connections allowed in the connection pool, depending on the amount of access to the system, you can find the best point by repeated testing.
How do I ensure the minimum number of connections in a connection pool? There are both dynamic and static strategies. Dynamic is to detect the connection pool at regular intervals, and if the number of connections is found to be less than the minimum number of connections, the corresponding number of new connections will be replenished to ensure the proper operation of the connection pool. Static is found when idle connection is insufficient to check again.
implementation of connection pooling
1. Connection Pool Model
The connection pools discussed in this article include a connection pool class (Dbconnectionpool) and a connection pool management class (Dbconnetionpoolmanager). The connection pool class is a "buffer pool" for all connections to a database, primarily for the following functions: ① Gets or creates an available connection from the connection pool, ② the connection to the connection pool after use, and ③ disconnects all connections and frees system resources from the connection before the system shuts down ④ is also able to handle invalid connections (originally registered as an available connection, for some reason no longer available, such as timeouts, communication problems), and able to limit the total number of connections in the connection pool to a predetermined value and no more than a predetermined value.
The connection pooling management class is the outer class of the Connection pool class (wrapper), which conforms to the singleton pattern, that is, there can be only one instance of the connection pool management class in the system. It is mainly used for the management of multiple connection pool objects, has the following functions: ① mount and register the JDBC driver for a particular database; ② creates a connection pool object based on the information given by the properties file, ③ to facilitate the management of multiple connection pool objects, give each connection pool object a name, Implements the mapping between the name of the connection pool and its instance, and ④ tracks the client's use of the connection so that the need to close the connection frees the resource. Connection pooling management classes are introduced primarily to facilitate the use and management of multiple connection pools, such as the need 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 properties of the connection pool class and the 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, based on//create time order for connections created but not yet assigned
private int minconn;//The minimum number of connections in the connection pool
private int maxconn;//The maximum number of connections allowed in the connection pool
Private String name;//A name for this connection pool for easy management
Private String password;//The password that is required to connect to the database
Private String url;//The address of the database to which the connection is to be created
Private String user;//The user name that is required to connect to the database
Public Timer timer;//Timer
Public Dbconnectionpool (string name, string URL, string user, String
password, int maxconn)//public constructor
Public synchronized void freeconnection (Connection con)//After the use is complete,//return the connection to the free pool
Public synchronized Connection getconnection (long timeout)//Get a connection,//timeout is the wait time
Public synchronized void Release ()//disconnect all connections, freeing up system resources that are occupied
Private Connection newconnection ()//Create a new database connection
Public synchronized void TimerEvent ()//Timer event handler function
}
public class Dbconnectionmanager {
Static private Dbconnectionmanager instance;//Unique instance of the connection pool management class
static private int clients;//number of customers
Private ArrayList drivers = new ArrayList ();//container, store database driver
Private HASHMAP pools = new HashMap ()//access the name of the connection pool/object and the connection pool object in the form of Name/value
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 the connection pooling management class
Private Dbconnectionmanager ()///proprietary constructor, in which the initialization function is called init ()
public void Freeconnection (String name, Connection con)//release a connection,//name is the name of a connection pool object
Public Connection getconnection (String name)//Get a connection from the connection pool object named name//
Public Connection getconnection (String name, long time)//from name for name
Connection pool object to get a connection, time is the wait
Public synchronized void Release ()//Release all resources
private void Createpools (properties props)//Create one or more connection pools based on the information provided by the properties file
private void init ()//Initializes a unique instance of the connection pool management class, 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 when the program is developed? The following example illustrates the use of connection pooling using a servlet.
The life cycle of a servlet is to invoke its initialization (init) method when it begins to build the servlet. Each subsequent user request causes a thread that invokes the service method of the previously established instance. Finally, when the server decides to unload a servlet, it first calls the servlet's Destroy method.
Depending on the characteristics 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 the connection pool name in the service method to perform database operations. Finally, the system resources that are used in the Destroy method are freed, such as:
public void Destroy () {
Connmgr.release (); Super.destroy ();
} |
Concluding remarks
The management of database connections is a difficult point in the development of database-related applications using JDBC. Many times, the management of the confusion caused by the connection of the system resource overhead becomes the bottleneck that restricts the application efficiency of large enterprise. For Web applications that are accessed by many users, systems with database connectivity are much better at efficiency and stability than traditional other systems. This paper expounds the technology of using JDBC to access the database ﹑ discusses the key problems of database connection management based on connection pooling technology and gives an implementation model. This paper gives a basic mode of connection pool management program, in order to improve the overall performance of the system, on this basis can also carry out a lot of meaningful expansion.
Database Connection Pool
