executesql.java

一个用java写的地震分析软件(无源码)

package org.trinet.jdbc.table;
import java.sql.*;
import java.util.*;
import org.trinet.jdbc.*;
import oracle.jdbc.driver.*;
/** Class contains static methods to enable JDBC interactions with tables of the NCEDC schema over a specified JDBC connection. 
* Key methods are:<P>
* rowQuery(...)             returns the ResultSet of an SQL query.<BR>
* rowUpdate(...)            executes SQL modification statements (UPDATE, DELETE, INSERT).<BR>
* lockTableForUpdate(...)   locks the specified table in Oracle ROW EXCLUSIVE MODE for updates.<BR>
* setSelectForUpdate(...)   flags whether an "FOR UPDATE" clause is appended to SQL query string.<BR>
* setPrintStringSQL(...)    flags whether SQL strings are printed to System.out before execution.<BR>
* getRowCountXXX(...)       gets a row count.<BR>
* getEventXXXId()           returns the preferred id.<BR>
* getOriginXXXId()<BR>
* parseResults(...)         returns an object array containing Object arrays parsed from a JDBC ResultSet.<BR>
* parseAllRows(...)         returns an object array of DataTableRow objects parsed from a JDBC ResultSet.<BR>
* parseOneRow(...)          returns an DataTableRow object parsed from the current row of a JDBC ResultSet.<BR>
* @ see #rowQuery(Statement, String)
* @ see #rowUpdate(Connection, String).
* @ see #lockTableForUpdate(Connection, String, String)
* @ see #setPrintStringSQL(boolean)
* @ see #setSelectForUpdate(boolean)
* @ see #parseResults()
* @ see #parseAllRows()
* @ see #parseOneRow()
*/
public class ExecuteSQL {

/** Flag to indicate printing to System.out any SQL string before JDBC method execution.
*/
    private static boolean printStringSQLFlag = false;
  //    private static boolean printStringSQLFlag = true;

/** Flag for rowQuery(...) method to use the "FOR UPDATE" clause appended to the SQL query.
*/
    private static boolean selectForUpdateTableFlag = false;
/** String value appended to SQL query in rowQuery(...) method when isSelectForUpdate == true.
*/
    private static final String SELECT_FOR_UPDATE = " FOR UPDATE";

/** Enables the SQL string print option for query and update methods.
* @see #isPrintStringSQL()
* @see #rowQuery(Statement, String)
* @see #rowUpdate(Connection, String)
*/
    public static void setPrintStringSQL(boolean value) {
	printStringSQLFlag = value;
	return;
    }

/** Checks setting of SQL string print option for rowQuery and rowUpdate methods.
* @see #setPrintStringSQL(boolean)
*/
    public static boolean isPrintStringSQL() {
	return printStringSQLFlag;
    }

/** Enables FOR UPDATE option for all table queries with rowQuery(...) method.
* @see #isSelectForUpdate()
*/
    public static void setSelectForUpdate(boolean value) {
	selectForUpdateTableFlag = value;
	return;
    }

/** Checks status setting of "FOR UPDATE" option for table queries with any of rowQuery(...) method.
* @see #setSelectForUpdate(boolean)
*/
    public static boolean isSelectForUpdate() {
	return selectForUpdateTableFlag;
    }

/** Returns the count of database table rows satisfying: "SELECT  COUNT( countExpression ) WHERE whereCondition". 
* If countExpression string is null, returns COUNT(*), the whereCondition input string can be null.
* The countExpression string is usually "*" or a combination of "DISTINCT or ALL" and a column name.
* Except for "*" expression, null column values are not included in the count unless the NVL function is used
* in the COUNT expression argument to specify an alternative value string.
* Thus, getRowCount("*", null) and getRowCount("*", "") both return a count of all rows in the table.
* The queried table name is that initialized by the constructor of this object instance.<P>
* Returns -1 if an error occurs while executing the JDBC query.
*/
    public static int getRowCount(Connection conn, String tableName, String countExpression, String whereCondition) {
	ResultSet rs;
	int nrows = 0;
	if (conn == null) {
	    System.err.println("ExecuteSQL getRowCount: JDBC connection null;" +
		" application must first instantiate a connection class" +
		"; see JDBConnect(String url, String driverName, String user, String passwd)");
	    return -1;
	}
// Weird conditional operator, a demo:
	String tmpExprString = (countExpression != null) ? countExpression : "*";
	String tmpWhereString = (NullValueDb.isEmpty(whereCondition)) ? null :
		(whereCondition.toUpperCase().indexOf("WHERE") > 0) ? whereCondition : "WHERE " + whereCondition;

	Statement sm = null;
	try {
	    sm = conn.createStatement();
	    rs = sm.executeQuery("SELECT COUNT( " + tmpExprString + " ) FROM " + tableName + " " + tmpWhereString); 
	    rs.next();
	    nrows = rs.getInt(1);
	    rs.close();
	}
	catch (SQLException ex) {
	    System.err.println("ExecuteSQL: getCount SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
	    return -1;
	}
	try {
	    if (sm != null) sm.close();
	}
	catch (SQLException exc) {
	    SQLExceptionHandler.prtSQLException(exc);
	}
	return nrows;
    }

/** Returns the count of database table rows satisfying: "SELECT COUNT(*) FROM tableName". 
*/
    public static int getRowCount(Connection conn, String tableName) {
        return getRowCount(conn, tableName, "*", "");
    }

/** Returns the preferred ORIGIN table orid for the specified event id.
*/
    public static int getEventPreforId(Connection conn, int evid) {
	return getPrefId(conn , "EVENT", "PREFOR", "EVID", evid);
    }
/** Returns the preferred NETMAG table magid for the specified event id.
*/
    public static int getEventPrefmagId(Connection conn, int evid) {
	return getPrefId(conn , "EVENT", "PREFMAG", "EVID", evid);
    }
/** Returns the preferred MEC table mecid for the specified event id.
*/
    public static int getEventPrefmecId(Connection conn, int evid) {
	return getPrefId(conn , "EVENT", "PREFMEC", "EVID", evid);
    }
/** Returns the preferred NETMAG table magid for the specified origin id.
*/
    public static int getOriginPrefmagId(Connection conn, int orid) {
	return getPrefId(conn , "ORIGIN", "PREFMAG", "ORID", orid);
    }
/** Returns the preferred MEC table mecid for the specified origin id.
*/
    public static int getOriginPrefmecId(Connection conn, int orid) {
	return getPrefId(conn , "ORIGIN", "PREFMEC", "ORID", orid);
    }

/** Returns the value of SELECT prefColumnName FROM tableName WHERE keyColumnName = id.
* PrefColumnName refers to the preferred foreign key column such as EVENT.PREFOR == ORIGIN.ORID.
* Creates a JDBC statement for the specified input connection and executes an SQL query.
* A return value of -1 indicates an JDBC error.
*/
    static int getPrefId(Connection conn, String tableName, String prefColumnName, String keyColumnName, int id) {
	ResultSet rs;
	int prefid = 0;
	if (conn == null) {
	    System.err.println("ExecuteSQL getPreferredOriginId: JDBC connection null;" +
		" application must first instantiate a connection class" +
		"; see JDBConnect(String url, String driverName, String user, String passwd)");
	    return -1;
	}
	String sql = "SELECT " + prefColumnName + " FROM " + tableName + " WHERE " + keyColumnName + " = " + id;
	Statement sm = null;
	try {
	    sm = conn.createStatement();
	    rs = sm.executeQuery(sql);
	    rs.next();
	    prefid = rs.getInt(1);
	    rs.close();
	}
	catch (SQLException ex) {
	    System.err.println("ExecuteSQL: getCount SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
	    return -1;
	}
	try {
	    if (sm != null) sm.close();
	}
	catch (SQLException exc) {
	    SQLExceptionHandler.prtSQLException(exc);
	}
	return prefid;
    }

/** Returns ResultSet handle returned by executing JDBC Statement.executeQuery(...).
* The query text is specified by the input string argument.
* Appends "FOR UPDATE" to the query if isSelectForUpdate() == true.<P>
* Method is wrapper invoking rowQuery(Statement, String, boolean) where
* the boolean can be set with the method setPrintStringSQL(), default value == false.
* Prints the SQL string to System.out before execution, if isPrintStringSQL() == true.<P>
* Returns null if the input JDBC Statement argument is null or an error occurs while executing.
* @see #isPrintStringSQL()
* @see #setPrintStringSQL(boolean)
*/
    public static ResultSet rowQuery(Statement sm, String sql) {
	return rowQuery(sm, sql, printStringSQLFlag);
    }

/** Returns ResultSet handle returned by executing JDBC Statement.executeQuery(...).
* The query text is specified by the input string argument.
* Appends "FOR UPDATE" to the query if isSelectForUpdate() == true.
* Prints the SQL string to System.out before execution, if print == true.<P>
* Returns null if the input JDBC Statement argument is null or an error occurs while executing.
*/
    public static ResultSet rowQuery(Statement sm, String sql, boolean print) {
	ResultSet rs;
	if (sm== null) {
	    System.err.println("ExecuteSQL rowQuery: JDBC connection statement null;" +
		" application must first instantiate a connection class and createStatement()" +
		"; see JDBConnect(String url, String driverName, String user, String passwd)");
	    return null;
	}
	String query = sql;
	if (isSelectForUpdate()) {
	    if (query.toUpperCase().indexOf(SELECT_FOR_UPDATE) < 0)  query = sql + SELECT_FOR_UPDATE;
/*
	    try {
	        ((OracleStatement) sm).setRowPrefetch(0);
	    }
	    catch (SQLException ex) {
		SQLExceptionHandler.prtSQLException(ex);
	    }
*/
	} 
	try {
//	    System.out.println("TEST of ExecuteSQL.rowQuery(...) SQL string:\n" + query);
	    if (print) System.out.println("ExecuteSQL.rowQuery(...) SQL string:\n" + query);
	    rs = sm.executeQuery(query); 
	}
	catch (SQLException ex) {
	    System.err.println("ExecuteSQL: rowQuery executeQuery statement SQLException");
	    System.err.println("ExecuteSQL.rowQuery SQL string:\n" + query);
	    SQLExceptionHandler.prtSQLException(ex);
	    return null;
	}
	return rs;
    }

// Methods to modify database table rows 
/** Executes JDBC SQL statement to lock named input table in mode specified in input string.<BR>
* Returns true if successful, otherwise false.
*/
    public static boolean lockTableForUpdate(Connection conn, String tableName, String mode) {
        String lockTableString = "LOCK TABLE " + tableName + " " + mode;
	boolean retVal = true;
	if (conn == null) {
	    System.err.println("ExecuteSQL lockTableForUpdate: JDBC connection input argument null;" +
		" application must first instantiate a connection class" +
		"; see JDBConnect(String url, String driverName, String user, String passwd)");
	    return false;
	}
	Statement sm = null;
	try {
	    sm = conn.createStatement();
	    //	    retVal = sm.execute(lockTableString); 
	    boolean stat = sm.execute(lockTableString); 

	}
	catch (SQLException ex) {
	    System.err.println("ExecuteSQL: lockTableForUpdate execute lock statement SQLException");
	    SQLExceptionHandler.prtSQLException(ex);
	    retVal =  false;
	}
	finally {
	    try { if (sm != null) sm.close(); }
	    catch (SQLException ex) {
		SQLExceptionHandler.prtSQLException(ex);