connection pool

老師 我下課跟你問到的connection pool問題 我想一下 因為你說 不要管理connection要從sql 語法去克服 但是我回家想一下 假設connection pool最大值是100 那不管connection狀況下 假設同時有101個人提出需求要connection 系統可能會出包 但在保證 所有登入的使用者 皆可順利操作的前提下 我撰寫下面方法 當然如果能改善sql寫法 效能才會到達最佳 這點我覺得是我要加強的

 public void isDate(String user){
  //判斷 使用者是否超過 20分鐘 1200秒 操作Connection
  Date date = null;
  //DBTimeMap 是 HashMap 儲存所有使用者上一次使用的時間記錄 以user為key
  date = (Date) DBTimeMap.get(user);
  if(date == null){
   date = Calendar.getInstance().getTime();
  }else{
   long d = Calendar.getInstance().getTime().getTime() - date.getTime();
   date = Calendar.getInstance().getTime();
   if(d/1000 > 1200-5){//超過20分鐘
//以下兩個方法會釋放掉 user 的 connection 以利後面程式重建connection
    this.releaseDBConnection(user);
    this.releaseDB2Connection(user);
   }
  }
  DBTimeMap.put(user, date);
 }

管理部份我使用的是Singleton設計方法

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.ServletContext;

public class Singleton {
 private static Singleton instance = null;
 
 private HashMap DBConMap = new HashMap();
 private HashMap DB2ConMap = new HashMap();
 private ServletContext context;
 
 private HashMap DBTimeMap = new HashMap();
 
 public HashMap SysMap;
 public HashMap DBMap;
 public HashMap AirMap;
 public static Singleton getInstance(ServletContext context) {
  if (instance == null){
   synchronized(Singleton.class){
    if(instance == null) {
     instance = new Singleton(context);
    }
   }
  }
  return instance;
 }
 private Singleton(ServletContext context){
  //同步區
  this.context = context;
//處理 SysMap DBMap DBMap…略
 }
 
 public void isDate(String user){
  //判斷 使用者是否超過 20分鐘 1200秒 操作Connection
  Date date = null;
  date = (Date) DBTimeMap.get(user);
  if(date == null){
   date = Calendar.getInstance().getTime();
  }else{
   long d = Calendar.getInstance().getTime().getTime() - date.getTime();
   date = Calendar.getInstance().getTime();
   if(d/1000 > 1200-5){//超過20分鐘
    this.releaseDBConnection(user);
    this.releaseDB2Connection(user);
   }
  }
  DBTimeMap.put(user, date);
 }
 
 public Connection getDBConnection(String user){
  isDate(user);
  Connection con = (Connection) DBConMap.get(user);
  try {
   if(con == null){
    //建立DBCon
    HashMap map = SysMap;
    Class.forName("com.mysql.jdbc.Driver");
    String Driver = 
     "jdbc:mysql://"+
     map.get("DB")+":"+map.get("DBport")+"/"+map.get("DBname")+"?" +
     "user="+map.get("DBuser")+"&" +
     "password="+map.get("DBpwd")+"&" +
     "useUnicode=true&characterEncoding=utf-8" +
     "&autoReconnect=true&initialTimeout=0";
    con = DriverManager.getConnection(Driver);
    
    DBConMap.put(user, con);
   }
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return con;
 }
 public Statement getDBStatement(String user){
  Connection con = this.getDBConnection(user);
  Statement stmt = null;
  try {
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return stmt;
 }
 public void releaseDBConnection(String user){
  DBConMap.remove(user);//釋放DBCon
 }
 public Connection getDB2Connection(String user){
  isDate(user);
  Connection con = (Connection) DB2ConMap.get(user);
  try{
   if(con == null){
    //建立DB2Con
    HashMap map = SysMap;
    Class.forName("com.mysql.jdbc.Driver");
    String Driver = 
     "jdbc:mysql://"+
     map.get("DB")+":"+map.get("DBport")+"/"+map.get("CorpDBname")+"?" +
     "user="+map.get("DBuser")+"&" +
     "password="+map.get("DBpwd")+"&" +
     "useUnicode=true&characterEncoding=utf-8" +
     "&autoReconnect=true&initialTimeout=0";
    con = DriverManager.getConnection(Driver);
    
    DB2ConMap.put(user, con);
   }
  }catch(ClassNotFoundException e){
   e.printStackTrace();
  }catch(SQLException e){
   e.printStackTrace();
  }
  return con;
 }
 public Statement getDB2Statement(String user){
  Connection con = this.getDB2Connection(user);
  Statement stmt = null;
  try {
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return stmt;
 }
 public void releaseDB2Connection(String user){
  DB2ConMap.remove(user);//釋放DB2Con
 }
}