菜鸟求可用的数据库连接池实例

用tomcat admin控制台建数据库连接迟，肯定没问题
package database;
import java.sql.*;
import sun.io.*;public class sqlbean {
private Connection con;
private ResultSet rs;
public static Connection getConnection()throws SQLException{
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

}catch(ClassNotFoundException ex)
{
ex.printStackTrace();
return null;
}
return DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=test","sa","sa");

}public ResultSet executeQuery(String sql){
try{
con=sqlbean.getConnection();
Statement statement=con.createStatement();
rs=statement.executeQuery(sql);
}
catch(SQLException ex)
{
System.out.print("error");
}
return rs;
}
public int executeUpdate(String sql)
{
int count=0;
Statement stmt=null;
try{
con=sqlbean.getConnection();
stmt=con.createStatement();
count=stmt.executeUpdate(sql);
}
catch(SQLException ex)
{

}
finally{
try{
if(stmt!=null)
stmt.close();
if(con!=null)
con.close();
}
catch(SQLException ex)
{
System.out.print(ex);
}
}
return count;
}
public void freeRs(ResultSet rs)
{
try
{
if(rs!=null)
{
rs.close();
con.close();
}
}
catch(Exception e)
{

}
}}
看到很多人要数据库连接池，这里贴一个不需要配置tomcat的方案，方案中采用了Singleton模式要注意。
1.通用类ConnectionPool
package yourapplication.utility.db;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;public class ConnectionPool implements Runnable {
  private String driver, url, username, password;
  private int maxConnections ;
  private boolean waitIfBusy;
  private Vector availableConnections, busyConnections;
  private boolean connectionPending = false;

  public ConnectionPool(String driver, String url,
                        String username, String password,
                        int initialConnections,
                        int maxConnections,
                        boolean waitIfBusy)
      throws SQLException {
    this.driver = driver;
    this.url = url;
    this.username = username;
    this.password = password;
    this.maxConnections = maxConnections;
    this.waitIfBusy = waitIfBusy;
    if (initialConnections > maxConnections) {
      initialConnections = maxConnections;
    }
    availableConnections = new Vector(initialConnections);
    busyConnections = new Vector();
    for(int i=0; i<initialConnections; i++) {
      availableConnections.addElement(makeNewConnection());
    }
  }

  public synchronized Connection getConnection()
      throws SQLException {
    if (!availableConnections.isEmpty()) {
      Connection existingConnection =
        (Connection)availableConnections.lastElement();
      int lastIndex = availableConnections.size() - 1;
      availableConnections.removeElementAt(lastIndex);
      // If connection on available list is closed (e.g.,
      // it timed out), then remove it from available list
      // and repeat the process of obtaining a connection.
      // Also wake up threads that were waiting for a
      // connection because maxConnection limit was reached.
      if (existingConnection.isClosed()) {
        notifyAll(); // Freed up a spot for anybody waiting
        return(getConnection());
      } else {
        busyConnections.addElement(existingConnection);
        return(existingConnection);
      }
    } else {

      // Three possible cases:
      // 1) You haven't reached maxConnections limit. So
      //    establish one in the background if there isn't
      //    already one pending, then wait for
      //    the next available connection (whether or not
      //    it was the newly established one).
      // 2) You reached maxConnections limit and waitIfBusy
      //    flag is false. Throw SQLException in such a case.
      // 3) You reached maxConnections limit and waitIfBusy
      //    flag is true. Then do the same thing as in second
      //    part of step 1: wait for next available connection.

      if ((totalConnections() < maxConnections) &&
          !connectionPending) {
        makeBackgroundConnection();
      } else if (!waitIfBusy) {
        throw new SQLException("Connection limit reached");
      }
      // Wait for either a new connection to be established
      // (if you called makeBackgroundConnection) or for
      // an existing connection to be freed up.
      try {
        wait();
      } catch(InterruptedException ie) {}
      // Someone freed up a connection, so try again.
      return(getConnection());
    }
  }   // You can't just make a new connection in the foreground
  // when none are available, since this can take several
  // seconds with a slow network connection. Instead,
  // start a thread that establishes a new connection,
  // then wait. You get woken up either when the new connection
  // is established or if someone finishes with an existing
  // connection.   private void makeBackgroundConnection() {
    connectionPending = true;
    try {
      Thread connectThread = new Thread(this);
      connectThread.start();
    } catch(OutOfMemoryError oome) {
      // Give up on new connection
    }
  }   public void run() {
    try {
      Connection connection = makeNewConnection();
      synchronized(this) {
        availableConnections.addElement(connection);
        connectionPending = false;
        notifyAll();
      }
    } catch(Exception e) { // SQLException or OutOfMemory
      // Give up on new connection and wait for existing one
      // to free up.
    }
  }   // This explicitly makes a new connection. Called in
  // the foreground when initializing the ConnectionPool,
  // and called in the background when running.

  private Connection makeNewConnection()
      throws SQLException {
    try {
      // Load database driver if not already loaded
      Class.forName(driver);
      // Establish network connection to database
      Connection connection =
        DriverManager.getConnection(url, username, password);
      return(connection);
    } catch(ClassNotFoundException cnfe) {
      // Simplify try/catch blocks of people using this by
      // throwing only one exception type.
      throw new SQLException("Can't find class for driver: " +
                             driver);
    }
  }   public synchronized void free(Connection connection) {
    busyConnections.removeElement(connection);
    availableConnections.addElement(connection);
    // Wake up threads that are waiting for a connection
    notifyAll();
  }

  public synchronized int totalConnections() {
    return(availableConnections.size() +
           busyConnections.size());
  }   /** Close all the connections. Use with caution:
   *  be sure no connections are in use before
   *  calling. Note that you are not <I>required</I> to
   *  call this when done with a ConnectionPool, since
   *  connections are guaranteed to be closed when
   *  garbage collected. But this method gives more control
   *  regarding when the connections are closed.
   */   public synchronized void closeAllConnections() {
    closeConnections(availableConnections);
    availableConnections = new Vector();
    closeConnections(busyConnections);
    busyConnections = new Vector();
  }   private void closeConnections(Vector connections) {
    try {
      for(int i=0; i<connections.size(); i++) {
        Connection connection =
          (Connection)connections.elementAt(i);
        if (!connection.isClosed()) {
          connection.close();
        }
      }
    } catch(SQLException sqle) {
      // Ignore errors; garbage collect anyhow
    }
  }

  public synchronized String toString() {
    String info =
      "ConnectionPool(" + url + "," + username + ")" +
      ", available=" + availableConnections.size() +
      ", busy=" + busyConnections.size() +
      ", max=" + maxConnections;
    return(info);
  }
}
2.单键类
package yourapplication.utility.db;import java.sql.SQLException;public class BookConnPool extends ConnectionPool {
private static BookConnPool pool = null; private BookConnPool(String driver, String url, String username,
String password, int initialConnections, int maxConnections,
boolean waitIfBusy) throws SQLException {
super(driver, url, username,
password, initialConnections, maxConnections,
waitIfBusy); // Call parent constructor
} public static synchronized BookConnPool getInstance() throws SQLException {
if (pool == null) {
//这里从配置文件中读取相应的参数
String driver = ""; //这里填你的数据库驱动
String url = "";//这里填db相应的url包含端口和服务名
                        String usrname = "";
                        String passwd = "";
pool = new BookConnPool(driver, url,usrname, passwd, 100,1000,true);
}
return (pool);
}}

调试易

菜鸟求可用的数据库连接池实例

解决方案 »