<div dir="ltr" style="text-align: justify;">
<p>The <b>SimpleJdbcCall </b>was introduced in Spring 2.5. It’s goal is to make calling stored procedures as simple as possible. It tries to use JDBC meta-data as much as possible, and it allows the developers to override settings. Using any other class like &#8220;<a href="https://dineshonjava.com/using-jdbctemplate-in-spring-chapter-33/"><b>JdbcTemplate</b></a>&#8221; or extending from &#8220;<b>StoredProcedure</b>&#8221; is considered &#8220;old school spring&#8221;.</p>
<div style="background-color: #f2f9fc; border: 1px solid #c9e6f2; border-radius: 3px; padding: 16px; line-height: 1.45;">
<p><span style="color: red; font-size: x-large; text-align: center;"><b>Popular Tutorials</b></span></p>
<ul style="text-align: left;">
<li><b><a href="https://dineshonjava.com/spring-tutorial/"><em><strong>Spring Tutorial</strong> </em></a></b></li>
<li><b><a href="https://dineshonjava.com/spring-web-mvc-framework-chapter-38/"><strong><em>Spring MVC Web Tutorial </em></strong></a></b></li>
<li><b><a href="https://dineshonjava.com/spring-boot-tutorial/"><strong>Spring Boot Tutorial</strong> </a></b></li>
<li><b><a href="https://dineshonjava.com/spring-security-take-baby-step-to-secure/"><em>Spring Security Tutorial</em></a></b></li>
<li><b><a href="https://dineshonjava.com/spring-aop-tutorial-with-example-aspect-advice-pointcut-joinpoint/"><em>Spring AOP Tutorial</em></a></b></li>
<li><b><a href="https://dineshonjava.com/using-spring-jdbc-framework-chapter-32/"><em>Spring JDBC Tutorial</em></a></b></li>
<li><b><a href="https://dineshonjava.com/spring-hateoas-hypermedia-driven-restful-web-service/"><em><strong>Spring HATEOAS </strong></em></a></b></li>
<li><b><a href="https://dineshonjava.com/microservices-with-spring-boot/"><em><strong>Microservices with Spring Boot</strong></em></a></b></li>
<li><b><a href="https://dineshonjava.com/jax-rs-web-service-tutorial/"><strong><em>REST Webservice</em> </strong></a></b></li>
<li><b><a href="https://dineshonjava.com/core-java-baby-step-to-be-best-java-ian/"><em><strong>Core Java </strong></em></a></b></li>
<li><b><a href="https://dineshonjava.com/hibernate-3-on-baby-steps/"><em><strong>Hibernate Tutorial</strong></em></a></b></li>
<li><b><a href="https://dineshonjava.com/spring-batch-process-with-example/"><strong><em>Spring Batch</em> </strong></a></b></li>
</ul>
</div>
<p><b>Step-1 </b>: Let&#8217;s create an Employee table which can be created in MySQL <b>DAVDB </b>database with the following DDL:</p>
<pre class="highlight">CREATE TABLE Employee( 
 EMPID INT NOT NULL AUTO_INCREMENT, 
 NAME VARCHAR(20) NOT NULL, 
 AGE INT NOT NULL, 
 SALARY BIGINT NOT NULL, 
 PRIMARY KEY (ID) 
); 
</pre>
<p><b>Step-2: </b>Let&#8217;s create a stored procedure which takes employee Id (empid) and returns corresponding employee&#8217;s name, age and salary using OUT parameters.</p>
<pre class="highlight">DELIMITER $$ 
 
DROP PROCEDURE IF EXISTS `DAVDB`.`getEmpRecord` $$ 
CREATE PROCEDURE `DAVDB`.`getEmpRecord` ( 
IN in_empid INTEGER, 
OUT out_name VARCHAR(26), 
OUT out_age INTEGER, 
OUT out_salary LONG) 
BEGIN 
 SELECT name, age, salary 
 INTO out_name, out_age, out_salary 
 FROM Employee where empid = in_empid ; 
END $$ 
 
