Muhammad Hewedy edited this page Mar 7, 2017 · 9 revisions

However spwrap is very simple, yet it is very important to understand Mappers correctly.

Mapper is a class that implements one of type interfaces ResultSetMapper and TypedOutputParamMapper.

  • The first interface ResultSetMapper you will have to implement to return ResultSet from your stored procedure.

  • The second interface TypedOutputParamMapper you will have to implement to return registered output parameters from your stored procedure.

These Mappers is used internally by spwrap to provide the final result for you:

List<Customer> listCustomersWithBirthDateBefore(@Param(java.sql.Types.DATE) birthDate);

In the above code snippet, suppose we have the stored procedure proc_list_all_customers_with_birhtdate_before that takes 1 input parameter and return a result set as a result of executing a select query like:

select id, firstName, lastName from customers where ....

NOTE: All stored procedures by default need to return 2 additional output parameters for result code and message, however you can override such behaviour using Configurations read more on configurations wiki page

In this case, the CustomerMapper need to implement ResultSetMapper and provide the mapping by implementing the map method:

public class CustomerMapper implements ResultSetMapper<Customer> {

	public Customer map(Result<?> result) {
		return new Customer(result.getInt(1), result.getString(2), result.getString(3));

NOTE: Mapping classes could be reused across your application, however I strongly recommend to have a set of General-usage Mapping classes and reuse them, such Mapping classes might be for example IdMapping class to return a single Long/Integer ID value of newly created recored.

We have seen an example of ResultSetMapper object, however the TypedOutputParamMapper is the same, it has additional method that you have to implement, List<Integer> getTypes() these are the types of the registered output parameters (excluding the 2 additional result code and message output parameters).

see this example:

Customer getCustomerInfo(@Param(Types.BIGINT)Long custId);

In this case because there's no @Mapping annotation, the domain object Customer will need to act as the mapping object. or you will get a CallException saying method return type is not void however no mapping provided!.

To avoid such Exception, your Customer class need to implement TypedOutputParamMapper:

public class Customer implements TypedOutputParamMapper<Customer> {

	private Integer id;
	private String firstName, lastName;

	// mandatory when implementing TypedOutputParamMapper or ResultSetMapper
	public Customer() {

	public Customer(Integer id, String firstName, String lastName) {
		super(); = id;
		this.firstName = firstName;
		this.lastName = lastName;

	public Integer id() {
		return id;

	public String firstName() {
		return firstName;

	public String lastName() {
		return lastName;

	public Customer map(Result<?> result) {
		return new Customer(null, result.getString(1), result.getString(2));

	public List<Integer> getTypes() {
		return Arrays.asList(VARCHAR, VARCHAR);

	public String toString() {
		return "Customer [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + "]";

NOTE: The Result object that is passed to your map method on either ResultSetMapper or TypedOutputParamMapper is 1-based index, so the first output parameter index is 1 regardless of its order in the whole stored procedure parameters, remember the first output parameter index is 1, the second 2 and so on. and the same thing applies to the result set.

NOTE: remember always, if you choose to have all your stored procedures have 2 additional output parameters for result code and message (default behaviour), you will not either see these two output parameters in your code or deal with them, completely ignore them when you implement the map and getTypes methods.

Because both interfaces ResultSetMapper or TypedOutputParamMapper have the following method:

T map(Result<?> result);

and if you have one class that implement both interfaces, you still can distinguish between the call of each interface using result.isResultSet or result.isCallableStatement:

public Customer map(Result<?> result) {
	if (result.isResultSet()) {
		//handle the call that returns a result set
	} else {
		//handle the call that returns an output parameters

NOTE the map method of ResultSetMapper need to return a new Object each time it is invoked.

NOTE A no-args constructor should be exists on the Mapping class.

Result class is just a wrapper around CallableStatement or ResultSet that its main purpose is to wrap the checked SQLException into the runtime CallException.

You can always access the underlying CallableStatement or ResultSet object using:

if (result.isResultSet()){
    java.sql.ResultSet rs = ((ResultSet)result.wrappedObject());
    // access data from rs

if (result.isCallableStatement()){
    java.sql.CallableStatement cstmt = ((CallableStatement)result.wrappedObject());
    // access data from cstmt

NOTE: when you access output parameters from CallableStatement, you will need to use the index of the output parameter in regard to the whole parameters (input and output), for example you have 3 IN and 3 out, so the first out param will be of index 4, not this is not the case if you access the parameter using the Result wrapper, the first output parameter is of index 1.

As said before, the DAO method can point to the Mapper class into two ways

  • the Mapper class is the same class that is returned from the DAO method.
MapperClass getSomeData();
  • the Mapper class is annotating the DAO method using @Mapper annotation.
ReturnObject getSomeData();

And the precedence is always for the Mapper class that provided in the @Mapper annotation. in other words, if you have 2 stored procedures that return customer information but in different order, for example first Stored procedure returns firstName, birthDate, age, and the second Stored procedure returns just age, lastName

and you have these 2 DAO methods to call the stored procedures:

Customer getFirstNameBirthDateAndAgeBy(@Param(BIGINT) Long custId);

Customer getAgeAndLastNameBy(@Param(BIGINT) Long custId);

In such case, the customer object cannot be used as a Mapper for both Stored procedure, so you can use it as a Mapper for one, and override it with @Mapper annotation with a Custom mapper for the other like this:

Customer getFirstNameBirthDateAndAgeBy(@Param(BIGINT) Long custId);

Customer getAgeAndLastNameBy(@Param(BIGINT) Long custId);

NOTE: the @Mapper annotation can take up to 2 Mapper classes one of each Type (ResultSetMapper and TypedOutputParamMapper, in other words, any DAO method can return up to 1 result set and 1 list of output parameters. and if a DAO method returned both in one call, then the return type should be Tuple. Example:

@Mapper({CustomerResultSetMapper.class, OrderParamMapper.class})
Tuple<Customer, Order> getAllCustomersAndOrderby(@Param(Types.BIGINT) Long orderId);

Tuple<Customer, Order> t = getAllCustomersAndOrderby(100l);
List<Customer> customerList = t.list();
Order order = t.object();

NOTE: If your stored procedure returns a single output parameter with no result set, then you can use the @Scalar annotation and you will not need to provide a Mapper class yourself, the mapping will done for you. read more about Scalar annotation.