Configuration of Tomcat sq2000 database connection pool in JSP

Environment: 1. Database: Microsoft SQL Server 2000

2. Database DRIVER: net. SourceForge. jtds. JDBC. Driver

JNDI (Java Naming and Directory Interface) Overview:

Tomcat4 (5) provides an initialcontext implementation instance that is compatible with Java Enterprise Edition application services. Its initial data settings are in the $ catalina_home/CONF/server. xml file and may be referenced by the following elements in the Web application environment description (/WEB-INF/Web. XML:

1) <env-entry> -- Environment entry, which sets how the application operates.

2) <resource-ref> -- resource parameters, such as database drivers, javamail sessions, and custom factory.

3) <resource-env-ref> -- Used in servlet 2.4 to simplify setting resource resources that do not require authentication information, such as environment parameters and resource-ref variables.

Initialcontext is set during webpage application initialization to support webpage application components. All entries and resources are stored in the Java: COMP/ENV segment of the JNDI namespace. Click the following URL for more information:

1) Java Naming and Directory Interface (Java Naming and Directory Interface)

2) J2EE platform description (J2EE Platform specification)

Set JNDI Resources

To set a JNDI resource, use the following identifier in the $ catalina_home/CONF/server. xml file:

1) <environment> -- set the name and value of the variable JNDI initialcontext entry of the domain (<env-entry> is equivalent to the one mentioned above ).

2) <resource> -- set the name and type of the available resources of the application (<resource-ref> is equivalent to the one mentioned above ).

3) <resourceparams> -- set the name of the Java resource factory or the an attribute to be used.

4) <resourcelink> -- add a link to the global JNDI context.

The above flags must be placed between <context> and </context> (for specialized web applications), <defaultcontext>, and </defaultcontext>.

In addition, the web application Descriptor (/WEB-INF/web. the name and value in XML are also set in the initial environment (initial context). When allowed by the <environemt> element value, the initial value is reset.

You can set global variables in <Server> child elements <globalnamingresources>.

Database Connection Pool Overview:

Database connection is a key, limited, and expensive resource, which is particularly prominent in the Web applications of multiple users. 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 designed to address this issue.

The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to repeat an existing database connection instead of reestablishing one; release the database connection with idle time exceeding the maximum idle time to avoid database connection omissions caused by the absence of the database connection. This technology can significantly improve the performance of database operations.

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 following factors must be taken into account when setting the minimum number of connections and maximum number of connections in the database connection pool:

1) The minimum number of connections is the 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;

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

3) if the difference between the minimum number of connections and the maximum number of connections is too large, the first connection request will be profitable. A connection request that exceeds the minimum number of connections is equivalent to creating a new database connection. However, these database connections greater than the minimum number of connections will not be released immediately after use. They will be placed in the connection pool for reuse or released after idle timeout.

Prerequisites for configuring the Tomcat database connection pool:

1. the Java Runtime Environment must be installed;

2. You must have an SQL Server2000 Database Server (which may not be local );

3. You must have jtds. jar in the $ catalina_home/common/lib directory ). It is used because Microsoft's Java SQL Server driver does not support secondary queries and can be found online. Jtds-0.6.jar is currently used.

Set the database connection pool in $ catalina_home/CONF/server. xml:

The following is the configuration code, which must be placed between

<Context Path = "/Quality" docbase = "quality" DEBUG = "0" reloadable = "true" crosscontext = "true"> <Logger classname = "org. Apache. Catalina. Logger. filelogger" prefix = "localhost_quality_log." suffix = ". txt" timestamp = "true"/> <Resource Name = "JDBC/connectdb" auth = "Container" type = "javax. SQL. datasource"/> <Resourceparams name = "JDBC/connectdb"> <Parameter> <Name> maxactive </Name> <! -- Maximum number of DB connections in pool. set to 0 for no limit. --> <Value> 100 </value> </Parameter> <Parameter> <Name> maxidle </Name> <! -- Maximum number of idle dB connections to retain in pool. set to 0 for no limit. --> <Value> 30 </value> </Parameter> <Parameter> <Name> maxwait </Name> <! -- Maximum time to wait for a DB connection to become available in ms. An exception is thrown if this timeout is exceeded. Set to-1 to wait indefinitely. --> <Value> 10000 </value> </Parameter> <Parameter> <Name> removeabandoned </Name> <! -- Abandoned dB connections are removed and recycled --> <Value> true </value> </Parameter> <Parameter> <Name> removeabandonedtimeout </Name> <! -- Use the removeabandonedtimeout parameter to set the number of seconds a DB connection has been idle before it is considered abandoned. --> <Value> 60 </value> </Parameter> <Parameter> <Name> logabandoned </Name> <! -- Log a stack trace of the Code which abandoned --> <Value> false </value> </Parameter> <Parameter> <Name> factory </Name> <! -DBCP basic datasource factory --> <Value> org. Apache. commons. DBCP. basicperformancefactory </value> </Parameter> <Parameter> <Name> username </Name> <! -- Database User Name --> <Value> iorishinier </value> </Parameter> <Parameter> <Name> password </Name> <! -- User Password --> <Value> mypasswd </value> </Parameter> <Parameter> <Name> driverclassname </Name> <! -- Database Driver Class Name --> <Value> net. SourceForge. jtds. JDBC. Driver </value> </Parameter> <Parameter> <Name> URL </Name> <! -- Database address --> <Value> JDBC: jtds: sqlserver: // 127.127.127.127: 1433/northwind </value> </Parameter> </Resourceparams> </Context>

The following describes some parameters:

<Context Path = "/Quality" docbase = "quality" DEBUG = "0" reloadable = "true" crosscontext = "true">

Where:

1) path specifies the path. Here the quality directory under $ catalina_home/webapps is set;

2) root directory of the docbase file.

3) reloader: whether to re-Compile when the webpage is updated.

4) Maximum number of database connections in the maxactive connection pool. If it is set to 0, there is no limit.

5) Maximum idle time for maxidle database connection. When the idle time is exceeded, the database connection is marked as unavailable and then released. If it is set to 0, there is no limit.

6) maxwait maximum connection wait time. If this time is exceeded, an exception occurs. If it is set to-1, there is no limit.

7) removeabandoned recycles the abandoned (usually forgot to release) database to connect to the connection pool.

8) When the removeabandonedtimeout database is connected for too long, it will not be considered abandoned and the connection pool will be withdrawn.

9) logabandoned records the collection of abandoned database connections to the log.

10) driverclassname JDBC driver.

11) URL database connection string

Set the referenced resource in $ catalina_home/webapps/quality/WEB-INF/Web. xml:

The following configuration code must be placed in <web-app> and </Web-app>.

<! -- Database Config start -->

<Resource-ref>

<Description> connectdb test </description>

<Res-ref-Name> JDBC/connectdb </RES-ref-Name>

<Res-type> javax. SQL. datasource </RES-type>

<Res-auth> container </RES-auth>

</Resource-ref>

<! -- Database Config end -->

The following is a description of the parameters:

1) Description: Description of the referenced resource.

2) RES-ref-name Resource Name. See <resourceparams name = "JDBC/connectdb">

3) RES-type resource type. See <Resource Name = "JDBC/connectdb" auth = "Container" type = "javax. SQL. datasource"/>

Use resources in JSP:

This is part of the JSP webpage file code in a subdirectory under $ catalina_home/webapps/quality:

<% @ Page contenttype = "text/html; charset = GBK" %> <% @ Page errorpage = "error. jsp" %> <% @ Page import = "javax. Naming. *" %> <% @ Page import = "javax. SQL. *" %> <% @ Page import = "Java. SQL. *" %> <HTML> <Head> </Head> <Body> <% .................. .................. // Database operations Context CTX = NULL; Connection CNN = NULL; Statement stmt = NULL; Resultset rs = NULL; Try { CTX = new initialcontext (); If (CTX = NULL) Throw new exception ("no matching environment "); Datasource DS = (datasource) CTX. Lookup ("Java: COMP/ENV/jdbc/connectdb "); If (DS = NULL) Throw new exception ("no matching database "); CNN = Ds. getconnection (); Stmt = CNN. createstatement (resultset. type_scroll_sensitive, resultset. concur_read_only ); Rs1_stmt.exe cutequery ("select * From Table1 "); .................. .................. } Finally { If (RS! = NULL) Rs. Close (); If (stmt! = NULL) Stmt. Close (); If (CNN! = NULL) CNN. Close (); If (CTX! = NULL) CTX. Close (); } %> </Body> </Html>

Code Description:

Datasource DS = (datasource) CTX. Lookup ("Java: COMP/ENV/jdbc/connectdb ");

The above sentence applies the resources just created.

Resources should be released after use, especially context resources. For details, see the finally code segment in try-catch-finally. This is a good habit. When a resource is released, if the upper-level resource is released, the lower-level resource is released first. For example, if CTX is released, the resource release sequence is Rs, stmt, CNN, and CTX. In other words, if CTX is released, RS, stmt, and CNN will be unavailable.

The resource release here only returns the database connection back to the connection pool, rather than actually releasing the resource. For details, see database connection pool overview.

By iorishinier (original)