Several configuration methods of database connection pool __ Database

Database connectivity is a critical, limited, and expensive resource, especially in multi-user Web applications. The management of database connections can significantly affect the scalability and robustness of the entire application, affecting the performance metrics of the program, and the database connection pool is proposed for this problem.

The database connection pool is responsible for allocating, managing, and freeing the database connection, which allows the application to reuse an existing database connection rather than re-establish one, freeing up database connections that have more free time than the maximum idle time to avoid missing database connections caused by not releasing the database connection. This can significantly improve the performance of database operations.

When the database connection pool is initialized, a certain number of database connections are created in the connection pool, and the number of database connections is set by the minimum number of database connections, and the connection pool is guaranteed to have at least as many connections, regardless of whether these database connections are used, When the application requests more connections to the connection pool than the maximum number of connections, these requests are added to the wait queue.

The minimum number of connections and maximum number of connections for a database connection pool is set to take into account the following factors:

1. The minimum number of connections is the database connection that the connection pool maintains, so if the application does not use the database connection very much, a large amount of database connection resources will be wasted.

2. The maximum number of connections is the maximum number of connection pool requests, and if the database connection request exceeds the number of times, subsequent database connection requests will be added to the pending, which affects the database operation after

If the minimum number of connections is too large for the maximum number of connections, then the first connection request will be profitable, then the connection request exceeding the minimum number of connections is equivalent to establishing a new database connection, however, these database connections smaller than the minimum number of connections will not be released immediately after use, It will be placed in the connection pool for reuse or idle timeout to be freed.

Instance uses a Tomcat version of 6.0

Method One: Configure in Tomcat's Conf/context.xml
The default values are configured in the Context.xml file in the tomcat\apache-tomcat-6.0.33\conf directory as follows:

<?xml version= ' 1.0 ' encoding= ' utf-8 '?>
<Context>
    <watchedresource>web-inf/web.xml</ Watchedresource>
</Context>

To configure connection pooling:

<?xml version= ' 1.0 ' encoding= ' utf-8 '?>

<Context> <WatchedResource>WEB-INF/web.xml</WatchedResource> <!--Configuring a connection pool for Oracle databases--> & Lt
        Resource name= "jdbc/oracleds" author= "Container" type= "Javax.sql.DataSource" maxactive= "100" maxidle= "maxwait=" 10000 "Username=" Scott "password=" Tiger "driverclassname=" Oracle. Jdbc.dirver.OracleDriver "url=" Jdbc:oracle:thin:@127.0.0.1:1521:ora9 "/> <!--Configure the MySQL database connection pool that requires 
        The extra step is to put MySQL's Java-driven classes into the Tomcat's Lib directory maxidle connection pool can be up to maxidle connections Minidle the minimum number of free maxidle connections in the connection pool InitialSize number of initial connections maxwait connection pool is exhausted, new request wait time, milliseconds username database username password Database password- > <resource name= "jdbc/mysqlds" auth= "Container" type= "Javax.sql.DataSource" username = "Root" password= "root" maxidle= "maxwait=" 10000 "maxactive=" Drivercla Ssname= "Com.mysql.jdbC.driver "url=" Jdbc:mysql://127.0.0.1:3306/db_blog "/> </Context> 

Two steps to pay attention to when configured

1. Put the corresponding database driver class into Tomcat's Lib directory XI ' an

2. Restart Tomcat server for configuration to take effect

Set the data source reference in the Web.xml of the Web application, as follows:

Add the following content to the <web-app></web-app> node

<resource-ref>

      <description>mysql database Connection Pool </description>
      <!--reference data source name, with Name attribute value in resource node configured in Tomcat jdbc/ Mysqlds "Consistent-->
      <res-ref-name>jdbc/mysqlds</res-ref-name>
      <!--resource type-->
      < res-type>javax.sql.datasource</res-type>
      <res-auth>Container</res-auth>
      < Res-sharing-scope>shareable</res-sharing-scope>
</resource-ref>

Error Resolution:

Javax.naming.NoInitialContextException:Need to specify class name in environment or system, or as a applet para meter, or in a application resource file:java.naming.factory.initial

    At Javax.naming.spi.NamingManager.getInitialContext (namingmanager.java:645) at
    JAVAX.NAMING.INITIALCONTEXT.GETDEFAULTINITCTX (initialcontext.java:288) at
    JAVAX.NAMING.INITIALCONTEXT.GETURLORDEFAULTINITCTX (initialcontext.java:325) at
    Javax.naming.InitialContext.lookup (initialcontext.java:392) at
    com.iblog.util.dbpoolutil.<clinit> ( DBPOOLUTIL.JAVA:34)

Solution:

The exception information above is caused by the absence of Jndi initialization in the configuration file

If none of the following problems exist

1. To check if the URL parameter of the connection database in the configuration file is correct 2. and whether the normal package 3 is imported. Check to Conf/server.xml file in Tomcat, check to set usenaming= "false", if yes, remove

2. That is tested by the main method, which does not support such a test method that the program will run in Tomcat to find the appropriate data source. [I made such a mistake during the test that caused the above error to appear]

<%@ page language= "java" pageencoding= "UTF-8" contenttype= "text/html; Charset=utf-8 "%>

<%@ page import= "java.sql.*"%>     
<%@ page import= "javax.naming.*"%>     
<%@ page import= " Javax.sql.DataSource "%>

Import= "java.sql.*, javax.sql.*, javax.naming.* 

<GlobalNamingResources>

    <resource name= "Userdatabase" auth= "Container" type= "Org.apache.catalina.UserDatabase"
              User database, can be updated and saved "
              factory=" Org.apache.catalina.users.MemoryUserDatabaseFactory
              " Pathname= "Conf/tomcat-users.xml"/>
</GlobalNamingResources>
 
Add the associated pool configuration information to the node as follows

<GlobalNamingResources>

<resource name= "Userdatabase" auth= "Container" type= "Org.apache.catalina.UserDatabase" desc ription= "User database" can be updated and saved "factory=" Org.apache.catalina.users.MemoryUserDatabaseF Actory "Pathname=" Conf/tomcat-users.xml/> <!--Configure the MySQL database connection pool, additional steps need to be made 
              The flash is to put MySQL Java-driven classes into Tomcat's lib directory--> <resource name= "Jdbc/mysqlds"  
              Auth= "Container" type= "Javax.sql.DataSource" username= "root" password= "root" maxidle= "maxwait=" "10000" maxactive= "Driverclassname=" Com.mysql.jdbc.Driver "url=" Jdbc:mysql://127.0.0.1:3306/db_blog "/> </GlobalNamingResources>
 
Add the following in the <Context></Context> node in Tomcat's Conf/context.xml file

<resourcelink name= "Jdbc/mysqlds" global= "Jdbc/mysqlds" type= "Javax.sql.DataSource"/>
 
Then configure the Web.xml in the Web-inf directory in the Web project

<resource-ref>

      <description>mysql database Connection Pool </description>
      <!--reference data source name, with Name attribute value in resource node configured in Tomcat jdbc/ Mysqlds "Consistent-->
      <res-ref-name>jdbc/mysqlds</res-ref-name>
      <!--resource type-->
      < res-type>javax.sql.datasource</res-type>
      <res-auth>Container</res-auth>
      < Res-sharing-scope>shareable</res-sharing-scope>
</resource-ref>
 
Once configured, the server needs to be restarted for the configuration to take effect.

Method Three: Configure virtual directories when conf/server.xml in Tomcat

When you configure a virtual directory, you add a pool configuration to the Context tab when you configure Server.xml under Conf.

Before saying this, let's say how to configure the virtual directory with Tomcat

Found in the tomcat\conf under the Server.xml.

 
In which to add:

<context path= "/WebSite" docbase= "F:/myweb" reloadable= "true" ></Context>
 
Attention:

Docbase to be changed into your project directory.

Path is the virtual path, access time, note: Be sure to add "/" debug recommended set to 0

Reloadable set to True.

This restarts Tomcat

The following configuration in the instance

<context path= "/WebSite" docbase= "D:/program Files/tomcat/apache-tomcat-6.0.33/webapps/iblog.war" True ">
</Context>
 
Next, add the pool configuration, as follows

<!--Configure virtual directory-->

<context path= "/WebSite" docbase= "D:/program Files/tomcat/apache-tomcat-6.0.33/webapps/iblog.war" True ">
            <resource name=" Jdbc/mysqlds " 
            auth= 
            " Container "type=" Javax.sql.DataSource " Username= "root" 
            password= "root" 
            maxidle= "maxwait=" 
            10000
            "maxactive=" Driverclassname= "Com.mysql.jdbc.Driver"
            url= "Jdbc:mysql://127.0.0.1:3306/db_blog"
            />
</ Context>
 
Start the server, test, note because we configured the path value to "/WebSite", we should access the paths of website. The following figure:


Method Four: Create a new file in the Meta-inf directory in the Web project Context.xml, write configuration

Note: Is the Meta-inf directory, not the Web-inf directory under the new

<?xml version= ' 1.0 ' encoding= ' utf-8 '?>

<Context>
    <resource name= "Jdbc/mysqlds" 
        auth= " 
        Container" type= "Javax.sql.DataSource" Username= "root" 
        password= "root" 
        maxidle= "maxwait=" 
        10000
        "maxactive=" Driverclassname= "Com.mysql.jdbc.Driver"
        url= "Jdbc:mysql://127.0.0.1:3306/db_blog"
        logabandoned= "true" />
</Context>
 
 
Then create a new Web.xml file in Web-inf, and then copy the following content, saving

<?xml version= "1.0" encoding= "UTF-8"?>

<web-app xmlns= "Http://java.sun.com/xml/ns/javaee"

Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"

Xsi:schemalocation= "Http://java.sun.com/xml/ns/javaee

Http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd "

version= "3.0"

Metadata-complete= "true" >


<display-name>test</display-name>

<welcome-file-list>

<welcome-file>test.jsp</welcome-file>

</welcome-file-list>

<resource-ref>

<description>db connection</description>

<res-ref-name>jdbc/mysqlds</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>