java连接sqlserver实例
2008-01-05 18:49:12 来源:WEB开发网核心提示:package sanpai.db;import java.io.*;import java.sql.*;import java.util.*;import java.util.Date;/** * This class is a Singleton that PRovides access to one or man
package sanpai.db; import java.io.*; import java.sql.*; import java.util.*; import java.util.Date; /** * This class is a Singleton that PRovides access to one or many * connection pools defined in a Property file. A client gets * access to the single instance through the static getInstance() * method and can then check-out and check-in connections from a pool. * When the client shuts down it should call the release() method * to close all open connections and do other clean up. */ public class DBConnectionManager { static private DBConnectionManager instance; // The single instance static private int clients; private Vector drivers = new Vector(); private PrintWriter log; private Hashtable pools = new Hashtable(); /** * This inner class represents a connection pool. It creates new * connections on demand, up to a max number if specified. * It also makes sure a connection is still open before it is * returned to a client. */ class DBConnectionPool { private int checkedOut; private Vector freeConnections = new Vector(); private int maxConn; private String name; private String passWord; private String URL; private String user; /** * Creates new connection pool. * * @param name The pool name * @param URL The JDBC URL for the database * @param user The database user, or null * @param password The database user password, or null * @param maxConn The maximal number of connections, or 0 * for no limit */ public DBConnectionPool(String name, String URL, String user, String password, int maxConn) { this.name = name; this.URL = URL; this.user = user; this.password = password; this.maxConn = maxConn; } /** * Checks in a connection to the pool. Notify other Threads that * may be waiting for a connection. * * @param con The connection to check in */ public synchronized void freeConnection(Connection con) { // Put the connection at the end of the Vector freeConnections.addElement(con); checkedOut--; notifyAll(); } /** * Checks out a connection from the pool. If no free connection * is available, a new connection is created unless the max * number of connections has been reached. If a free connection * has been closed by the database, it's removed from the pool * and this method is called again recursively. */ public synchronized Connection getConnection()throws SQLException { Connection con = null; if (freeConnections.size() > 0) { // Pick the first Connection in the Vector // to get round-robin usage con = (Connection) freeConnections.firstElement(); freeConnections.removeElementAt(0); try { if (con.isClosed()) { log("Removed bad connection from " + name); // Try again recursively con = getConnection(); } } catch (SQLException e) { log("Removed bad connection from " + name); // Try again recursively con = getConnection(); } } else if (maxConn == 0 checkedOut < maxConn) { con = newConnection(); } if (con != null) { checkedOut++; } return con; } /** * Checks out a connection from the pool. If no free connection * is available, a new connection is created unless the max * number of connections has been reached. If a free connection * has been closed by the database, it's removed from the pool * and this method is called again recursively. * <P> * If no connection is available and the max number has been * reached, this method waits the specified time for one to be * checked in. * * @param timeout The timeout value in milliseconds */ public synchronized Connection getConnection(long timeout) throws SQLException { long startTime = new Date().getTime(); Connection con; while ((con = getConnection()) == null) { try { wait(timeout); } catch (InterruptedException e) {} if ((new Date().getTime() - startTime) >= timeout) { // Timeout has eXPired return null; } } return con; } /** * Closes all available connections. */ public synchronized void release() { Enumeration allConnections = freeConnections.elements(); while (allConnections.hasMoreElements()) { Connection con = (Connection) allConnections.nextElement(); try { con.close(); log("Closed connection for pool " + name); } catch (SQLException e) { log(e, "Can't close connection for pool " + name); } } freeConnections.removeAllElements(); } /** * Creates a new connection, using a userid and password * if specified. */ private Connection newConnection() throws SQLException { Connection con = null; try { if (user == null) { con = DriverManager.getConnection(URL); } else { con = DriverManager.getConnection(URL, user, password); } log("Created a new connection in pool " + name); } catch (SQLException e) { log(e, "Can't create a new connection for " + URL); throw e; } return con; } } /** * A private constrUCtor since this is a Singleton */ private DBConnectionManager() { init(); } /** * Creates instances of DBConnectionPool based on the properties. * A DBConnectionPool can be defined with the following properties: * <PRE> * <poolname>.url The JDBC URL for the database * <poolname>.user A database user (optional) * <poolname>.password A database user password (if user specified) * <poolname>.maxconn The maximal number of connections (optional) * </PRE> * * @param props The connection pool properties */ private void createPools(Properties props) { Enumeration propNames = props.propertyNames(); while (propNames.hasMoreElements()) { String name = (String) propNames.nextElement(); if (name.endsWith(".url")) { String poolName = name.substring(0, name.lastIndexOf(".")); String url = props.getProperty(poolName + ".url"); if (url == null) { log("No URL specified for " + poolName); continue; } String user = props.getProperty(poolName + ".user"); String password = props.getProperty(poolName + ".password"); String maxconn = props.getProperty(poolName + ".maxconn", "0"); int max; try { max = Integer.valueOf(maxconn).intValue(); } catch (NumberFormatException e) { log("Invalid maxconn value " + maxconn + " for " + poolName); max = 0; } DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, max); pools.put(poolName, pool); log("Initialized pool " + poolName); } } } /** * Returns a connection to the named pool. * * @param name The pool name as defined in the properties file * @param con The Connection */ public void freeConnection(String name, Connection con) { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { pool.freeConnection(con); } } /** * Returns an open connection. If no one is available, and the max * number of connections has not been reached, a new connection is * created. * * @param name The pool name as defined in the properties file * @return Connection The connection or null */ public Connection getConnection(String name) throws SQLException { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { return pool.getConnection(); } return null; } /** * Returns an open connection. If no one is available, and the max * number of connections has not been reached, a new connection is * created. If the max number has been reached, waits until one * is available or the specified time has elapsed. * * @param name The pool name as defined in the properties file * @param time The number of milliseconds to wait * @return Connection The connection or null */ public Connection getConnection(String name, long time) throws SQLException { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { return pool.getConnection(time); } return null; } /** * Returns the single instance, creating one if it's the * first time this method is called. * * @return DBConnectionManager The single instance. */ static synchronized public DBConnectionManager getInstance() { if (instance == null) { instance = new DBConnectionManager(); } clients++; return instance; } /** * Loads properties and initializes the instance with its values. */ private void init() { InputStream is = getClass().getResourceAsStream("/db.properties"); Properties dbProps = new Properties(); try { dbProps.load(is); } catch (Exception e) { System.err.println("Can't read the properties file. " + "Make sure db.properties is in the CLASSPATH"); return; } String logFile = dbProps.getProperty("logfile", "DBConnectionManager.log"); try { log = new PrintWriter(new FileWriter(logFile, true), true); } catch (IOException e) { System.err.println("Can't open the log file: " + logFile); log = new PrintWriter(System.err); } loadDrivers(dbProps); createPools(dbProps); } /** * Loads and registers all JDBC drivers. This is done by the * DBConnectionManager, as opposed to the DBConnectionPool, * since many pools may share the same driver. * * @param props The connection pool properties */ private void loadDrivers(Properties props) { String driverClasses = props.getProperty("drivers"); StringTokenizer st = new StringTokenizer(driverClasses); while (st.hasMoreElements()) { String driverClassName = st.nextToken().trim(); try { Driver driver = (Driver) Class.forName(driverClassName).newInstance(); DriverManager.registerDriver(driver); drivers.addElement(driver); log("Registered JDBC driver " + driverClassName); } catch (Exception e) { log("Can't register JDBC driver: " + driverClassName + ", Exception: " + e); } } } /** * Writes a message to the log file. */ private void log(String msg) { log.println(new Date() + ": " + msg); } /** * Writes a message with an Exception to the log file. */ private void log(Throwable e, String msg) { log.println(new Date() + ": " + msg); e.printStackTrace(log); } /** * Closes all open connections and deregisters all drivers. */ public synchronized void release() { // Wait until called by the last client if (--clients != 0) { return; } Enumeration allPools = pools.elements(); while (allPools.hasMoreElements()) { DBConnectionPool pool = (DBConnectionPool) allPools.nextElement(); pool.release(); } Enumeration allDrivers = drivers.elements(); while (allDrivers.hasMoreElements()) { Driver driver = (Driver) allDrivers.nextElement(); try { DriverManager.deregisterDriver(driver); log("Deregistered JDBC driver " + driver.getClass().getName()); } catch (SQLException e) { log(e, "Can't deregister JDBC driver: " + driver.getClass().getName()); } } } /** * release all resources when this object is destroyed. */ public void finalize() { release(); }}
赞助商链接