Poor memory Head 21-JAVA database connection pool DBCP, 21-javadbcp

Source: Internet
Author: User

Poor memory Head 21-JAVA database connection pool DBCP, 21-javadbcp


Each request requires a link to the database. Creating a connection to the database usually consumes a relatively large amount of resources and takes a long time to create a connection. Assume that the website has 1 million accesses a day, and the database server needs to create 1 million connections, which greatly wastes database resources and easily leads to database server connection overflow, and users' usage will be slow, affects user experience;

Database connection management can significantly affect the scalability and robustness of the entire application, and affect the performance indicators of the program.

The database connection pool is created to address these problems. The database connection pool is responsible for allocating, managing, and releasing database resources. It allows applications to reuse an existing database connection, rather than reestablishing one.

During database connection pool initialization, a certain number of database connections are created in the connection pool. The number of connections to these databases is set by the minimum number of database connections. whether or not these database connections are used, the connection pool will always have at least so many connections. the maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

 

The minimum number of connections and maximum number of connections in the database connection pool are the two most important parameter indicators.

Minimum connections: it is a database connection that has been maintained by the connection pool. Therefore, if the application uses a small amount of database connections, a large amount of database connection resources will be wasted.

Maximum number of connections: the maximum number of connections that can be applied by the connection pool. If the number of database connection requests exceeds the limit, the subsequent database connection requests will be added to the waiting queue, which will affect subsequent database operations.

1. Source Code Data Source)

The implementation of DataSource is called a data source in English. The data source includes the implementation of the database connection pool. Currently, many WEB servers (Weblogic, WebSphere, and Tomcat) provide built-in DataSoruce management.

Some open-source organizations also provide other implementations of data sources, such as the famous DBCP database connection pool and C3P0 database connection pool.

DBCP is an open source connection pool implementation under the Apache Software Foundation. To use the DBCP data source, you need to add the following two jar files to the system:

 

Commons-dbcp.jar: Implementation of Connection Pool

Commons-pool.jar: dependency library implemented by connection pool

The connection pool of atat6.0 and earlier versions is implemented using this connection pool. The database connection pool can be integrated with the application server or used independently by the application. (Tomcat7.0 uses the built-in connection pool)

 

2. parameters required for DBCP

Username: the Username used to establish a database connection;

Password: the Password used to establish a database connection;

URL passed to the JDBC driver for establishing a connection

Complete and valid java class name of the JDBC driver used by DriverClassName

ConnectionProperties connection parameters sent to the JDBC driver when a new connection is established. The format must be key = value.

 

3. Explanation of other DBCP Parameters

InitialSize initialize connection: Number of initialized connections created when the connection pool is started

MaxActive maximum active connections: the maximum number of active connections that can be allocated by the connection pool at the same time, similar to the concurrency. If it is set to 0, there is no limit. In maxActive 2.0, this method has been removed, the alternative method is setMaxTotal ();

MaxIdle maximum number of idle connections: the maximum number of connections allowed to remain idle in the connection pool. If the number of idle connections exceeds the limit, the connections are released.

MinIdle minimum idle connection: the minimum number of connections that are allowed to remain idle in the connection pool. A connection smaller than this number will be created. If this number is set to 0, no connection will be created.

MaxWait maximum connection establishment wait time: when no connection is available, the connection pool waits for the connection to be returned (in milliseconds). If the connection pool exceeds the time limit, an exception is thrown, if it is set to-1, this method is removed from 2.0. The alternative method is setMaxWaitMillis ();

Whether removeAbandoned automatically recycles timeout connections

RemoveAbandonedTimeout has a point to note when setting the timeout value. The timeout value is the current time-the time when the Connection is created in the program. If maxActive is large, for example, if it exceeds 100, removeAbandonedTimeout can be set to a longer value, for example, 180, that is, a connection with no response in three minutes is recycled. Of course, the length of different settings varies with the application.

