Java database connection pool configuration methods (take MySQL database as an example), database connection mysql

Java database connection pool configuration methods (take MySQL database as an example), database connection mysql
Several configuration methods for Java Database Connection Pool (taking MySQL database as an example)

1. Tomcat configuration data source:

Prerequisites: You need to put the jar package for connecting to the MySQL database driver into the lib directory under the common folder in the Tomcat installation directory.

1. Method 1: Create a folder META-INF under WebRoot, and create a file context. xml, as shown below:

<? Xml version = "1.0" encoding = "UTF-8"?>
<Context>
<Resource name = "jdbc/chaoshi" auth = "Container"

Type = "javax. SQL. DataSource"
MaxActive = "50" maxIdle = "30" maxWait = "10000" logAbandoned = "true"
Username = "root" password = "root" driverClassName = "com. mysql. jdbc. Driver"
Url = "jdbc: mysql: // localhost: 3306/DBName">
</Resource>
</Context>

Method 2: In the context. xml file under the conf directory of tomcat, modify the original context tag:

<Context>
<! -- Default set of monitored resources -->
<WatchedResource> WEB-INF/web. xml </WatchedResource>
<Resource name = "jdbc/test" auth = "Container"

Type = "javax. SQL. DataSource"
MaxActive = "50" maxIdle = "30" maxWait = "10000" logAbandoned = "true"
Username = "root" password = "root"

DriverClassName = "com. mysql. jdbc. Driver"
Url = "jdbc: mysql: // localhost: 3306/testdb"/>
</Context>
Method 3: When configuring the virtual directory, that is, When configuring server. xml under conf, change it to the context label:
<Context path = "/WebRoot" reloadable = "true"

DocBase = "E: \ workspace \ DataSource \ WebRoot">
<Resource name = "jdbc/test" auth = "Container"

Type = "javax. SQL. DataSource"
MaxActive = "50" maxIdle = "30" maxwait= "10000"

LogAbandoned = "true"
Username = "root" password = "root"

DriverClassName = "com. mysql. jdbc. Driver"
Url = "jdbc: mysql: // localhost: 3306/testdb"/>

</Context>

</Meaning of each attribute in the Resource tag in the configuration file:
Full name of the database Driver Class used by driverClassName-JDBC.
MaxActive-Maximum number of active connections provided by the connection pool at the same time.
MaxIdle-Maximum number of connections that the connection pool maintains during idle time.
MaxWait-Maximum number of milliseconds the database waits when an exception occurs (when no available connection is available ).

Username-login name when connecting to the database
Password-password used to connect to the database.
Url-connect to the driver URL. (For backward compatibility, DRIVERNAME is also allowed .)
Test code:
ContextinitContext = new InitialContext ();
Context envContext = (Context) initContext. lookup ("java:/comp/env ");
DataSource ds = (DataSource) envContext. lookup ("jdbc/test ");
System. out. println (ds. getConnection ());
It should be successful if it is not null.
Note: during testing, you need to test in tomcat, that is, in the TOMCAT container (do not worry, write a simple JSP page for testing, and use a <%... %> it is quite simple ). If it is not tested in the tomcat container, an exception is thrown:

