MySQL Stored Procedure Programming

Using Stored Procedures with Spring

Spring (http://www.springframework.org) is a popular, lightweight framework for the development of Java applications. Spring offers many facilities that support the development of Java applications, including support for Model-View-Controller design, POJO (Plain Old Java Objects) , integration with J2EE objects, Aspect Oriented Programming, integration with other complementary frameworks such as Hibernate, and abstraction layers for transaction management and database access. Spring aims to deliver on many of the promises of the J2EE framework, but in a less invasive and more productive manner.

Spring's JDBC abstraction layer eliminates much of the repetitive coding normally associated with even simple SQL queries. The abstraction layer includes a StoredProcedure class that can be used to incorporate stored procedure calls into a Spring application. In this section we will provide a brief overview of how to access a MySQL stored procedure from within a Spring application.

Example 14-38 shows the stored procedure we are going to use in our Spring example. It accepts a single input parameter—the department_id—and returns two result sets. The first result set contains a list of employees in that department, and the second contains a list of customers associated with the department. The stored procedure includes an OUT parameter that returns the total value of all sales associated with the department.

Example 14-38. Stored procedure for use with ...