Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Spring JDBC Template for calling Stored Procedures

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 6.34k
    Comment on it

    In Spring we can call the stored Procedures using the SimpleJdbcCall class. we have use it with IN and OUT parameters. Firstly we need to create the procedure in database.

    DELIMITER //
    
    DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
    CREATE PROCEDURE `TEST`.`getRecord` (
    IN in_id INTEGER,
    OUT out_name VARCHAR(20),
    OUT out_age  INTEGER)
    BEGIN
       SELECT name, age
       INTO out_name, out_age
       FROM Employee where id = in_id;
    END //
    
    DELIMITER ;
    

    Then we create the DAO interface and define the following method Employee interface

    package com.sumit;
    
    import java.util.List;
    import javax.sql.DataSource;
    
    public interface EmployeeDAO {
    
       public void setDataSource(DataSource ds);
    
       public void create(String name, Integer age);
    
       public Employee getEmployee(Integer id);
    
       public List<Employee> listEmployees();
    
    }
    

    Create the POJO Object of Employee

    package com.sumit;
    
    public class Employee {
       private Integer age;
       private String name;
       private Integer id;
    
       public void setAge(Integer age) {
          this.age = age;
       }
       public Integer getAge() {
          return age;
       }
    
       public void setName(String name) {
          this.name = name;
       }
       public String getName() {
          return name;
       }
    
       public void setId(Integer id) {
          this.id = id;
       }
       public Integer getId() {
          return id;
       }
    }
    

    Create the Employee Mapper Class

    package com.sumit;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.springframework.jdbc.core.RowMapper;
    
    public class EmployeeMapper implements RowMapper<Employee> {
       public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
          Employee emp = new Employee();
          emp.setId(rs.getInt("id"));
          emp.setName(rs.getString("name"));
          emp.setAge(rs.getInt("age"));
          return emp;
       }
    }
    

    Following is the implementation class file EmployeeJDBCTemplate.java for the defined DAO interface StudentDAO:

    package com.sumit;
    
    import java.util.Map;
    
    import javax.sql.DataSource;
    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;
    
    public class EmployeeJDBCTemplate implements EmployeeDAO {
       private DataSource dataSource;
       private SimpleJdbcCall jdbcCall;
    
       public void setDataSource(DataSource dataSource) {
          this.dataSource = dataSource;
          this.jdbcCall =  new SimpleJdbcCall(dataSource).
                           withProcedureName("getRecord");
       }
    
       public void create(String name, Integer age) {
          JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
          String SQL = "insert into Employee (name, age) values (?, ?)";
    
          jdbcTemplateObject.update( SQL, name, age);
          System.out.println("Created Record Name = " + name + " Age = " + age);
          return;
       }
    
       public Employee getEmployee(Integer id) {
          SqlParameterSource in = new MapSqlParameterSource().
                                  addValue("in_id", id);
          Map<String, Object> out = jdbcCall.execute(in);
    
          Employee emp = new Employee();
          emp.setId(id);
          emp.setName((String) out.get("out_name"));
          emp.setAge((Integer) out.get("out_age"));
    
          return emp;
       }
    
       public List<Employee> listEmployees() {
          String SQL = "select * from Employee";
    
          List <Employee> employees = jdbcTemplateObject.query(SQL, 
                                          new EmployeeMapper());
          return employees;
       }
    
    }
    

    In Above code we have creating an SqlParameterSource contains the IN parameter that is necessary to match the name provided for the input value with the parameter declared in your stored procedure. The execute method takes the IN parameters and return a Map containing any out parameters keyed by the name as specified in your stored procedure

    package com.sumit;
    import java.util.List;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import com.sumit.EmployeeJDBCTemplate;
    
    public class MainApp {
       public static void main(String[] args) {
          ApplicationContext context = 
                 new ClassPathXmlApplicationContext("Beans.xml");
    EmployeeJDBCTemplate employeeJDBCTemplate = 
          (EmployeeJDBCTemplate)context.getBean("employeeJDBCTemplate");
    
          System.out.println("------Records Creation--------" );
          employeeJDBCTemplate.create("Manish", 25);
          employeeJDBCTemplate.create("Sumit", 26);
          employeeJDBCTemplate.create("Aditya", 27);
    
          System.out.println("------Get Multiple Records--------" );
          List<Employee> employees = employeeJDBCTemplate.listEmployees();
          for (Employee record : employees) {
             System.out.print("ID : " + record.getId() );
             System.out.print(", Name : " + record.getName() );
             System.out.println(", Age : " + record.getAge());
          }
    
          System.out.println("----Get Record Employee ID = 101 -----" );
          Employee employee = employeeJDBCTemplate.getEmployee(101);
          System.out.print("ID : " + employee.getId() );
          System.out.print(", Name : " + employee.getName() );
          System.out.println(", Age : " + employee.getAge());
    
       }
    }
    
    
    
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
    
       <!-- Initialization for data source -->
       <bean id="dataSource" 
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
          <property name="username" value="root"/>
          <property name="password" value="password"/>
       </bean>
    
       <!-- Definition for employeeJDBCTemplate bean -->
       <bean id="employeeJDBCTemplate" 
          class="com.sumit.EmployeeJDBCTemplate">
          <property name="dataSource"  ref="dataSource" />    
       </bean>
    
    </beans>
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: