querysender.java

eq跨平台查询工具源码 eq跨平台查询工具源码

/*
 * QuerySender.java
 *
 * Copyright (C) 2002, 2003, 2004, 2005, 2006 Takis Diakoumis
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 */


package org.executequery.databasemediators;

import java.math.BigDecimal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Hashtable;
import java.util.List;
import java.util.StringTokenizer;
import org.executequery.datasource.ConnectionManager;
import org.executequery.util.Log;
import org.underworldlabs.jdbc.DataSourceException;
import org.underworldlabs.util.MiscUtils;


/* ----------------------------------------------------------
 * CVS NOTE: Changes to the CVS repository prior to the 
 *           release of version 3.0.0beta1 has meant a 
 *           resetting of CVS revision numbers.
 * ----------------------------------------------------------
 */

/** 
 * This class handles all database query functions
 * such as the execution of SQL SELECT, INSERT, UPDATE
 * etc statements.
 *
 * <p>This class will typically be used by the Database
 * Browser or Query Editor where all SQL statements to be
 * executed will pass through here. In the case of a Query
 * Editor, a dedicated connection is maintained by this class
 * for the editor's use. This was shown to decrease some overhead
 * associated with constantly retrieving conenctions from the
 * pool. Also, when the commit mode is not set to auto-commit
 * within an editor, a dedicated connection is required
 * so as to maintain the correct rollback segment.
 *
 * @author   Takis Diakoumis
 * @version  $Revision: 1.6 $
 * @date     $Date: 2006/05/26 01:56:46 $
 */
public class QuerySender {
    
    /** Whether this object is owned by a QueryEditor instance */
    private boolean keepAlive;
    
    /** The connection's commit mode */
    private boolean commitMode;
    
    /** The database connection */
    private Connection conn;
    
    /** The database <code>Statement</code> object */
    private Statement stmnt;
    
    /** The database <code>CallableStatement</code> object */
    private CallableStatement cstmnt;
    
    /** The connection use count */
    private int useCount = 0;
    
    /** The specified maximum connection use count */
    private int maxUseCount;

    /** the query result object */
    private SqlStatementResult statementResult;
    
    /** the database connection properties object */
    private DatabaseConnection databaseConnection;
    
    /** the meta data value retrieval object */
    private MetaDataValues metaData;
    
    // ---------------------------------
    // SQL statement type int constants
    // ---------------------------------
    
    public static final int ALL_UPDATES = 80;
    
    /** An SQL INSERT statement */
    public static final int INSERT = 80;
    /** An SQL UPDATE statement */
    public static final int UPDATE = 81;
    /** An SQL DELETE statement */
    public static final int DELETE = 82;
    /** An SQL SELECT statement */
    public static final int SELECT = 10;
    /** A DESCRIBE statement - table meta data */
    public static final int DESCRIBE = 16;
    /** An SQL EXPLAIN statement */
    public static final int EXPLAIN = 15;
    /** An SQL EXECUTE statement (procedure) */
    public static final int EXECUTE = 11;
    /** An SQL DROP TABLE statement */
    public static final int DROP_TABLE = 20;
    /** An SQL CREATE TABLE statement */
    public static final int CREATE_TABLE = 21;
    /** An SQL ALTER TABLE statement */
    public static final int ALTER_TABLE = 22;
    /** An SQL CREATE SEQUENCE statement */
    public static final int CREATE_SEQUENCE = 23;
    /** An SQL CREATE FUNCTION statement */
    public static final int CREATE_FUNCTION = 26;
    /** An SQL CREATE PROCEDURE statement */
    public static final int CREATE_PROCEDURE = 25;
    /** An SQL GRANT statement */
    public static final int GRANT = 27;
    /** An SQL GRANT statement */
    public static final int CREATE_SYNONYM = 28;
    /** An unknown SQL statement */
    public static final int UNKNOWN = 99;
    /** A commit statement */
    public static final int COMMIT = 12;
    /** A rollback statement */
    public static final int ROLLBACK = 13;
    /** A connect statement */
    public static final int CONNECT = 14;
        
