ConnectionPooljar数据连接池文件包的制作计划流程

1、关于ConnectionPool.jar数据连接池文件包的制作流程目的：制作通用型数据连接池文件包，便于应用于通用工程结构：工程包内classes目录内1.ConnectionPool ：连接池治理程序|_ConnectionPool.java|_ConnectionWrapper.java|_LogFile.java|_LogWriter.java|_MakeDateTime.java|_PoolManager.java2. com ：MySql JDBC驱动程序3. org ：MySql JDBC驱动程序4. net ：MSSqlserver JTDS 驱动程序5. javax ：Orac

2、le JDBC 驱动程序6. oracle ：Oracle JDBC 驱动程序制作流程：新建一个工程命名为Pool，在工程内建立一个war模块命名为Poolwar；在工程内新建class文件，文件的包起名为ConnectionPool；依次新建下列文件：ConnectionPool.java、ConnectionWrapper.java、LogFile.java、LogWriter.java、MakeDateTime.java、PoolManager.java将Oracle for JDBC驱动classes12.jar文件用winrar解压缩，得到javax和oracle两个文件夹，将这两个

3、文件夹复制到Pool工程内的classes目录下；将MS-Sqlserver for JDBC驱动jtds-1.2.jar文件用winrar解压缩，得到net那个文件夹，将那个文件夹复制到Pool工程内的classes目录下；将MySql for JDBC驱动mysql-connector-java-3.0.16-ga-bin.jar文件用winrar解压缩，得到com和org两个文件夹，将这两个文件夹复制到Pool工程内的classes目录下；假如需要添加其他驱动程序，可参照4-6进行；选择JBuilder工具条上Wizards-Archive Builder ；9.Archive Buil

4、der Step 1 of 5 : Archive Type 选择Basic（具体类型含义参考JBuilerX使用手册）；10. Archive Builder Step 2 of 5 : 将Name 和 File 命名为需要的名字，那个地点同意将其命名为ConnectionPool，其他的选项均不做更改；11Archive Builder Step 3 of 5 : 指定结构文件包含内容，那个地点不做任何修改；12Archive Builder Step 4 of 5 : 将servlet 选择为 Allways include all classes and resources;13Arc

5、hive Builder Step 4 of 5 : 保持默认状态不改变，按finish完成结构文件的定义；14在工程窗口用鼠标右键点 ConnectionPool 结构，然后选择make 生成结构文件；使用方法：将该jar文件添加到 ToolsConfigure Librarise 中；新加工程，在工程 Required Librarise 中添加该库文件；在工程中新建一个“连接池初始化类”负责建立数据连接；在工程中新建一属性文件-perties，负责描述数据库驱动及用户信息等，该属性文件存放的位置在PoolManager.java中定义；在PoolManager.java中默认为“./ p

6、erties”,该位置表示在/WEB-INF 目录下；附录：文件代码*文件开始*perties /属性文件*XBDBManager Propertiespoolname = sqlpool oraclepool 注：中间以空格分隔drivers = net.sourceforge.jtds.jdbc.Driver oracle.jdbc.driver.OracleDriver 注：中间以空格分隔logfile = d:logfile.txtsqlpool.url = jdbc:jtds:sqlserver:/:1433;DatabaseName=pdasqlpool.initconns=50s

7、qlpool.maxconns=80sqlpool.user = pdasqlpool.password = pdapass1234oraclepool.url = jdbc:oracle:thin:1521:infodboraclepool.initconns=50oraclepool.maxconns=80oraclepool.user = tjoldoraclepool.password = tjold*文件结束*文件开始*ConnectionPool.java*package ConnectionPool;import java.io.*;import java.sql.*;impor

8、t java.util.*;public class ConnectionPool private String name; private String URL; private String user; private String password; private int maxConns; private int timeOut; private LogWriter logWriter; private int checkedOut; private Vector freeConnections = new Vector(); public ConnectionPool(String

9、 name, String URL, String user, String password, int maxConns, int initConns, int timeOut, PrintWriter pw, int logLevel) = name; this.URL = URL; this.user = user; this.password = password; this.maxConns = maxConns; this.timeOut = timeOut 0 ? timeOut : 5; logWriter = new LogWriter(name, logLevel, pw)