DELIMITER ; 
</pre>
<p> ;</p>
<div class="separator" style="clear: both; text-align: center;"><img src="https://dineshonjava.com/wp-content/uploads/2012/12/storedprocedure.jpg" border="0" /></div>
<p><b>Step-3: </b>Create Java Project &#8220;<b>SpringSPDemo</b>&#8221; and add the required <b>spring library and mysql library</b> as following.</p>
<div class="separator" style="clear: both; text-align: center;"><img src="https://dineshonjava.com/wp-content/uploads/2012/12/spproject.jpg" border="0" /></div>
<p> ;</p>
<p><b>Step-4: </b>Create DAO interface <b><i>EmpDAO</i> </b>and list down all the required methods. Though it is not required and you can directly write <b><i>EmployeeJDBCTemplate</i> </b>class, but as a good practice, let&#8217;s do it.</p>
<pre class="highlight">package com.dineshonjava.sdnext.dao; 
 
import java.util.List; 
 
import com.dineshonjava.sdnext.domain.Employee; 
 
/** 
 * @author Dinesh Rajput 
 * 
 */ 
public interface EmpDao { 
 
 /** 
 * This is the method to be used to create 
 * a record in the Employee table. 
 */ 
 void create(String name, Integer age, Long salary); 
 /** 
 * This is the method to be used to list down 
 * a record from the Employee table corresponding 
 * to a passed Employee id. 
 */ 
 Employee getEmployee(Integer empid); 
 /** 
 * This is the method to be used to list down 
 * all the records from the Employee table. 
 */ 
 List listEmployees(); 
 /** 
 * This is the method to be used to delete 
 * a record from the Employee table corresponding 
 * to a passed Employee id. 
 */ 
 void delete(Integer empid); 
 /** 
 * This is the method to be used to update 
 * a record into the Employee table. 
 */ 
 void update(Integer empid, Integer age); 
} 
</pre>
<p><b>Step-5:</b> Create other required Java classes <b>Employee</b>, <b>EmployeeMapper</b>, <b>EmployeeDaoImpl </b>and <b>EmpMainApp </b>under the <i><b>com.dineshonjava.sdnext.*</b></i> package.<br />
<b>Employee.java</b></p>
<pre class="highlight">package com.dineshonjava.sdnext.domain; 
 
/** 
 * @author Dinesh Rajput 
 * 
 */ 
public class Employee { 
 private int empid; 
 private String name; 
 private int age; 
 private long salary; 
 /** 
 * @return the empid 
 */ 
 public int getEmpid() { 
 return empid; 
 } 
 /** 
 * @param empid the empid to set 
 */ 
 public void setEmpid(int empid) { 
 this.empid = empid; 
 } 
 /** 
 * @return the name 
 */ 
 public String getName() { 
 return name; 
 } 
 /** 
 * @param name the name to set 
 */ 
 public void setName(String name) { 
 this.name = name; 
 } 
 /** 
 * @return the age 
 */ 
 public int getAge() { 
 return age; 
 } 
 /** 
 * @param age the age to set 
 */ 
 public void setAge(int age) { 
 this.age = age; 
 } 
 /** 
 * @return the salary 
 */ 
 public long getSalary() { 
 return salary; 
 } 
 /** 
 * @param salary the salary to set 
 */ 
 public void setSalary(long salary) { 
 this.salary = salary; 
 } 
 public String toString(){ 
 return "EMPLOYEE{empid- "+this.empid+" name- "+this.name+ 
 " age- "+this.age+" salary- "+this.salary+"}"; 
 } 
} 
</pre>
<p><b>EmployeeMapper.java</b></p>
<pre class="highlight">package com.dineshonjava.sdnext.jdbc.utils; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
import org.springframework.jdbc.core.RowMapper; 
 
import com.dineshonjava.sdnext.domain.Employee; 
 
/** 
 * @author Dinesh Rajput 
 * 
 */ 
