Advanced application of JDBC in servlet development

Servlet| Advanced Link Database

JDBC uses the database URL to describe the database driver. The database URL is similar to a generic URL, but sun simplifies the definition with the following syntax:

Jdbc::[node]/[database]

Where the Child Protocol (subprotocal) defines the driver type, node provides the location and port number of the network database, followed by an optional parameter. For example:

String url= "Jdbc:inetdae:myserver:1433?language=us-english&sql7=true"

Represents the use of the Inetdae driver to connect to the MyServer database server on port 1433, select the language as U.S. English, and the version of the database is MSSQL Server 7.0.

The Java application loads a driver class by specifying DriverManager. The syntax is as follows:

Class.forName ("");

Or

Class.forName (""). newinstance ();

Then, DriverManager creates a specific connection:

Connection connection=drivermanager.getconnection (Url,login,password);

The connection interface connects the database by specifying the database location, login name, and password. Connection interface to create a query for the execution of a statement instance:

Statement stmt=connection.createstatement ();

Statement has a variety of methods (APIs), such as Executequery,execute, that can return a query's result set. The result set is a ResultSet object. Specific can be developed through the JDBC document view. can be downloaded at Sun's site

The following example illustrates:

Import java.sql.*; Enter JDBC Package

String url = "jdbc:inetdae:myserver:1433";//host name and port
String login = "user";//Login name
String password = "";//Password

try {
Drivermanager.setlogstream (System.out); file://open a stream for displaying some information
FILE://calls the driver, whose name is Com.inet.tds.TdsDriver
File://Class.forName ("Com.inet.tds.TdsDriver");
file://Setting timeout
Drivermanager.setlogintimeout (10);
file://Open a connection
Connection Connection = drivermanager.getconnection (Url,login,password);
file://get the database driver version

DatabaseMetaData CONMD = Connection.getmetadata ();
System.out.println ("Driver name:\t" + conmd.getdrivername ());
System.out.println ("Driver version:\t" + conmd.getdriverversion ());

FILE://Select Database
Connection.setcatalog ("MyDatabase");

FILE://Create statement

Statement st = Connection.createstatement ();

file://Execute Query

ResultSet rs = st.executequery ("SELECT * from MyTable");

file://get results, output to screen

while (Rs.next ()) {
for (int j=1; J<=rs.getmetadata (). getColumnCount (); j + +) {
System.out.print (Rs.getobject (j) + "T");
}
System.out.println ();
}

file://Close Object
St.close ();
Connection.close ();
catch (Exception e) {
E.printstacktrace ();
}

　　 Establish a connection pool

A dynamic web site frequently obtains data from the database to form an HTML page. A database operation occurs every time a page is requested. But connecting to a database is a time-consuming task, because requesting a connection requires establishing communication, allocating resources, and authenticating permissions. These jobs are rarely completed in a two-second period. Therefore, building a connection and then using this connection in subsequent queries can greatly improve performance. Because the servlet can maintain state across different requests, the use of a database connection pool is a direct solution.

The servlet resides in the server's process space to maintain database connections easily and lastingly. Next, we introduce a complete implementation of the connection pool. In the implementation, there is a connection pool manager that manages the connection pool object, where each connection pool maintains a set of database connection objects that can be used by any servlet.

One, database connection pool class Dbconnectionpool, provides the following method:

1, from the pool to obtain an open connection;

2. Return a connection to the pool;

3. Release all resources at shutdown and close all connections.

In addition, Dbconnectionpool handles connection failures, such as timeouts, communication failures, and limits on the number of connections in the pool based on predefined parameters.

Second, the manager class, Dbconnetionmanager, is a container to encapsulate the connection pool, and manage the connection pool. It has the following methods:

1. Invoke and register all JDBC drivers;

2, according to the parameter table to create Dbconnectionpool objects;

3, mapping the name of the connection pool and dbconnectionpool examples;

4. When all the connecting customers exit, close all connection pool.

The implementation of these classes and how to use the connection pool in the servlet are explained in the following articles

The Dbconnectionpool class represents a database connection pool identified by a URL. As we mentioned earlier, the URL for JDBC consists of three parts: the protocol identifier (always JDBC), the Child protocol identifier (for example, Odbc.oracle), and the database identity (related to a particular database). The connection pool also has a name for the client program to reference. In addition, the connection pool also has a username, a password, and a maximum number of allowed connections. If the Web application allows all users to use certain database operations, while others are limited, you can create two connection pools, with the same URL, different user name and password, to handle two different kinds of operation permissions separately. The Dbconnectionpool is described in detail as follows:

Iii. Construction of Dbconnectionpool

constructor to obtain all of the above parameters:

Public Dbconnectionpool (string name, string URL, String user,
String password, int maxconn) {
THIS.name = name;
This. url = URL;
This.user = user;
This.password = password;
This.maxconn = Maxconn;
}

Save all parameters in the instance variable.

Four, open a connection from the pool

Dbconnectionpool provides two ways to check for connections. Both methods return an available connection and create a new connection if there is no extra connection. If the maximum number of connections has been reached, the first method returns NULL, and the second method waits for a connection to be freed by another process.

Public synchronized Connection getconnection () {
Connection con = null;
if (freeconnections.size () > 0) {
Pick the ' the ' the ' the ' Connection in the Vector
To get Round-robin usage
Con = (Connection) freeconnections.firstelement ();
Freeconnections.removeelementat (0);
try {
if (con.isclosed ()) {
Log ("Removed bad connection from" + name);
Try again recursively
con = getconnection ();
}
}
catch (SQLException e) {
Log ("Removed bad connection from" + name);
Try again recursively
con = getconnection ();
}
}
else if (maxconn = 0 | | Checkedout < maxconn) {
con = newconnection ();
}
if (con!= null) {
checkedout++;
}
return con;
}

All idle connection objects are kept in a vector called freeconnections. If there is at least one idle connection, getconnection () returns the first connection. Below, you will see that the connection released by the process is returned to the end of the freeconnections. This minimizes the risk that the database will be shut down accidentally because one connection is not active.

Before returning to the customer, isclosed () checks to see if the connection is valid. If the connection is closed, or if an error occurs, the method recursively calls for another connection.

If no connection is available, the method checks whether the maximum number of connections is set to 0 to indicate an unlimited number of connections, or the maximum number of connections is reached. If a new connection can be created, a new connection is created. Otherwise, NULL is returned.

Method Newconnection () is used to create a new connection. This is a private method that determines whether a new connection can be created based on the user name and password.

Private Connection newconnection () {
Connection con = null;
try {
if (user = null) {
con = drivermanager.getconnection (URL);
}
else {
con = drivermanager.getconnection (URL, user, password);
}
Log ("Created a new connection in pool" + name);
}
catch (SQLException e) {
Log (E, "Can not create a new connection for" + URL);
return null;
}
return con;
}

JDBC's DriverManager provides a series of getconnection () methods that allow you to create a connection using a URL and a parameter such as a username, password, and so on.

The second getconnection () method comes with a timeout parameter timeout that specifies the number of milliseconds that the customer is willing to wait for a connection. This method invokes the previous method.

Public synchronized Connection getconnection (long timeout) {
Long starttime = new Date (). GetTime ();
Connection con;
while (con = getconnection ()) = = null) {
try {
Wait (timeout);
}
catch (Interruptedexception e) {}
if ((New Date (). GetTime ()-starttime) >= timeout) {
Timeout has expired
return null;
}
}
return con;
}

