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.

Solution

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;
    }

Comments

[Alex78191]

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

[Alex78191]

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

[ALI]

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

[Alex78191]

It's not JDBCTemplate, its JPA.

[Kathiravan Ramaswamy]

Thanks for response @Alex78191

[Alex78191]

Why do you need to register parameters twice?

[Alex78191]

Why is it so verbose?

[Alex78191]

Isn't it easier to use pure jdbc?

[Alex78191]

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