MyBatis is a first class persistence framework for Java. It’s feature rich and very straightforward in most cases. However, it’s missing documentation for advanced usage. The official document doesn’t have an concrete example for how to handle OUT parameters from an SQL procedure, specifically, a CURSOR type OUT parameter.

In this post, we will walk through a simple use case and demonstrate how to call a DB procedure with CURSOR type OUT parameter.

For example, we want to call a DB procedure to find all users from a specific country. The procedure has one IN parameter: p_country and an OUT parameter p_users. The OUT parameter p_users is a CURSOR to multiple User records.

Setup

Create the table:

CREATE TABLE users {
	id BIGINT PK
	first_name TEXT
	last_name TEXT
	country TEXT
}

Create the procedure:

CREATE PROCEDURE get_users_by_country(IN p_country VARCHAR, OUT p_users SYS_REFCURSOR) AS
BEGIN
	OPEN p_users FOR
	  SELECT id, first_name, last_name, country FROM users WHERE country=p_country;
END;

MyBatis

There are two ways to configure MyBatis mappers: XML or Java annotation. I prefer Java annotation. It combines the Java mapper interface and the actual query in the same place. It’s easy to navigate and maintain. The down side is, the annotations are not very descriptive in certain cases. Sometimes, you have to use multiple annotations to define one SQL mapper method while with XML, it might be just one XML tag.

So, let’s start to define our mapper interface first. Based on our requirement and the DB procedure, we may want to define our mapper method like below:

public interface UserMapper {
	List<User> getUserByCountry(String country);
}

Next, we would like to add MyBatis annotations and bind the method to the actual DB query. As the DB procedure is a query, we use Select annotation.

public interface UserMapper {
	@Results(id = "userMap", value = {
		@Result(property = "id", column = "id"),
		@Result(property = "firstName", column = "first_name"),
		@Result(property = "lastName", column = "last_name"),
		@Result(property = "country", column = "country")
	})
	@Select("{ call get_users_by_country(#{p_country}, #{p_users, mode=OUT, jdbcType=CURSOR, resultMap=userMap}) }")
	@Options(statementType = StatementType.CALLABLE)
	@ResultType(User.class)
	List<User> getUserByCountry(@Param("p_country") String country);
}

Besides @Select annotation, we also need to use @Options to specify the statement type is CALLABLE. @ResultType tells MyBatis convert the rows in the ResultSet to User class. @Results annotation defines the result mapping between the User class and the rows in the ResultSet.

It looks good till now. But when you run the query by calling the mapper method:

SqlSessionFactory factory = ...;
try (SqlSession session = factory.openSession()) {
    SampleMapper mapper = session.getMapper(UserMapper.class);
    List<User> result = mapper.getUserByCountry("Germany");
    System.out.println(result);
}

It doesn’t work. The method returns null. What’s going on? It turns out MyBatis doesn’t take OUT parameter as return value. It makes sense since there might be multiple OUT parameters in a procedure. So, how can we retrieve the value of OUT parameters? After reading the source code, the OUT parameters are being set to metaParam. metaParam is the object holding all parameters for a bound SQL in MyBatis. For example, for getUserByCountry method, the metaParam will contain a key p_country. After executing the statement, the value of OUT parameter will be set to the metaParam by key p_users. But how can we retrieve the value from metaParam? We have to provide the metaParam if we want to retrieve values for OUT parameters. So, we have to change our mapper method to:

public interface UserMapper {
	@Results(id = "userMap", value = {
		@Result(property = "id", column = "id"),
		@Result(property = "firstName", column = "first_name"),
		@Result(property = "lastName", column = "last_name"),
		@Result(property = "country", column = "country")
	})
	@Select("{ call get_users_by_country(#{country}, #{users, mode=OUT, jdbcType=CURSOR, resultMap=userMap}) }")
	@Options(statementType = StatementType.CALLABLE)
	@ResultType(User.class)
	void getUserByCountry(Map<String, Object> params);
}

Note that, the method no longer has return values. And, the argument is a Map now, instead of the original single String argument. As we pass in a Map. MyBatis will use it as metaParam. We can retrieve the value of users from the Map after executing the query.

SqlSessionFactory factory = ...;
try (SqlSession session = factory.openSession()) {
    SampleMapper mapper = session.getMapper(UserMapper.class);
    Map<String, Object> params = new HashMap<>();
    params.put("country", "Germany");
    mapper.getUserByCountry(params);
    System.out.println(params.get("users"));
}

We can improve it and make it more strong-typed by using a POJO class:

public class GetUserByCountryParams {
	private String country;
	private List<User> users;

	private GetUserByCountryParams(String country) {
		this.country = country;
	}

	public String getCountry() {
		return this.country;
	}

	public void setCountry(String country) {
		this.country = country;
	}

	public List<User> getUsers() {
		return this.users;
	}

	public void setUsers(List<User> users) {
		this.users = users;
	}

	public static GetUserByCountryParams create(String country) {
		return new GetUserByCountryParams(country);
	}
}

We can replace the Map as a bean instance:

SqlSessionFactory factory = ...;
try (SqlSession session = factory.openSession()) {
    SampleMapper mapper = session.getMapper(UserMapper.class);
    GetUserByCountryParams params = GetUserByCountryParams.create("Germany");
    mapper.getUserByCountry(params);
    System.out.println(params.getUsers());
}

Conclusion

To retrieve the values of OUT parameters in a callable statment with MyBatis, we need to use a Map or a bean as the parameter object to the mapper method. MyBatis will set the values of OUT parameters to the Map or bean using the parameter name specified in the SQL. After invoking the mapper method, we can get the value by the parameter names from the parameter object.