Search code examples
javaspringoracle-databasespring-jdbc

Output parameter as Integer using spring StoredProcedure

I'm trying to store the output parameter of an Oracle Stored Procedure in my variable and I have this problem :

java.math.BigDecimal cannot be cast to java.lang.Integer

I'm using StoredProcedure from org.springframework.jdbc.object execute method that returns a Map<String, Object> and then I do something like this

Integer proposalId = procedure.execute().get("po_proposal_id");

Where po_proposal_id is defined in the PROCEDURE as

po_proposal_id          Out Number,

I know that I could do something like this:

Integer proposalId = ((BigDecimal) 
        procedure.execute().get("po_proposal_id")).intValue();

But I don't want to. Why should I do that? Does execute method from Spring StoredProcedure always map Number SQL Parameters as BigDecimal?

I've tried to declare the parameter from the procedure as 

po_proposal_id          Out Integer,

but with no luck.

And I have also tried to change how I mapped the output parameter in Java from oracle.types.Number to oracle.types.Integer but with no luck.

I have found this table Table 3-1 SQL and PL/SQL Data Type to Oracle and JDBC Mapping Classes (you need to scroll down a little bit) that shows mapping between oracle data types and java but it seems that I can't get it working.

Solution

  • Hi Gerogia , I will try to answer I did some boring analysis on the Spring StoredProcedure class when you call the execute method of StoredProcedure class it calls the below method

    getJdbcTemplate().call(newCallableStatementCreator(inParams), getDeclaredParameters())

    After a series of calls, it finally calls getObject(int column index) method of the ResultSet class which is Database provider dependent for oracle ,mysql its completely different and this method is responsible for returning the Data Type for the object this is invoked by the Column Row Mapper which maps the column of each column name and the corresponding value used the StoredProcedure Class

    **Column Row Mapper Class :**

@Override
public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    Map<String, Object> mapOfColumnValues = createColumnMap(columnCount);
    for (int i = 1; i <= columnCount; i++) {
        String column = JdbcUtils.lookupColumnName(rsmd, i);
        mapOfColumnValues.put(getColumnKey(column), getColumnValue(rs, i));
    }
    return mapOfColumnValues;
}

    The getColumnValue(rs, i) is responsible for invoking the getResultSetValue(ResultSet rs, int index) method which is the responsible for invoking the getObject(int column index) of the resultSet which actually determines the data type returned.

    Code Example :

    public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
    Object obj = rs.getObject(index);
    String className = null;
    if (obj != null) {
        className = obj.getClass().getName();
    }
    if (obj instanceof Blob) {
        Blob blob = (Blob) obj;
        obj = blob.getBytes(1, (int) blob.length());
    }
    else if (obj instanceof Clob) {
        Clob clob = (Clob) obj;
        obj = clob.getSubString(1, (int) clob.length());
    }
    else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
        obj = rs.getTimestamp(index);
    }
    else if (className != null && className.startsWith("oracle.sql.DATE")) {
        String metaDataClassName = rs.getMetaData().getColumnClassName(index);
        if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
            obj = rs.getTimestamp(index);
        }
        else {
            obj = rs.getDate(index);
        }
    }
    else if (obj instanceof java.sql.Date) {
        if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
            obj = rs.getTimestamp(index);
        }
    }
    return obj;
}

    So Coming to the actual question why only its returning BigDecimal Type the getObject(int column index) method of the ResultSet class is invoking the below method for oracle driver

    Datum var4 = this.getOracleObject(var1)

    Which invokes different accessors depending on the individual data types of the columns and each accessor has the getObject implemented in your case oracle considers all the data types such as int, float as Numeric data types refer https://www.techonthenet.com/oracle/datatypes.php so its using NumberCommonAccessor which always return BigDecimal for all the data types such as int, float so you have explicitly typecast and you will receive the data type as always BigDecimal.It does not depend on the SqlOutParameter which is to specify that you need some list of output parameters from the stored procedure and it has no effect on data type.