dbwrapper.java

图书馆检索系统

package library;

import java.sql.*;
import java.io.*;
import java.util.StringTokenizer;
import java.util.Properties;
import java.util.Random;

/**
* Class <b>DBWrapper</b> contains 
* wrapper routines for using JDBC
* to access the database.
*
* @author dms
* @version 1.0
*/
public class DBWrapper
{
  
 	private static int CONNECTION_RETRIES = 10;
 	private static int QUERY_RETRIES = 10;
 	
    private String mSQL = "";   // Last called sql statement
    private int miCount;        // Count of last query

	private Connection dbCon;	

	/**
	* DBWrapper constructor
	*/
	public DBWrapper() {}

	/**
	* Returns last run SQL statement
	* @return String containing SQL
	*/
	public String getSQL() {
	    return mSQL;
	}
	
	/**
	* Returns record count of last query
	* @return int 
	*/
	public int getCount() {
	    return miCount;
	}
	
	/**
	* opens a database connection
	* @return true if success, false if otherwise
	*/
	public boolean openDatabase() {
	 
	 	// This is the only place in the library project where
	 	// the database connection parameters are specified.
	 	// So if you need to change them, you only have to
	 	// do it here.
	    String driverClassName = "org.postgresql.Driver";
	    String URL = "jdbc:postgresql:library";
	    String user = "webuser";
	    String password = "webuser";
	    
	    boolean opened = false;
	    
	    try {
	    	Driver driver = (Driver) Class.forName(driverClassName).newInstance();
	    	DriverManager.registerDriver(driver);
		} catch (ClassNotFoundException cnfe) {
			System.err.println(cnfe.toString() + "\nMake sure your PATH is set correctly\n");
			return false;	
		} catch (Exception e) {
			System.err.println(e.toString());
			return false;				
		}
	    
	    int retry = 0;
	    while (retry++ <= CONNECTION_RETRIES) {
	    
		    try {
		        dbCon = DriverManager.getConnection(URL, user, password);
		        opened = true;
		        break;
		        
		    } catch (Exception e) {
		    	
		    	System.err.println("Attempt #" + retry + " Failed:\nError opening database: " + e.toString());
		    }
		}
		
		return opened;
		
	}
	
	private String getFrom(String search) {
	    
	    // From a valid SQL statement, extract
	    // and return everything past the FROM
	    // keyword.
	    int iFromLocation = search.indexOf("FROM");
	    
	    if (iFromLocation > 0) {
	        String sAfterFrom = search.substring(iFromLocation + 5);
	        
	        int iOrderBy = sAfterFrom.indexOf("ORDER BY");
	        if (iOrderBy > 0) {
	            return sAfterFrom.substring(0,iOrderBy -1);
	        }
	        else
	            return sAfterFrom;
	    }
	    else return "";
	                
	}
	
	/**
	* Executes a query and returns a resultset.  Also
	* counts the number of records returned
	*
	* @param <b>String</b> containing a SQL statement
	* @param <b>boolean</b> if true, count the number of records
	* @return <b>Resultset</b> containing the resulting records
	* @throws SQLException
	*/
	public ResultSet runQuery(String sSQL, boolean count) throws SQLException {
	    
	    // Run the query to get the count
	    String sFrom = getFrom(sSQL);
		int retry = 0;
		
	    if (!sFrom.equals("") && count) {
	        
	        ResultSet rCount = null;
	    	while (retry++ < QUERY_RETRIES) {    
	        	try {
	        		Statement sCount = dbCon.createStatement();
	        		String sCountSQL = "";
	        		sCountSQL = "SELECT Count(*) FROM " + sFrom.trim();
	        		rCount = sCount.executeQuery(sCountSQL);
	        		break;
	        	} catch (SQLException se) {
	    			System.err.println(sSQL + "\n" + se.toString());
	        	}
	     	}
	     	
     		if (rCount == null) return null;
        	rCount.next();
        	miCount = rCount.getInt(1);
	    }
	
	    // Execute the query to get the resultset.
	    retry = 0;
	    while (retry++ < QUERY_RETRIES) {
	    	try {
	    		Statement s = dbCon.createStatement();
	    		ResultSet r = s.executeQuery(sSQL);
	    		return r;
	    	} catch (SQLException se) {
				System.err.println(sSQL + "\n" + se.toString());
			}
		}
		
		throw new SQLException();
	}
	
	/**
	* Executes a query and returns a resultset.  
	*
	* @param <b>String</b> containing a SQL statement
	* @return <b>Resultset</b> containing the resulting records
	* @throws SQLException
	*/
	public ResultSet runQuery(String sSQL) throws SQLException{
	
	    // Execute the query to get the resultset.
	    int retry = 0;
	    while (retry++ < QUERY_RETRIES) {

			try {
	    		Statement s = dbCon.createStatement();
	    		ResultSet r = s.executeQuery(sSQL);
	    		return r;
	    	} catch (SQLException se) {
	    		System.err.println(sSQL + "\n" + se.toString());
	    	}
	 	}
	 	
	 	throw new SQLException();
	}
	
	/**
	 * Executes an update style query
	 * @param <b>String</b> containing a SQL UPDATE, INSERT, or DELETE statement
	 * @throws SQLException
	 */ 
	public void executeQuery(String sSQL) throws SQLException {
	    
	    // Execute the query.
	    int retry = 0;
	    while (retry++ < QUERY_RETRIES) {
	    	try {
	    		Statement s = dbCon.createStatement();
	    		s.executeUpdate(sSQL);
	    		return;
	    	} catch (SQLException se) {
				System.err.println(se.toString());					    		
	    	}
	    	
	 	}
	    	
		throw new SQLException();
	}
}