Stored Procedure

Generics

Model execute method (thank you Simon)…

public Offer execute(final Offer offer) {
    final Map<String, Object> inputs = new HashMap<String, Object>();
    inputs.put(ID, offer.getId());
    inputs.put(TITLE, offer.getTitle());
    final Map<?, ?> map = super.execute(inputs);
    final List<?> offers = (List<?>) map.get(SQLResultKeys.RECORDS_KEY);
    if (offers == null || offers.isEmpty()) {
        return null;
    } else {
        return (Offer) offers.get(0);
    }
}

Output Parameter

For a stored procedure which sets an output parameter e.g:

ALTER PROCEDURE [dbo].[get_offer_details]
    @id INT,
    @errorMessage VARCHAR(100) OUT
AS
BEGIN

We can read the output parameter as follows:

public class GetOfferDetailsStoredProcedure extends StoredProcedure {

    static final String ERROR_MESSAGE_KEY = "errorMessage";
    static final String RECORDS_KEY = "records";
    static final String SP_NAME = "get_offer_details";
    static final String ID = "id";

    public GetOfferDetailsStoredProcedure(final DataSource dataSource) {
        super(dataSource, SP_NAME);
        declareParameter(new SqlReturnResultSet(RECORDS_KEY,
                new OfferRowMapper()));
        declareParameter(new SqlParameter(ID, Types.INTEGER));
        declareParameter(new SqlOutParameter(ERROR_MESSAGE_KEY,
                Types.VARCHAR));
        compile();
    }

    public Offer execute(final long id) {
        final Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(ID, id);
        final Map<?, ?> map = super.execute(inputs);
        final List<?> offers = (List<?>) map.get(RECORDS_KEY);
        if (offers == null || offers.size() == 0) {
            final String errorMessage = (String) map
                    .get(ERROR_MESSAGE_KEY);
            if (errorMessage != null) {
                logger.error(errorMessage);
            }
            return null;
        } else {
            return (Offer) offers.get(0);
        }
    }
}

Note:

• This example assumes that the error message is only set when there are no results returned.

• Do not use this example as a model (I am not very happy with the way we just log the error message).

• Have a look at SingleColumnRowMapper if only returning a single row…

SingleColumnRowMapper

Note: This code hasn’t been tested yet…

public class GetExternalReferenceForRewardStoredProcedure extends
        StoredProcedure {

    private static final String SP_NAME = "loyalty_get_external_reference_for_reward";
    private static final String REWARD_ID = "reward_id";
    private static final String IMPORT_SOURCE = "import_source";

    public GetExternalReferenceForRewardStoredProcedure(
            final DataSource dataSource) {
        super(dataSource, SP_NAME);
        declareParameter(new SqlReturnResultSet(SQLResultKeys.RECORDS_KEY,
                new SingleColumnRowMapper(String.class)));
        declareParameter(new SqlParameter(REWARD_ID, Types.INTEGER));
        declareParameter(new SqlParameter(IMPORT_SOURCE, Types.VARCHAR));
        compile();
    }

    public String execute(final int rewardId, final String importSource) {
        final Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(REWARD_ID, rewardId);
        inputs.put(IMPORT_SOURCE, importSource);
        final Map<?, ?> map = super.execute(inputs);
        final List<?> externalReferences = (List<?>) map
                .get(SQLResultKeys.RECORDS_KEY);
        if (externalReferences == null || externalReferences.isEmpty()) {
            return null;
        } else {
            return (String) externalReferences.get(0);
        }
    }
}