10、; initPool(initConns); logWriter.log(New pool created, LogWriter.INFO); String lf = System.getProperty(line.separator); logWriter.log( url= + URL + user= + user + / password= + password + initconns= + initConns + maxconns= + maxConns + logintimeout= + this.timeOut, LogWriter.INFO); logWriter.log(get

11、Stats(), LogWriter.INFO); private void initPool(int initConns) for (int i = 0; i initConns; i+) try Connection pc = newConnection(); freeConnections.addElement(pc); catch (SQLException e) public Connection getConnection() throws SQLException logWriter.log(Request for connection received, LogWriter.D

12、EBUG); try Connection conn = getConnection(timeOut * 1000); return new ConnectionWrapper(conn, this); catch(SQLException e) logWriter.log(e, Exception getting connection, logWriter.ERROR); throw e; synchronized void wrapperClosed(Connection conn) freeConnections.addElement(conn); checkedOut-; notify

13、All(); logWriter.log(Returned wrapperClosed to pool, LogWriter.INFO); logWriter.log(getStats(), LogWriter.INFO); private synchronized Connection getConnection(long timeout) throws SQLException / Get a pooled Connection from the cache or a new one. / Wait if all are checked out and the max limit has

14、/ been reached. long startTime = System.currentTimeMillis(); long remaining = timeout; Connection conn = null; while (conn = getPooledConnection() = null) try logWriter.log(Waiting for connection. Timeout= + remaining, LogWriter.DEBUG); wait(remaining); catch (InterruptedException e) remaining = tim

15、eout - (System.currentTimeMillis() - startTime); if (remaining 0) / Pick the first Connection in the Vector / to get round-robin usage conn = (Connection) freeConnections.firstElement(); freeConnections.removeElementAt(0); else if (maxConns = 0 | checkedOut maxConns) conn = newConnection(); return c

16、onn; private Connection newConnection() throws SQLException Connection conn = null; if (user = null) conn = DriverManager.getConnection(URL); else conn = DriverManager.getConnection(URL, user, password); logWriter.log(Opened a new connection, LogWriter.INFO); logWriter.log(getStats(), LogWriter.INFO

17、); return conn; public synchronized void freeConnection(Connection conn) / Put the connection at the end of the Vector freeConnections.addElement(conn); checkedOut-; notifyAll(); logWriter.log(Returned freeConnection to pool, LogWriter.INFO); logWriter.log(getStats(), LogWriter.INFO); public synchro

18、nized void release() Enumeration allConnections = freeConnections.elements(); while (allConnections.hasMoreElements() Connection con = (Connection) allConnections.nextElement(); try con.close(); logWriter.log(release Closed connection, LogWriter.INFO); catch (SQLException e) logWriter.log(e, release

19、 Couldnt close connection, LogWriter.ERROR); freeConnections.removeAllElements(); private String getStats() return Total connections: + (freeConnections.size() + checkedOut) + Available: + freeConnections.size() + Checked-out: + checkedOut; *文件结束*文件开始*ConnectionWrapper.java*package ConnectionPool;im

20、port java.sql.*;import java.util.*;/* * This class is a wrapper around a Connection, overriding the * close method to just inform the pool that its available for * reuse again, and the isClosed method to return the state * of the wrapper instead of the Connection. */class ConnectionWrapper implement

21、s Connection / realConn should be private but we use package scope to / be able to test removal of bad connections Connection realConn; private ConnectionPool pool; private boolean isClosed = false; public ConnectionWrapper(Connection realConn, ConnectionPool pool) this.realConn = realConn; this.poo

22、l = pool; /* * Inform the ConnectionPool that the ConnectionWrapper * is closed. */ public void close() throws SQLException isClosed = true; pool.wrapperClosed(realConn); /* * Returns true if the ConnectionWrapper is closed, false * otherwise. */ public boolean isClosed() throws SQLException return

23、isClosed; /* * Wrapped methods. */ public void clearWarnings() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); realConn.clearWarnings(); public void commit() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); realCmit(); publ

24、ic Statement createStatement() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.createStatement(); public boolean getAutoCommit() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.getAutoCommit

25、(); public String getCatalog() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.getCatalog(); public DatabaseMetaData getMetaData() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.getMetaData

26、(); public int getTransactionIsolation() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.getTransactionIsolation(); public SQLWarning getWarnings() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return rea

27、lConn.getWarnings(); public boolean isReadOnly() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.isReadOnly(); public String nativeSQL(String sql) throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return real

28、Conn.nativeSQL(sql); public CallableStatement prepareCall(String sql) throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); return realConn.prepareCall(sql); public PreparedStatement prepareStatement(String sql) throws SQLException if (isClosed) throw new SQLExceptio

29、n(Pooled connection is closed); return realConn.prepareStatement(sql); public void rollback() throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); realConn.rollback(); public void setAutoCommit(boolean autoCommit) throws SQLException if (isClosed) throw new SQLExcep

