com.netspective.axiom.sql.StoredProcedureParameter.java Source code

Java tutorial

Introduction

Here is the source code for com.netspective.axiom.sql.StoredProcedureParameter.java

Source

/*
 * Copyright (c) 2000-2004 Netspective Communications LLC. All rights reserved.
 *
 * Netspective Communications LLC ("Netspective") permits redistribution, modification and use of this file in source
 * and binary form ("The Software") under the Netspective Source License ("NSL" or "The License"). The following
 * conditions are provided as a summary of the NSL but the NSL remains the canonical license and must be accepted
 * before using The Software. Any use of The Software indicates agreement with the NSL.
 *
 * 1. Each copy or derived work of The Software must preserve the copyright notice and this notice unmodified.
 *
 * 2. Redistribution of The Software is allowed in object code form only (as Java .class files or a .jar file
 *    containing the .class files) and only as part of an application that uses The Software as part of its primary
 *    functionality. No distribution of the package is allowed as part of a software development kit, other library,
 *    or development tool without written consent of Netspective. Any modified form of The Software is bound by these
 *    same restrictions.
 *
 * 3. Redistributions of The Software in any form must include an unmodified copy of The License, normally in a plain
 *    ASCII text file unless otherwise agreed to, in writing, by Netspective.
 *
 * 4. The names "Netspective", "Axiom", "Commons", "Junxion", and "Sparx" are trademarks of Netspective and may not be
 *    used to endorse or appear in products derived from The Software without written consent of Netspective.
 *
 * THE SOFTWARE IS PROVIDED "AS IS" WITHOUT A WARRANTY OF ANY KIND. ALL EXPRESS OR IMPLIED REPRESENTATIONS AND
 * WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT,
 * ARE HEREBY DISCLAIMED.
 *
 * NETSPECTIVE AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE OR ANY THIRD PARTY AS A
 * RESULT OF USING OR DISTRIBUTING THE SOFTWARE. IN NO EVENT WILL NETSPECTIVE OR ITS LICENSORS BE LIABLE FOR ANY LOST
 * REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER
 * CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THE SOFTWARE, EVEN
 * IF IT HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 */
package com.netspective.axiom.sql;

import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import javax.naming.NamingException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.netspective.axiom.ConnectionContext;
import com.netspective.commons.value.Value;
import com.netspective.commons.value.ValueContext;
import com.netspective.commons.value.ValueSource;
import com.netspective.commons.xdm.XdmEnumeratedAttribute;
import com.netspective.commons.xdm.XdmParseContext;
import com.netspective.commons.xdm.XmlDataModelSchema;
import com.netspective.commons.xdm.exception.DataModelException;

/**
 * Class representing an in or out parameter of a callable statement object
 */
public class StoredProcedureParameter implements XmlDataModelSchema.ConstructionFinalizeListener {
    public static class Type extends XdmEnumeratedAttribute {
        public static final int IN = 0;
        public static final int OUT = 1;
        public static final int IN_OUT = 2;

        public static final String IN_NAME = "in";
        public static final String OUT_NAME = "out";
        public static final String IN_OUT_NAME = "in-out";

        public static final String[] VALUES = new String[] { IN_NAME, OUT_NAME, IN_OUT_NAME };

        public Type() {
            super();
        }

        public Type(int index) {
            super(index);
        }

        public String[] getValues() {
            return VALUES;
        }
    }

    private Log log = LogFactory.getLog(StoredProcedureParameter.class);
    private StoredProcedureParameters parent;
    private String name;
    /* configured value for this parameter */
    private ValueSource value;
    private QueryParameterType paramType;
    private int index;
    private Type type;
    /* declares whether or not the bind column is allowed to have a SQL NULL */
    private boolean allowNull = false;
    /* declares the name of the array type (e.g create or replace type NUM_ARRAY as table of number;) */
    private String typeName;

    public StoredProcedureParameter(StoredProcedureParameters parent) {
        setParent(parent);
    }

    /**
     * Sets the type of value the parameter will contain
     */
    public void setSqlType(QueryParameterTypeEnumeratedAttribute sqlType) {
        String paramTypeName = sqlType.getValue();
        if (paramTypeName != null) {
            paramType = QueryParameterType.get(paramTypeName);
            if (paramType == null)
                throw new RuntimeException("param type '" + paramTypeName + "' is invalid for param '" + getName()
                        + "' in stored procedure '" + parent.getProcedure().getQualifiedName() + "'");
        }
    }

    /**
     * Gets the special TYPE defined in database
     */
    public String getTypeName() {
        return typeName;
    }

    /**
     * Sets the special TYPE defined in database
     * (e.g create or replace type NUM_ARRAY as table of number;)
     * <p/>
     * IMPORTANT: PLSQL types are known to exactly PLSQL and PLSQL alone. SQL types -- stored in the data dictionary --
     * are visible to all, usable by all. you must use a SQL type via create type.
     */
    public void setTypeName(String typeName) {
        this.typeName = typeName;
    }

    /**
     * Gets the parameter's value type object
     */
    public QueryParameterType getSqlType() {
        return paramType;
    }

    /**
     * Gets the parameters java data type name
     */
    public String getSqlIdentifierType() {
        return (paramType != null ? paramType.getIdentifier() : null);
    }

    /**
     * Gets the parameter's JDBC data type
     */
    public int getSqlJdbcType() {
        return (paramType != null ? paramType.getJdbcType() : -999999);
    }

    /**
     * Gets the type of parameter (IN, OUT, or IN/OUT)
     */
    public Type getType() {
        return type;
    }

    /**
     * Sets the type of parameter ( in or Out)
     */
    public void setType(Type type) {
        this.type = type;
    }

    /**
     * @param pc
     * @param element
     * @param elementName
     *
     * @throws DataModelException
     */
    public void finalizeConstruction(XdmParseContext pc, Object element, String elementName)
            throws DataModelException {
        // The In, out, or in/out type MUST be defined
        if (type == null) {
            RuntimeException e = new RuntimeException("Parameter '" + getName() + "'  for stored procedure '"
                    + this.parent.getProcedure().getQualifiedName() + "' has no 'type' attribute.");
            this.parent.getProcedure().getLog().error(e.getMessage(), e);
            throw e;
        }
        if (value == null) {
            RuntimeException e = new RuntimeException("Parameter '" + getName() + "'  for stored procedure '"
                    + this.parent.getProcedure().getQualifiedName() + "' has no 'value' or 'values' attribute.");
            this.parent.getProcedure().getLog().error(e.getMessage(), e);
            throw e;
        }
        // NOTE: The Sql Type MUST be defined, if it is not it will default to VARCHAR!
    }