... Javax. naming. NoInitialContextException: Need to specifyclass name in environment or system property, or as an appletparameter, or in an application resource file: java. naming. factory. initial
2. There are three methods for configuring the connection pool in Hibernate:
Method 1 use the connection pool that comes with Hibernate.
<Hibernate-configuration>
<Session-factory>
<! -- JDBC driver -->
<Propertyname = "connection. driver_class"> com. mysql. jdbc. Driver </property>
<! -- Connection URL -->
<Propertyname = "connection. url">
Jdbc: mysql: // localhost: 3306/feifei
</Property>
<! -- Connection login name -->
<Propertyname = "connection. username"> root </property>
<! -- Logon password -->
<Propertyname = "connection. password"> </property>
<! -- Whether to output SQL statements generated during runtime to logs for debugging -->
<Propertyname = "show_ SQL"> true </property>
<! -- Specify the connection language -->
<Propertyname = "dialect"> org. hibernate. dialect. MySQLDialect </property>
<! -- Map resources -->
<Mapping resource = "/xx. hbm. xml"/>
</Session-factory>
</Hibernate-configuration>
My own configuration is as follows. The comments are public and need to be used in other connection pools!
<Hibernate-configuration>
<Session-factory>
<Propertyname = "show_ SQL"> true </property>
<! -- Common conf dbcp/c3p0 needed
<Propertyname = "connection. username"> informix </property>
<Propertyname = "connection. password"> informix </property>
<Propertyname = "connection. driver_class"> com. informix. jdbc. IfxDriver </property>
<Propertyname = "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>
</Hibernate-configuration>
========================================================== =
Method 2: Use the database connection pool specified by the configuration file.
The connection pool now includes dbcp, c3p0, and proxoop. In fact, I knew dbcp
The configuration of dbcp and c3p0 only needs to be added to the above configuration. hibernate will automatically identify the database connection pool.
Dbcp configuration needs to be added:
<! -- Dbcp conf
<Propertyname = "dbcp. maxActive"> 100 </property>
<Propertyname = "dbcp. whenExhaustedAction"> 1 </property>
<Propertyname = "dbcp. maxWait"> 60000 </property>
<Propertyname = "dbcp. maxIdle"> 10 </property>
<Propertyname = "dbcp. ps. maxActive"> 100 </property>
<Propertyname = "dbcp. ps. whenExhaustedAction"> 1 </property>
<Propertyname = "dbcp. ps. maxWait"> 60000 </property>
<Propertyname = "dbcp. ps. maxIdle"> 10 </property>
-->
To configure c3p0, add:
<! -- C3p0 conf
<Propertyname = "c3p0. min_size"> 5 </property>
<Propertyname = "c3p0. max_size"> 30 </property>
<Propertyname = "c3p0. time_out"> 1800 </property>
<Propertyname = "c3p0. max_statement"> 50 </property>
-->
The configuration of proxoop is somewhat different. You cannot just add it, but you still need to modify it:
The basic configuration is as follows:
<Propertyname = "proxool. pool_alias"> dbpool </property>
<Propertyname = "proxool. xml"> test/huatech/conf/ProxoolConf. xml </property>
<Propertyname = "connection. provider_class"> org. hibernate. connection. ProxoolConnectionProvider </property>
Note: the path of the following files must be correctly configured; otherwise, FileNotFound
Associated file: 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 itself -->
<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>
<! -- The proxool automatically detects the time interval (in milliseconds) of each connection status. When idle connections are detected, the system immediately recycles the connection status and destroys the connection timeout. -->
<House-keeping-sleep-time> 90000 <! -- Indicates the maximum number of requests waiting in the queue because no idle connections can be allocated. User connections exceeding the number of requests will not be accepted. -->
<Maximum-new-connections> 20 </maximum-new-connections>
<! -- Minimum number of idle connections maintained -->
<Prototype-count> 5 </prototype-count>
<! -- The maximum number of connections allowed. If the connection is exceeded, the requests are placed in the queue. the maximum number of waiting requests is determined by maximum-new-connections. -->
<Maximum-connection-count> 100 </maximum-connection-count>
<! -- Minimum connections -->
<Minimum-connection-count> 10 </minimum-connection-count>
</Proxool>
</Something-else-entirely>
========================================================== =
Method 3: Obtain the connection pool from the container (for example, Tomcat)
Use the connection pool of the server itself: such as Tomcat, resin, weblogic, etc.
The hibernate configuration is as follows:
<! --
<Propertyname = "hibernate. connection. datasource">
Java: comp/env/jdbc/crm
</Property>
<Propertyname = "show_ SQL"> true </property>
<Property name = "dialect">
Com. huatech. sysframe. webapp. common. dao. hibernate. dialet. BaseInformixDialect
</Property>
<Propertyname = "hibernate. generate_statistics"> true </property>
-->
The jdbc/crm of java: comp/env/jdbc/crm is the name of the database connection pool in the corresponding server, which must be configured in the corresponding environment.
Tomcat configuration is described in the First Tomcat configuration method. Note that the jndi name should be modified according to the situation and correspond to the name used by hibernate.
==========================================================
The jar package of the respective database connection pool is used in the above configuration. It is included in the hibernate package. If you need the latest package, you can download it from your own website.
3. Configure the connection pool using Spring:
<Bean id = "dataSource" class = "org. apache. commons. dbcp. BasicDataSource" destroy-method = "close">
<Propertyname = "driverClassName">
<Value> com. mysql. jdbc. Driver </value>
</Property>
<Propertyname = "url">
<Value> jdbc: mysql: // localhost: 3306/dbname </value>
</Property>
<Propertyname = "username">
<Value> root </value>
</Property>
<Propertyname = "password">
<Value> ****** </value>
</Property>
<Propertyname = "maxActive">
<Value> 100 </value>
</Property>
<Propertyname = "maxWait">
<Value> 1000 </value>
</Property>
<Propertyname = "maxIdle">
<Value> 30 </value>
</Property>
<Propertyname = "defaultAutoCommit">
<Value> true </value>
</Property>
<Propertyname = "removeAbandoned"> // automatically recycles the connection pool to avoid leakage of the connection pool.
<Value> true </value>
</Property>
<Propertyname = "removeAbandonedTimeout">
<Value> 60 </value>
</Property>
</Bean>
4. The last one I want to talk about today is to write and configure the connection through code.Connection Pool, the Code is as follows:
Import java. SQL .*;
Import java. SQL. DataSource;
Import org. apache. commons. dbcp. BasicDataSource;
Public class ConnectionPool {
Privatestatic BasicDataSource dbs = null;
Publicstatic DataSource setupDataSource (){
Bds = new BasicDataSource ();
// Set the driver
Bds. sestDriverClassName ("com. mysql. jdbc. Driver ");
// Set the connection User Name
Bds. setUsername ("root ");
// Set the connection password
Bds. setPassword ("root ");
// Set the connection address
Bds. setUrl ("jdbc: mysql: // localhost: 3306/databasename ");
// Set the total number of initialized connections
Bds. setInitialSize (50 );
// Set the total number of connections simultaneously 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 longest waiting time
Bds. setMaxWait (-1 );
Return (DataSource) bds;
}
// Display the number of connections in the connection pool
Publicstatic void printDataSourceStats (DataSource ds) throwsSQLException {
Bds = (BasicDataSource) ds;
System. out. println ();
System. out. println ();
}
// Method for closing the connection pool
Public static void shutdownDataSource (DataSource ds) throwsSQLException {
Bds = (BasicDataSource) ds;
Bds. close ();
}
}
You only need to use the static method setupDataSource () in the connection pool.

 

 

 

Original article: http://blog.sina.com.cn/s/blog_85d71fb70101ab99.html