The local variable StartTime initializes the current time. A while loop first attempts to obtain a connection, and if it fails, the wait () function is called to await the time required. As you'll see later, the Wait () function returns when another process calls notify () or Notifyall (), or waits until the time elapses. To determine why the wait () is returned, we use the start time minus the current time to check for greater than timeout. Returns NULL if the result is greater than timeout, otherwise, call the getconnection () function here.

Five, return a connection to the pool

There is a freeconnection method in the Dbconnectionpool class that returns the connection as an argument, returning it to the connection pool.

Public synchronized void freeconnection (Connection con) {
Put the connection in the Vector
Freeconnections.addelement (con);
checkedout--;
Notifyall ();
}

The connection is added at the end of the freeconnections vector, the number of connections is reduced by 1, and the call to the Notifyall () function informs other waiting customers that they now have a connection.

Vi. closing

Most servlet engines provide a complete shutdown method. The database connection pool needs to be notified to properly shut down all connections. Dbconnectionmanager is responsible for coordinating shutdown events, but the connection is closed by each connection pool itself. Method Relase () is called by Dbconnectionmanager.

Public synchronized void release () {
Enumeration allconnections = Freeconnections.elements ();
while (Allconnections.hasmoreelements ()) {
Connection con = (Connection) allconnections.nextelement ();
try {
Con.close ();
Log ("Closed connection for Pool" + name);
}
catch (SQLException e) {
Log (E, "Can not close connection for pool" + name);
}
}
Freeconnections.removeallelements ();
}

This method traverses the freeconnections vector to close all connections.

