Spring JDBCTemplate Stored Procedure with ResultSet and OutPut Parameter

Question

I created a stored procedure which returns result rows and two output parameters. I am unable to find any thing in spring from which i can get ResultSet and outPutParameters. I want to achieve something like this using Spring framework.

Ricercare

Why not to use SimpleJdbcCall? stackoverflow.com/a/45542450 – Weathers

Yes, SimpleJdbcCall cannot return ResultSet, it returns List<LinkedCaseInsensitiveMap>. – Weathers

Answer 1

We use something like the following in our code

public Map<String, Object> findData() {
        List prmtrsList = new ArrayList();
        prmtrsList.add(new SqlParameter(Types.VARCHAR));
        prmtrsList.add(new SqlParameter(Types.VARCHAR));
        prmtrsList.add(new SqlOutParameter("result", Types.VARCHAR));

        Map<String, Object> resultData = jdbcTemplate.call(connection -> {
            CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?)}");
            callableStatement.setString(1, "first");
            callableStatement.setString(2, "last");
            callableStatement.registerOutParameter(3, Types.VARCHAR);
            return callableStatement;
        }, prmtrsList);
        return resultData;
    }

Nauseating

Just to make this clear from above returned Map<String,Object> resultData you would extract result set as resultData .get("#result-set-1"). where #result-set-1 clearly is first result set – Ricercare

Why do you need to register parameters twice? – Weathers

Why is it so verbose? – Weathers

Isn't it easier to use pure jdbc? – Weathers

Is it easier because you don’t need to close the statement and commit? – Weathers

Calling resultData .get("#result-set-1") you are not getting ResultSet instance, instead it's ArrayList. ResultSet is processed inside jdbcTemplate.call. – Gonorrhea

Answer 2

@SuppressWarnings("rawtypes")
    @Override
    public List<?> applyLeave(int leave_id, int emp_id, boolean is_emer_lev, int is_emer_appr_by, String emp_role,
            int rep_id, String reason, String backup_person, int bak_per_count, String leave_status, String comments,
            int leave_det_id, int leave_type, Timestamp leave_apply_frm_date, int no_of_days, Timestamp worked_date,
            String reason_for_cancellation, boolean active, boolean is_submit, int user_id) {

        StoredProcedureQuery query = entitymanager.createStoredProcedureQuery("sp_iu_leave");

        query.registerStoredProcedureParameter("p_leave_id", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_emp_id", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_is_emgen_leave", Boolean.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_is_emgen_leave_apr_by", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_emp_role", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_rpt_person_id", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_reason", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_backup_person", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_backup_person_cnt", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_leave_status", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_comments", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_leave_det_id", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_leave_type", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_leave_apply_frm_dt", Timestamp.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_no_of_days", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_worked_date", Timestamp.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_reason_for_cancellation", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_active", Boolean.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_is_submit", Boolean.class, ParameterMode.IN)
        .registerStoredProcedureParameter("p_usr_id", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter("error_msg", String.class, ParameterMode.OUT);

        query.setParameter("p_leave_id", leave_id);
        query.setParameter("p_emp_id", emp_id);
        query.setParameter("p_is_emgen_leave", is_emer_lev);
        query.setParameter("p_is_emgen_leave_apr_by", is_emer_appr_by);
        query.setParameter("p_emp_role", emp_role);
        query.setParameter("p_rpt_person_id", rep_id);
        query.setParameter("p_reason", reason);
        query.setParameter("p_backup_person", backup_person);
        query.setParameter("p_backup_person_cnt", bak_per_count);
        query.setParameter("p_leave_status", leave_status);
        query.setParameter("p_comments", comments);
        query.setParameter("p_leave_det_id", leave_det_id);
        query.setParameter("p_leave_type", leave_type);
        query.setParameter("p_leave_apply_frm_dt", leave_apply_frm_date);
        query.setParameter("p_no_of_days", no_of_days);
        query.setParameter("p_worked_date", worked_date);
        query.setParameter("p_reason_for_cancellation", reason_for_cancellation);
        query.setParameter("p_active", active);
        query.setParameter("p_is_submit", is_submit);
        query.setParameter("p_usr_id", user_id);
        query.execute();

        String errString = (String) query.getOutputParameterValue("error_msg");
        System.out.println(errString);
        List res = query.getResultList();
        if(errString.equals("NO_ERR"))
        {
            return res;
        }
        else
        {
            return res;
        }

    }

You can use StoredProcedurequery also to return the resultset, if wrong please respond thanks!!

Athwart

It's not JDBCTemplate, its JPA. – Weathers

Thanks for response @Weathers – Athwart