Springjdbc 简明教程

Spring JDBC - Calling Stored Function

以下示例将演示如何使用 Spring JDBC 调用存储函数。我们将通过调用存储函数读取 Student Table 中的某个可用记录。我们将传递一个 ID 并接收一个学生姓名。

Following example will demonstrate how to call a stored function using Spring JDBC. We’ll read one of the available records in Student Table by calling a stored function. We’ll pass an id and receive a student name.

Syntax

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withFunctionName("get_student_name");

SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
String name = jdbcCall.executeFunction(String.class, in);

Student student = new Student();
student.setId(id);
student.setName(name);

其中，

Where,

  1. in − SqlParameterSource object to pass a parameter to a stored function.

  2. jdbcCall − SimpleJdbcCall object to represent a stored function.

  3. jdbcTemplateObject − StudentJDBCTemplate object to called stored function from database.

  4. student − Student object.

SimpleJdbcCall 类可用于调用带有 IN 参数和返回值的存储函数。在使用像 Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle 和 Sybase 这样的 RDBMS 时，您可以使用此方法。

The SimpleJdbcCall class can be used to call a stored function with IN parameter and a return value. You can use this approach while working with either of the RDBMS such as Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.

要了解此方法，请考虑以下 MySQL 存储过程，它获取学生 ID 并返回相应学生的姓名。因此，让我们使用 MySQL 命令提示符在 TEST 数据库中创建此存储函数 -

To understand the approach, consider the following MySQL stored procedure, which takes student Id and returns the corresponding student’s name. So let us create this stored function in your TEST database using MySQL command prompt −

DELIMITER $$

DROP FUNCTION IF EXISTS `TEST`.`get_student_name` $$
CREATE FUNCTION `get_student_name` (in_id INTEGER)
RETURNS varchar(200)
BEGIN
DECLARE out_name VARCHAR(200);
   SELECT name
   INTO out_name
   FROM Student where id = in_id;

RETURN out_name;

DELIMITER ;

为了解与 Spring JDBC 相关的以上概念，让我们编写一个调用存储函数的示例。为了编写我们的示例，我们准备安装 Eclipse IDE，并使用以下步骤来创建一个 Spring 应用程序。

To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will call a stored function. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.

Step

Description

1

Update the project Student created under chapter Spring JDBC - First Application.

2

Update the bean configuration and run the application as explained below.

以下是对数据访问对象接口文件 StudentDAO.java 的内容。

Following is the content of the Data Access Object interface file 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 文件的内容。

Following is the content of the Student.java file.

package com.tutorialspoint;

public class Student {
   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;
   }
}

以下是对 StudentMapper.java 文件的内容。

Following is the content of the StudentMapper.java file.

package com.tutorialspoint;

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

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;
   }
}

以下是对所定义的 DAO 接口 StudentDAO 实现的类文件 StudentJDBCTemplate.java 。

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

package com.tutorialspoint;

import java.util.List;
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 StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;

   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   public Student getStudent(Integer id) {
      SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withFunctionName("get_student_name");
      SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
      String name = jdbcCall.executeFunction(String.class, in);

      Student student = new Student();
      student.setId(id);
      student.setName(name);
      return student;
   }
}

您为调用执行编写的代码涉及创建一个包含 IN 参数的 SqlParameterSource。重要的是使为输入值提供的名称与存储函数中声明的参数名称匹配。executeFunction 方法获取 IN 参数并根据存储函数的规定返回一个 String。

The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. It’s important to match the name provided for the input value with that of the parameter name declared in the stored function. The executeFunction method takes the IN parameters and returns a String as specified in the stored function.

以下是 MainApp.java 文件的内容

Following is the content of the MainApp.java file

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) {
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");

      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");

      Student student = studentJDBCTemplate.getStudent(1);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
   }
}

以下是配置文件 Beans.xml 。

Following is the configuration file 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://localhost: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 配置文件，让我们运行此应用程序。如果应用程序一切正常，它将打印以下信息。

Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.

ID : 1, Name : Zara