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 parameterthe department_idand 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 our Spring example

CREATE PROCEDURE sp_department_report
 (in_dept_id INTEGER, OUT sales_total DECIMAL(8,2))
BEGIN

 SELECT employee_id, surname, firstname, address1, address2, salary
 FROM employees
 WHERE department_id = in_dept_id;

 SELECT customer_id, customer_name, address1, address2, zipcode
 FROM customers
 WHERE sales_rep_id IN
 (SELECT employee_id FROM employees
 WHERE department_id = in_dept_id);

 SELECT SUM(sale_value)
 INTO sales_total
 FROM sales
 WHERE customer_id IN
 (SELECT customer_id
 FROM customers
 WHERE sales_rep_id IN
 (SELECT employee_id
 FROM employees
 WHERE department_id = in_dept_id));
END

The natural way to represent the customer and employee rows returned by the stored procedure is to create customer and employee Java classes. Example 14-39 shows part of the class that would represent employees. We created a similar class for customers.

Example 14-39. Java class to represent employees

public class Employee
{
 private long id;
 private String surname;
 private String firstName;
 private String address1;
 private String address2;
 private double salary;

 public Employee(long id, String surname, String firstName,
 String address1, String address2, double salary)
 {
 this.id = id;
 this.surname = surname;
 this.firstName = firstName;
 this.address1 = address1;
 this.address2 = address2;
 this.salary = salary;
 }

 public String toString( ) {
 return "Employee : " + employeeId + " " + surname;
 }

 public String getSurname( ) {
 return surname;
 }

 public String getFirstName( ) {
 return firstName;
 }

 /* Other getters and setters would go here */
}

To represent the stored procedure, we create a new class that extends the Spring StoredProcedure class, as shown in Example 14-40.

Example 14-40. Class to represent a stored procedure in Spring

1 private class MyStoredProcedure extends StoredProcedure
2 {
3 public MyStoredProcedure(DataSource ds)
4 {
5 setDataSource(ds);
6 setSql("sp_department_report");
7
8 declareParameter(new SqlReturnResultSet("Employees",
9 new RowMapper( ) {
10 public Object mapRow(ResultSet rs, int rowNum)
11 throws SQLException {
12 Employee e = new Employee(
13 rs.getInt("employee_id"),
14 rs.getString("surname"),
15 rs.getString("firstname"),
16 rs.getString("address1"),
17 rs.getString("address2"),
18 rs.getDouble("salary"));
19 return e;
20 }
21 }));
22
23 declareParameter(new SqlReturnResultSet("Customers",
24 new RowMapper( ) {
25 public Object mapRow(ResultSet rs, int rowNum)
26 throws SQLException {
27 Customer c = new Customer(
28 rs.getInt("customer_id"),
29 rs.getString("customer_name"),
30 rs.getString("address1"),
31 rs.getString("address2"),
32 rs.getString("zipcode"));
33 return c;
34 }
35 }));
36
37 declareParameter(new SqlParameter("department_id", Types.INTEGER));
38
39 declareParameter(new SqlOutParameter("sales_total", Types.DOUBLE));
40
41 compile( );
42 }
43
44 }

Let's look at the significant lines of this class:

Line(s)	Explanation
3	The constructor method for the class. It takes a single argument that represents the MySQL server connection.
5	Set the data source that was provided as an argument.
6	Set the SQL associated with the stored procedure. The SQL should contain only the stored procedure name parentheses, the CALL statement, and parameter placeholders are neither required nor allowed.
839	The declareParameter( ) method invocations define input and output parameters and also any result sets returned by the stored procedure.
821	Specify the definition of the firstemployee listresult set. The SqlReturnResultSet class represents a result set.
9	Create an implementation of the RowMapper interface that will map the result set rows.
10	The mapRow( ) method processes a single row in a result set. It returns an object that represents the row.
1218	Create an Employee object to hold a single employee row from the result set. We create the Employee object using the default constructor with the values of the current row as arguments. We use the normal JDBC syntax to retrieve each column from the row and assign it to the appropriate constructor argument.
19	Return the new Employee object to the RowMapper, which will add it to the Map being constructed for the current result set.
2335	Repeat the process for the second result set, which is used to create a Map of customer objects.
37	Define our single input parameterdepartment_idusing the SqlParameter method.
39	Define our single output parametersales_totalusing the SqlOutParameter method.

Now that we have created a class that knows how to process the inputs and outputs of our stored procedure, we are ready to use the stored procedure within our Java code. The StoredProcedure class takes, as its argument, a Map that includes all of the required parameters to the stored procedure call. The class returns a Map that contains all of the result sets and output parameters. Example 14-41 shows us using the StoredProcedure class in our Java code.

Example 14-41. Using a Spring stored procedure class

1 MyStoredProcedure msp = new MyStoredProcedure(datasource);
2 Map inParameters = new HashMap( );
3 inParameters.put("department_id", new Integer(department_id));
4 Map results = msp.execute(inParameters);
5
6 List employees = (List) results.get("Employees");
7 System.out.println("Employees of department " + department_id);
8 for (int i = 0; i < employees.size( ); i++) {
9 Employee e = (Employee) employees.get(i);
10 System.out.println(e.getEmployeeId( ) + "	" +
11 e.getFirstname() + "	" + e.getSurname( ));
12 }
13
14 List customers = (List) results.get("Customers");
15 System.out.println("Customers of department " + department_id);
16 for (int i = 0; i < customers.size( ); i++) {
17 Customer c = (Customer) customers.get(i);
18 System.out.println(c.getCustomerId() + "	" + c.getCustomerName( ));
19 }
20
21 Double salesTotal = (Double) results.get("sales_total");
22 System.out.println("Total sales for the department " +
23 department_id + "=" + salesTotal);

Here is an explanation of this code:

Line(s)	Explaination
1	Create a new instance of our MyStoredProcedure class, passing an existing DriverManagerDataSource object (datasource) to represent the MySQL connection.
2	Create a HashMap that will hold the procedure's input parameters.
3	Add name-value pairs to the HashMap for each input parameter. In this case, we have only a single parameterdepartment_id.
4	Use the execute( ) method of the StoredProcedure object to execute the stored procedure. We pass in the Map containing input parameters, and we retrieve a new Map containing all the outputs of the stored procedure call.
6	Use the get( ) method of the Map to retrieve a List that represents the rows in the first result set (employees).
8	Iterate through each element in the List. This is equivalent to moving through each row in the result set.
9	Cast each list entry to an Employee object representing the current row in the result set.
10	Use the methods we created for the Employee class to extract and display the details for the current employee.
14-19	Process the second result set (customers) in the same way as for the employees result set.
21-23	Retrieve and display the value of the single OUT parameter (sales_total).