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

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

 

Download Java Source Code

 

Did you like my blog post?

Signup now and receive an email when I publish new luv2code content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Share

28 Comments

  1. Comments  Derp   |  Thursday, 20 September 2012 at 1:59 pm

    Unless I am retarded, where is this DAOConstants defined?

  2. Comments  Chad Darby   |  Thursday, 20 September 2012 at 2:56 pm

    Hi Derp, I added the code for DAOConstants.java.

  3. Comments  Vaughne   |  Wednesday, 12 December 2012 at 11:21 am

    Where/how do you call mapRow? Thx

  4. Comments  Chad Darby   |  Wednesday, 12 December 2012 at 12:32 pm

    Hi,

    The mapRow() method is called by Spring’s JDBC Template. See the docs here for details: http://static.springsource.org/spring/docs/3.1.3.RELEASE/javadoc-api/org/springframework/jdbc/core/RowMapper.html

    Cheers!

  5. Comments  Melvin   |  Wednesday, 09 January 2013 at 10:40 am

    I’m getting a ‘java.lang.IllegalArgumentException: jdbcTemplate must not be null’ for my DAO.

    Maybe you could post your app context xml file as well?

  6. Comments  Chad Darby   |  Saturday, 12 January 2013 at 7:33 am

    Hi Melvin,

    Thanks for your suggestion. I updated the post to include the Spring configuration file (it is at the bottom of the post).

    In regards to your error, make sure you have your DAO impl configured similar to my spring config example. Also, make sure you Java DAO impl extends JdbcDaoSupport. Hope this helps 🙂

  7. Comments  Kavitha   |  Saturday, 13 July 2013 at 12:48 pm

    Hi,
    Could you please provide an example of ‘Calling stored procedure for INSERT’ using jdbc template?
    Actually i’m trying to implements Bulk upload of csv file, which will insert data into multiple tables.

    Thanks,
    Kavitha

  8. Comments  Chad Darby   |  Sunday, 11 August 2013 at 6:35 am

    Hi Kavitha,

    The same code in this post will work for ‘Calling stored procedure for INSERT’ using jdbc template. All you need to change is the code inside of the stored procedure. The stored procedure should be updated to perform an INSERT statement based on the CSV data that you pass in.

  9. Comments  bikash kumar sinha   |  Thursday, 12 September 2013 at 5:11 am

    Suppose if i want to insert a list of objects/data, then how can we manage transaction(not in database procedure) through java.

  10. Comments  Chad Darby   |  Thursday, 12 September 2013 at 9:40 pm

    @bikash: You can manage database transactions in JDBC. See this link for details: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

  11. Comments  Deekay   |  Thursday, 27 March 2014 at 6:51 am

    Hi..I am getting JdbcTemplate as null,Even my wiring is proper in appContext.xml.

    <!– –>

    I hav externalized into new file called persistent.xml and imported to applicationContext.xml.

  12. Comments  Chad Darby   |  Thursday, 27 March 2014 at 11:17 am

    Hi Deekay,

    Thanks for the note. Unfortunately, during your post, the XML is stripped out, so I can’t see your file.

    However, if possible, please use my Spring XML file as a starting point.

  13. Comments  Prashant Shukla   |  Thursday, 03 April 2014 at 3:00 am

    Hi,

    I am getting the same error-‘java.lang.IllegalArgumentException: jdbcTemplate must not be null’ for my DAO.
    I have extended my dao with JdbcDaoSupport also and configured my spring-mvc.xml as same as yours.
    Below is spring-mvc.xml file-

    Thanks,
    Prashant

  14. Comments  Chad Darby   |  Tuesday, 15 April 2014 at 12:31 pm

    Hi Prashant,

    Start with my example. Get it working locally on your computer. Then migrate this to your Spring MVC app.

  15. Comments  Chad   |  Wednesday, 10 September 2014 at 6:10 pm

    Where is Car class/Object defined? Could you please post it?

  16. Comments  Chad Darby   |  Thursday, 11 September 2014 at 8:28 am

    I updated the post to include Car.java.

  17. Comments  Chad   |  Thursday, 11 September 2014 at 9:44 am

    Thanks. However, I am still did not see the code for CarsDAO, I bet you defined it as Interface? Could you please also show thCarsDAO code besides defining it as bean?

  18. Comments  Chad Darby   |  Thursday, 11 September 2014 at 9:54 am

    Thanks. I just added CarsDAO.java to the post 🙂

  19. Comments  Chad   |  Friday, 12 September 2014 at 2:56 pm

    Somehow, I got the error of “the app is currently unreachable” when doing the download things.

  20. Comments  Chad   |  Friday, 12 September 2014 at 3:00 pm

    And your “Step 4: Spring Configuration” is kindA CHOOPED OFF NOW? Thanks.

  21. Comments  Chad Darby   |  Friday, 12 September 2014 at 3:14 pm

    Here’s the file: http://luv2code.com/downloads/spring-jdbc/spring-jdbc-calling-stored-procedures.zip

  22. Comments  Chad Darby   |  Friday, 12 September 2014 at 3:20 pm

    The XML file is updated. The HTML wordpress mangled the XML code.

  23. Comments  Chad   |  Friday, 12 September 2014 at 4:03 pm

    You da man, Chad.

  24. Comments  Chad Darby   |  Friday, 12 September 2014 at 4:49 pm

    Thanks 🙂 BTW: Over the next couple of weeks, I will start rolling out new video tutorials on the Spring framework. Let me know if there are specific Spring topics you would like to see video tutorials for (or any other topic in general).

    Subscribe to my YouTube channel to get notified when the videos will be available:
    https://www.youtube.com/user/luv2codetv?sub_confirmation=1

    Thanks!

  25. Comments  Chad   |  Monday, 15 September 2014 at 12:15 pm

    I am experiencing an issue here: my procedure involves a “refcursor” as OUT parameter, just do not want to define the parameter as VARCHAR and sure it won’t work that way, it is an object, but I do not know how to deal with that. Could you please share some examples like returned refcursor for list of cars? Thanks.

  26. Comments  Chad Darby   |  Monday, 15 September 2014 at 12:25 pm

    Hi,

    This link may help you. It covers how to handle Oracle refcursors with JDBC.

    http://www.enterprisedt.com/publications/oracle/result_set.html

  27. Comments  Chad   |  Monday, 15 September 2014 at 12:59 pm

    Are they (the link) still in JAVA Spring Frame work?

  28. Comments  Chad Darby   |  Tuesday, 16 September 2014 at 7:10 am

    The link is a plain JDBC example. You can use this as a basis for doing a similar thing in Spring.

Leave a Reply