30、tion(Pooled connection is closed); realConn.setAutoCommit(autoCommit); public void setCatalog(String catalog) throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); realConn.setCatalog(catalog); public void setReadOnly(boolean readOnly) throws SQLException if (isClose

31、d) throw new SQLException(Pooled connection is closed); realConn.setReadOnly(readOnly); public void setTransactionIsolation(int level) throws SQLException if (isClosed) throw new SQLException(Pooled connection is closed); realConn.setTransactionIsolation(level); public void setHoldability(int holdab

32、ility) throws SQLException realConn.setHoldability(holdability); public int getHoldability() throws SQLException return realConn.getHoldability(); public Savepoint setSavepoint() throws SQLException return realConn.setSavepoint(); public Savepoint setSavepoint(String name) throws SQLException return

33、 realConn.setSavepoint(name); public void releaseSavepoint(Savepoint savepoint) throws SQLException realConn.releaseSavepoint(savepoint); public void rollback(Savepoint savepoint) throws SQLException realConn.rollback(savepoint); public Statement createStatement(int resultSetType, int resultSetConcu

34、rrency, int resultSetHoldability) throws SQLException return realConn.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability); public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException return real

35、Conn.prepareStatement(sql,resultSetConcurrency,resultSetHoldability); public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException return realConn.prepareCall(sql,resultSetConcurrency,resultSetHoldability); public Prepare

36、dStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException return realConn.prepareStatement(sql,autoGeneratedKeys); public PreparedStatement prepareStatement(String sql, int columnIndexes) throws SQLException return realConn.prepareStatement(sql,columnIndexes); public Prepare

37、dStatement prepareStatement(String sql, String columnNames) throws SQLException return realConn.prepareStatement(sql,columnNames); public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException return realConn.createStatement(resultSetType,resultSetConcurrency); pu

38、blic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException return realConn.prepareStatement(sql,resultSetType ,resultSetConcurrency); public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLExce

39、ption return realConn.prepareCall(sql,resultSetType,resultSetConcurrency); public Map getTypeMap() throws SQLException return realConn.getTypeMap(); public void setTypeMap(Map map) throws SQLException realConn.setTypeMap(map); *文件结束*文件开始*LogFile.java*package ConnectionPool;import java.io.*;import ja

40、va.util.*;public class LogFile private static String myDir = c:test; private static String myFile = _tbit.log; public void LogFile() public static void log(String s) try MakeDateTime qdt = new MakeDateTime(); String dateString = qdt.getTDashDate1(); String stemp = qdt.getDate8(); String filename = m

