怎样建立连接池
在频繁数据的存取中，一般是采用连接池
基本原理我是知道，但是具体该怎么做呢？不太清楚。
谁能帮帮我啊？
问题点数：100、回复次数：8Top
1 楼asdmonster（呆鸟四号）回复于 2002-11-18 12:12:24 得分 0
在线等待啊！！！！Top
2 楼beyond_xiruo（CorruptionException）回复于 2002-11-18 12:23:42 得分 30
servlet连接池的例子
***************************************
import javax.servlet.* ;
import javax.servlet.http.* ;
import java.io.* ;
import java.sql.* ;
import java.util.Vector;
import oracle.jdbc.driver.*;
import java.util.Enumeration;
import java.util.Properties;
import com.unitech.connectionpool.* ;
public class dbTest extends HttpServlet {
//Initialize global variables
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
// 数据库连接：Connetcion conn = null ;
Connection conn = null ;
//数据库查询内容执行：Statement stment = null ;
Statement stment = null ;
// 数据库连接池的初始化
DBConnectionManager connMgr = DBConnectionManager.getInstance();
//初始化数据库连接池，并且获取数据库连接
private void initDatabaseDriver () {
conn = connMgr.getConnection("oracle");
if (conn == null) {
System.out.println("数据库连接失败。");
return;
}
try {
stment = conn.createStatement();
}
catch (SQLException e) {
e.printStackTrace() ;
}
}
//释放数据库连接
private void freeConnectionPool() {
connMgr.freeConnection("oracle", conn) ;
}
//获取记录集，并返回给VERTOR V
public Vector getForumList() {
String[] s = {"","","","",""} ;//与选取的列数相等。
Vector v = new Vector() ;
this.initDatabaseDriver();
try{
String queryStr = null ;
queryStr = "SELECT BBS_ID,BBS_NAME,DESCRIPTION,MANAGER_ID, CREATE_DATE FROM BBS WHERE IS_SYSTEM='0' ORDER BY CREATE_DATE DESC" ;
ResultSet rSet = stment.executeQuery(queryStr) ;
while (rSet.next()) {
s[0] = Integer.toString(rSet.getInt("BBS_ID")) ;
s[1] = rSet.getString("BBS_NAME") ;
s[2] = rSet.getString("DESCRIPTION") ;
s[3] = rSet.getString("MANAGER_ID") ;
Timestamp createdate = rSet.getTimestamp("CREATE_DATE") ;
String tmp = createdate.toString() ;
s[4] = tmp.substring(0,(tmp.length()-2)) ;
v.addElement(s.clone());
}
rSet.close();
stment.close();
this.freeConnectionPool();
}
catch(Exception e) {
try {
stment.close();
this.freeConnectionPool();
}
catch(SQLException ee) {
ee.printStackTrace();
}
e.printStackTrace() ;
}
return v ;
}
//Process the HTTP Get request
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter out = new PrintWriter (response.getOutputStream());
response.setContentType("text/html");
out.println("");
out.println("The servlet has received a GET. This is the reply.");
out.println("");
out.println("");
// 将记录集循环输出到页面。
Vector v = new Vector() ;
v = this.getForumList() ;
for (int i=0; i"
+ ""+s[0]+""
+ ""+s[1]+""
+ ""+s[2]+""
+ ""+s[3]+""
+ ""+s[4]+"");
}
out.println("");
out.close();
}
}
Top
3 楼Brain（无缺公子）回复于 2002-11-18 12:52:54 得分 5
如果使用应用服务器，则一般的在服务器中建立数据源和连接池，方法参看该服务器文档
程序中使用jndi进行查找数据源，然后获取连接Top
4 楼kevin_c（）回复于 2002-11-18 16:24:26 得分 45
/**
* @author Umesh
* @version 1.0
*
* Development Environment : Oracle9i JDeveloper
* Name of the Application : ConnCacheBean.java
* Creation/Modification History :
*
* Umesh 25-Nov-2001 Created
*
* Overview of Application : This Bean Class is used by all the JSPs
* to perform database interaction. This class uses JDBC to perform any DML/DDL
* operations. The key concept illustarted here is Connection Caching.
*
* As JSPs execute in middle tier, getting an individual database connection
* everytime for every user is an expensive operation. This is true especially
* when number of users involved are large in numbers.
*
* With the help of Connection Caching, the overhead of instantiating a new physical
* database connection can be easily overcome.
*
* This bean is implemented as a SingleTon Class meaning that there can be only
* one instance of this bean per JVM. In the constructor of the bean, Connection
* Cache is initialized and
*
**/
package oracle.otnsamples.oracle9ijdbc.conncachesample;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import oracle.jdbc.pool.OracleConnectionCacheImpl;
import java.util.Hashtable;
import java.util.Vector;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.naming.NameNotFoundException;
import javax.naming.Context;
public class ConnCacheBean {
// Connection Cache Variable
private OracleConnectionCacheImpl m_ocacheimpl = null;
// Data Source Variable
private OracleConnectionPoolDataSource m_cpds = null;
// Variable pointing to this instance
private static ConnCacheBean m_thisInstance = null;
/**
* Private Constructor : This approach makes it easy to implement this class as
* SingleTon Class.
*
* This method initializes Cache if not already initialized.
**/
private ConnCacheBean() throws Exception {
if (m_ocacheimpl == null)
initializeConnectionCache();
}
/**
* Method which returns a single instance of this bean.
**/
public static ConnCacheBean getInstance() throws Exception {
if ( m_thisInstance == null ) {
m_thisInstance = new ConnCacheBean();
}
return m_thisInstance;
}
Top
5 楼kevin_c（）回复于 2002-11-18 16:25:29 得分 0
/**
* This Method initializes Connection Cache.
**/
public void initializeConnectionCache() throws Exception {
if (m_ocacheimpl == null) {
try {
this.initializeConnectionCacheDataSrc();
// Initialize Connection Cache
m_ocacheimpl = new OracleConnectionCacheImpl(m_cpds);
// Set Max Limit for the Cache
m_ocacheimpl.setMaxLimit(5);
// Set Min Limit for the Cache
m_ocacheimpl.setMinLimit(1);
// Set Caching Scheme as DYNAMIC_SCHEME
// This scheme means that after reaching the connection active size to 5,
// the next request for Connection will be served by creating a new pooled
// connection instance and closed automatically when no longer in use.
m_ocacheimpl.setCacheScheme(OracleConnectionCacheImpl.DYNAMIC_SCHEME);
} catch (java.sql.SQLException ex) { // Trap SQL Errors
throw new Exception("SQL Error while Instantiating Connection Cache : \n" +
ex.toString());
} catch (javax.naming.NamingException ex) { // Trap Naming Errors
throw new Exception("Naming Exception : \n" + ex.toString());
} catch (java.lang.Exception ex) { // Trap other errors
throw new Exception("Exception : \n" + ex.toString());
}
}
}
/**
* Method which returns Product Information for all products in a Vector.
**/
public Vector getProductInformation() throws Exception {
// Declare Vector to hold Product Information
Vector prodVector = new Vector();
// Connection from a Connection Cache
Connection conn = null;
// Statement and ResultSet Object for fetching Product Information
Statement stmt = null;
ResultSet rset = null;
// If Cache is not initialized properly then throw error
if (m_ocacheimpl == null) {
throw new Exception("Connection Cache Not Properly Initialized");
}
try {
// Get Connection From the Cache
conn = m_ocacheimpl.getConnection();
stmt = conn.createStatement();
// Execute Statement to get product Information from the Database
rset = stmt.executeQuery(" SELECT P.PRODUCT_ID, P.PRODUCT_NAME, " +
"P.PRODUCT_DESCRIPTION, P.LIST_PRICE, " + " C.CATEGORY_NAME FROM " +
"PRODUCT_INFORMATION P, CATEGORIES_TAB C WHERE " +
"P.CATEGORY_ID = C.CATEGORY_ID AND ROWNUM <= 10 AND P.PRODUCT_ID >= 1781 " +
" ORDER BY C.CATEGORY_NAME ");
// Loop through the Result Set
while (rset.next()) {
// Concatenate the information and add the String to the Vector
String prodString = rset.getInt(1) + "****" + rset.getString(2) + "****" +
rset.getString(3) + "****" + new Float(rset.getFloat(4)).toString() +
"****" + rset.getString(5);
prodVector.addElement(prodString);
}
} catch (SQLException ex) { // Trap SQL Errors
throw new Exception("SQL Error : \n" + ex.toString());
} finally {
try {
// Close Result Set and Statement
rset.close();
stmt.close();
// Closing connection returns the Connection to the Cache.
conn.close();
} catch (Exception ex) { // Trap Errors
throw new Exception("SQL Error while closing objects = " + ex.toString());
}
}
return prodVector; // Return Product List
}
/**
* This method returns Order Information for a particular product.
* Order Information is returned in a Vector.
**/
public Vector getOrderInformation(String productId) throws Exception {
// Vector holding order Information
Vector orderVector = new Vector();
String orderEntry = "";
// Connection from Connection Cache
Connection conn = null;
//Statement and ResultSet to fetch Order Information
PreparedStatement pstmt = null;
ResultSet rset = null;
// If Connection Cache is not properly initialized, then throw error
if (m_ocacheimpl == null) {
throw new Exception("Connection Cache Not Properly Initialized");
}
// Fetch Order Information from the database
try {
// Get Connection from Connection Cache.
conn = m_ocacheimpl.getConnection();
// Prepare Query to fetch Order Information
pstmt = conn.prepareStatement("SELECT O.ORDER_ID, C.CUST_FIRST_NAME, "+
" C.CUST_LAST_NAME, TO_CHAR(O.ORDER_DATE, 'DD-MON-YYYY'), OI.UNIT_PRICE, " +
" OI.QUANTITY, " + " O.ORDER_STATUS FROM ORDERS O, ORDER_ITEMS OI, CUSTOMERS C " +
" WHERE OI.PRODUCT_ID = ? AND OI.ORDER_ID = O.ORDER_ID AND " + " O.CUSTOMER_ID = C.CUSTOMER_ID ");
int prdId = new Integer(productId).intValue();
// Bind Product Dd
pstmt.setInt(1, prdId);
// Execute Query
rset = pstmt.executeQuery();
// Loop through the result set
while (rset.next()) {
orderEntry = "";
orderEntry = rset.getInt(1) + "****" + rset.getString(2) + " " +
rset.getString(3) + "****" + rset.getString(4) + "****" + rset.getFloat(5) +
"****" + rset.getInt(6) + "****" + rset.getString(7);
orderVector.addElement(orderEntry);
}
}catch (java.sql.SQLException ex) { // Trap SQL Errors
throw new Exception("SQL Error = " +ex.toString());
} finally {
try {
// Close Result Set and Statement
rset.close();
pstmt.close();
// Closing connection returns the Connection to the Cache.
conn.close();
} catch (Exception ex) { //Trap Errors
throw new Exception("SQL Error while closing objects = " +ex.toString());
}
}
return orderVector; // Return Order Information
}
/**
* This method returns active size of the Cache.
**/
public int getActiveSize() {
return m_ocacheimpl.getActiveSize();
}
/**
* This method returns connection cache size.
**/
public int getCacheSize() {
return m_ocacheimpl.getCacheSize();
}
/**
* This Method initializes the variable 'm_cpds' with value of valid Connection
* Cache Data Source.
**/
private void initializeConnectionCacheDataSrc() throws Exception {
// Logical Datasource name to lookup in the naming service
String logicalDatasourceName = "connCacheSampledb";
Context ctx = null;
try {
// Environment variable
Hashtable env = new Hashtable(2);
// Specify the initial context factory
env.put (Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
// Configuration information
env.put(Context.PROVIDER_URL,"file:.");
// Initialize the context
ctx = new InitialContext(env);
// Initialize the Datasource
m_cpds = new OracleConnectionPoolDataSource();
// Configure the Datasource with proper values of Host Name, User Name etc
this.configureDataSource(m_cpds);
// Bind Datasource with Naming Service
ctx.rebind(logicalDatasourceName,m_cpds);
// Lookup in the naming service for the Datasource using the logical name
m_cpds = (OracleConnectionPoolDataSource)ctx.lookup(logicalDatasourceName);
} catch (NamingException namingEx) { // Trap JNDI Naming Errors
throw new Exception("Naming Errors = " + namingEx.toString());
} catch (SQLException sqlEx){ // Trap SQL Errors
throw new Exception("SQL Errors = " + sqlEx.toString());
} catch (Exception ex) { // Trap Generic Errors
throw new Exception("Generic Errors = " + ex.toString());
}
}
Top
6 楼kevin_c（）回复于 2002-11-18 16:25:55 得分 0
/**
* This method configures the Datasource with appropriate values of Host Name,
* User Name, Password etc.
*
* Note that the configuration parameters are stored in ConnectionParams.java
* file.
**/
private void configureDataSource(OracleConnectionPoolDataSource p_ods) {
// Set Driver type, either 'thin' or 'oci8'
// When 'oci8' is used TNSEntryName has to be set.
p_ods.setDriverType("thin");
// Set Network protocol
p_ods.setNetworkProtocol("tcp");
// Set Host name
p_ods.setServerName (ConnectionParams.s_hostName);
// Set Database SID
p_ods.setDatabaseName(ConnectionParams.s_databaseSID);
// Set Port number
p_ods.setPortNumber (ConnectionParams.s_portNumber);
// Set User name
p_ods.setUser (ConnectionParams.s_userName);
// Set Password
p_ods.setPassword (ConnectionParams.s_password);
}
/**
* This method closes the connection cache.
**/
public void closeConnCache() throws SQLException {
if(m_ocacheimpl != null) {
m_ocacheimpl.close();
}
}
}Top
7 楼hbzxf（阿好）回复于 2002-11-18 16:31:52 得分 20
最简单的方法
下载一个连接池的bean
仔细研究两遍
大致是这个意思：
1、建立一个连接bean
2、生成一个唯一的连接池对象
3、调取连接bean
连接池是一个类中类Top
8 楼asdmonster（呆鸟四号）回复于 2002-11-18 19:09:28 得分 0
你们的解答都很好，让我怎么给分啊？不好、意思，只有按字的多少来了。
谢谢大家。Top
