Spring JcbcTemplate to call Oracle Stored Proc. Spring 3.2

Question

I have some straight JDBC code using a CallableStatement working. I have been trying to convert it to Spring leveraging the DataSource, JdbcTemplate and SimpleJdbcCall. I have tried basically every tutorial, example and snippets from the Spring docs that I can find. With tweaking, all the Spring solutions yield the same result:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P_GET_CLASS_SCHEDULE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here's the log section where the statement is being prepared:

2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_classsched_ref_out
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_term
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_scauid
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_pidm
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:336 - JdbcCall call not compiled before execution - invoking compile
2015-12-29 17:17:18 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:18 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@umadmn.umt.edu:7895:ADMNRED]
2015-12-29 17:17:21 DEBUG CallMetaDataProviderFactory:123 - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2015-12-29 17:17:21 DEBUG CallMetaDataProvider:278 - Retrieving metadata for UPCLSCH/AP_ADMN/P_GET_CLASS_SCHEDULE
2015-12-29 17:17:22 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:304 - Compiled stored procedure. Call string is [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:282 - SqlCall for procedure [p_get_class_schedule] compiled
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:385 - The following parameters are used for call {call UPCLSCH.P_GET_CLASS_SCHEDULE()} with: {}
2015-12-29 17:17:22 DEBUG JdbcTemplate:937 - Calling stored procedure [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:22 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@xxxxx.xxx.xxx:7895:PRIVATE]
2015-12-29 17:17:24 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource

Here is the straight JDBC code that works (sans connection details):

private static List<ScheduledClass> callOracleStoredProcCURSORParameter() throws SQLException {
        Connection connection = null;
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        List<ScheduledClass> scheduledClassList = new ArrayList<ScheduledClass>();

        String getDBUSERCursorSql = "{call upclsch.p_get_class_schedule (?, ?, ?, ?)}";

        try {
            connection = getApConnection();
            callableStatement = connection.prepareCall(getDBUSERCursorSql);

            callableStatement.registerOutParameter("p_classsched_ref_out", OracleTypes.CURSOR);
            callableStatement.setString("p_term", "201570");          //term code
            callableStatement.setString("p_scauid", "rs213498");
            callableStatement.setString("p_pidm", null);

            callableStatement.executeUpdate();
            rs = (ResultSet) callableStatement.getObject("p_classsched_ref_out");

            while (rs.next()) {
                ScheduledClass sc = new ScheduledClass();
                sc.setCourseNumber(rs.getString("subject_code") + rs.getString("course_number"));
                sc.setCourseTitle(rs.getString("course_title"));
                scheduledClassList.add(sc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scheduledClassList;
    }

Here is my non-working Spring Code (note commented out section that produces same result when passing "in" to sjc.execute()):

public void setDataSource(DataSource dataSource){
    this.jt = new JdbcTemplate(dataSource);
    jt.setResultsMapCaseInsensitive(true);
    sjc = new SimpleJdbcCall(jt)
            .withCatalogName("upclsch")
            .withProcedureName("p_get_class_schedule");
}

    public Map<String, Object> execute(String termCode, String netId){

        sjc.useInParameterNames("p_term", "p_scauid", "p_pidm")
            .declareParameters(new SqlOutParameter("p_classsched_ref_out", OracleTypes.CURSOR),
            new SqlParameter("p_term", OracleTypes.VARCHAR),
            new SqlParameter("p_scauid", OracleTypes.VARCHAR),
            new SqlParameter("p_pidm", OracleTypes.VARCHAR));

//        SqlParameterSource in = new MapSqlParameterSource()
//                .addValue("p_scauid", netId, OracleTypes.VARCHAR)
//                .addValue("p_term", termCode, OracleTypes.VARCHAR)
//                .addValue("p_classsched_ref_out", OracleTypes.CURSOR);


        Map<String, Object> results = sjc.execute();

        return results;
    }

I can't seem to get any additional info at TRACE or DEBUG level to see if my parameters are ordered incorrectly. Thus, I'm looking for assistance from anyone that has accomplished this task using this technique. I'm not looking to extend StoredProcedure, as the Spring docs recommend this for 3.2.

Solution 1

Solved: I figured it out after stepping through the Spring source. For those who may be interested, it involves declaring parameters that the procedure actually uses, then using the SqlParameterSource to hold the values mapped to the names as they were declared. Notice that I add the values to the map in the reverse order as I added the parameters. Also note that I added the: .withoutProcedureColumnMetaDataAccess() . This is important when declaring your own parameters as I have done.

public class ScheduledClassesDAO {
    private DataSource dataSource;
    private JdbcTemplate jt;
    private SimpleJdbcCall sjc;

    public void setDataSource(DataSource dataSource){
        this.jt = new JdbcTemplate(dataSource);
        jt.setResultsMapCaseInsensitive(true);
        sjc = new SimpleJdbcCall(jt)
                .withCatalogName("upclsch")
                .withProcedureName("p_get_class_schedule");
    }

    /**
     * This method is used to return scheduled classes by calling a stored-proc.
     * @param termCode   String: The term/semester for this lookup.
     * @param netId      String: The netId of the student to lookup
     * @return           Map<String, Object>
     */
    public Map<String, Object> execute(String termCode, String netId){

        sjc.useInParameterNames("p_term", "p_scauid", "p_pidm")
            .withoutProcedureColumnMetaDataAccess()
            .declareParameters(new SqlOutParameter("p_classsched_ref_out", OracleTypes.CURSOR),
                    new SqlParameter("p_term", OracleTypes.VARCHAR),
                    new SqlParameter("p_scauid", OracleTypes.VARCHAR),
                    new SqlParameter("p_pidm", OracleTypes.NUMBER));

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_pidm", null)
                .addValue("p_scauid", netId)
                .addValue("p_term", termCode);


        Map<String, Object> results = sjc.execute(in);

        return results;
    }
}

Solution 2

You can use StoredProcedure for this use case. You're probably not passing parameters to the procedure.

See the example code below.

TestProcedure.java

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

public class TestProcedure {
    public void main(String[] args) {
        System.out.println("Started");
        JdbcTemplate jdbcTemplate = null;//get your jdbcTemplate

        MyProcedure proc = new MyProcedure(jdbcTemplate);

        Map<String, Object> resultMap = proc.execute("201570","rs213498",null);
        List<Map> classschedList = (List)resultMap.get(MyProcedure.P_CLASSSCHED_REF_OUT);
    }

}

MyProcedure

import java.util.HashMap;
import java.util.Map;

import oracle.jdbc.internal.OracleTypes;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class MyProcedure extends StoredProcedure {
    public static final String P_CLASSSCHED_REF_OUT = "p_classsched_ref_out";
    public static final String P_TERM = "p_term";
    public static final String P_SCAUID = "p_scauid";
    public static final String P_PIDM = "p_pidm";
    public static final String PROC_NAME = "upclsch.p_get_class_schedule";

    public MyProcedure(JdbcTemplate jdbcTemplate) {
        setDataSource(jdbcTemplate.getDataSource());
        setSql(PROC_NAME);
        setFetchSize(100);

        declareParameter(new SqlOutParameter(P_CLASSSCHED_REF_OUT, OracleTypes.CURSOR, new MyRowMapper()));
        declareParameter(new SqlParameter(P_TERM, OracleTypes.VARCHAR));
        declareParameter(new SqlParameter(P_SCAUID, OracleTypes.VARCHAR));
        declareParameter(new SqlParameter(P_PIDM, OracleTypes.VARCHAR));

        compile();
    }

    /**
     * Execute stored procedure.
     */
    public Map<String, Object> executeProcedure(String term, String scauid, String pidm) {
        // set the input params
        Map<String, Object> inParameters = new HashMap<String, Object>();
        inParameters.put(P_TERM, term);
        inParameters.put(P_SCAUID, scauid);
        inParameters.put(P_PIDM, pidm);
        // now execute
        Map<String, Object> outputMap = execute(inParameters); // Call on parent class
        return outputMap;
    }
}

MyRowMapper

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;

public class MyRowMapper implements RowMapper<Map> {
    public Map mapRow(ResultSet rs, int paramInt) throws SQLException {
        Map response = new HashMap();
        // here we populate the data using the returned cursor resultset
        // response.setTerm(rs.getString("cursor_col1");
        return response;
    }
}

Comments

[Magnus]

The stacktrace shows no arguments making it to the db, you need to pass an array of arguments(termCode, netId, null) in the execute call.

[user3596751]

I've tried that too. If you look at the part that's commented out, you see it all gets added to a SqlParamaterSource named "in." When I try that approach, I use: sjc.execute(in) as opposed to the empty call you see in the code.

[user3596751]

But, I will definitely try adding them using your (@Magnus) method.

[user3596751]

I just tried it this way: Map<String, Object> results = sjc.execute(termCode, netId, null); The log is identical to above.

[user3596751]

Here's what that looks like in debug: sjc:SimpleJdbcCall@1658 termCode: "201570" netId: "rs213498"

[user3596751]

Here's the log output too: 2015-12-29 21:53:38 DEBUG CallMetaDataProvider:278 - Retrieving metadata for UPCLSCH/AP_ADMN/P_GET_CLASS_SCHEDULE 2015-12-29 21:53:39 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:304 - Compiled stored procedure. Call string is [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}] 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:282 - SqlCall for procedure [p_get_class_schedule] compiled 2015-12-29 21:53:39 DEBUG SimpleJdbcCall:385 - The following parameters are used for call {call UPCLSCH.P_GET_CLASS_SCHEDULE()} with: {}

[Ryan]

wrote 'Notice that I add the values to the map in the reverse order as I added the parameters'. You are using a MapSqlParameterSource - the order that the values get added to the map doesn't matter.