    /**
     * Apply the IN/OUT parameters of the callable statement object
     *
     * @param vac  the context in which the apply action is being performed
     * @param stmt the statement object
     */
    public void apply(StoredProcedureParameters.ValueApplyContext vac, ConnectionContext cc, CallableStatement stmt)
            throws SQLException {
        int paramNum = vac.getNextParamNum();
        int jdbcType = getSqlType().getJdbcType();
        if (getType().getValueIndex() == Type.IN || getType().getValueIndex() == Type.IN_OUT) {
            String text = value.getTextValue(cc);
            switch (jdbcType) {
            case Types.VARCHAR:
                // user override value if it exists
                if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                    text = (String) vac.getActiveParamOverrideValue();
                if (allowNull && text == null)
                    stmt.setNull(paramNum, Types.VARCHAR);
                else if (text != null)
                    stmt.setObject(paramNum, text);
                else
                    log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
                break;

            case Types.CHAR:
                if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                    text = (String) vac.getActiveParamOverrideValue();
                if (allowNull && text == null)
                    stmt.setNull(paramNum, Types.CHAR);
                else if (text != null)
                    stmt.setObject(paramNum, text.substring(0, 1));
                else
                    log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
                break;

            case Types.INTEGER:
                if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                    text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                            : null;
                if (allowNull && text == null)
                    stmt.setNull(paramNum, Types.INTEGER);
                else if (text != null)
                    stmt.setInt(paramNum, Integer.parseInt(text));
                else
                    log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

                break;

            case Types.DOUBLE:
                if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                    text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                            : null;
                if (allowNull && text == null)
                    stmt.setNull(paramNum, Types.DOUBLE);
                else if (text != null)
                    stmt.setDouble(paramNum, Double.parseDouble(text));
                else
                    log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

                break;

            case Types.ARRAY:
                // Arrays are quite tricky. Right now, this is supporting String arrays only.
                // TODO: Support integer and float arrays also
                String[] textValues = value.getTextValues(cc);
                if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                    textValues = (String[]) vac.getActiveParamOverrideValue();
                applyInArrayValue(cc, stmt, paramNum, textValues);
                break;
            default:
                log.warn("Unknown JDBC type for parameter '" + getName() + "' (index=" + paramNum
                        + ") of stored procedure '" + parent.getProcedure() + "'.");
                break;
            }
        }
        if (getType().getValueIndex() == Type.OUT || getType().getValueIndex() == Type.IN_OUT) {
            String identifier = getSqlType().getIdentifier();
            // result sets are returned differently for different vendors
            if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER))
                stmt.registerOutParameter(paramNum, getVendorSpecificResultSetType(cc));
            else
                stmt.registerOutParameter(paramNum, jdbcType);
        }
    }

    /**
     * Applies an array object as an IN parameter
     *
     * @param cc       Connection context
     * @param stmt     Callable statement object
     * @param paramNum the index
     * @param array    the java array object
     */
    private void applyInArrayValue(ConnectionContext cc, CallableStatement stmt, int paramNum, Object[] array) {
        // TODO: This is the initial implementation. NOT TESTED YET. 01/13/2004 AT
        try {
            if (isOracleDriver(cc)) {
                // doing the following ORACLE specific calls
                // oracle.sql.ArrayDescriptor descriptor =  oracle.sql.ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
                // oracle.sql.ARRAY array_to_pass = new oracle.sql.ARRAY( descriptor, conn, javaArrray );
                // stmt.setARRAY( paramNum, array_to_pass );

                // create the array descriptor
                Class arrayDescriptorClass = Class.forName("oracle.sql.ArrayDescriptor");
                Method createDescriptor = arrayDescriptorClass.getMethod("createDescriptor",
                        new Class[] { String.class, Connection.class });
                Object descriptor = createDescriptor.invoke(null,
                        new Object[] { getTypeName(), cc.getConnection() });

                // create the array to pass to the database
                Class arrayClass = Class.forName("oracle.sql.ARRAY");
                Constructor arrayConstructor = arrayClass.getConstructor(
                        new Class[] { arrayDescriptorClass, Connection.class, getSqlType().getJavaClass() });
                arrayConstructor.newInstance(new Object[] { descriptor, cc.getConnection(), array });

                Class oracleStmt = Class.forName("oracle.jdbc.OracleCallableStatement");
                Method setArrayMethod = oracleStmt.getMethod("setARRAY", new Class[] { int.class, arrayClass });
                setArrayMethod.invoke(stmt, new Object[] { new Integer(paramNum), array });
            }
        } catch (Exception e) {
            log.error("Failed to apply the ARRAY IN parameter at index " + paramNum + " in stored procedure " + "'"
                    + parent.getProcedure() + "'.");
        }
    }

    /**
     * Checks to see if the connection context is connected to an Oracle database
     */
    private boolean isOracleDriver(ConnectionContext cc) throws NamingException, SQLException {
        String driverName = cc.getConnection().getMetaData().getDriverName();
        return (driverName.indexOf("Oracle") != -1) ? true : false;
    }

    /**
     * Database vendors implement the process of returning result set from a stored procedure
     * differently. This method will try to detect the database vendor through the driver name and
     * register the correct out parameter type specific to the vendor.
     * If it isn't able to guess the database from the drive name, it will return <code>Types.OTHER</code>
     * as the result set type.
     *
     * @param cc connection context object
     *
     * @return the JDBC type code defined by <code>java.sql.Types</code>.
     */
    public int getVendorSpecificResultSetType(ConnectionContext cc) {
        // set the default type
        int sqlType = Types.OTHER;
        try {
            if (isOracleDriver(cc)) {
                // ORACLE DRIVER
                Class oClass = Class.forName("oracle.jdbc.driver.OracleTypes");
                sqlType = oClass.getField("CURSOR").getInt(null);
            }
        } catch (Exception e) {
            log.error("Failed to get the column type for the result set cursor for stored procedure" + "'"
                    + parent.getProcedure() + "'.");
        }
        return sqlType;
    }

    /**
     * Checks to see if this parameter is available as an Output type
     */
    public boolean isOutType() {
        return (type.getValueIndex() == Type.OUT || type.getValueIndex() == Type.IN_OUT) ? true : false;
    }

    /**
     * Gets the extracted value
     *
     * @param vc the value context
     */
    public Object getExtractedValue(ValueContext vc) {
        return value.getValue(vc).getValue();
    }

    /**
     * Extract the OUT parameter values from the callable statment and
     * assign them to the value of the parameter.
     */
    public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException {
        if (getType().getValueIndex() == StoredProcedureParameter.Type.IN)
            return;

        int index = this.getIndex();
        QueryParameterType paramType = getSqlType();
        int jdbcType = paramType.getJdbcType();
        String identifier = paramType.getIdentifier();

        // result sets are special
        if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) {
            ResultSet rs = (ResultSet) stmt.getObject(index);
            QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs);
            value.getValue(cc).setValue(qrs);
            return;
        }

        switch (jdbcType) {
        case Types.VARCHAR:
            value.getValue(cc).setTextValue(stmt.getString(index));
            break;
        case Types.INTEGER:
            value.getValue(cc).setValue(new Integer(stmt.getInt(index)));
            break;
        case Types.DOUBLE:
            value.getValue(cc).setValue(new Double(stmt.getDouble(index)));
            break;
        case Types.CLOB:
            Clob clob = stmt.getClob(index);
            value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length()));
            break;
        case java.sql.Types.ARRAY:
            Array array = stmt.getArray(index);
            value.getValue(cc).setValue(array);
            break;
        case java.sql.Types.BIGINT:
            long bigint = stmt.getLong(index);
            value.getValue(cc).setValue(new Long(bigint));
            break;
        case java.sql.Types.BINARY:
            value.getValue(cc).setTextValue(new String(stmt.getBytes(index)));
            break;
        case java.sql.Types.BIT:
            boolean bit = stmt.getBoolean(index);
            value.getValue(cc).setValue(new Boolean(bit));
        case java.sql.Types.BLOB:
            value.getValue(cc).setValue(stmt.getBlob(index));
            break;
        case java.sql.Types.CHAR:
            value.getValue(cc).setTextValue(stmt.getString(index));
            break;
        case java.sql.Types.DATE:
            value.getValue(cc).setValue(stmt.getDate(index));
            break;
        case java.sql.Types.DECIMAL:
            value.getValue(cc).setValue(stmt.getBigDecimal(index));
            break;
        case java.sql.Types.DISTINCT:
            value.getValue(cc).setValue(stmt.getObject(index));
            break;
        case java.sql.Types.FLOAT:
            value.getValue(cc).setValue(new Float(stmt.getFloat(index)));
            break;
        case java.sql.Types.JAVA_OBJECT:
            value.getValue(cc).setValue(stmt.getObject(index));
            break;
        case java.sql.Types.LONGVARBINARY:
            value.getValue(cc).setTextValue(new String(stmt.getBytes(index)));
            break;
        case java.sql.Types.LONGVARCHAR:
            value.getValue(cc).setTextValue(stmt.getString(index));
            break;
        //case java.sql.Types.NULL:
        //    value.getValue(cc).setValue(null);
        //    break;
        case java.sql.Types.NUMERIC:
            value.getValue(cc).setValue(stmt.getBigDecimal(index));
            break;
        case java.sql.Types.OTHER:
            value.getValue(cc).setValue(stmt.getObject(index));
            break;
        case java.sql.Types.REAL:
            value.getValue(cc).setValue(new Float(stmt.getFloat(index)));
            break;
        //case java.sql.Types.REF:
        //    Ref ref = stmt.getRef(index);
        //    break;
        case java.sql.Types.SMALLINT:
            short sh = stmt.getShort(index);
            value.getValue(cc).setValue(new Short(sh));
            break;
        case java.sql.Types.STRUCT:
            value.getValue(cc).setValue(stmt.getObject(index));
            break;
        case java.sql.Types.TIME:
            value.getValue(cc).setValue(stmt.getTime(index));
            break;
        case java.sql.Types.TIMESTAMP:
            value.getValue(cc).setValue(stmt.getTimestamp(index));
            break;
        case java.sql.Types.TINYINT:
            byte b = stmt.getByte(index);
            value.getValue(cc).setValue(new Byte(b));
            break;
        case java.sql.Types.VARBINARY:
            value.getValue(cc).setValue(stmt.getBytes(index));
            break;
        default:
            throw new RuntimeException(
                    "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'.");
        }
    }

    /**
     * @param vrc
     * @param cc
     *
     * @throws SQLException
     */
    public void retrieve(StoredProcedureParameters.ValueRetrieveContext vrc, ConnectionContext cc)
            throws SQLException {
        // TODO: This needs to be tested.. no checking for stored procedure situation yet
        int jdbcType = getSqlType().getJdbcType();
        if (jdbcType != Types.ARRAY) {
            if (jdbcType == Types.VARCHAR) {
                vrc.addInOutValue(value != null ? value.getTextValue(cc) : null, jdbcType, type);
            } else {
                switch (jdbcType) {
                case Types.INTEGER:
                    vrc.addInOutValue(value != null ? new Integer(value.getValue(cc).getIntValue()) : null,
                            jdbcType, type);
                    break;

                case Types.DOUBLE:
                    vrc.addInOutValue(value != null ? new Double(value.getValue(cc).getDoubleValue()) : null,
                            jdbcType, type);
                    break;
                }
            }
        } else {
            if (value != null && type.getValueIndex() == Type.IN) {
                String[] textValues = value.getTextValues(cc);
                for (int q = 0; q < textValues.length; q++)
                    vrc.addInOutValue(textValues[q], Types.VARCHAR, type);
            }
        }
    }

    /**
     * Gets teh index of this parameter
     */
    public int getIndex() {
        return index;
    }

    /**
     * Sets the index of this parameter
     */
    public void setIndex(int index) {
        this.index = index;
        if (name == null)
            setName("param-" + index);
    }

    /**
     * Gets the name of this parameter
     */
    public String getName() {
        return name;
    }

    /**
     * Sets the name of this parameter
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * Gets the parent collection object
     */
    public StoredProcedureParameters getParent() {
        return parent;
    }

    /**
     * Sets the parent collection object
     */
    public void setParent(StoredProcedureParameters parent) {
        this.parent = parent;
    }

    /**
     * Gets the dynamic value for this parameter
     */
    public ValueSource getValue() {
        return value;
    }

    /**
     * Sets the dynamic value for this parameter
     */
    public void setValue(ValueSource value) {
        this.value = value;
    }

    public boolean isListType() {
        return paramType != null && paramType.getJdbcType() == Types.ARRAY ? true : false;
    }

    /**
     * Appends a list of bind parameters and their respective information used for debugging to the buffer
     */
    public void appendBindText(StringBuffer text, ValueContext vc, String terminator) {
        text.append("[" + index + "]");
        int jdbcType = getSqlType().getJdbcType();
        if (jdbcType != Types.ARRAY) {
            Object ov = value.getValue(vc);
            text.append(value.getSpecification().getSpecificationText());
            text.append(" = ");
            text.append(((Value) ov).getValueForSqlBindParam());
            text.append(" (java: ");
            text.append(ov != null ? ov.getClass().getName() : "<NULL>");
            text.append(", sql: ");
            text.append(jdbcType);
            text.append(", ");
            text.append(QueryParameterType.get(jdbcType));
            text.append(")");
        } else {
            text.append(value.getSpecification().getSpecificationText());
            text.append(" = ");

            String[] textValues = value.getTextValues(vc);
            if (value != null) {
                for (int v = 0; v < textValues.length; v++) {
                    if (v > 0)
                        text.append(", ");
                    text.append("'" + textValues[v] + "'");
                }
            } else {
                text.append("null");
            }

            text.append(" (text list)");
        }
        if (terminator != null)
            text.append(terminator);
    }
}