Dbconnetionmanager constructors are private functions to prevent other classes from creating instances of them.

Private Dbconnectionmanager () {

Init ();

}

The Dbconnetionmanager client invokes the getinstance () method to get a reference to a single instance of the class.

Static synchronized public Dbconnectionmanager getinstance () {
if (instance = = null) {
Instance = new Dbconnectionmanager ();
}
clients++;
return instance;
}

　　 Connection Pool Usage instances

A single instance is created on the first call, and a subsequent call returns the static application of that instance. A counter records all the number of customers until the customer releases the reference. This counter is used later to coordinate the shutdown of the connection pool.

First, initialization

The constructor calls a private init () function to initialize the object.

private void init () {
InputStream is = GetClass (). getResourceAsStream ("/db.properties");
Properties Dbprops = new properties ();
try {
Dbprops.load (IS);
}
catch (Exception e) {
System.err.println ("Can not read the properties file." + "Make sure db.properties are in the CLASSPATH");
Return
}

String logFile = Dbprops.getproperty ("LogFile",
"DBConnectionManager.log");
try {
Log = new PrintWriter (new FileWriter (LogFile, True), true);
}
catch (IOException e) {
System.err.println ("Can not open the log file:" + logFile);
Log = new PrintWriter (SYSTEM.ERR);
}
Loaddrivers (Dbprops);
Createpools (Dbprops);
}

Method getResourceAsStream () is a standard method for opening an external input file. The location of the file depends on the class loader, and the standard class loader starts the search from Classpath. The Db.properties file is a porperties-formatted file that holds the Key-value pairs defined in the connection pool. Some of the following commonly used properties can be defined:

Drivers the list of JDBC drivers separated by spaces

LogFile the absolute path of the log file

Other properties are also used in each connection pool. These properties begin with the name of the connection pool:

. The jdbc URL for the URL database

. maxconn maximum number of connections. 0 represents Infinity.

. user Connection Pool username

. Password related passwords

The URL property is required and the other attributes are optional. The username and password must match the database defined.

The following is an example of a db.properties file under the Windows platform. There is a INSTANTDB connection pool and a data source for an Access database that is connected via ODBC, named Demo.

Drivers=sun.jdbc.odbc.jdbcodbcdriver Jdbc.idbdriver

Logfile=d:\\user\\src\\java\\dbconnectionmanager\\log.txt

Idb.url=jdbc:idb:c:\\local\\javawebserver1.1\\db\\db.prp

idb.maxconn=2

Access.url=jdbc:odbc:demo

Access.user=demo

Access.password=demopw

Note that the backslash must be double written under the Windows platform.

Initialization Method Init () creates a Porperties object and loads the Db.properties file, and then reads the log file properties. If the log file is not named, it is created in the current directory using the default name DBConnectionManager.log. In this case, a system error is recorded.

Method Loaddrivers () registers and mounts all the specified JDBC drivers.

private void Loaddrivers (Properties props) {
String driverclasses = Props.getproperty ("Drivers");
StringTokenizer st = new StringTokenizer (driverclasses);
while (St.hasmoreelements ()) {
String driverclassname = St.nexttoken (). Trim ();
try {
Driver Driver = (Driver)
Class.forName (Driverclassname). newinstance ();
Drivermanager.registerdriver (driver);
Drivers.addelement (driver);
Log ("Registered JDBC driver" + driverclassname);
}
catch (Exception e) {
Log ("Can not register JDBC driver:" + Driverclassname + ", Exception:" + e);
}
}
}

Loaddrivers () uses StringTokenizer to divide the Dirvers property into separate driver strings and loads each driver into the Java Virtual machine. An instance of the driver is registered in the JDBC DriverManager and is added to a private vector drivers. Vector drivers is used to close and unregister all drivers.

The Dbconnectionpool object is then created by the private Method Createpools ().

private void Createpools (Properties props) {
Enumeration propnames = Props.propertynames ();
while (Propnames.hasmoreelements ()) {
String name = (string) propnames.nextelement ();
if (Name.endswith (". url")) {
String poolname = name.substring (0, Name.lastindexof ("."));
String url = props.getproperty (poolname + ". url");
if (url = = null) {
Log ("No URL specified for" + poolname);
Continue
}
String user = Props.getproperty (poolname + ". User");
String password = props.getproperty (poolname + ". Password");
String maxconn = props.getproperty (poolname + ". Maxconn", "0");
int Max;
try {
max = integer.valueof (maxconn). Intvalue ();
}
catch (NumberFormatException e) {
Log ("Invalid maxconn value" + Maxconn + "for" + poolname);
max = 0;
}
Dbconnectionpool pool = new Dbconnectionpool (poolname, url, user, password, max);
Pools.put (poolname, pool);
Log ("initialized pool" + poolname);
}
}
}