public class EmployeeMapper implements RowMapper { 
 public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { 
 Employee employee = new Employee(); 
 employee.setEmpid(rs.getInt("empid")); 
 employee.setName(rs.getString("name")); 
 employee.setAge(rs.getInt("age")); 
 employee.setSalary(rs.getLong("salary")); 
 return employee; 
 } 
} 
</pre>
<p><b>EmployeeDaoImpl.java</b></p>
<pre class="highlight">package com.dineshonjava.sdnext.dao.impl; 
 
import java.util.List; 
import java.util.Map; 
 
import javax.sql.DataSource; 
 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; 
import org.springframework.jdbc.core.namedparam.SqlParameterSource; 
import org.springframework.jdbc.core.simple.SimpleJdbcCall; 
import org.springframework.stereotype.Component; 
 
import com.dineshonjava.sdnext.dao.EmpDao; 
import com.dineshonjava.sdnext.domain.Employee; 
import com.dineshonjava.sdnext.jdbc.utils.EmployeeMapper; 
 
/** 
 * @author Dinesh Rajput 
 * 
 */ 
@Component 
public class EmployeeDaoImpl implements EmpDao { 
 @Autowired 
 private DataSource dataSource; 
 @Autowired 
 private JdbcTemplate jdbcTemplateObject; 
 private SimpleJdbcCall simpleJdbcCall; 
 
 @Override 
 public void create(String name, Integer age, Long salary) { 
 String SQL = "INSERT INTO Employee (name, age, salary) VALUES (?, ?, ?)"; 
 jdbcTemplateObject.update(SQL, new Object[]{name, age, salary} ); 
 System.out.println("Created Record Name = " + name + " Age = " + age+ " Salary = " + salary); 
 } 
 
 /** 
 * @param jdbcTemplateObject the jdbcTemplateObject to set 
 */ 
 public void setJdbcTemplateObject(JdbcTemplate jdbcTemplateObject) { 
 this.jdbcTemplateObject = jdbcTemplateObject; 
 } 
 
 /** 
 * @param dataSource the dataSource to set 
 */ 
 @Autowired 
 public void setDataSource(DataSource dataSource) { 
 this.dataSource = dataSource; 
 this.simpleJdbcCall = new SimpleJdbcCall(this.dataSource).withProcedureName("getEmpRecord"); 
 } 
 
@Override 
public Employee getEmployee(Integer empid) { 
 SqlParameterSource in = new MapSqlParameterSource().addValue("in_empid", empid); 
 Map out = simpleJdbcCall.execute(in); 
 Employee employee = new Employee(); 
 employee.setEmpid(empid); 
 employee.setName((String)out.get("out_name")); 
 employee.setAge((Integer)out.get("out_age")); 
 employee.setSalary(Long.valueOf((String)out.get("out_salary"))); 
 return employee; 
} 
 
 @Override 
 public List listEmployees() { 
 String SQL = "SELECT * FROM Employee"; 
 List employees = (List) jdbcTemplateObject.query(SQL, new EmployeeMapper()); 
 return employees; 
 } 
 
 @Override 
 public void delete(Integer empid) { 
 String SQL = "DELETE FROM Employee WHERE empid = ?"; 
 jdbcTemplateObject.update(SQL, new Object[]{empid}); 
 System.out.println("Deleted Record with EMPID = " + empid ); 
 } 
 
 @Override 
 public void update(Integer empid, Integer age) { 
 String SQL = "UPDATE Employee SET age = ? WHERE empid = ?"; 
 jdbcTemplateObject.update(SQL, new Object[]{age, empid}); 
 System.out.println("Updated Record with EMPID = " + empid ); 
 } 
} 
</pre>
<p><b>EmpMainApp.java</b></p>
<pre class="highlight">package com.dineshonjava.sdnext.main; 
 
import java.util.List; 
 
import org.springframework.context.ApplicationContext; 
import org.springframework.context.support.ClassPathXmlApplicationContext; 
 
import com.dineshonjava.sdnext.dao.EmpDao; 
import com.dineshonjava.sdnext.domain.Employee; 
 
