SimpleJDBCCall handle out paramater with resultset

Question

I am using spring jdbc. I want result set with out param. separately i done but together i am not able to do.

    CREATE DEFINER=`xxx`@`%` PROCEDURE `client_xxxx`(
    IN p_xxxx TINYINT(1) UNSIGNED,
    IN p_result SMALLINT(2) UNSIGNED,
    OUT p_result BIT ) BEGIN 
       IF EXISTS(SELECT 1 FROM xxx WHERE xxx = 1 AND xxx = 1) THEN
           SELECT ...;
           SET p_result = 0;
       ELSE
         SELECT ...;
         SET p_result = 1;
       END IF;
    END

spring jdbc code

SimpleJdbcCall jdbcCall =  new SimpleJdbcCall(dataSource).withProcedureName(sp);
List<Map<String, Object>> list = (List<Map<String, Object>>) jdbcCall.execute(paramsArray).get("#result-set-1");

list get the result set with result set how can i get p_result with that.

Answer 1

You can try morejdbc (available in maven central) to call your procedure, it's more laconic and it's type safe:

import static org.morejdbc.SqlTypes.BIGINT;
import static org.morejdbc.NamedJdbcCall.call;
import org.morejdbc.*;
...
private JdbcTemplate jdbcTemplate;
...
Out<Integer> out = Out.of(INTEGER);
jdbcTemplate.execute(call("client_xxxx")
    .in("p_xxxx", pValue)
    .out("p_result", out));

System.out.println("Result is " + out.get());

For ref_cursor out parameter and Oracle database you can use

Out<List<Record>> out = Out.of(OracleSqlTypes.cursor((rs, idx) -> new Record(rs)));
jdbcTemplate.execute(call("client_xxxx")
    .in("p_xxxx", pValue)
    .out("p_result", out)); // will auto-close ref-cursor

System.out.println("Got result records: " + out.get());