An enumeration object holds all the property names, and if the property name ends with a. URL, it means that a connection pool object needs to be instantiated. The created connection pool object is saved in a Hashtable instance variable. The connection pool name as the index, and the connection pool object as a value.

Ii. getting and returning connections

Dbconnectionmanager provides getconnection () methods and Freeconnection methods that are used by client programs. All methods are parameterized by the connection pool name and call a specific connection pool object.

Public Connection getconnection (String name) {
Dbconnectionpool pool = (dbconnectionpool) pools.get (name);
if (pool!= null) {
return Pool.getconnection ();
}
return null;
}

Public Connection getconnection (String name, long time) {
Dbconnectionpool pool = (dbconnectionpool) pools.get (name);
if (pool!= null) {
Return pool.getconnection (time);
}
return null;
}

public void Freeconnection (String name, Connection con) {
Dbconnectionpool pool = (dbconnectionpool) pools.get (name);
if (pool!= null) {
Pool.freeconnection (con);
}
}

Third, close

Finally, a release () method is used to close the connection pool in good condition. Each Dbconnectionmanager customer must invoke the GetInstance () method reference. There is a counter that tracks the number of customers. Method Release () is invoked when the customer shuts down, and the technical device is reduced by 1. When the last client is released, Dbconnectionmanager closes all connection pools.

Public synchronized void release () {
Wait until called by the last client
if (--clients!= 0) {
Return
}

Enumeration allpools = Pools.elements ();
while (Allpools.hasmoreelements ()) {
Dbconnectionpool pool = (dbconnectionpool) allpools.nextelement ();
Pool.release ();
}

Enumeration alldrivers = Drivers.elements ();
while (Alldrivers.hasmoreelements ()) {
Driver Driver = (Driver) alldrivers.nextelement ();
try {
Drivermanager.deregisterdriver (driver);
Log ("deregistered JDBC driver" + driver.getclass (). GetName ());
}
catch (SQLException e) {
Log (E, "Can not deregister JDBC driver:" + driver.getclass (). GetName ());
}
}
}

When all connection pooling is turned off, all JDBC drivers are also logged off.

　　The role of the connection pool

Now we're going to combine the Dbconnetionmanager and Dbconnectionpool classes to explain the use of connection pools in the servlet:

First, a brief introduction to the lifecycle of the servlet:

The Servlet API defines the servlet lifecycle as follows:

1, the Servlet is created and initialized (init () method).

2, for 0 or more customer calls to provide services (service () method).

3, the servlet is destroyed, memory is reclaimed (Destroy () method).

Examples of using connection pooling in the servlet

The typical performance of a servlet using a connection pool in three phases is:

1. In Init (), call Dbconnectionmanager.getinstance () and save the returned reference in the instance variable.

2. In Sevice (), call getconnection (), perform a series of database operations, and then call Freeconnection () to return the connection.

3. In Destroy (), call Release () to free all resources and close all connections.

The following example shows how to use a connection pool.

Import java.io.*;
Import java.sql.*;
Import javax.servlet.*;
Import javax.servlet.http.*;

public class Testservlet extends HttpServlet {
Private Dbconnectionmanager connmgr;

public void init (ServletConfig conf) throws Servletexception {
Super.init (conf);
Connmgr = Dbconnectionmanager.getinstance ();
}

public void Service (HttpServletRequest req, httpservletresponse Res)
Throws IOException {
Res.setcontenttype ("text/html");
PrintWriter out = Res.getwriter ();
Connection con = connmgr.getconnection ("IDB");
if (con = = null) {
Out.println ("Cant get connection");
Return
}
ResultSet rs = null;
ResultSetMetaData MD = NULL;
Statement stmt = null;
try {
stmt = Con.createstatement ();
rs = Stmt.executequery ("SELECT * from EMPLOYEE");
md = Rs.getmetadata ();
Out.println ("Employee data");
while (Rs.next ()) {
Out.println ("");
for (int i = 1; i < Md.getcolumncount (); i++) {
Out.print (rs.getstring (i) + ",");
}
}
Stmt.close ();
Rs.close ();
}
catch (SQLException e) {
E.printstacktrace (out);
}
Connmgr.freeconnection ("IDB", con);
}
public void Destroy () {
Connmgr.release ();
Super.destroy ();
}
}