Java stored procedure call MyBatis with OUT parameters

Question

First question: I'm trying to return a single OUT parameter, not a result set with annotations. First, is this possible? If so, how to do it?

MyBatis: 3.0.6

Database: SQL Server 2008

Here is an example of the syntax of my method call in 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 return a message from all my “saved” procedures and therefore I can return a response to the user: “User successfully saves”, “Error saving user”, “You do not have permission to save this user”, etc. I know that returning a result set will solve the problem, namely that I do not want to change all my procedures!

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

 @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'm trying to return a single OUT parameter, not a result set with annotations. First, is this possible? If so, how will this be done?

err, sorta. Mapper will not return out parameters, but you can force Mybatis to set them on a parameter object or put them on a map, for example.

Therefore, a simple java object with getters and setters for all fields is given. After calling the cartographer, the out parameters will be set to 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 will look something like this.

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

How hard is it to explain, does it make sense?

Answer 2

Using MyBatis-Spring annotations, write your code as follows.

 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. OUT parameters are filled in by the data access layer and are present in the params object after the converter is called.

 @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 entry: http://ibatis.10938.n7.nabble.com/IBatis-3-0-beta-10-annotations-stored-procedures-td7806.html