Spring JDBC - StoredProcedure 類

---

---

概述

org.springframework.jdbc.core.StoredProcedure 類是 RDBMS 儲存過程的物件抽象的超類。此類是抽象類，並且旨在子類將提供用於呼叫的型別化方法，該方法委託給提供的 execute(java.lang.Object...) 方法。繼承的 SQL 屬性是 RDBMS 中儲存過程的名稱。

類宣告

以下是 org.springframework.jdbc.core.StoredProcedure 類的宣告：

public abstract class StoredProcedure
   extends SqlCall

以下示例將演示如何使用 Spring StoredProcedure 呼叫儲存過程。我們將透過呼叫儲存過程讀取 Student 表中的一條可用記錄。我們將傳遞一個 id 並接收學生記錄。

語法

class StudentProcedure extends StoredProcedure{
   public StudentProcedure(DataSource dataSource, String procedureName){
      super(dataSource,procedureName);
      declareParameter(new SqlParameter("in_id", Types.INTEGER));
      declareParameter(new SqlOutParameter("out_name", Types.VARCHAR));
      declareParameter(new SqlOutParameter("out_age", Types.INTEGER));
      compile();
   }
   public Student execute(Integer id){
      Map<String, Object> out = super.execute(id);
      Student student = new Student();
      student.setId(id);
      student.setName((String) out.get("out_name"));
      student.setAge((Integer) out.get("out_age"));
      return student;  	
   }
}

其中，

• StoredProcedure - 表示儲存過程的 StoredProcedure 物件。

• StudentProcedure - StudentProcedure 物件擴充套件 StoredProcedure 以宣告輸入、輸出變數並將結果對映到 Student 物件。

• student - Student 物件。

示例

為了理解上面提到的與 Spring JDBC 相關的概念，讓我們編寫一個示例，該示例將呼叫儲存過程。要編寫我們的示例，讓我們準備好一個可用的 Eclipse IDE，並使用以下步驟建立一個 Spring 應用程式。

StudentDAO.java

以下是資料訪問物件介面檔案 StudentDAO.java 的內容。

package com.tutorialspoint;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
   /** 
    * This is the method to be used to initialize
    * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
    * This is the method to be used to list down
    * a record from the Student table corresponding
    * to a passed student id.
   */
   public Student getStudent(Integer id);  
}

Student.java

以下是 Student.java 檔案的內容。

package com.tutorialspoint;

// Student POJO for Student Table
public class Student {
   private Integer age;
   private String name;
   private Integer id;

   // setter/getter methods
   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;
   }
}

StudentMapper.java

以下是 StudentMapper.java 檔案的內容。

package com.tutorialspoint;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

// Row Mapper Object to map Student table entry with Student Object
public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
   }
}

StudentJDBCTemplate.java

以下是為已定義的 DAO 介面 StudentDAO 定義的實現類檔案 StudentJDBCTemplate.java。

package com.tutorialspoint;

import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.object.StoredProcedure;

// DAO instance to persist Student values
public class StudentJDBCTemplate implements StudentDao {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   // set the datasource and jdbctemplate
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   
   // get a student based on id 
   public Student getStudent(Integer id) {    
      StudentProcedure studentProcedure = new StudentProcedure(dataSource, "getRecord");
      return studentProcedure.execute(id);      
   }    
}

class StudentProcedure extends StoredProcedure {
   // initialize stored procedure call
   public StudentProcedure(DataSource dataSource, String procedureName) {
      super(dataSource,procedureName);
      declareParameter(new SqlParameter("in_id", Types.INTEGER));
      declareParameter(new SqlOutParameter("out_name", Types.VARCHAR));
      declareParameter(new SqlOutParameter("out_age", Types.INTEGER));
      compile();
   }
   // call the stored procedure
   public Student execute(Integer id){
      Map<String, Object> out = super.execute(id);
      Student student = new Student();
      student.setId(id);
      student.setName((String) out.get("out_name"));
      student.setAge((Integer) out.get("out_age"));
      return student;  	
   }
}

您為執行呼叫編寫的程式碼涉及建立包含 IN 引數的 SqlParameterSource。務必將為輸入值提供的名稱與儲存過程中宣告的引數名稱相匹配。execute 方法獲取 IN 引數並返回一個 Map，其中包含任何以儲存過程中指定的名稱為鍵的輸出引數。

MainApp.java

以下是 MainApp.java 檔案的內容。

package com.tutorialspoint;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.StudentJDBCTemplate;

public class MainApp {
   public static void main(String[] args) {
      // Create the application context by reading Beans.xml  
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
	  
      // Create the JDBCTemplate instance from spring context
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
	  
      // get the student based on id and print its details
      Student student = studentJDBCTemplate.getStudent(1);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
      System.out.println(", Age : " + student.getAge()); 
   }
}

Beans.xml

以下是配置檔案 Beans.xml。

<?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.cj.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://:3306/TEST"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "admin"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>      
</beans>

輸出

建立原始檔和 bean 配置檔案後，讓我們執行應用程式。如果應用程式一切正常，它將列印以下訊息。

ID : 1, Name : Zara, Age : 10