psqlpool.java

利用广度优先遍历搜索一定范围内的所有网页,可用于建立搜索引擎和查找网络错误.

package db;


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

public abstract class PsqlPool {
	public static Hashtable<Connection, Boolean> connections = new Hashtable<Connection, Boolean>();;
	private static int increment=2;		// the number of connections to be incremented at a time.
	private static int intialConnections=1;
	private static String dbURL, user, password;

	//public PsqlPool(String connStr, String username, String pwd ) {
	public static void init(String connStr, String username, String pwd ) {
		dbURL = connStr;
		user = username;
		password = pwd;

		MyPrintStream.out.println("[PsqlPool] User: " + user + ", Password: " + password + ", Connection string: " + dbURL);
		//DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());

		// load the postgresql driver
		try {
			// load the postgresql driver
			Class.forName("org.postgresql.Driver");	
		}
		catch (ClassNotFoundException e) {
			System.err.println("[PsqlPool] Can't load the postgresql driver.");
			System.exit(1);
		}

		try {
			if( connections.size() == 0 ) {
				MyPrintStream.out.println("[PsqlPool] Initializing PostgreSQL Connection Pool.");
				// Put pool of connection into hashtable
				// set value to false to show connection not in use
				for( int i = 0; i < intialConnections; i++ ) {
					MyPrintStream.out.println("[PsqlPool] Pool count: " + i);
					//Connection conn = DriverManager.getConnection(dbURL, user, password);
					connections.put(DriverManager.getConnection(dbURL, user, password), Boolean.FALSE);
				} // for i
			} // if
		} catch(SQLException e) {
			//System.err.println("[PsqlPool] Creation of Pool instance un-successful");
			e.printStackTrace( MyPrintStream.out );
		}
	}

	public static Connection getConnection() throws SQLException {
		Connection con = null;

		Enumeration conxxx = connections.keys();
		int y=0;
		while( conxxx.hasMoreElements() ) {
			y++;
			con = (Connection)conxxx.nextElement();
			
			if( _DEBUG_PSQLPOOL )
				MyPrintStream.out.println("[PsqlPool.getConnection] Pool #" + y + ", in-use: " + connections.get(con).toString());
		} // while

		Enumeration cons = connections.keys();
		synchronized( connections ) {
			y=0;
			while( cons.hasMoreElements() ) {
				y++;
				con = (Connection)cons.nextElement();

				Boolean b = (Boolean)connections.get(con);
				if( b == Boolean.FALSE ) {
					try{
						if( _DEBUG_PSQLPOOL )
							MyPrintStream.out.println("[PsqlPool.getConnection] Attempting to use connection pool #" + y);
						//Found an unused connection
						//test to determine if good connection
						// If the connection isn't operable, you'll get an SQLException if
						// you attempt to execute anything over it
						con.setAutoCommit(true);

						Statement stmt = con.createStatement();
						//MyPrintStream.out.println("[PsqlPool.getConnection] Statement stmt = " + stmt);

						ResultSet rs = stmt.executeQuery("select 1+3");

						// Do some arbitrary database work
						if( rs!=null ) {
							while (rs.next()) {;}
						}
						rs.close();
						stmt.close();
					} catch(SQLException e) {
						//MyPrintStream.out.println("[PsqlPool.getConnection] Use of Pool #" + y + " Failed! Replacing this Connection Object.");
						e.printStackTrace( MyPrintStream.out );

						//problem with connection - replace the connection
						//con = DriverManager.getConnection(dbURL, user, password);
					} //catch sqlexception

					// Update connection hash table to show connection in use
					connections.put(con, Boolean.TRUE);

					// return the connection
					return con;
				} //if con not in use?
			} //end of while loop
		} //end of synchronized block

		// if reach this point there were no free connection objects
		// need to create some more
		MyPrintStream.out.println("[PsqlPool.getConnection] No connection pool available. Increment the pool size by "+increment+" to "+(connections.size()+2)+".");
		for( int i=0; i<increment; i++ ) {
			connections.put(DriverManager.getConnection(dbURL, user, password), Boolean.FALSE);
		}
		// Recurse to get one of the new connection
		return getConnection();
	}

	public static void returnConnection(Connection returned){
		Connection con;
		Enumeration cons = connections.keys();
		while(cons.hasMoreElements()){
			con = (Connection)cons.nextElement();
			if( con == returned ) {
				connections.put(con, Boolean.FALSE);
				break;
			} // if
		} // while
		if( _DEBUG_PSQLPOOL )
			MyPrintStream.out.println("[PsqlPool.returnConnection] returned.");
	}
	public static void connClose(ResultSet rs, Statement stmt, Connection conn){
		try{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) returnConnection(conn);
		} catch (Exception e) {
			e.printStackTrace( MyPrintStream.out );
		}
	}
	public static int size() {
		return connections.size();
	}
	@SuppressWarnings("unused")
	private void stmtClose(ResultSet rs, Statement stmt){
		try{
			if( rs!=null ) rs.close();
			if( stmt!=null ) stmt.close();
		}
		catch (Exception e){
			System.out.print("Exception in pool.stmtClose()\r\n " + e);
			e.printStackTrace();
		}
	}

	public static void main (String args[]) {
		String dbHostName = "jdbc:postgresql://localhost:5432/crawler";
		String dbUsername = "postgres";
		String dbPassword = "postgres";

		//PsqlPool pool = new PsqlPool(dbHostName, dbUsername, dbPassword);
		PsqlPool.init(dbHostName, dbUsername, dbPassword);
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;

		String buf;

		try {
			for( int j=0; j<5; j++ ) {
				//pool = new PsqlPool(dbHostName, dbUsername, dbPassword);
				//conn = pool.getConnection();
				conn = PsqlPool.getConnection();
				stmt = conn.createStatement();

				rs = stmt.executeQuery(
						//"SELECT * FROM link WHERE target = 'http://www.apache.org/' ORDER BY source"
						"SELECT count(*) FROM link"
				);

				while (rs.next()) {
					buf = rs.getString(1);

					MyPrintStream.out.println("Tuple count: " + buf);
				} // End of while (rs.next())
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		//pool.connClose(rs, stmt, conn);
	} //End of main

	private static boolean _DEBUG_PSQLPOOL = false;
}