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!
Hi Derp, I added the code for DAOConstants.java.
Where/how do you call mapRow? Thx
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!
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?
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 🙂
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
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.
Suppose if i want to insert a list of objects/data, then how can we manage transaction(not in database procedure) through java.
@bikash: You can manage database transactions in JDBC. See this link for details: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html
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.
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.
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
Hi Prashant,
Start with my example. Get it working locally on your computer. Then migrate this to your Spring MVC app.
Where is Car class/Object defined? Could you please post it?
I updated the post to include Car.java.
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?
Thanks. I just added CarsDAO.java to the post 🙂
Somehow, I got the error of “the app is currently unreachable” when doing the download things.
Here’s the file: https://luv2code.com/downloads/spring-jdbc/spring-jdbc-calling-stored-procedures.zip
And your “Step 4: Spring Configuration” is kindA CHOOPED OFF NOW? Thanks.
The XML file is updated. The HTML wordpress mangled the XML code.
You da man, Chad.
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!
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.
Hi,
This link may help you. It covers how to handle Oracle refcursors with JDBC.
http://www.enterprisedt.com/publications/oracle/result_set.html
Are they (the link) still in JAVA Spring Frame work?
The link is a plain JDBC example. You can use this as a basis for doing a similar thing in Spring.