Spring: Invoking Stored Procedure

PROBLEM

There are many ways to skin a cat… so is invoking a stored procedure using Spring.

Let’s assume we want to invoke the following stored procedure that accepts 3 arguments (person ID, start date, end date):-

{ call calendar_sp (?, ?, ?) }

This stored procedure returns a result set where each row contains a person ID, a date and a description.

SOLUTION

Create a class that extends org.springframework.jdbc.object.StoredProcedure:-

@Component
public class CalendarStoredProcedure extends StoredProcedure {

    @Autowired
    public CalendarStoredProcedure(DataSource dataSource) {
        super(dataSource, "calendar_sp");

        // input parameters
        declareParameter(new SqlParameter("person_id", Types.INTEGER));
        declareParameter(new SqlParameter("start_date", Types.VARCHAR));
        declareParameter(new SqlParameter("end_date", Types.VARCHAR));

        // handling result set
        declareParameter(new SqlReturnResultSet("calendarDays", new RowMapper<CalendarDayBean>() {
            @Override
            public CalendarDayBean mapRow(ResultSet rs, int i) throws SQLException {
                return new CalendarDayBeanBuilder()
                        .setPersonId(rs.getLong("person_id"))
                        .setDate(CommonUtils.convertSQLDateToLocalDate(rs.getDate("calendar_date")))
                        .setDescription(rs.getString("description"))
                        .createCalendarDayBean();
            }
        }));

        compile();
    }

    @SuppressWarnings("unchecked")
    public Collection<CalendarDayBean> getCalendar(Long personId, LocalDate startDate, LocalDate endDate) {
        final String strStartDate = ...; // convert `startDate` to string
        final String strEndDate = ...; // convert `endDate` to string
							
        final Map<String, Object> results = execute(personId, strStartDate, strEndDate);

        return ImmutableList.copyOf((List<CalendarDayBean>) results.get("calendarDays"));
    }
}

The data source is wired into this spring-managed bean. A helper API called getCalendar(...) is created to allow the DAO to invoke this stored procedure.

Now, create a DAO class and wire the stored procedure class into it:-

@Service
public class CalendarDAO {
    private final CalendarStoredProcedure calendarStoredProcedure;

    @Autowired
    public CalendarDAO(CalendarStoredProcedure calendarStoredProcedure) {
        this.calendarStoredProcedure = calendarStoredProcedure;
    }

    @Override
    public Collection<CalendarDayBean> getCalendar(final Long personId,
                                                   final LocalDate startDate,
                                                   final LocalDate endDate) {

        return calendarStoredProcedure.getCalendar(personId, startDate, endDate);
    }
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s