分为三个文件：第一个文件是引用ORACLE自带的连接池的类，第二个文件是连接SQL2K的类文件，第三个是一个JSP文件，用于显示结果
******************
OraPooledSQL.java
******************
______________________________________________________________________
/*
* Created on 2004-7-9
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package myclass.DBconn;
/**
* @author Ericsson
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
public class
{
private PooledConnection dbpool;
/**
@param ConnectionURL
连接名 如:jdbc:oracle:thin:@192.168.1.232:1521:orcl
@param UserID 用户名
@param PassWord 用户密码
*/
public OraPooledSQL(String ConnectionURL,String UserID,String PassWord)
{
try{
OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
ocpds.setURL(ConnectionURL);
ocpds.setUser(UserID);
ocpds.setPassword(PassWord);
dbpool = ocpds.getPooledConnection();
System.out.println("connect_test Success");
}
catch(Exception ex)
{
System.err.println("Error in PooledSQL-construct : ");
ex.printStackTrace(System.err);
}
}//end OraPooledSQL
//close dbpool
protected void finalize()
{
if( dbpool != null )
{
try
{
dbpool.close();
}
catch(Exception ex)
{
}
}
}
/**
用于更新、添加或删除的SQL语句
@param SQL SQL语句字串，如：insert into tablename values(id,......)
*/
public int Update(String SQL)
{
Statement stmt = null;
int rc = 0;
Connection connection = null;
try
{
connection = dbpool.getConnection();
stmt = connection.createStatement();
rc = stmt.executeUpdate(SQL);
}
catch( Exception ex )
{
System.err.println("Error in Update - OraPooledSQL : ");
ex.printStackTrace(System.err);
}
return rc;
} //end Update()
/**
用于查询的SQL语句
@param SQL SQL语句字串，如：select * from tablename
*/
public ResultSet Query(String SQL)
{
Statement stmt = null;
ResultSet rs = null;
Connection connection = null;
try
{
connection = dbpool.getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery(SQL);
}
catch( Exception ex )
{
System.err.println("Error in Query - SQLBean : ");
ex.printStackTrace(System.err);
}
return rs;
} //end Query
} //end Class
______________________________________________________________________
***************
DBconn.java
***************
______________________________________________________________________
/*
* Created on 2004-7-8
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package myclass.DBconn;
import java.sql.*;
/**
* @author Ericsson
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class DBconn {
String url = "jdbc:odbc:lmt";
String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
public static Connection con = null;
ResultSet rs = null;
Statement stmt;
public DBconn() {
try{
Class.forName(DBDriver);//加载数据库驱动程序
}//try
catch(java.lang.ClassNotFoundException e){//出错情况的处理
System.out.println("DBconn():" + e.getMessage());
}
}//DBconn()
/**
* executeQuery操作，用于数据查询，主要是Select
* @param sql 查询字段
* @return 数据集
* @throws SQLException 捕捉错误
*/
public ResultSet executeQuery(String sql){
rs = null;
try {
con = DriverManager.getConnection(url,"sa","");
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
}//try
catch(SQLException ex){//出错情况的处理
System.err.println("aq.executequery:" + ex.getMessage());
}
return rs;
}//executeQuery();
/**
* executeUpdate操作，用于数据更新，主要是Update，Insert
* @param sql 查询字段
* @throws SQLException 捕捉错误
*/
public void executeUpdate(String sql) throws SQLException {
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(sql);
}
catch (SQLException ex) {
ex.printStackTrace();
System.out.println("dbTrans.executeUpdate:"+ex.getMessage());
throw ex;
}
}
}//class
______________________________________________________________________
***************
db_test.jsp
***************
______________________________________________________________________
<%@ page contentType="text/html; charset=GBK" %>
<%@ page import = "java.net.URL"%>
<%@ page import = "java.sql.*"%>
<%@ page import = "myclass.DBconn.OraPooledSQL" %>
<html>
<head>
<title>数据库连接测试</title>
</head>
<jsp:useBean id="DBconn" scope="session" class="myclass.DBconn.DBconn" />
<jsp:setProperty name="DBconn" property="*" />
<body>
<%
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy年MM月dd日");
java.util.Date currentTime_1 = new java.util.Date();
out.print(formatter.format(currentTime_1)+"<br>");
OraPooledSQL orcl = new OraPooledSQL("jdbc:oracle:thin:@192.168.1.232:1521:orcl","webstat","khkj");
ResultSet orcl_rs = orcl.Query("SELECT * FROM s_table");
ResultSet Rs = DBconn.executeQuery("SELECT * FROM test");
%>
<table width="180" border="1" align="left" cellpadding="0" cellspacing="0" bordercolorlight="#C0C0C0" bordercolordark="#FFFFFF">
<tr align="center" bgcolor="#99CCFF"><td colspan="2">连接SQL2K测试</td></tr>
<%while(Rs.next()){ //循环显示查询结果 %>
<tr>
<td><%out.print(Rs.getString("name"));%></td>
<td><%out.print(Rs.getString("sex"));%></td>
</tr>
<%}
Rs.close();%>
</table>
<table width="180" border="1" align="left" cellpadding="0" cellspacing="0" bordercolorlight="#C0C0C0" bordercolordark="#FFFFFF">
<tr align="center" bgcolor="#99CCFF"><td colspan="2">oracle连接池测试</td></tr>
<%while(orcl_rs.next()){ //循环显示查询结果 %>
<tr>
<td><%out.print(orcl_rs.getString("name"));%></td>
<td><%out.print(orcl_rs.getString("caption"));%></td>
</tr>
<%}
orcl_rs.close();%>
</table>
</body>
</html>
______________________________________________________________________
