Database Connection Pool Configuration

Today encountered the data source connection pool configuration problems, found that there are many ways to configure, now summarized below, I hope to be helpful: (MySQL database as an example)

One, tomcat configuration data Source:

mode one: Under the Webroot to build a folder Meta-inf, inside a file context.xml, the contents are as follows:
<Context>
<resource name= "Jdbc/test" auth= "Container" type= "Javax.sql.DataSource"
Maxactive= "maxidle=" "maxwait=" 10000 "logabandoned=" true "
Username= "root" password= "111111" driverclassname= "Com.mysql.jdbc.Driver"
Url= "Jdbc:mysql://localhost:3306/testdb"/>
</Context>

Mode two: In the tomcat6.0 directory conf The following context.xml, modify the original context label, changed to the content as follows:
<Context>

<!--Default set of monitored resources--
<WatchedResource>WEB-INF/web.xml</WatchedResource>

<resource name= "Jdbc/test" auth= "Container" type= "Javax.sql.DataSource"
Maxactive= "maxidle=" "maxwait=" 10000 "logabandoned=" true "
Username= "root" password= "111111" driverclassname= "Com.mysql.jdbc.Driver"
Url= "Jdbc:mysql://localhost:3306/testdb"/>

</Context>

Mode three: When configuring the virtual directory, that is, in the configuration conf below the Server.xml, in the context tag is changed to the following form:
<context path= "/webroot" reloadable= "true" docbase= "E:\workspace\DataSource\WebRoot" >
<resource name= "Jdbc/test" auth= "Container" type= "Javax.sql.DataSource"
Maxactive= "maxidle=" "maxwait=" 10000 "logabandoned=" true "
Username= "root" password= "111111" driverclassname= "Com.mysql.jdbc.Driver"
Url= "Jdbc:mysql://localhost:3306/testdb"/>
</Context>

The meanings of the attributes in the resource tag in the configuration file:

The full name of the database-driven class used by the DRIVERCLASSNAME-JDBC.

Maxactive-The maximum number of active connections that the connection pool provides at the same time.

Maxidle-The maximum number of connections that the connection pool remains at idle time.

Maxwait-The maximum number of milliseconds the database waits when an exception occurs (when no connection is available).

Password-password to connect to the database.

URL-Connect to the driver's URL. (For backwards compatibility, drivername is also allowed.)

User-database username.

The range of configuration methods should also be different. I do not elaborate here, in short, the context tag is configured under a resource tag can be.

Test code:
Context initcontext = new InitialContext ();
Context Envcontext = (context) initcontext.lookup ("java:/comp/env");
DataSource ds = (DataSource) envcontext.lookup ("Jdbc/test");
System.out.println (Ds.getconnection ());
Printing out is not NULL should be successful.
Note that the test should be tested in Tomcat, that is, in the Tomcat container (do not idle trouble, write a simple JSP page to test, with a <%...%> on it, quite simple). Not being tested in the Tomcat container will throw an exception:

... javax.naming.NoInitialContextException:Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file:java.naming.factory.initial

Two, hibernate configuration connection pooling There are three ways:

Mode 1 uses a connection pool that comes with hibernate.

<session-factory >
<!--JDBC Driver--
<property name= "Connection.driver_class" >com.mysql.jdbc.Driver</property>
Url--> of <!--connected Database
<property name= "Connection.url" >
Jdbc:mysql://localhost:3306/feifei
</property>
<!--connected Login name-
<property name= "Connection.username" >root</property>
<!--login password--
<property name= "Connection.password" ></property>
<!--whether to output the SQL generated by the run-time to the log for debugging--
<property name= "Show_sql" >true</property>
<!--Specify the language of the connection--
<property name= "dialect" >org.hibernate.dialect.MySQLDialect</property>
<!--mapping Resources--
<mapping resource= "/xx/xx.hbm.xml"/>
</session-factory>
My own configuration is as follows,The comment section, he is common, other connection pool need to use!
<session-factory>
<property name= "Show_sql" >true</property>
<!--common conf dbcp/c3p0 needed
<property name= "Connection.username" >informix</property>
<property name= "Connection.password" >informix</property>
<property name= "Connection.driver_class" >com.informix.jdbc.IfxDriver</property>
<property name= "Connection.url" >
JDBC:INFORMIX-SQLI://192.168.0.188:1526/DB_CRM:INFORMIXSERVER=OL_SX; Newlocale=zh_cn,en_us; newcodeset=gbk,8859_1,819;
</property>
<property name= "dialect" >
Com.huatech.sysframe.webapp.common.dao.hibernate.dialet.BaseInformixDialect
</property>
-
... ... ... ...
</session-factory>