    /** <p>Creates a new instance */
    public QuerySender() {
        this(null, false);
    }

    /** 
     * Creates a new instance with the specified connection
     * properties object as the connection provider and a keep flag 
     * that determines whether connections are retained or closed between
     * requests.
     *
     * @param the connection properties object
     * @param whether the connection should be kept between requests 
     */
    public QuerySender(DatabaseConnection databaseConnection) {
        this(databaseConnection, false);
    }

    /** 
     * Creates a new instance with the specified connection
     * properties object as the connection provider and a keep flag 
     * that determines whether connections are retained or closed between
     * requests.
     *
     * @param the connection properties object
     * @param whether the connection should be kept between requests 
     */
    public QuerySender(DatabaseConnection databaseConnection, boolean keepAlive) {
        this.keepAlive = keepAlive;
        this.databaseConnection = databaseConnection;
        maxUseCount = ConnectionManager.getMaxUseCount();
        statementResult = new SqlStatementResult();
    }
    
    /** <p>Retrieves a description of the specified table using
     *  the connection's <code>DatabaseMetaData</code> object
     *  and the method <code>getColumns(...)</code>.
     *
     *  @param  the table name to describe
     *  @return the query result
     */
    public SqlStatementResult getTableDescription(String tableName) throws Exception {

        if (!prepared()) {
            return statementResult;
        }
        
        try {
            
            /* -------------------------------------------------
             * Database meta data values are case-sensitive.
             * search for a match and use as returned from dmd.
             * -------------------------------------------------
             */

            String _tableName = null;
            String _schemaName = null;
            String schemaName = databaseConnection.getUserName();
            
            boolean valueFound = false;
            DatabaseMetaData dmd = conn.getMetaData();
            ResultSet rs = dmd.getSchemas();

            while (rs.next()) {
                _schemaName = rs.getString(1);
                if (_schemaName.equalsIgnoreCase(schemaName)) {
                    valueFound = true;
                    break;
                }
            } 
            rs.close();

            if (!valueFound) {
                _schemaName = null;
            }
            
            valueFound = false;
            rs = dmd.getTables(null, _schemaName, null, null);
            
            while (rs.next()) {
                _tableName = rs.getString(3);
                if (_tableName.equalsIgnoreCase(tableName)) {
                    valueFound = true;
                    break;
                }
            }             
            rs.close();
            
            if (!valueFound) {
                statementResult.setMessage("Invalid table name");
            }
            else {
                rs = dmd.getColumns(null, _schemaName, _tableName, null);
                statementResult.setResultSet(rs);
            }

        }
        catch (SQLException e) {
            statementResult.setSqlException(e);
            if (stmnt != null) {
                stmnt.close();
            }
            closeConnection(conn);
        }
        catch (OutOfMemoryError e) {
            statementResult.setMessage(e.getMessage());
            releaseResources();
        }
        return statementResult;        
    }
    
    private boolean prepared() throws SQLException {

        if (databaseConnection == null || 
                !databaseConnection.isConnected()) {
            statementResult.setMessage("Not Connected");
            return false;
        }
        
        // check the connection is valid
        if (conn == null) {
            try {
                conn = ConnectionManager.getConnection(databaseConnection);
                if (keepAlive) {
                    conn.setAutoCommit(commitMode);
                }
                useCount = 0;
            } catch (DataSourceException e) {
                handleDataSourceException(e);
            }
        }
        // check its still open
        else if (conn.isClosed()) {
            statementResult.setMessage("Connection closed.");
            return false;
        }

        statementResult.reset();
        if (conn != null) { // still null?
            conn.clearWarnings();
        } else {
            statementResult.setMessage("Connection closed.");
            return false;
        }
        return true;
    }
    
