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.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-call-proc-storedprocedure</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-call-proc-storedprocedure</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<jdk.version>1.8</jdk.version>
		<junit.version>4.11</junit.version>
		<mysql.version>5.1.27</mysql.version>
		<org.springframework.version>4.1.5.RELEASE</org.springframework.version>
	</properties>

	<dependencies>
		<!-- Spring framework -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- MySQL Driver -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql.version}</version>
		</dependency>

		<!-- junit -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

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.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:task="http://www.springframework.org/schema/task"
	xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd">

	<bean id="jdbcProperties"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
		name="configProperties">
		<property name="order" value="99999" />
		<property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
		<property name="ignoreUnresolvablePlaceholders" value="true" />
		<property name="properties">
			<value>

				JDBC.DRIVER=com.mysql.jdbc.Driver
				JDBC.URL=jdbc:mysql://localhost:3306/roytuts
				JDBC.USERNAME=root
				JDBC.PASSWORD=

			</value>
		</property>
	</bean>

</beans>

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

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:task="http://www.springframework.org/schema/task"
	xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd">

	<context:annotation-config></context:annotation-config>
	<import resource="spring-call-proc-storedprocedure-properties.xml" />

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${JDBC.DRIVER}" />
		<property name="url" value="${JDBC.URL}" />
		<property name="username" value="${JDBC.USERNAME}" />
		<property name="password" value="${JDBC.PASSWORD}" />
	</bean>

	<bean id="studentDao"
		class="com.roytuts.spring.storedprocedure.dao.impl.StudentDAOImpl" />

</beans>

Step 7. Create a MySQL table

CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) NOT NULL,
  `student_dob` varchar(10) NOT NULL,
  `student_email` varchar(80) NOT NULL,
  `student_address` varchar(250) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 8. Dump some data into a MySQL table