=================================================================================
Method 2: Use the configuration file to specify the database connection pool.
The connection pool now has dbcp, C3P0, Proxoop, actually I originally knew DBCP
where dbcp, C3P0 configuration only need to add some configuration on the above configuration on the line, hibernate will automatically identify the database connection pool

Configure DBCP to join:
<!--DBCP conf
<property name= "Dbcp.maxactive" >100</property>
<property name= "Dbcp.whenexhaustedaction" >1</property>
<property name= "Dbcp.maxwait" >60000</property>
<property name= "Dbcp.maxidle" >10</property>

<property name= "Dbcp.ps.maxActive" >100</property>
<property name= "Dbcp.ps.whenExhaustedAction" >1</property>
<property name= "Dbcp.ps.maxWait" >60000</property>
<property name= "Dbcp.ps.maxIdle" >10</property>
-
Configure C3P0 to join:
<!--C3P0 conf
<property name= "C3p0.min_size" >5</property>
<property name= "C3p0.max_size" >30</property>
<property name= "C3p0.time_out" >1800</property>
<property name= "C3p0.max_statement" >50</property>
-

Configuration Proxoop is a bit different, not just to join, but also to change:
The basic configuration is as follows:
<property name= "Proxool.pool_alias" >dbpool</property>
<property name= "Proxool.xml" >test/huatech/conf/ProxoolConf.xml</property>
<property name= "Connection.provider_class" >org.hibernate.connection.proxoolconnectionprovider</ Property>

Special note: The path to the following file is configured correctly, otherwise FileNotFound
Associated files: The Test/huatech/conf/proxoolconf.xml is configured as follows:

<?xml version= "1.0" encoding= "Utf-8"?>
<something-else-entirely>
<proxool>
<alias>dbpool</alias>
<!--Proxool can only manage connections generated by yourself--
<driver-url>
JDBC:INFORMIX-SQLI://192.168.0.188:1526/DB_CRM:INFORMIXSERVER=OL_SX; Newlocale=zh_cn,en_us; newcodeset=gbk,8859_1,819;
</driver-url>
<driver-class>com.informix.jdbc.IfxDriver</driver-class>
<driver-properties>
<property name= "user" value= "Informix"/>
<property name= "Password" value= "Informix"/>
</driver-properties>
<!--Proxool automatically detects the time interval (in milliseconds) of each connection state, and detects that an idle connection is immediately recovered and the timeout is destroyed--
<!--refers to the maximum number of requests waiting in a queue because no idle connections can be allocated, and user connections that exceed the number of requests will not be accepted-
<maximum-new-connections>20</maximum-new-connections>
<!--minimum number of idle connections to keep--
<prototype-count>5</prototype-count>
<!--allow the maximum number of connections, exceeding this connection, and then waiting in the queue when requested, the maximum number of wait requests determined by maximum-new-connections-
<maximum-connection-count>100</maximum-connection-count>
< minimum number of connections!----
<minimum-connection-count>10</minimum-connection-count>
</proxool>
</something-else-entirely>

=================================================================================

Method 3: Get the connection pool from the container (e.g. Tomcat)
Connection pooling with the server itself: such as Tomcat, resin, weblogic, etc.
Hibernate is configured as follows:
<!--
<property name= "Hibernate.connection.datasource" >
Java:comp/env/jdbc/crm
</property>
<property name= "Show_sql" >true</property>
<property name= "dialect" >
Com.huatech.sysframe.webapp.common.dao.hibernate.dialet.BaseInformixDialect
</property>
<property name= "Hibernate.generate_statistics" >true</property>
-
The Jdbc/crm in Java:comp/env/jdbc/crm is the name of the database connection pool in the corresponding server, which needs to be configured in the corresponding environment .

The tomcat configuration is described in the first Tomcat configuration, note that the Jndi name is modified according to the situation, and corresponds to the name that Hibernate uses.

=================================================================================
The above configuration needs to use the respective database connection pool jar package, in the Hibernate package, if you need the latest can be downloaded to the respective website.

