Using Stored Procedures with Spring

Spring ( 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))

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

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)
 { = 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");
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 }));
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 }));
37 declareParameter(new SqlParameter("department_id", Types.INTEGER));
39 declareParameter(new SqlOutParameter("sales_total", Types.DOUBLE));
41 compile( );
42 }
44 }

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




The constructor method for the class. It takes a single argument that represents the MySQL server connection.


Set the data source that was provided as an argument.


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.


The declareParameter( ) method invocations define input and output parameters and also any result sets returned by the stored procedure.


Specify the definition of the firstemployee listresult set. The SqlReturnResultSet class represents a result set.


Create an implementation of the RowMapper interface that will map the result set rows.


The mapRow( ) method processes a single row in a result set. It returns an object that represents the row.


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.


Return the new Employee object to the RowMapper, which will add it to the Map being constructed for the current result set.


Repeat the process for the second result set, which is used to create a Map of customer objects.


Define our single input parameterdepartment_idusing the SqlParameter method.


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




Create a new instance of our MyStoredProcedure class, passing an existing DriverManagerDataSource object (datasource) to represent the MySQL connection.


Create a HashMap that will hold the procedure's input parameters.


Add name-value pairs to the HashMap for each input parameter. In this case, we have only a single parameterdepartment_id.


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.


Use the get( ) method of the Map to retrieve a List that represents the rows in the first result set (employees).


Iterate through each element in the List. This is equivalent to moving through each row in the result set.


Cast each list entry to an Employee object representing the current row in the result set.


Use the methods we created for the Employee class to extract and display the details for the current employee.


Process the second result set (customers) in the same way as for the employees result set.


Retrieve and display the value of the single OUT parameter (sales_total).

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 © 2008-2020.
If you may any questions please contact us: