querysender.java

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

            int dataType = -1;
            
            // the parameter input value
            String value = null;

            // register the out params
            for (int i = 0, n = outs.size(); i < n; i++) {
                //Log.debug("setting out at index: " + index);
                cstmnt.registerOutParameter(index, outs.get(i).getDataType());
                index++;
            }

            try {
            
                // register the in params
                for (int i = 0, n = ins.size(); i < n; i++) {
                    value = ins.get(i).getValue();
                    dataType = ins.get(i).getDataType();

                    if (MiscUtils.isNull(value) ||
                          value.equalsIgnoreCase(NULL)) {
                        cstmnt.setNull(index, dataType);
                    }
                    else {

                        switch (dataType) {

                            case Types.TINYINT:
                                byte _byte = Byte.valueOf(value).byteValue();
                                cstmnt.setShort(index, _byte);
                                break;

                            case Types.SMALLINT:
                                short _short = Short.valueOf(value).shortValue();
                                cstmnt.setShort(index, _short);
                                break;

                            case Types.CHAR:
                            case Types.VARCHAR:
                            case Types.LONGVARCHAR:
                                cstmnt.setString(index, value);
                                break;

                            case Types.BIT:
                            case Types.BOOLEAN:
                                boolean _boolean = Boolean.valueOf(value).booleanValue();
                                cstmnt.setBoolean(index, _boolean);
                                break;

                            case Types.BIGINT:
                                long _long = Long.valueOf(value).longValue();
                                cstmnt.setLong(index, _long);
                                break;

                            case Types.REAL:
                                float _float = Float.valueOf(value).floatValue();
                                cstmnt.setFloat(index, _float);
                                break;

                            case Types.INTEGER:
                                int _int = Integer.valueOf(value).intValue();
                                cstmnt.setInt(index, _int);
                                break;

                            case Types.DECIMAL:
                            case Types.NUMERIC:
                                cstmnt.setBigDecimal(index, new BigDecimal(value));
                                break;
    /*
                      case Types.DATE:
                      case Types.TIMESTAMP:
                      case Types.TIME:
                        cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value));
    */
                            case Types.FLOAT:
                            case Types.DOUBLE:
                                double _double = Double.valueOf(value).doubleValue();
                                cstmnt.setDouble(index, _double);
                                break;

                        }

                    }

                    // increment the index
                    index++;
                }
            
            }
            // catch formatting exceptions
            catch (Exception e) {
                statementResult.setOtherErrorMessage(
                        e.getClass().getName() + ": " + e.getMessage());
                return statementResult;
            }

        }
        
        try {
            cstmnt.clearWarnings();
            boolean hasResultSet = cstmnt.execute();
            Hashtable results = new Hashtable();

            if (hasOut) {
                // incrementing index
                int index = 1;
                
                // return value from each registered out
                String returnValue = null;

                for (int i = 0; i < param.length; i++) {
                    
                    int type = param[i].getType();
                    int dataType = param[i].getDataType();
                    
                    if (type == DatabaseMetaData.procedureColumnOut ||
                            type == DatabaseMetaData.procedureColumnResult ||
                            type == DatabaseMetaData.procedureColumnReturn ||
                            type == DatabaseMetaData.procedureColumnUnknown ||
                            type == DatabaseMetaData.procedureColumnInOut) {
                        
                        switch (dataType) {

                            case Types.TINYINT:
                                returnValue = Byte.toString(cstmnt.getByte(index));
                                break;

                            case Types.SMALLINT:
                                returnValue = Short.toString(cstmnt.getShort(index));
                                break;

                            case Types.CHAR:
                            case Types.VARCHAR:
                            case Types.LONGVARCHAR:
                                cstmnt.getString(index);
                                break;

                            case Types.BIT:
                            case Types.BOOLEAN:
                                returnValue = Boolean.toString(cstmnt.getBoolean(index));
                                break;

                            case Types.INTEGER:
                                returnValue = Integer.toString(cstmnt.getInt(index));
                                break;

                            case Types.BIGINT:
                                returnValue = Long.toString(cstmnt.getLong(index));
                                break;

                            case Types.REAL:
                                returnValue = Float.toString(cstmnt.getFloat(index));
                                break;

                            case Types.DECIMAL:
                            case Types.NUMERIC:
                                returnValue = cstmnt.getBigDecimal(index).toString();
                                break;

                            case Types.DATE:
                            case Types.TIMESTAMP:
                            case Types.TIME:
                                returnValue = cstmnt.getDate(index).toString();
                                break;

                            case Types.FLOAT:
                            case Types.DOUBLE:
                                returnValue = Double.toString(cstmnt.getDouble(index));
                                break;

                        }

                        if (returnValue == null) {
                            returnValue = "NULL";
                        }
                        results.put(param[i].getName(), returnValue);
                        index++;                        
                    }
                    
                }

            }

            if (!hasResultSet) {
                statementResult.setUpdateCount(cstmnt.getUpdateCount());
            } else {
                statementResult.setResultSet(cstmnt.getResultSet());
            }

            useCount++;
            statementResult.setOtherResult(results);
        }
        catch (SQLException e) {
            e.printStackTrace();
            statementResult.setSqlException(e);
        }
        catch (Exception e) {
            statementResult.setMessage(e.getMessage());
        }
        finally {
            if (cstmnt != null) {
                cstmnt.close();
            }
            cstmnt = null;
            closeConnection(conn);            
        }
        return statementResult;
    }
    
    /** <p>Executes the specified procedure 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 procedure to execute
     *  @return the query result
     */
    public SqlStatementResult executeProcedure(String query) throws Exception {

        if (!prepared()) {
            return statementResult;
        }
        
        //Log.debug("query " + query);
        
        String execString = "EXECUTE ";
        String callString = "CALL ";

        int nameIndex = -1;
        int index = query.toUpperCase().indexOf(execString);

        // check if EXECUTE was entered
        if (index != -1) {
            nameIndex = execString.length();
        } 
        else { // must be CALL
            nameIndex = callString.length();
        }

        // the procedure name
        String procedureName = null;
        
        // check for input brackets
        boolean possibleParams = false;
        index = query.indexOf("(", nameIndex);
        if (index != -1) {
            possibleParams = true;
            procedureName = query.substring(nameIndex, index);
        }
        else {
            procedureName = query.substring(nameIndex);
        }

        //Log.debug("name: " + procedureName);
        
        if (metaData == null) {
            metaData = new MetaDataValues(databaseConnection, false);
        }
        else {
            metaData.setDatabaseConnection(databaseConnection);
        }
        
        DatabaseProcedure procedure = 
                metaData.getProcedureColumns(null, null, procedureName);
        
        if (procedure != null) {
            
            if (possibleParams) {
                String params = query.substring(index + 1, query.indexOf(")"));
                if (!MiscUtils.isNull(params)) {

                    // check that the proc accepts params
                    if (!procedure.hasParameters()) {
                        statementResult.setSqlException(
                                new SQLException("Procedure call was invalid"));
                        return statementResult;
                    }
                    
                    int paramIndex = 0;
                    ProcedureParameter[] parameters = procedure.getParameters();
                    
                    // extract the parameters
                    StringTokenizer st = new StringTokenizer(params, ",");
                    while (st.hasMoreTokens()) {
                        String value = st.nextToken().trim();

                        // check applicable param
                        for (int i = paramIndex; i < parameters.length; i++) {
                            paramIndex++;

                            int type = parameters[i].getType();
                            if (type == DatabaseMetaData.procedureColumnIn ||
                                  type == DatabaseMetaData.procedureColumnInOut) {
                                
                                // check the data type and remove quotes if char
                                int dataType = parameters[i].getDataType();
                                if (dataType == Types.CHAR || 
                                        dataType == Types.VARCHAR || 
                                        dataType == Types.LONGVARCHAR) {

                                    if (value.indexOf("'") != -1) {
                                        // assuming quotes at start and end
                                        value = value.substring(1, value.length() - 1);
                                    }

                                }
                                

                                parameters[i].setValue(value);
                                break;
                            }
                        }

                    }

                }
            }
            
            // execute the procedure
            return executeProcedure(procedure);
        }
        else {
            statementResult.setSqlException(
                    new SQLException("Procedure or Function name specified is invalid"));
            return statementResult;
        }

        /*
        StringBuffer sb = new StringBuffer("{ call ");
        int indexOfExec = query.indexOf(execString);
        
        // check if EXECUTE was entered
        if (indexOfExec == -1) {
            sb.append(query.substring(
                          query.indexOf(callString) + 5, query.length()));
        }
        else {
            sb.append(query.substring(indexOfExec + 8, query.length()));
        }
        
        sb.append(" }");
        cstmnt = conn.prepareCall(sb.toString());
        boolean isResultSet = false;

        try {
            cstmnt.setEscapeProcessing(false);
            cstmnt.clearWarnings();
            
            isResultSet = cstmnt.execute();
            
            if (isResultSet) {
                ResultSet rs = cstmnt.getResultSet();
                statementResult.setResultSet(rs);
            }
            else {
                int result = cstmnt.getUpdateCount();
                if (result == -1) {
                    result = -10000;
                }
                statementResult.setUpdateCount(result);
            }

            useCount++;
            statementResult.setSqlWarning(cstmnt.getWarnings());
            
        }        
        catch (SQLException e) {
            statementResult.setSqlException(e);
        }
        finally {
            if (cstmnt != null) {
                cstmnt.close();
            }
            cstmnt = null;
            closeConnection(conn);
        }
        return statementResult;
         */
    }
    
    public SqlStatementResult executeQuery(String query) throws Exception {
        return executeQuery(getQueryType(query), query);
    }
    
    public SqlStatementResult executeQuery(int type, String query) throws Exception {
        statementResult.setType(type);

        switch (type) {
            case SELECT:
            case EXPLAIN:
                return getResultSet(query);
            case INSERT:
            case UPDATE:
            case DELETE:
            case DROP_TABLE:
            case CREATE_TABLE:
            case ALTER_TABLE:
            case CREATE_SEQUENCE:
            case CREATE_FUNCTION:
            case CREATE_PROCEDURE:
            case GRANT:
            case CREATE_SYNONYM:
                return updateRecords(query);

            case UNKNOWN:
                return execute(query);

            case DESCRIBE:
                int tableNameIndex = query.indexOf(" ");
                return getTableDescription(query.substring(tableNameIndex + 1));
                
            case EXECUTE:
                return executeProcedure(query);

            case COMMIT:
                return commitLast(true);

            case ROLLBACK:
                return commitLast(false);
            
            /*
            case CONNECT:
                return establishConnection(query.toUpperCase());
             */
        }
        return statementResult;
    }
    
    public SqlStatementResult execute(String query) throws Exception {
        return execute(query, true);
    }

    public SqlStatementResult execute(String query, boolean enableEscapes) 
        throws SQLException {

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

        stmnt = conn.createStatement();
        boolean isResultSet = false;
        
        try {
            stmnt.setEscapeProcessing(enableEscapes);
            isResultSet = stmnt.execute(query);
            
            if (isResultSet) {
                ResultSet rs = stmnt.getResultSet();
                statementResult.setResultSet(rs);
            }            
            else {
                int updateCount = stmnt.getUpdateCount();

                if (updateCount == -1)
                    updateCount = -10000;