Getting oracle Stored procedure resultset in java spring mvc

Question

I have the following stored procedure which takes three parameters and returns three ref cursors.

variable id refcursor
variable item refcursor
variable amount refcursor
exec getdata(123,date1,date2, :id, :item, :amount) ;

print id;
print item;
print amount;

i have three resultsets for this stored procedure output. How can i call this in spring mvc and display these three resultsets. I was using the following code to fetch the data through sql query. But now i have developed a stored procedure. so how can i call this SP output insted of my query output.

public Optional<List<student>> getStudentDetails(String id) {

NamedParameterJdbcTemplate parameterJdbcTemplate = new 
NamedParameterJdbcTemplate(dataSource);

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("Id", id);

List<student> studentList = 
parameterJdbcTemplate.query(StudentQueryRepository.STUDENT_DETAIL_QUERY,
namedParameters, new studentDecodeRowMapper());

if (studentList.isEmpty()) {
return Optional.empty();
}   else {
return Optional.of(studentList);
}

}

please remove the sql-server tag if it's not required

Tanner
– Tanner

2017-10-17 07:49:36 +00:00
Commented Oct 17, 2017 at 7:49 — Tanner
– Tanner, Commented Oct 17, 2017 at 7:49

Alex78191 · Accepted Answer · 2019-10-09 18:21:08Z

Try this:

List<CommunicationContact> campaigns = jdbcTemplate.execute(
    new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement cs = con.prepareCall("{? = call MY_SERVICES.GET_CAMPAIGNS(?,?)}");
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.setString(2, customer);
            cs.setString(3, channel);
            return cs;
        }
    },
    new CallableStatementCallback<List<CommunicationContact>>() {
        @Override
        public List<CommunicationContact> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(1);
            List<CommunicationContact> communications = commContactRsExtractor.extractData(rs);
            return communications;
        }
    }
);

And the I have a function on the database:

FUNCTION GET_CAMPAIGNS(p_cust IN VARCHAR2,
                                     p_channel IN VARCHAR2)
    RETURN SYS_REFCURSOR
  IS
    l_campaigns_cursor SYS_REFCURSOR;
    BEGIN
      BEGIN
        OPEN l_campaigns_cursor FOR
        SELECT...

        EXCEPTION
        WHEN OTHERS THEN
        ...
      END;

      RETURN l_campaigns_cursor;
    END GET_CAMPAIGNS;

I believe that a stored procedure with out parameter will also work.

Collectives™ on Stack Overflow

Getting oracle Stored procedure resultset in java spring mvc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related