Java MyBatis stored procedure call with OUT parameters

Question

First question: I am trying to return one OUT parameter and not a result set with annotations. First, is it even possible? If it is, how would one do this?

MyBatis: 3.0.6

Database: SQL Server 2008

Here is an example of the syntax of my method call in the UserDAO:

@Select(value= "{ CALL saveUser( "
        + "#{userId, mode=IN, jdbcType=INTEGER},"
        + "#{firstname, mode=IN, jdbcType=VARCHAR},"
        + "#{lastname, mode=IN, jdbcType=VARCHAR},"
        + "#{message, mode=OUT, jdbcType=VARCHAR}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
public String saveUser(
        @Param("userId") int userId,
        @Param("firstname") String firstname,
        @Param("lastname") String lastname);

I am returning a message from all of my "save" procedures and so I can return a response to the user: "User save successfully","Error saving user","You do not have permission to save this user", etc. I know that returning a result set will solve the problem, its just that I don't want to change all of my procedures!

Second question: Is it possible to return a "SaveProcedureResponse" populated from multiple OUT parameters? For example:

@Select(value= "{ CALL saveUser( "
        + "#{userId, mode=IN, jdbcType=INTEGER},"
        + "#{firstname, mode=IN, jdbcType=VARCHAR},"
        + "#{lastname, mode=IN, jdbcType=VARCHAR},"
        + "#{message, mode=OUT, jdbcType=VARCHAR},"
        + "#{status, mode=OUT, jdbcType=VARCHAR},"
        + "#{returnCode, mode=OUT, jdbcType=INTEGER}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
public SaveProcedureResponse saveUser(
        @Param("userId") int userId,
        @Param("firstname") String firstname,
        @Param("lastname") String lastname);

Where the bean looks like this:

public class SaveProcedureResponse {
    private String message;
    private String status;
    private int returnCode;

    public SaveProcedureResponse(String message, String status, int returnCode) {
        this.message = message;
        this.status = status;
        this.returnCode = returnCode;
    }
}

Thanks!

Answer 1

First question: I am trying to return one OUT parameter and not a result set with annotations. First, is it even possible? If it is, how would one do this?

err, sorta. The Mapper won't return the out parameters, but you can get Mybatis to set them onto the parameter object, or put them into a map like this.

So given a simple java object with getters and setters for all the fields. After a call to the mapper, the out parameters will be set onto the object.

<update id="someProcedure" statementType="CALLABLE">
    {call some procedure(
            #{someInParamA, mode=IN},
            #{someInParamB, jdbcType=ARRAY, mode=IN},
            #{someOutParamA, javaType=Boolean, jdbcType=NUMERIC, mode=OUT },
            #{someOutParamB, javaType=Object, jdbcType=ARRAY, jdbcTypeName=SOMEJDBCTYPE, mode=OUT})}
</update>

So to get the out parameters, it would look something like this.

mapper.getSomeProcedure(someBean);
//out params populated on the object passed to the mapper :)
String outA = bean.getSomeOutParamA();

It's kinda hard to explain, does that make sense?