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);
}
}