insert  into `student`(`student_id`,`student_name`,`student_dob`,`student_email`,`student_address`) values (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-1992','souvik@email.com','Alipore'),
(5,'Liton','01-01-1990','liton@email.com','Salt Lake');

Step 9. Create a MySQL procedures

Procedure 1 

DELIMITER $$

CREATE
    PROCEDURE `roytuts`.`get_student`(IN in_student_id INTEGER,
	    OUT out_student_name VARCHAR(30),
	    OUT out_student_dob VARCHAR(10),
	    OUT out_student_email VARCHAR(80),
	    OUT out_student_address VARCHAR(255))
    BEGIN
	SELECT student_name, student_dob, student_email, student_address
	INTO out_student_name, out_student_dob, out_student_email, out_student_address
	FROM student WHERE student_id = in_student_id;
    END$$

DELIMITER ;

If you run the above procedure using the following command

CALL get_student(1,@name,@dob,@email,@address)

Now get the result by executing below SELECT statement

SELECT @name,@dob,@email,@address

Result

@name	@dob	    @email	          @address
Sumit	01-01-1980	sumit@email.com	  Garifa

Procedure 2

DELIMITER $$

CREATE
    PROCEDURE `roytuts`.`get_all_students`()
    BEGIN
	SELECT * FROM student;
    END$$

DELIMITER ;

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.

package com.roytuts.spring.storedprocedure.dto;

public class Student {

	private Integer studentId;
	private String studentName;
	private String studentDob;
	private String studentEmail;
	private String studentAddress;

	public Student() {

	}

	public Student(Integer studentId, String studentName, String studentDob,
			String studentEmail, String studentAddress) {
		this.studentId = studentId;
		this.studentName = studentName;
		this.studentDob = studentDob;
		this.studentEmail = studentEmail;
		this.studentAddress = studentAddress;
	}

	public Integer getStudentId() {
		return studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public String getStudentDob() {
		return studentDob;
	}

	public void setStudentDob(String studentDob) {
		this.studentDob = studentDob;
	}

	public String getStudentEmail() {
		return studentEmail;
	}

	public void setStudentEmail(String studentEmail) {
		this.studentEmail = studentEmail;
	}

	public String getStudentAddress() {
		return studentAddress;
	}

	public void setStudentAddress(String studentAddress) {
		this.studentAddress = studentAddress;
	}

	@Override
	public String toString() {
		return "Student Id :" + studentId + ", Name : " + studentName
				+ ", Date of Birth : " + studentDob + ", Email : "
				+ studentEmail + ", Address : " + studentAddress;
	}

}

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

package com.roytuts.spring.storedprocedure.dao;

import java.util.List;

import com.roytuts.spring.storedprocedure.dto.Student;

public interface StudentDAO {

	Student getStudentUsingProc(int studentId);

	List<Student> getAllStudents();
}

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

package com.roytuts.spring.storedprocedure.dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.roytuts.spring.storedprocedure.dto.Student;

public class StudentMapper implements RowMapper<Student> {

  @Override
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
    Student student = new Student();
	student.setStudentId(rs.getInt("student_id"));
	student.setStudentName(rs.getString("student_name"));
	student.setStudentDob(rs.getString("student_dob"));
	student.setStudentEmail(rs.getString("student_email"));
	student.setStudentAddress(rs.getString("student_address"));
    return student;
  }

}

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

package com.roytuts.spring.storedprocedure.dao.impl;

import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.StoredProcedure;

import com.roytuts.spring.storedprocedure.dao.StudentDAO;
import com.roytuts.spring.storedprocedure.dao.mapper.StudentMapper;
import com.roytuts.spring.storedprocedure.dto.Student;

public class StudentDAOImpl implements StudentDAO {

	@Autowired
	private DriverManagerDataSource dataSource;

	private final String PROC_CATALOG = "roytuts";
	private final String PROC_GET_STUDENT = "get_student";
	private final String PROC_GET_ALL_STUDENTS = "get_all_students";

	@Override
	public Student getStudentUsingProc(int studentId) {
		StudentProcedure studentProcedure = new StudentProcedure();
		Student student = studentProcedure.execute(studentId);
		return student;
	}

	private class StudentProcedure extends StoredProcedure {

		public StudentProcedure() {
			super(dataSource, PROC_CATALOG + "." + PROC_GET_STUDENT);
			declareParameter(new SqlParameter("in_student_id", Types.INTEGER));
			declareParameter(new SqlOutParameter("out_student_name",
					Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_dob",
					Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_email",
					Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_address",
					Types.VARCHAR));
			compile();
		}

		public Student execute(int studentId) {
			Map<String, Object> result = super.execute(studentId);
			// get Student object
			Student student = new Student();
			// simple the studentId
			student.setStudentId(studentId);
			// set Name, must be same as out param in procedure
			student.setStudentName((String) result.get("out_student_name"));
			// set Date of Birth, must be same as out param in procedure
			student.setStudentDob((String) result.get("out_student_dob"));
			// set Email, must be same as out param in procedure
			student.setStudentEmail((String) result.get("out_student_email"));
			// set Address, must be same as out param in procedure
			student.setStudentAddress((String) result
					.get("out_student_address"));
			return student;
		}
	}

	@Override
	public List<Student> getAllStudents() {
		StudentProcedure2 procedure2 = new StudentProcedure2();
		Map<String, Object> map = procedure2.execute();
		@SuppressWarnings("unchecked")
		List<Student> students = (List<Student>) map.get("students");
		return students;
	}

	private class StudentProcedure2 extends StoredProcedure {

		public StudentProcedure2() {
			super(dataSource, PROC_CATALOG + "." + PROC_GET_ALL_STUDENTS);
			declareParameter(new SqlReturnResultSet("students",
					new StudentMapper()));
			compile();
		}

		public Map<String, Object> execute() {
			// no IN parameter so passing empty HashMap
			Map<String, Object> results = super.execute(new HashMap<>());
			return results;
		}
	}
}

Step 14. Create JUnit test class

package com.roytuts.spring.storedprocedure.dao.impl;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.roytuts.spring.storedprocedure.dto.Student;

public class StudentDAOImplTest {

	StudentDAOImpl studentDAOImpl;

	@Before
	public void setUp() {
		@SuppressWarnings("resource")
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
				"spring-call-proc-storedprocedure.xml");
		studentDAOImpl = (StudentDAOImpl) applicationContext
				.getBean("studentDao");
	}

	@Test
	public void testGetStudentUsingProc() {
		Student student = studentDAOImpl.getStudentUsingProc(1);
		System.out.println("Get a student");
		System.out.println(student);
	}

	@Test
	public void testGetAllStudents() {
		List<Student> students = studentDAOImpl.getAllStudents();
		System.out.println("Get all students");
		for (Student student : students) {
			System.out.println(student);
		}
	}

}

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

Apr 07, 2015 8:12:11 AM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1c5e7b4: startup date [Tue Apr 07 08:12:11 IST 2015]; root of context hierarchy
Apr 07, 2015 8:12:11 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-call-proc-storedprocedure.xml]
Apr 07, 2015 8:12:11 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-call-proc-storedprocedure-properties.xml]
Apr 07, 2015 8:12:11 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Get a student
Student Id :1, Name : Sumit, Date of Birth : 01-01-1980, Email : sumit@email.com, Address : Garifa
Apr 07, 2015 8:12:11 AM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@78a03f: startup date [Tue Apr 07 08:12:11 IST 2015]; root of context hierarchy
Apr 07, 2015 8:12:11 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-call-proc-storedprocedure.xml]
Apr 07, 2015 8:12:11 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-call-proc-storedprocedure-properties.xml]
Apr 07, 2015 8:12:11 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Get all students
Student Id :1, Name : Sumit, Date of Birth : 01-01-1980, Email : sumit@email.com, Address : Garifa
Student Id :2, Name : Gourab, Date of Birth : 01-01-1982, Email : gourab@email.com, Address : Garia
Student Id :3, Name : Debina, Date of Birth : 01-01-1982, Email : debina@email.com, Address : Salt Lake
Student Id :4, Name : Souvik, Date of Birth : 01-01-1992, Email : souvik@email.com, Address : Alipore
Student Id :5, Name : Liton, Date of Birth : 01-01-1990, Email : liton@email.com, Address : Salt Lake

That’s all. Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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