Third, spring configures the connection pooling method:

<bean id= "DataSource" class= "Org.apache.commons.dbcp.BasicDataSource" destroy-method= "Close" >
<property name= "Driverclassname" >
<value>com.mysql.jdbc.Driver</value>
</property>
<property name= "url" >
<value>jdbc:mysql://localhost:3306/dbname</value>
</property>
<property name= "username" >
<value>root</value>
</property>
<property name= "Password" >
<value>******</value>
</property>
<property name= "Maxactive" >

<value>100</value>
</property>
<property name= "Maxwait" >

<value>1000</value>
</property>
<property name= "Maxidle" >
<value>30</value>
</property>

<property name= "Defaultautocommit" >
<value>true</value>
</property>
<property name= "removeabandoned" >//Auto Reclaim connection pool, avoid connection pool leakage
<value>true</value>
</property>
<property name= "Removeabandonedtimeout" >
<value>60</value>
</property>

</bean>

four, which is the last one I want to say today, is to configure the connection pool by writing code as follows:

Import java.sql.*;

Import Java.sql.DataSource;

Import Org.apache.commons.dbcp.BasicDataSource;

public class connectionpool{

private static Basicdatasource dbs = NULL;

public static DataSource Setupdatasource () {

BDS = new Basicdatasource ();

Setting up drivers

Bds.sestdriverclassname ("Com.mysql.jdbc.Driver");

Setting the connection user name

Bds.setusername ("root");

Setting the connection password

Bds.setpassword ("root");

Setting the connection Address

Bds.seturl ("Jdbc:mysql://localhost:3306/databasename");

Set the total number of initialization connections

Bds.setinitialsize (50);

Set the total number of simultaneous connections applied

Bds.setmaxactive (-1);

Set the maximum number of connections in the buffer pool

Bds.setmaxidle (-1);

Set the minimum number of connections in the buffer pool

Bds.setminidle (0);

Set the maximum wait time

Bds.setmaxwait (-1);

Return (DataSource) BDS;

}

Ways to show the number of connections to a connection pool

public static void Printdatasourcestats (DataSource ds) throws sqlexception{

BDS = (Basicdatasource) DS;

System.out.println ();

System.out.println ();

}

Ways to turn off connection pooling

public static void Shutdowndatasource (DataSource ds) throws sqlexception{

BDS = (Basicdatasource) DS;

Bds.close ();

}

}

At present the database connection pool product is very many, DBCP, C3P0, Proxool and so on are very outstanding products. The performance and stability of the connection pool can have a significant impact on our programs, so it is necessary to make some choices about these connection pooling products. In addition, the configuration of the connection pool is appropriate and will determine the performance and stability of the connection pool, so this article will give some points to the connection pool configuration. In these connection pool products to make a choice is difficult, each excellent product has its own characteristics, and it is difficult to find a variety of operating environment is the best performance of the product, therefore, this article will select some of the more excellent products, briefly introduce their configuration points, And how to use it in our projects. As for the production environment, which product will perform best, then ....

　The Hibernate Development Group recommends using the C3p0,spring Development Group to recommend the use of DBCP (DBCP connection pool has WebLogic connection pool The same problem is that after a forced shutdown of the connection or a database restart, cannot be reconnect, but can be resolved through configuration), Hibernate in action recommends the use of C3P0 and Proxool. There are many other commercial products, but performance and stability overall performance is not as good as these open source database connection pool. From the online search situation, but also dbcp, C3P0, proxool the use of more extensive. However, it is difficult to get conclusions about the performance and stability of controversial.
DBCP is produced by Apache, is also active in development, and is also used in a very wide range of database connection pooling products. The stability of DBCP is somewhat problematic in terms of the data searched online and the experience of using DBCP in the past. But its development is more active, so we can believe it will solve these problems, and some problems can be solved by additional configuration.
C3P0, stability seems to be good, in this regard seems to have a good reputation. As for performance, should not be the best, is the type of good.
Proxool's word of mouth seems to be very good, not to see negative evaluation, from the official information, there are many useful features and characteristics, is recommended by many people. But the development is not active enough and the users are less.
I selected these three products as a database connection pool for our system and used Proxool as a connection pool product in our current development environment (Proxool for easy monitoring).

Database Connection Pool Configuration