Call Stored Procedure using Spring StoredProcedure

This tutorial will show you how we can stored procedure using Spring SimpleJdbcCall. For this tutorial we will create a standalone maven project in Eclipse.

If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.

Sometimes we need to call a stored procedure while a single SQL statement is unable to fulfil our requirements because, it permits to execute multiple SQL statements and it also allows us to execute these SQL statements in a transactional unit in an efficient manner to avoid any inconsistency sometimes occurs during the execution of multiple SQL statements.

Prerequisites

The following things are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven installed and configured
Spring dependencies in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : spring-call-proc-storedprocedure

Step 2. Modify the pom.xml file as shown below.

Step 3. If you see JRE System Library[J2SE-1.4] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.4], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.

Step 4. Create src/main/resources folder for putting the resource files.

Do right-click on the project and go New -> Source Folder. Give Folder name: as src/main/resources and click on Finish button.

Step 5. Create an XML properties file under src/main/resources.

Do right-click on src/main/resources in the project and go New -> file. Give File name: as spring-call-proc-storedprocedure-properties.xml and click on Finish button.

Step 6. Create spring-call-proc-storedprocedure.xml file under src/main/resources with the below content.

Step 7. Create a MySQL table

Step 8. Dump some data into a MySQL table

Step 9. Create a MySQL procedures

Procedure 1 

If you run the above procedure using the following command

Now get the result by executing below SELECT statement

Result

Procedure 2

Execute

student_id student_name student_dob student_email student_address
1 Sumit 01-01-1980 sumit@email.com Garifa
2 Gourab 01-01-1982 gourab@email.com Garia
3 Debina 01-01-1982 debina@email.com Salt Lake
4 Souvik 01-01-1990 souvik@email.com Alipore
5 Liton 01-01-1992 liton@email.com Salt Lake

Step 10. Create a Student POJO class.

Step 11. Create a DAO in com.roytuts.spring.storedprocedure.dao package

Note : if you get error then you can change the Compiler compliance level, Generated .class files compatibility, Source compatibility to 1.7

Step 12. Create StudentMapper class which imeplements RowMapper and maps each row to a Student object

Step 13. Now create a DAO implementation class in com.roytuts.spring.storedprocedure.dao.impl package.

Step 14. Create JUnit test class

Step 15. Run the test case, you will get the following output in the console

That’s all. Thanks for reading.

Soumitra

Software Professional, I am passionate to work on web/enterprise application. For more information please go to about me. You can follow on Twitter. You can be a friend on Facebook or Google Plus or Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.