Used Together, minEvictableIdleTimeMillis checks idle connections in the connection pool every timeBetweenEvictionRunsMillis milliseconds, disconnects connections that have been idle for more than minEvictableIdleTimeMillis milliseconds until connections in the connection pool reach minIdle.

TestOnBorrow, testOnReturn, and testWhileIdle attributes indicate whether to verify the obtained, returned object, and idle objects. Check whether the objects are valid. The default value is false, indicating that the objects are not verified. Therefore, when DBCP is used, the database connection is disconnected for some reason, and the connection is obtained from the connection pool without verification. In this case, the database connection is invalid when the connection is actually obtained. This is the case for many DBCP bugs on the Internet. Only by setting these attributes to true and then providing the _ validationQuery statement can ensure that the database connection is always effective.

 

4. Use DBCP to implement source code of the Database Connection Pool

Package test. ffm83.commons. dbcp;

Importorg. apache. commons. dbcp. BasicDataSource;

Importorg. apache. commons. dbcp. BasicDataSourceFactory;

Import org. apache. commons. lang. StringUtils;

Import java. SQL. PreparedStatement;

Import java. SQL. ResultSet;

Import java. SQL. SQLException;

Import java. SQL. Connection;

Import java. util. Properties;

/* Connect to the oracle database through dbcp

 

* Implemented using version 1.4

 

* @ Author fan fangming

 

**/

 

Public classDbcpUsage {

Private static BasicDataSource dataSource = null;

Public DbcpUsage (){

}

Public static void init (){

 

If (dataSource! = Null ){

 

Try {

 

DataSource. close ();

 

} Catch (Exception e ){

 

E. printStackTrace ();

 

}

 

DataSource = null;

 

}

 

 

 

Try {

 

Propertiesp = newProperties ();

 

P. setProperty ("driverClassName", "oracle. jdbc. driver. OracleDriver ");

 

P. setProperty ("url", "jdbc: oracle: thin: @ 192.168.19.1: 1521: fanfangming ");

 

P. setProperty ("password", "ffm ");

 

P. setProperty ("username", "ffm ");

 

P. setProperty ("maxActive", "30 ");

 

P. setProperty ("maxIdle", "10 ");

 

P. setProperty ("maxWait", "1000 ");

 

P. setProperty ("removeAbandoned", "false ");

 

P. setProperty ("removeAbandonedTimeout", "120 ");

 

P. setProperty ("testOnBorrow", "true ");

 

P. setProperty ("logAbandoned", "true ");

 

 

 

DataSource = (BasicDataSource) BasicDataSourceFactory

 

. CreateDataSource (p );

 

} Catch (Exception e ){

 

E. printStackTrace ();

 

}

 

}

 

 

 

Public static synchronized ConnectiongetConnection () throwsSQLException {

 

If (dataSource = null ){

 

Init ();

 

}

 

Connectionconn = null;

 

If (dataSource! = Null ){

 

Conn = dataSource. getConnection ();

 

}

 

Return conn;

 

}

 

 

 

Public static void main (String [] args) throws Exception {

 

Connectioncon = null;

 

Try {

 

Con = DbcpUsage. getConnection ();

 

Stringsql = "select sysdate from dual ";

 

PreparedStatement ps = con. prepareStatement (SQL );

 

ResultSet rs = ps.exe cuteQuery ();

 

While (rs. next ()){

 

String value = rs. getString ("sysdate ");

 

System. out. println (StringUtils. center (value + ", database connection successful", 50 ,"-"));

 

}

 

} Catch (Exception e ){

 

E. printStackTrace ();

 

} Finally {

 

If (con! = Null ){

 

Con. close ();

 

}

 

}

 

}

 

}

 

Run the following command:

 

---------- 2014-12-1612: 38: 32.0, database connection successful -----------

5. Running result

Run the following command:

 

---------- 2014-12-1612: 38: 32.0, database connection successful -----------

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.