Calling Stored Procedures with Spring JDBC Templates

In this article, you learn how to call stored procedures using the Spring JDBC Templates API. Stored procedures are commonly used to encapsulate complex SQL queries. Application developers can leverage the functionality of stored procedures with the Spring JDBC Templates API.

In this example, we have a stored procedure named: getCars. This stored procedure will search for cars based on the given criteria. The procedure returns a result set and status information.

The parameters are listed below:

IN parameters
• Make – varchar
• Model – varchar
• Year – int
• Maximum Price – double

OUT parameters
• Result Set
• Status Code
• Status Message

Here’s an overview of the development process
Step 0: Set up database table, stored procedure and sample data
Step 1: Define an access class for the stored procedure
Step 2: Define a mapper to convert a row in result set to a domain object
Step 3: Call the stored procedure from your DAO implementation
Step 4: Spring configuration
Step 5: Simple test program

Now, let’s walk thru the process step-by-step

Set up database table, stored procedure and sample data
Execute the following SQL code to create the table, create the stored procedure and insert sample data. This is for MySQL. If you are using a different database, then adjust the code accordingly.

This file is included in the source code download. File location: sql/table-setup.sql.

Step 1: Define an access class for the stored procedure

Step 2: Define a mapper to convert a row in result set to a domain object

Step 3: Call the stored procedure from your DAO implementation

Supporting code for DAOConstants.java

Supporting code for Car.java

Supporting code for CarsDAO.java

Step 4: Spring Configuration

Here’s the code for the Spring configuration file:

Step 5: Simple test program

Let’s create a simple test program. We’ll get an instance of our CarsDAOImpl and then search for Nissan Altimas less than $50,000.

File: SpringDemoApp.java

Once you run the SpringDemoApp, then it will print out the following (assuming you used the same database setup script from Step 0).

Status Code=0
Status Messsage=success
Car{make=Nissan, model=Altima, year=2010, price=12000.0}
Car{make=Nissan, model=Altima, year=2013, price=18000.0}
Car{make=Nissan, model=Altima, year=1992, price=1299.0}

You now have the steps to call a stored procedure with Spring JDBC Templates. Enjoy!