Configuration environment of tomcat's SQL Server2000 database connection pool in JSP:
1. Database: Microsoft SQL Server 2000
2. Database driver: net.sourceforge.jtds.jdbc.Driver
JNDI (Java Naming and Directory Interface) overview:
Tomcat4(5) provides a JNDI--InitialContext implementation example that is compatible with Java Enterprise Edition application services. Its initial data is set 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, set how the application operates.
2) <resource-ref>--Resource parameters, usually database drivers, JavaMail Session, custom class factories, etc.
3) <resource-env-ref>--In Servlet 2.4, it is used to simplify the setting of resources that do not require authentication information, such as environment parameters and resource-ref variables.
InitialContext is set when the web application is initialized to support web application components. All entries and resources are placed in the java:comp/env section of the JNDI namespace. Click on the following URLs for more information:
1) Java Naming and Directory Interface
2) J2EE Platform Specification
Setting up JNDI resources To set up JNDI resources, use the following identifiers in the $CATALINA_HOME/conf/server.xml file:
1) <Environment>--Set the name and value of a variable JNDI InitialContext entry in the domain (equivalent to the <env-entry> mentioned above).
2) <Resource>--Set the name and type of resources available to the application (equivalent to <resource-ref> mentioned above).
3) <ResourceParams>--Set the name of the Java resource class factory or the JavaBean attribute to be used.
4) <ResourceLink>--Add a link to the global JNDI environment (JNDI Context).
The above identifiers must be placed between <Context> and </Context> (for specialized web applications) or between <DefaultContext> and </DefaultContext>.
In addition, the names and values set in the Web Application Descriptor (/WEB-INF/web.xml) are also set in the Initial Context, when enabled by the <Environemt> element value. is reset to its initial value.
Global variables can be set in the <GlobalNamingResources> of the <Server> child element.
Database connection pool overview:
Database connections are 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 and affect the performance indicators of the program. The database connection pool was proposed to address this problem.
The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re-establishing one; release database connections whose idle time exceeds the maximum idle time to avoid errors due to failure to release the database connection. Caused by missing database connection. This technology can significantly improve the performance of database operations.
The database connection pool will create a certain number of database connections and put them into the connection pool during initialization. The number of these database connections is set by the minimum number of database connections. Regardless of whether these database connections are used or not, the connection pool will always be guaranteed to have at least this 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 from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue. The setting of the minimum number of connections and the maximum number of connections in the database connection pool should take into account the following factors:
1) The minimum number of connections is the number of database connections maintained by the connection pool, so if the application does not use a large 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 the connection pool can apply for. If the database connection request exceeds this number, 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, and subsequent connection requests exceeding the minimum number of connections are equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections will not be released immediately after use. They will be placed in the connection pool to wait for reuse or be released after the idle timeout.
Prerequisites for configuring Tomcat database connection pool:
1. Java runtime environment must be installed;
2. There must be a SQL Server2000 database server (it does not need to be local);
3. You must have jtds.jar and place it in the $CATALINA_HOME/common/lib directory (only here). It is used because Microsoft's Java SQL Server driver does not support secondary queries, which can be found online. Currently using jtds-0.6.jar.
Set the database connection pool in $CATALINA_HOME/conf/server.xml:
The following is the configuration code, which must be placed between <Host> and </Host>.
<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.BasicDataSourceFactory</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 is a description of some parameters:
<Context path="/quality" docBase="quality" debug="0" reloadable="true" crossContext="true">
in:
1) path specifies the path, the quality directory set here is $CATALINA_HOME/webapps;
2) docBase file root directory.
3) reloader Whether to recompile when the web page is updated.
4) maxActive The maximum number of database connections in the connection pool. Set to 0 for no limit.
5) maxIdle The maximum idle time of the database connection. Beyond this idle time, the database connection will be marked as unavailable and then released. Set to 0 for no limit.
6) maxWait is the maximum waiting time for establishing a connection. If this time is exceeded, an exception will be received. Set to -1 to indicate no limit.
7) removeAbandoned recycles abandoned (usually forgotten to release) database connections into the connection pool.
8) removeAbandonedTimeout If the database connection is not used for a long time, it will be considered abandoned and returned to the connection pool.
9) logAbandoned logs the recycling of abandoned database connections.
10) driverClassName JDBC driver.
11) The url database connection string sets the referenced resource in $CATALINA_HOME/webapps/quality/WEB-INF/web.xml:
The following is the configuration code, which 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 the necessary description of the parameters:
1) description A description of the referenced resource.
2) res-ref-name resource name. See <ResourceParams name="jdbc/connectDB"> above
3) res-type resource type. See <Resource name="jdbc/connectDB" auth="Container" type="javax.sql.DataSource"/> above
Using resources in JSP:
This is part of the code for the jsp web page file in a certain 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);
rs=stmt.executeQuery("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 set.
It is a good habit to release resources after use, especially Context resources. See the finally code segment in the try-catch-finally section. If the upper-level resources are released during resource release, the lower-level resources will be released first. For example: if ctx is released, the resource release order will be rs, stmt, cnn, ctx. In other words, if ctx is released, rs, stmt and cnn will be unavailable.
The release of resources here only returns the database connection to the connection pool, and does not actually release the resources. See the overview of database connection pools.