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?

Answer 2

Using MyBatis-Spring annotations, write your code like this.

public interface ProductMapper
{
    @Select("exec Pup_ProductSearch_Sel #{searchString}, #{pageNum}, #{pageSize}, #{totalRows,mode=OUT,jdbcType=NUMERIC}")
    @Options(statementType = StatementType.CALLABLE)
    public List<Map<String, Object>> productSearch(ProductSearchParameters params);


    public class ProductSearchParameters
    {
        private String searchString = null;
        private Integer pageNum = 1;
        private Integer pageSize = 5;
        private Integer totalRows = -1;

        // Accessors go here...
    }
}

The IN parameters are populated by us in the calling code. The OUT parameters are populated by the data access layer, and are present in the params object after invoking the mapper.

@Service
public class TestService
{
    @Resource
    private ProductMapper mapper;


    public void run()
    {
        final ProductMapper.ProductSearchParameters params = new ProductMapper.ProductSearchParameters();
        params.setSearchString("book"); // IN parameter
        final List<Map<String, Object>> results = mapper.productSearch(params);

        for(final Map<String, Object> product : results)
        {
            System.out.println(product.get("title"));
        }

        System.out.println("Total results: " + params.getTotalRows()); // OUT parameter
    }
}

Inspired by this post: http://ibatis.10938.n7.nabble.com/IBatis-3-0-beta-10-annotations-stored-procedures-td7806.html