/** 
 * @author Dinesh Rajput 
 * 
 */ 
public class EmpMainApp { 
 
 /** 
 * @param args 
 */ 
 public static void main(String[] args) { 
 ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml"); 
 EmpDao empDao = (EmpDao) context.getBean("employeeDaoImpl"); 
 
 System.out.println("------Records Creation--------" ); 
 empDao.create("Dinesh", 25, 50000l); 
 empDao.create("Anamika", 23, 30000l); 
 empDao.create("Nimmo", 24, 30020l); 
 empDao.create("Adesh", 24, 30011l); 
 empDao.create("Vinesh", 22, 20011l); 
 
 System.out.println("------Listing Multiple Records--------" ); 
 List employees = empDao.listEmployees(); 
 for (Employee employee : employees) { 
 System.out.print(employee); 
 } 
 
 System.out.println("----Updating Record with EMPID = 2 -----" ); 
 empDao.update(2, 20); 
 
 System.out.println("----Listing Record with EMPID = 2 -----" ); 
 Employee employee = empDao.getEmployee(2); 
 System.out.print(employee); 
 } 
} 
</pre>
<p><b>Step-6: </b>Let&#8217;s create spring configuration file Spring.xml.</p>
<pre class="highlight"><;beans xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:><br />
After creating source and spring bean configuration, now run this application, it will print following output on the console:</p>
<div style="background-color: #ff99cc; border-width: thin; border: solid;"><b>Output:</b><br />
Dec 9, 2012 4:10:43 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh<br />
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d: display name [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]; startup date [Sun Dec 09 16:10:43 IST 2012]; root of context hierarchy<br />
Dec 9, 2012 4:10:43 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions<br />
INFO: Loading XML bean definitions from class path resource [spring.xml]<br />
Dec 9, 2012 4:10:43 PM org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory<br />
INFO: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]: org.springframework.beans.factory.support.DefaultListableBeanFactory@1e8a1f6<br />
Dec 9, 2012 4:10:43 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons<br />
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1e8a1f6: defining beans [org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,employeeDaoImpl,dataSource,jdbcTemplateObject]; root of factory hierarchy<br />
<b>----Listing Record with EMPID = 2 -----</b><br />
Dec 9, 2012 4:10:43 PM org.springframework.jdbc.core.JdbcTemplate extractReturnedResults<br />
INFO: Added default SqlReturnUpdateCount parameter named #update-count-1<br />
<b>EMPLOYEE{empid- 2 name- Anamika age- 20 salary- 30000}</b></div>
<div style="text-align: center;"><b><;<;</b><b><a href="https://dineshonjava.com/using-jdbctemplate-in-spring-chapter-33/">Using JdbcTemplate</a></b><b> |<a href="https://dineshonjava.com/spring-tutorial/">index</a>| </b><b><a href="https://dineshonjava.com/dao-support-classes-in-spring-chapter-35/">DAO Support Classes</a></b><b>>;>;</b></div>
</div>
<div class="wp-post-navigation"> 
									 <div class="wp-post-navigation-pre"> 
									 <a href="https://dineshonjava.com/spring-simplejdbcinsert-example/">Previous</a> 
									 </div> 
									 <div class="wp-post-navigation-next"> 
									 <a href="https://dineshonjava.com/transaction-management-in-spring/">Next</a> 
									 </div> 
									</div>
<script type="text/javascript">
jQuery(document).ready(function($) {
 $.post('https://dineshonjava.com/wp-admin/admin-ajax.php', {action: 'mts_view_count', id: '630'});
});
</script>
Strategy Design Patterns We can easily create a strategy design pattern using lambda. To implement…
Decorator Pattern A decorator pattern allows a user to add new functionality to an existing…
Delegating pattern In software engineering, the delegation pattern is an object-oriented design pattern that allows…
Technology has emerged a lot in the last decade, and now we have artificial intelligence;…
Managing a database is becoming increasingly complex now due to the vast amount of data…
Overview In this article, we will explore Spring Scheduler how we could use it by…