Skip to main content

Stored Procedure Calls

dbVisitor supports stored procedures with IN/OUT arguments. Common scenarios include:

Execute a Stored Procedure​

Each section is an independent example; do not create same-named procedures twice. In the MySQL client, configure the delimiter for CREATE PROCEDURE, or send the complete definition in one JDBC call.

This MySQL procedure accepts one argument and returns one result set.

1. MySQL procedure
create procedure proc_select_table(in userName varchar(200))
begin
    select * from test_user where name = userName;
end;
2. Compose the call
{call proc_select_table(?)} @{resultSet,name=res,javaType=net.demo.UserDTO}
  • Calls proc_select_table with positional argument binding.
  • Uses the RESULT rule to map the result set to UserDTO.
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call("{call proc_select_table(?)} @{resultSet,name=res,javaType=net.demo.UserDTO}",
                                    "Kevin");
List<UserDTO> result = (List<UserDTO>)res.get("res");
  • Final SQL with rule applied.
  • Input argument.
  • Retrieve the result set named res.
info

If the procedure emits multiple result sets, add more RESULT rules to handle each one.

Named Arguments​

Execute with named argument
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call("{call proc_select_table(:userName)} @{resultSet,name=res,javaType=net.demo.UserDTO}",
                                    CollectionUtils.asMap("userName", "Kevin"));
List<UserDTO> result = (List<UserDTO>)res.get("res");

Named parameters replace ? placeholders with names such as :userName.

info

See Arguments for more binding styles.

OUT Arguments​

1. MySQL procedure with OUT argument
create procedure proc_select_table(in userName varchar(200), out outName varchar(200)) -- OUT parameter
begin
    select * from test_user where name = userName;
    set outName = concat(userName,'-str');
end;
  • userName is the input argument.
  • outName is the output argument.
  • The SELECT statement produces a separate result set.
2. Compose the call
{
 call proc_select_table(#{userName,jdbcType=varchar},
                        #{outName, mode=out,jdbcType=varchar})
} @{resultSet,name=res,javaType=net.demo.UserDTO}
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> args = CollectionUtils.asMap("userName", "Kevin");
Map<String, Object> res = jdbc.call(
                "{call proc_select_table(#{userName,jdbcType=varchar}, " +
                "                        #{outName, mode=out,jdbcType=varchar})" +
                "} @{resultSet,name=res,javaType=net.demo.UserDTO}",
        args
);

String outName = (String)res.get("outName");
List<UserDTO> result = (List<UserDTO>)res.get("res");

OUT Arguments with Cursor​

1. Oracle procedure returning a cursor
create or replace procedure proc_out_cursor(
    userName in varchar2,
    tableCursor out sys_refcursor)
as
begin
    open tableCursor for select * from test_user where name = userName;
end;
  • userName is the input argument.
  • tableCursor returns the result set through an OUT cursor.
2. Compose the call
{
 call proc_out_cursor(#{userName,jdbcType=varchar},
                      #{tableCursor,mode=cursor,javaType=net.demo.UserDTO})
}
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> args = CollectionUtils.asMap("userName", "Kevin");
Map<String, Object> res = jdbc.call(
                "{call proc_out_cursor(#{userName,jdbcType=varchar}," +
                "                      #{tableCursor,mode=cursor,javaType=net.demo.UserDTO})" +
                "}", args);

List<UserDTO> result = (List<UserDTO>)res.get("tableCursor");
  • For cursors, set mode=cursor when declaring the OUT argument.
info

Some databases require an active transaction to fetch cursor OUT arguments. Consult your database documentation.

Calling functions​

When a function value is returned as a query column, use a JdbcTemplate query method. For example, with MySQL:

Integer value = jdbc.queryForObject("SELECT ABS(?)", new Object[] { -5 }, Integer.class);

Use list or object queries for row results. JDBC function return parameters instead require a callable statement and an OUT declaration. The accepted function syntax and return shape depend on the data source; see Programmatic API differences.