6.6 Argument Options

Argument options fall into three categories:

• General Options: common option effects and usage.
• Stored Procedure Options: options related to stored procedures.
• Cursor Options: extensions for cursor arguments and how to use them.

General Options

javaType, typeHandler, and jdbcType are the most common options and cover most needs.

Name	Type	Description
javaType	Class	Optional Java type of the argument.
jdbcType	Integer	Optional JDBC type of the argument; can be a java.sql.JDBCType enum name or numeric code.
typeHandler	TypeHandler	Optional Type handler to use, overriding the default TypeHandler choice.

Example 1: javaType option

select * from users where age = #{age,javaType=java.lang.Integer}

• javaType can be any Java type supported by the TypeHandlerRegistry.

Example 2: jdbcType option

select * from users where name = #{name,jdbcType=varchar}

• jdbcType can be any JDBC type supported by the TypeHandlerRegistry.

info

javaType and jdbcType can be combined to determine a specific TypeHandler.

Example 3: typeHandler option

select * from users where name = #{name,typeHandler=net.hasor.dbvisitor.types.handler.string.StringTypeHandler}

info

typeHandler explicitly selects the TypeHandler. dbVisitor ships with many built-in TypeHandler.

Example 4: Serialize/deserialize a JSON field

select * 
from users 
where name = #{name,
            javaType= net.demos.UserDTO,
            typeHandler=net.hasor.dbvisitor.types.handler.json.JsonUseForFastjson2TypeHandler}

• By setting javaType and typeHandler, JSON fields can be auto-serialized/deserialized.

Stored Procedure Options

Use the mode option to adapt different stored-procedure argument types.

• When mode is IN, it passes a value to the procedure (same usage as general options).
• When mode is OUT or INOUT, you can additionally use the options below.

Name	Type	Description
mode	SqlMode	Optional Direction of the argument; defaults to IN. Needed for stored-procedure OUT arguments.
jdbcType	Integer	Optional JDBC type; can be a java.sql.JDBCType enum name or numeric code.
name	String	Optional Name of the OUT argument in the result set. When mode is INOUT, can differentiate in/out names.
typeName	String	Optional Type name for the OUT argument; see CallableStatement.registerOutParameter.
scale	Integer	Optional Scale for the OUT argument; see CallableStatement.registerOutParameter.

Example 1: IN argument

{call test_procedure(#{arg, mode=in})} -- shorthand: {call test_procedure(#{arg})}

Example 2: OUT argument with JDBC VARCHAR

{call test_procedure(#{arg, mode=out, jdbcType=varchar})}

Example 3: INOUT argument with JDBC VARCHAR

{call test_procedure(#{arg, mode=inout, jdbcType=varchar})}

Example 4: OUT argument with jdbcType and typeName

{call test_procedure(#{arg, mode=out, jdbcType=varchar, typeName=varchar2})}

Example 5: OUT argument with jdbcType and scale

{call test_procedure(#{arg, mode=out, jdbcType=varchar, scale=3})}

info

When both typeName and scale are present, typeName takes precedence. Priority of common OUT option combos:

• jdbcType + typeName (highest)
• jdbcType + scale
• jdbcType alone (lowest)
• See CallableStatement.registerOutParameter for details.

Example 6: OUT argument with custom TypeHandler

{call test_procedure(#{arg, 
            mode=out,
            jdbcType=varchar,
            typeHandler=net.hasor.dbvisitor.types.handler.string.StringAsMonthDayTypeHandler})
}

Example 7: Alias an OUT argument and save the OUT result as outArg

{call test_procedure(#{arg, name=outArg, mode=out, jdbcType=varchar})}

Cursor Options

Cursor arguments are usually result sets exposed via stored-procedure OUT arguments. You can handle cursor data with rowMapper, rowHandler, or extractor.

Name	Type	Description
mode	SqlMode	Required Must be set to CURSOR when using cursor arguments.
name	String	Optional Name of the cursor argument in the result set.
javaType	Class	Optional Map the cursor result set to a Java type; see Object Mapping.
rowMapper	RowMapper	Optional Same as above; see RowMapper.
rowHandler	RowCallbackHandler	Optional Same as above; see RowCallbackHandler.
extractor	ResultSetExtractor	Optional Same as above; see ResultSetExtractor.

Oracle stored procedure that outputs a cursor

create or replace procedure proc_out_cursor(
    userName in varchar2,
    tableCursor out sys_refcursor)
as
begin
    open tableCursor for select * from my_users where c_name = userName;
end;

Example 1: Map cursor to UserDTO

{call proc_out_cursor(#{name},#{res,
            mode=cursor,
            javaType=net.demos.dto.UserDTO})}

Example 2: Map cursor with custom UserRowMapper

{call proc_out_cursor(#{name},#{res,
            mode=cursor,
            rowMapper=net.demos.mapper.UserRowMapper})}

Example 3: Process cursor rows with UserRowHandler

{call proc_out_cursor(#{name},#{res,
            mode=cursor,
            rowHandler=net.demos.handler.UserRowHandler})}

info

Because of RowCallbackHandler characteristics, processing cursor data with it does not produce a result set. See RowCallbackHandler.

info

When javaType, rowMapper, rowHandler, and extractor appear together (or partially), only one takes effect.

• Priority: javaType > rowMapper > rowHandler > extractor.