41、yDir + HK + stemp + .log; BufferedWriter out = new BufferedWriter(new FileWriter(filename, true); out.write(dateString + : + s); out.write(n); out.close(); catch(Exception e) ; public static void log(String _dir, String s) try MakeDateTime qdt = new MakeDateTime(); String dateString = qdt.getTDashDa

42、te1(); String stemp = qdt.getDate8(); String filename = _dir; BufferedWriter out = new BufferedWriter(new FileWriter(filename, true); out.write(dateString + : + s); out.write(n); out.close(); catch(Exception e) ; *文件结束*文件开始*LogWriter.java*package ConnectionPool;import java.io.*;import java.util.*;pu

43、blic class LogWriter public static final int NONE = 0; public static final int ERROR = 1; public static final int INFO = 2; public static final int DEBUG = 3; private static final String ERROR_TEXT = error; private static final String INFO_TEXT = info; private static final String DEBUG_TEXT = debug;

44、 private PrintWriter pw; private String owner; private int logLevel; public LogWriter(String owner, int logLevel, PrintWriter pw) this.pw = pw; this.owner = owner; this.logLevel = logLevel; public LogWriter(String owner, int logLevel) this(owner, logLevel, null); public int getLogLevel() return logL

45、evel; public PrintWriter getPrintWriter() return pw; public void setLogLevel(int logLevel) this.logLevel = logLevel; public void setPrintWriter(PrintWriter pw) this.pw = pw; public void log(String msg, int severityLevel) if (pw != null) if (severityLevel + qdt.getDate(); System.out.println(String St

46、r = qdt.getDashDate(); = + qdt.getDashDate(); System.out.println(String Str = qdt.getDotDate(); = + qdt.getDotDate(); System.out.println(String Str = qdt.getTime(); = + qdt.getTime(); System.out.println(String Str = qdt.getTimeSSS(); = + qdt.getTimeSSS(); System.out.println(String Str = qdt.getDate1

47、7(); = + qdt.getDate17(); System.out.println(String Str = qdt.getTDate0(); = + qdt.getTDate0(); System.out.println(String Str = qdt.getTDate1(); = + qdt.getTDate1(); System.out.println(String Str = qdt.getTDashDate0(); = + qdt.getTDashDate0(); System.out.println(String Str = qdt.getTDashDate1(); = +

48、 qdt.getTDashDate1(); System.out.println(String Str = qdt.getTDotDate0(); = + qdt.getTDotDate0(); System.out.println(String Str = qdt.getTDotDate1(); = + qdt.getTDotDate1(); System.out.println(String Str = qdt.getDate14(); = + qdt.getDate14(); System.out.println(String Str = qdt.getDate8(); = + qdt.

49、getDate8(); System.out.println(String Str = qdt.getTime9(); = + qdt.getTime9(); System.out.println(String Str = qdt.getTime6(); = + qdt.getTime6(); System.out.println(String Str = qdt.getYear(); = + qdt.getYear(); System.out.println(String Str = qdt.getMonth(); = + qdt.getMonth(); System.out.println

50、(String Str = qdt.getDay(); = + qdt.getDay(); System.out.println(String Str = qdt.getHH(); = + qdt.getHH(); System.out.println(String Str = qdt.getMM(); = + qdt.getMM(); System.out.println(String Str = qdt.getSS(); = + qdt.getSS(); System.out.println(String Str = qdt.getSSS(); = + qdt.getSSS(); Syst

51、em.out.println(String Str = qdt.getRandom(); = + qdt.getRandom(); *文件结束*文件开始*PoolManager.java*package ConnectionPool;import java.io.*;import java.sql.*;import java.util.*;public class PoolManager static private PoolManager instance; static private int clients; private LogWriter logWriter; private Pr

52、intWriter pw; private Vector drivers = new Vector(); private Hashtable pools = new Hashtable(); private PoolManager() init(); static synchronized public PoolManager getInstance() if (instance = null) instance = new PoolManager(); clients+; return instance; private void init() / Log to System.err unt

53、il we have read the logfile property pw = new PrintWriter(System.err, true); logWriter = new LogWriter(PoolManager, LogWriter.INFO, pw); InputStream is = getClass().getResourceAsStream(./perties); Properties dbProps = new Properties(); try dbProps.load(is); catch (Exception e) / Make sure perties is

54、 in the CLASSPATH); logWriter.log(Cant read the properties file. + Make sure perties is in the CLASSPATH, LogWriter.ERROR); return; String logFile = dbProps.getProperty(logfile); if (logFile != null) try pw = new PrintWriter(new FileWriter(logFile, true), true); logWriter.setPrintWriter(pw); catch (

55、IOException e) logWriter.log(Cant open the log file: + logFile + . Using System.err instead, LogWriter.ERROR); loadDrivers(dbProps); createPools(dbProps); private void loadDrivers(Properties props) String driverClasses = props.getProperty(drivers); StringTokenizer st = new StringTokenizer(driverClas

56、ses); while (st.hasMoreElements() String driverClassName = st.nextToken().trim(); try Driver driver = (Driver) Class.forName(driverClassName).newInstance(); DriverManager.registerDriver(driver); drivers.addElement(driver); logWriter.log(Registered JDBC driver + driverClassName,LogWriter.INFO); catch

57、 (Exception e) logWriter.log(e, Cant register JDBC driver: + driverClassName, LogWriter.ERROR); private void createPools(Properties props) Enumeration propNames = pertyNames(); while (propNames.hasMoreElements() String name = (String) propNames.nextElement(); if (name.endsWith(.url) String poolName

58、= name.substring(0, name.lastIndexOf(.); String url = props.getProperty(poolName + .url); if (url = null) logWriter.log(No URL specified for + poolName,LogWriter.ERROR); continue; String user = props.getProperty(poolName + .user); String password = props.getProperty(poolName + .password); String max

59、Conns = props.getProperty(poolName+.maxconns, 0); int max; try max = Integer.valueOf(maxConns).intValue(); catch (NumberFormatException e) logWriter.log(Invalid maxconns value + maxConns + for + poolName, LogWriter.ERROR); max = 0; String initConns = props.getProperty(poolName +.initconns, 0); int i

60、nit; try init = Integer.valueOf(initConns).intValue(); catch (NumberFormatException e) logWriter.log(Invalid initconns value + initConns + for + poolName, LogWriter.ERROR); init = 0; String loginTimeOut = props.getProperty(poolName + .logintimeout, 5); int timeOut; try timeOut = Integer.valueOf(logi