    /** <p>Executes the specified query (SELECT) and returns
     *  a <code>ResultSet</code> object from this query.
     *  <p>If an exception occurs, null is returned and
     *  the relevant error message, if available, assigned
     *  to this object for retrieval.
     *
     *  @param  the SQL query to execute
     *  @return the query result
     */
    public SqlStatementResult getResultSet(String query)  throws SQLException {
        
        if (!prepared()) {
            return statementResult;
        }
        
        stmnt = conn.createStatement();
        
        try {
            ResultSet rs = stmnt.executeQuery(query);
            statementResult.setResultSet(rs);
            useCount++;
            return statementResult;
        }
        catch (SQLException e) {
            statementResult.setSqlException(e);
            if (stmnt != null) {
                stmnt.close();
            }
            closeConnection(conn);
            return statementResult;
        }
        
    }

    /** <p>Executes the specified procedure.
     *
     *  @param  the SQL procedure to execute
     *  @return the query result
     */
    public SqlStatementResult executeProcedure(DatabaseProcedure proc) throws Exception {

        if (!prepared()) {
            return statementResult;
        }

        ProcedureParameter[] param = proc.getParameters();
        Arrays.sort(param, new ParameterSorter());

        String procQuery = null;
        boolean hasOut = false;
        boolean hasParameters = (param != null && param.length > 0);

        List<ProcedureParameter> outs = null;
        List<ProcedureParameter> ins = null;

        if (hasParameters) {
            
            // split the params into ins and outs
            outs = new ArrayList<ProcedureParameter>();
            ins = new ArrayList<ProcedureParameter>();

            int type = -1;
            for (int i = 0; i < param.length; i++) {
                type = param[i].getType();                
                if (type == DatabaseMetaData.procedureColumnIn ||
                      type == DatabaseMetaData.procedureColumnInOut) {
                    
                    // add to the ins list
                    ins.add(param[i]);
                    
                }
                else if (type == DatabaseMetaData.procedureColumnOut ||
                            type == DatabaseMetaData.procedureColumnResult ||
                            type == DatabaseMetaData.procedureColumnReturn ||
                            type == DatabaseMetaData.procedureColumnUnknown ||
                            type == DatabaseMetaData.procedureColumnInOut) {

                    // add to the outs list
                    outs.add(param[i]);

                }
            }

            char QUESTION_MARK = '?';
            String COMMA = ", ";

            // init the string buffer
            StringBuffer sb = new StringBuffer("{ ");

            // build the out params place holders
            for (int i = 0, n = outs.size(); i < n; i++) {
                sb.append(QUESTION_MARK);
                if (i < n - 1) {
                    sb.append(COMMA);
                }
            }

            sb.append(" = call ");
            
            if (proc.getSchema() != null) {
               sb.append(proc.getSchema()).
                  append('.');
            }

            sb.append(proc.getName()).
               append("( ");
            
            // build the ins params place holders
            for (int i = 0, n = ins.size(); i < n; i++) {
                sb.append(QUESTION_MARK);
                if (i < n - 1) {
                    sb.append(COMMA);
                }
            }

            sb.append(" ) }");
            
            // determine if we have out params
            hasOut = !(outs.isEmpty());
            procQuery = sb.toString();
        }
        else {
            StringBuffer sb = new StringBuffer();
            sb.append("{ call ");

            if (proc.getSchema() != null) {
               sb.append(proc.getSchema()).
                  append('.');
            }

            sb.append(proc.getName()).
               append("( ) }");

            procQuery = sb.toString();
        }

        //Log.debug(procQuery);

        // null value literal
        String NULL = "null";
        
        // whether a result set is returned
        boolean isResultSet = false;

        // clear any warnings
        conn.clearWarnings();
        
        Log.info("Executing: " + procQuery);

        try {
            // prepare the statement
            cstmnt = conn.prepareCall(procQuery);
        } catch (SQLException e) {
            statementResult.setSqlException(e);
            return statementResult;
        }

        // check if we are passing parameters
        if (hasParameters) {
            // the parameter index counter
            int index = 1;
            
            // the java.sql.Type value