数据库连接池

1、src 下建立luckydb.properties
#---Database---
DB.DRIVER=oracle.jdbc.driver.OracleDriver
DB.URL=jdbc:oracle:thin:@tekt2:1521:bak
DB.USER=readonly 
DB.PASSWORD=readonly
DB.MAX_CONNECTIONS=30

2、建立 DBConnectionManage

/*
 * Created on 2004-8-10
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.tek.all.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Vector;
import java.sql.DriverManager;
import com.tek.all.database.Configuration;

/**
 * @author qianzh
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class DBConnectionManage {
 private final static DBConnectionManage instance=new DBConnectionManage();
    private DBConnectionPool pool;
 
 public static DBConnectionManage getInstance(){
  return instance;
 }
 
 
 public void freeConnection(Connection con) throws SQLException{
  pool.freeConnection(con);
 }
  
 public Connection getConnection() throws SQLException{
  return pool.getConnection();
 }
 
 
 private DBConnectionManage(){
   init();
 }

 private void init(){
  Configuration cfg=Configuration.getInstance();
  String db_driver=cfg.getValue("DB.DRIVER");
  String db_url=cfg.getValue("DB.URL");
  String db_user=cfg.getValue("DB.USER");
  String db_password=cfg.getValue("DB.PASSWORD");
  
  int db_maxConn=Integer.parseInt(cfg.getValue("DB.MAX_CONNECTIONS"));
  try{
   Class.forName(db_driver);
  }catch(ClassNotFoundException ex){
   System.out.println(ex);
  }
  
  pool = new DBConnectionPool(db_url,db_user,db_password,db_maxConn);
  
 }
 
 
 class DBConnectionPool{
  private Vector freeConnections=new Vector();
  private int maxConn;
  private int connNumb;
  
  private String URL;
  private String password;
  private String user;
  
  
  public DBConnectionPool(String URL,String user,String password,int maxConn){
   this.URL=URL;
   this.user=user;
   this.password=password;
   this.maxConn=maxConn;
   
   //System.out.print("这里是OK的！！！"+URL+"___"+user+password);
   
  }
  public synchronized void freeConnection(Connection con){
   freeConnections.addElement(con);
   connNumb--;
   notifyAll();  
  }
  public synchronized Connection getConnection() throws SQLException{
   Connection con=null;
   if(freeConnections.size()>0){
     con=(Connection)freeConnections.firstElement();
     freeConnections.removeElementAt(0);
     try{
      if(con.isClosed()){
       con=getConnection();
       //System.out.print("3这里是OK的！！！");
      }
     }catch(SQLException e){
      con=getConnection();
     }
   }else if(maxConn==0||connNumb<maxConn){
    con=newConnection();
   }if (con != null) {
                connNumb++;
            }
            return con;  
  }
 
  
 private Connection newConnection() throws SQLException{
            Connection con =DriverManager.getConnection(URL,user, password);
            return con;
        }
 }
   
}

3、建立Configuration
/*
 * Created on 2004-8-10
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.tek.all.database;

import java.util.Properties;
import java.io.InputStream;
import java.io.IOException;

/**
 * @author qianzh
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Configuration {
 
 private Properties properties;
 private final static Configuration cfg=new Configuration();
 
 private Configuration(){
  properties=new Properties();
  InputStream is=null;
  try{
   is=getClass().getResourceAsStream("/luckydb.properties");
   properties.load(is);  
   //System.out.print("1这里是OK的！！！");
  }catch(Exception e){
   System.out.print("can't read the file");
  }finally{
   try{
    if(is!=null){
     is.close();
    } 
   }catch(IOException ex){ }    
  }  
 }
/
 public static Configuration getInstance(){
  return cfg;
 }

 public String getValue(String key){ 
  return properties.getProperty(key); 
 }

}

4、建立DBAccess
/*
 * Created on 2004-8-11
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.tek.all.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
/**
 * @author qianzh
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class DBAccess {
 
 protected Statement statement;
 protected ResultSet resutlset;
 protected Connection connection;
 
 
 public DBAccess(Connection connection){
  this.connection=connection;
 }
 
    public void closeSelect() throws SQLException{
     if(statement!=null){
      statement.close();
     }  
    }
   
    public ResultSet openSelect(String sql) throws SQLException{
     
     statement=connection.createStatement();
        ResultSet rs=statement.executeQuery(sql);
        return rs;
    }
   
    public int runSql(String sql) throws SQLException{
     
     statement=connection.createStatement();
     int result=statement.executeUpdate(sql);
     statement.close();
     return result;
    }
   

}

5、下面建立HelpBean选取数据库数据。

package com.tek.all.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.tek.all.filter.ChangeISO;

import com.tek.all.database.DBConnectionManage;
import com.tek.all.struts.form.*;

import java.sql.*;
import java.util.*;

import com.tek.all.struts.form.ManageUserForm;
import com.tek.all.database.DBAccess;

/**
 * @author qianzh
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class HelpBean {
 
 private DBConnectionManage dbm=DBConnectionManage.getInstance();
 Connection conn=null;
 ChangeISO iso=new ChangeISO();
 
    public void savaRegForm(RegForm regform,int i) throws Exception{
     int t=i+1;
     conn=dbm.getConnection();
     String sql="insert into tek_user "+
     "(u_id,user_name, "+
     "password, "+
     "department, "+
     "work_id, "+
     "full_name, "+
     "contact_mobile, "+
     "contact_phone, "+
     "contact_email_in, "+
     "contact_email_out, "+
     "flag, "+
     "reg_date) "+
     "values('"+t+"',?,?,?,?,?,?,?,?,?,1,sysdate)";
     PreparedStatement pStmt=conn.prepareStatement(sql);
     pStmt.setString(1,regform.getUser_name());
     pStmt.setString(2,regform.getPassword());
     pStmt.setString(3,iso.trans(regform.getDepartment()));
     pStmt.setString(4,regform.getWork_id());
     pStmt.setString(5,iso.trans(regform.getFull_name()));
     pStmt.setString(6,regform.getContact_mobile());
     pStmt.setString(7,regform.getContact_phone());
     pStmt.setString(8,regform.getContact_Email_in());
     pStmt.setString(9,regform.getContact_Email_out());
     pStmt.executeUpdate();
     pStmt.close();
     
     dbm.freeConnection(conn);
     
    }

一切OK拉！