mysql database connection pool configuration tutorial

Step 1: write javabean

 
package withouttears.jdbc.db; 
import java.util.HashMap; 
import java.sql.*; 
//JNDI There are two core interfaces Context and DirContext .  
//Context Contains the basic name operation, and DirContext These operations are extended to the directory service.  
import javax.naming.Context; 
import javax.naming.InitialContext; 
// The connection factory for the database resource is javax.sql.DataSource Object,  
// It can create java.sql.Connection Database connection object.  
import javax.sql.DataSource; 
// At present you can learn from Java Developer link ( http://java.sun.com/products/jdbc/download.html#rowsetcobundle1_0 )  
// download CachedRowSet The implementation of the. After downloading and unzipping the installation file, the "rowset.jar" Put the file in your class directory.  
//CachedRowSet in sun.jdbc.rowset In the package.  
import sun.jdbc.rowset.CachedRowSet; 
/** 
*  The author: wiThouTTears 
*  Time: 2006-12-13 
* */ 
public class Database { 
/**************************************************************/ 
/*  Function: localhost 
*  Function: establish connection pool  
* */ 
private static DataSource localhost(){ 
DataSource ds=null; 
// in HashMap Through the get() In order to get value Through the put() To insert value .  
//ContainsKey() Is used to verify that the object already exists  
HashMap<Object,Object> cachedDs=new HashMap<Object,Object> (); 
if(cachedDs.containsKey("ds"))// Fetch the database connection in the idle state  
{ 
/*  in DataSource In which multiple database connections were previously established,  
*  These database connections are kept in the connection pool (Connect Pool) In the.  
* Java When the program accesses the database, it only needs to take the idle database connection out of the connection pool.  
*  When the program has finished accessing the database, put the database connection back into the connection pool.  
* */ 
ds = (DataSource)cachedDs.get("ds"); 
} 
else 
try 
{ 
/* in javax.naming It's provided in the package Context Interface,  
*  This interface provides a way to bind objects to their names and retrieve objects by name.  
* */ 
Context initCtx = new InitialContext(); 
//lookup(String name) : returns the object bound to the specified name , Get the database connection factory  
ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/testdb"); 
cachedDs.put("ds", ds); 
} 
catch(Exception e) 
{ 
e.printStackTrace(); 
} 
return ds; 
} 
/**************************************************************/ 
/*  Function: getConnection 
*  Function: library connection  
* */ 
private static Connection getConnection(){ 
Connection conn = null; 
try 
{ 
DataSource ds = localhost(); 
conn = ds.getConnection(); 
} 
catch(Exception e) 
{ 
e.printStackTrace(); 
} 
return conn; 
} 
/**************************************************************/ 
/*  Function: close 
*  Function: close the connection  
* */ 
private static void close(Connection conn) 
{ 
try 
{ 
if(conn != null) 
conn.close(); 
} 
catch(SQLException e) 
{ 
e.printStackTrace(); 
} 
} 
/**************************************************************/ 
/*  Function: executeQuery 
*  Function: data query  
* */ 
public static CachedRowSet executeQuery(String sql) 
{ 
Connection conn=null; 
CachedRowSet rs=null; 
try{ 
rs=new CachedRowSet(); 
conn=getConnection(); 
Statement stmt=conn.createStatement(); 
ResultSet rs1=stmt.executeQuery(sql); 
rs.populate(rs1); 
} 
catch(Exception e) 
{ 
//System.out.println(e.toString()); 
} 
finally{ 
try 
{ 
conn.close(); 
} 
catch(Exception ex) 
{} 
} return rs; 
} 
/**************************************************************/ 
/*  Function: executeUpdate 
*  Function: data update (add / To change the / Delete)  
* */ 
public static boolean executeUpdate(String sql) 
{ 
boolean bl; 
bl = false; 
Connection conn = getConnection(); 
try 
{ 
Statement stmt = conn.createStatement(); 
if(stmt.executeUpdate(sql) > 0) 
stmt.close(); 
bl = true; 
} 
catch(SQLException e) 
{ 
} 
finally 
{ 
close(conn); 
} 
return bl; 
} 
/**************************************************************/ 
} 

Compile get withouttears/db/Database class on E: / MyWorkSpace/test/WEB INF/classes, namely E: / MyWorkSpace/test/WEB INF/classes withouttears/db/Database class, be careful not to mistake.
Step 2: configure Tomcat (I use Tomcat 5.5.7)
In C: / Program Files/Tomcat 5.5.7 / conf/Catalina/localhost under new test. 1 xml, content is as follows: < Context docBase="E:/MyWorkSpace/test" path="/test" > < /Context >
Note: docBase is the location of your web file. I used E:/MyWorkSpace/test. path can be written, but it must be written under Linux, Windows can't be written under Windows, it's better to write. Here test. xml the specified folder does not like we normally use in C: / Program Files/Tomcat 5.5.7 / webapps/test, but 1 sample is intended use http: / / localhost: 8080 / test/to access, the IIS equivalent virtual directory, can be arbitrary.
2. Create context.xml under C:/Program Files/Tomcat 5.5.7/conf/ and create WEB-INF/web.xml under E:/MyWorkSpace/test.
context.xml

 
<!-- The contents of this file will be loaded for each web application --> 
<Context> 
<!-- Default set of monitored resources --> 
<WatchedResource>WEB-INF/web.xml</WatchedResource> 
<WatchedResource>META-INF/context.xml</WatchedResource> 
<!-- Uncomment this to disable session persistence across Tomcat restarts --> 
<!-- 
<Manager pathname="" /> 
--> 
<Resource name="jdbc/testdb" 
auth="Container" 
type="javax.sql.DataSource" 
driverClassName="com.mysql.jdbc.Driver" 
url="jdbc:mysql://localhost/mytestdb" 
username="root" 
password="157744375" 
maxActive="100" 
maxIdle="30" 
maxWait="10000" 
/> 
</Context> 

Note: the link pool configuration file so that we can read the jdbc/testdb name in content.xml using the localhost() function in Javabean class Database written in step 1
web.xml

 
<?xml version="1.0" encoding="gbk"?> 
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> 
<display-name> 
test</display-name> 
<welcome-file-list> 
<welcome-file>test.jsp</welcome-file> 
</welcome-file-list> 
<!-- JSPC servlet mappings start --> 
<!-- JSPC servlet mappings end --> 
</web-app> 

Note: web.xml puts the default home page of web (e.g. test.jsp or index.jsp) and the servlet mapping is used in the program, whether it is not used here.
Step 3: write test.jsp

 
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> 
<%@page import="java.sql.*"%> 
<%@page import="withouttears.jdbc.db.*"%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=gbk"> 
<title>Insert title</title> 
</head> 
<body> 
<% 
String sql=null; 
sql="select * from table_test"; 
ResultSet rs=Database.executeQuery(sql); 
try{ 
while(rs.next()){ 
%> 
 Name: <%=rs.getString("name")%><br> 
 Telephone: <%=rs.getString("mobile")%><br> 
<%}}catch(Exception e){} %> 
</body> 
</html> 

Step 4: test