Spring JDBC 快速指南

---

---

Spring JDBC - 概述

使用普通的JDBC操作数据库时，编写不必要的代码来处理异常、打开和关闭数据库连接等会变得很麻烦。然而，Spring JDBC框架负责所有底层细节，从打开连接、准备和执行SQL语句、处理异常、处理事务，到最终关闭连接。

您只需要做的就是定义连接参数并指定要执行的SQL语句，并在从数据库提取数据时为每次迭代执行所需的工作。

Spring JDBC 提供了几种方法，以及相应的不同类来与数据库交互。在本教程中，我们将采用经典且最流行的方法，该方法使用框架的JdbcTemplate类。这是管理所有数据库通信和异常处理的中心框架类。

JdbcTemplate 类

JdbcTemplate类执行SQL查询、更新语句和存储过程调用，对ResultSet进行迭代并提取返回的参数值。它还会捕获JDBC异常并将它们转换为在org.springframework.dao包中定义的通用、更具信息量的异常层次结构。

一旦配置，JdbcTemplate类的实例是线程安全的。因此，您可以配置JdbcTemplate的单个实例，然后安全地将此共享引用注入到多个DAO中。

使用JdbcTemplate类时，一种常见的做法是在Spring配置文件中配置一个DataSource，然后将该共享DataSource bean依赖注入到您的DAO类中。JdbcTemplate是在DataSource的setter中创建的。

数据访问对象 (DAO)

DAO代表**数据访问对象**，通常用于数据库交互。DAO的存在是为了提供一种读取和写入数据库数据的方法，它们应该通过一个接口公开此功能，应用程序的其余部分将通过该接口访问它们。

Spring中的数据访问对象(DAO)支持使您可以以一致的方式使用JDBC、Hibernate、JPA或JDO等数据访问技术。

Spring JDBC - 环境设置

本教程中的所有示例都是使用Eclipse IDE编写的。因此，我们建议您应该在您的机器上安装最新版本的Eclipse。

要安装Eclipse IDE，请从www.eclipse.org/downloads下载最新的Eclipse二进制文件。下载安装程序后，将二进制分发版解压缩到方便的位置。例如，在Windows上为C:\eclipse，在Linux/Unix上为/usr/local/eclipse，最后适当地设置PATH变量。

可以通过在Windows机器上执行以下命令启动Eclipse，或者您可以简单地双击eclipse.exe

%C:\eclipse\eclipse.exe 

可以通过在Unix（Solaris、Linux等）机器上执行以下命令启动Eclipse：

$/usr/local/eclipse/eclipse

成功启动后，如果一切正常，则应显示以下结果：

步骤3 - 下载Maven存档

从https://maven.apache.org/download.cgi下载Maven 3.8.4。

步骤4 - 解压Maven存档

将存档解压缩到您希望安装Maven 3.8.4的目录中。将从存档创建子目录apache-maven-3.8.4。

步骤5 - 设置Maven环境变量

将M2_HOME、M2、MAVEN_OPTS添加到环境变量中。

步骤6 - 将Maven bin目录位置添加到系统路径

现在将M2变量附加到系统路径。

步骤7 - 验证Maven安装

现在打开控制台并执行以下**mvn**命令。

最后，验证上述命令的输出，结果应如下所示：

Spring JDBC - 配置数据源

让我们在我们的数据库**TEST**中创建一个数据库表**Student**。我假设您使用的是MySQL数据库，如果您使用的是任何其他数据库，则可以相应地更改您的DDL和SQL查询。

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

现在我们需要向JdbcTemplate提供一个DataSource，以便它可以自行配置以获得数据库访问权限。您可以使用如下所示的代码片段在XML文件中配置DataSource：

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

在下一章中，我们将编写第一个使用已配置数据库的应用程序。

Spring JDBC - 第一个应用程序

概述

为了理解与使用JdbcTemplate类的Spring JDBC框架相关的概念，让我们编写一个简单的示例，该示例将在以下Student表上实现插入和读取操作。

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

让我们继续编写一个简单的基于控制台的Spring JDBC应用程序，它将演示JDBC概念。

创建项目

让我们打开命令控制台，转到C:\MVN目录并执行以下**mvn**命令。

C:\MVN>mvn archetype:generate -DgroupId=com.tutorialspoint 
-DartifactId=Student -DarchetypeArtifactId=maven-archetype-quickstart 
-DinteractiveMode=false

Maven将开始处理并将创建完整的Java应用程序项目结构。

[INFO] Scanning for projects...
[INFO]
[INFO] ------------------< org.apache.maven:standalone-pom >-------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] --------------------------------[ pom ]---------------------------------
[INFO]
[INFO] >>> maven-archetype-plugin:3.2.0:generate (default-cli) > generate-sources @ standalone-pom >>>
[INFO]
[INFO] <<< maven-archetype-plugin:3.2.0:generate (default-cli) < generate-sources @ standalone-pom <<<
[INFO]
[INFO]
[INFO] --- maven-archetype-plugin:3.2.0:generate (default-cli) @ standalone-pom ---
[INFO] Generating project in Batch mode
[INFO] ----------------------------------------------------------------------------
[INFO] Using following parameters for creating project from Old (1.x) Archetype: maven-archetype-quickstart:1.0
[INFO] ----------------------------------------------------------------------------
[INFO] Parameter: basedir, Value: C:\MVN
[INFO] Parameter: package, Value: com.tutorialspoint
[INFO] Parameter: groupId, Value: com.tutorialspoint
[INFO] Parameter: artifactId, Value: Student
[INFO] Parameter: packageName, Value: com.tutorialspoint
[INFO] Parameter: version, Value: 1.0-SNAPSHOT
[INFO] project created from Old (1.x) Archetype in dir: C:\MVN\Student
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  6.842 s
[INFO] Finished at: 2022-01-01T13:49:20+05:30
[INFO] ------------------------------------------------------------------------

现在转到C:/MVN目录。您将看到一个名为student（如artifactId中指定）的已创建Java应用程序项目。更新POM.xml以包含Spring JDBC依赖项。添加Student.java、StudentMapper.java、MainApp.java、StudentDAO.java和StudentJDBCTemplate.java文件。

POM.xml

<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/maven-v4_0_0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>com.tutorialspoint</groupId>
   <artifactId>Student</artifactId>
   <packaging>jar</packaging>
   <version>1.0-SNAPSHOT</version>
   <name>Student</name>
   <url>http://maven.apache.org</url>

   <dependencies>
      <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.11</version>
         <scope>test</scope>
      </dependency>

      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>5.3.14</version>
      </dependency>

      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context</artifactId>
         <version>5.3.14</version>
      </dependency>

      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.27</version>
      </dependency>
   </dependencies>
</project>

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 create
    * a record in the Student table.
   */
   public void create(String name, Integer age);
   
   /** 
    * This is the method to be used to list down
    * all the records from the Student table.
   */
   public List<Student> listStudents();
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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);
   }
   
   // create and persist a new student   
   public void create(String name, Integer age) {
      String SQL = "insert into Student (name, age) values (?, ?)";
      
      jdbcTemplateObject.update( SQL, name, age);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }
   
   // get list of all students
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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");
      
	  // create and persist students
      System.out.println("------Records Creation--------" );
      studentJDBCTemplate.create("Zara", 11);
      studentJDBCTemplate.create("Nuha", 2);
      studentJDBCTemplate.create("Ayan", 15);

      // get list of all students from database
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print each student details	  
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15

Spring JDBC - 创建查询

以下示例将演示如何使用Spring JDBC借助Insert查询创建查询。我们将在Student表中插入一些记录。

语法

String insertQuery = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( insertQuery, name, age);

其中，

• **insertQuery** - 包含占位符的Insert查询。

• **jdbcTemplateObject** - 用于将学生对象插入数据库的StudentJDBCTemplate对象。

示例

为了理解上述与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 create
    * a record in the Student table.
   */
   public void create(String name, Integer age);
   
   /** 
    * This is the method to be used to list down
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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);
   }
   
   // persist a new student
   public void create(String name, Integer age) {
      String insertQuery = "insert into Student (name, age) values (?, ?)";
      jdbcTemplateObject.update( insertQuery, name, age);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }
   
   // get list of all students
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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");
      
      // create and persist students	  
      System.out.println("------Records Creation--------" );
      studentJDBCTemplate.create("Zara", 11);
      studentJDBCTemplate.create("Nuha", 2);
      studentJDBCTemplate.create("Ayan", 15);

      // get all students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15

Spring JDBC - 读取查询

概述

以下示例将演示如何使用Spring JDBC读取查询。我们将读取Student表中可用的记录。

语法

String selectQuery = "select * from Student";
List <Student> students = jdbcTemplateObject.query(selectQuery, new StudentMapper());

其中，

• **selectQuery** - 读取学生的Select查询。

• **jdbcTemplateObject** - 用于从数据库读取学生对象的StudentJDBCTemplate对象。

• **StudentMapper** - StudentMapper是一个RowMapper对象，用于将每个提取的记录映射到学生对象。

示例

为了理解上述与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
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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 all the students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print all the students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15

Spring JDBC - 更新查询

概述

以下示例将演示如何使用Spring JDBC更新查询。我们将更新Student表中可用的记录。

语法

String updateQuery = "update Student set age = ? where id = ?";
jdbcTemplateObject.update(updateQuery, age, id);

其中，

• **updateQuery** - 用于更新带有占位符的学生的Update查询。

• **jdbcTemplateObject** - 用于在数据库中更新学生对象的StudentJDBCTemplate对象。

示例

为了理解上述与 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 update
    * a record into the Student table.
   */
   public void update(Integer id, Integer age);

   /** 
    * 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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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);
   }
   
   // update the student age based on id   
   public void update(Integer id, Integer age){
      String SQL = "update Student set age = ? where id = ?";
      jdbcTemplateObject.update(SQL, age, id);
      System.out.println("Updated Record with ID = " + id );
      return;
   }
   
   // get the student based on id
   public Student getStudent(Integer id) {
      String SQL = "select * from Student where id = ?";
      Student student = jdbcTemplateObject.queryForObject(
         SQL, new Object[]{id}, new StudentMapper()
      );
      return student;
   }
}

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");
      
      // update the student based on id
      System.out.println("----Updating Record with ID = 2 -----" );
      studentJDBCTemplate.update(2, 20);
      
      // get the student based on id and print its details
      System.out.println("----Listing Record with ID = 2 -----" );
      Student student = studentJDBCTemplate.getStudent(2);
      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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

----Updating Record with ID = 2 -----
Updated Record with ID = 2
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 20

Spring JDBC - 删除查询

概述

以下示例将演示如何使用 Spring JDBC 删除查询。我们将删除 Student 表中的一条可用记录。

语法

String deleteQuery = "delete from Student where id = ?";
jdbcTemplateObject.update(deleteQuery, id);

其中，

• deleteQuery − 删除带有占位符的学生的删除查询。

• jdbcTemplateObject − 用于在数据库中删除学生对象的 StudentJDBCTemplate 对象。

示例

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();
   
   /** 
    * This is the method to be used to delete
    * a record from the Student table corresponding
    * to a passed student id.
   */
   public void delete(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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   
   // delete a student based on id
   public void delete(Integer id){
      String SQL = "delete from Student where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }
}

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");
      
      // delete a record based on id
      System.out.println("----Delete Record with ID = 2 -----" );
      studentJDBCTemplate.delete(2);

      // get list of all students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print all students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

----Updating Record with ID = 2 -----
Updated Record with ID = 2
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 20

Spring JDBC - 调用存储过程

概述

以下示例将演示如何使用 Spring JDBC 调用存储过程。我们将通过调用存储过程来读取 Student 表中的一条可用记录。我们将传递一个 ID 并接收学生记录。

语法

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
Map<String, Object> out = jdbcCall.execute(in);

Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));

其中，

• jdbcCall − 表示存储过程的 SimpleJdbcCall 对象。

• in − 用于向存储过程传递参数的 SqlParameterSource 对象。

• student − Student 对象。

• out − 表示存储过程调用结果输出的 Map 对象。

SimpleJdbcCall 类可用于调用带有输入 (IN) 和输出 (OUT) 参数的存储过程。在使用 Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle 和 Sybase 等任何 RDBMS 时，都可以使用这种方法。

为了理解这种方法，请考虑以下 MySQL 存储过程，它接受学生 ID 并使用 OUT 参数返回相应学生的姓名和年龄。让我们使用 MySQL 命令提示符在 TEST 数据库中创建此存储过程：

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 Student where id = in_id;
END $$

DELIMITER ;

示例

为了理解上述与 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.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;

// 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 the student based on id
   public Student getStudent(Integer id) {
      SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");

      SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
      Map<String, Object> out = jdbcCall.execute(in);

      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，其中包含任何按存储过程中指定的名称作为键的输出 (out) 参数。

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 details 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 : 11

Spring JDBC - 调用存储函数

概述

以下示例将演示如何使用 Spring JDBC 调用存储函数。我们将通过调用存储函数来读取 Student 表中的一条可用记录。我们将传递一个 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);

其中，

• in − 用于向存储函数传递参数的 SqlParameterSource 对象。

• jdbcCall − 表示存储函数的 SimpleJdbcCall 对象。

• jdbcTemplateObject − 用于从数据库调用存储函数的 StudentJDBCTemplate 对象。

• student − Student 对象。

SimpleJdbcCall 类可用于调用带有输入 (IN) 参数和返回值的存储函数。在使用 Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle 和 Sybase 等任何 RDBMS 时，都可以使用这种方法。

为了理解这种方法，请考虑以下 MySQL 存储函数，它接受学生 ID 并返回相应学生的姓名。因此，让我们使用 MySQL 命令提示符在您的 TEST 数据库中创建此存储函数：

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 应用程序。

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

// 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 the student based on id
   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) 参数并返回存储函数中指定的字符串。

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 student details based on id and print its details
      Student student = studentJDBCTemplate.getStudent(1);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
   }
}

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

Spring JDBC - 处理BLOB

概述

以下示例将演示如何在 Spring JDBC 的帮助下使用更新查询更新 BLOB。我们将更新 Student 表中的可用记录。

Student 表

CREATE TABLE Student(
   ID INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   IMAGE BLOB,
   PRIMARY KEY (ID)
);

语法

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("image",  new SqlLobValue(new ByteArrayInputStream(imageData), 
   imageData.length, new DefaultLobHandler()), Types.BLOB);

String SQL = "update Student set image = :image where id = :id";
NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
jdbcTemplateObject.update(SQL, in);

其中，

• in − 用于向更新查询传递参数的 SqlParameterSource 对象。

• SqlLobValue − 用于表示 SQL BLOB/CLOB 值参数的对象。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 NamedParameterJdbcTemplate 对象。

示例

为了理解上述与 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 update
    * a record into the Student table.
   */
   public void updateImage(Integer id, byte[] imageData);
}

Student.java

以下是**Student.java**文件的内容。

package com.tutorialspoint;

// Student POJO for Student Table
public class Student {
   private Integer age;
   private String name;
   private Integer id;
   private byte[] image;

   // 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;
   }
   public byte[] getImage() {
      return image;
   }
   public void setImage(byte[] image) {
      this.image = image;
   }
}

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"));
      student.setImage(rs.getBytes("image"));
      return student;
   }
}

StudentJDBCTemplate.java

以下是为已定义的DAO接口StudentDAO编写的实现类文件**StudentJDBCTemplate.java**。

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.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import java.io.ByteArrayInputStream;
import java.sql.Types;

// 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 NamedParameterJdbcTemplate(dataSource);
   }
   
   // update the image based on id
   public void updateImage(Integer id, byte[] imageData) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("image",  new SqlLobValue(new ByteArrayInputStream(imageData), 
         imageData.length, new DefaultLobHandler()), Types.BLOB);

      String SQL = "update Student set image = :image where id = :id";
      
      jdbcTemplateObject.update(SQL, in);
      System.out.println("Updated Record with ID = " + id );
   }
}

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");

      // create an image data      
      byte[] imageData = {0,1,0,8,20,40,95};
      // persist the image
      studentJDBCTemplate.updateImage(1, imageData);  
   }
}

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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Updated Record with ID = 1

您可以通过查询数据库来检查存储的 byte[]。

Spring JDBC - 处理CLOB

概述

以下示例将演示如何在 Spring JDBC 的帮助下使用更新查询更新 CLOB。我们将更新 Student 表中的可用记录。

Student 表

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   DESCRIPTION LONGTEXT,
   PRIMARY KEY (ID)
);

语法

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

String SQL = "update Student set description = :description where id = :id";
NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);

jdbcTemplateObject.update(SQL, in);

其中，

• in − 用于向更新查询传递参数的 SqlParameterSource 对象。

• SqlLobValue − 用于表示 SQL BLOB/CLOB 值参数的对象。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 NamedParameterJdbcTemplate 对象。

示例

为了理解上述与 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 update
    * a record into the Student table.
   */
   public void updateDescription(Integer id, String description);
}

Student.java

以下是**Student.java**文件的内容。

package com.tutorialspoint;

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

   // 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;
   }
   public String getDescription() {
      return description;
   }
   public void setDescription(String description) {
      this.description = description;
   }
}

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"));
      student.setDescription(rs.getString("description"));
      return student;
   }
}

StudentJDBCTemplate.java

以下是为已定义的DAO接口StudentDAO编写的实现类文件**StudentJDBCTemplate.java**。

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.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import java.io.ByteArrayInputStream;
import java.sql.Types;

// 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 NamedParameterJdbcTemplate(dataSource);
   }
   
   // Update the description of student based on id
   public void updateDescription(Integer id, String description) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

      String SQL = "update Student set description = :description where id = :id";
      
      jdbcTemplateObject.update(SQL, in);
      System.out.println("Updated Record with ID = " + id );
   }
}

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");
      
      // Update the description field
      studentJDBCTemplate.updateDescription(1, "This can be a very long text upto 4 GB of size."); 
   }
}

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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Updated Record with ID = 1

您可以通过查询数据库来检查存储的描述。

Spring JDBC - 批处理操作

概述

以下示例将演示如何使用 Spring JDBC 进行批量更新。我们将通过单个批量操作更新 Student 表中的可用记录。

语法

String SQL = "update Student set age = ? where id = ?";
int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL, new BatchPreparedStatementSetter() {
   
   public void setValues(PreparedStatement ps, int i) throws SQLException {
      ps.setInt(1, students.get(i).getAge());						
      ps.setInt(2, students.get(i).getId());	
   }
   public int getBatchSize() {
      return students.size();
   }
}); 

其中，

• SQL − 更新学生年龄的更新查询。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 StudentJDBCTemplate 对象。

• BatchPreparedStatementSetter − 批量执行器，根据学生对象列表和索引 i 为 PerparedStatement 设置值。getBatchSize() 返回批处理的大小。

• updateCounts − 包含每个更新查询的已更新行数的 int 数组。

示例

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();  
   public void batchUpdate(final List<Student> students);
}

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.PreparedStatement;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import java.sql.SQLException;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   // update a batch of multiple students
   public void batchUpdate(final List<Student> students){
      String SQL = "update Student set age = ? where id = ?";
      // run the batch of multiple SQL commands	  
      int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL,
         new BatchPreparedStatementSetter() {
         
         public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setInt(1, students.get(i).getAge());						
            ps.setInt(2, students.get(i).getId());	
         }
         public int getBatchSize() {
            return students.size();
         }
      }); 
      System.out.println("Records updated!");
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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 all the students
      List<Student> initialStudents = studentJDBCTemplate.listStudents();
	  
      System.out.println("Initial Students");
      // print all the students
      for(Student student2: initialStudents){
         System.out.print("ID : " + student2.getId() );
         System.out.println(", Age : " + student2.getAge()); 
      }
	  
      // create a new student to persist	  
      Student student = new Student();
      student.setId(1);
      student.setAge(10);

      // create another student to persist	 
      Student student1 = new Student();
      student1.setId(3);
      student1.setAge(10);

      // create a list of students to persist
      List<Student> students = new ArrayList<Student>();
      students.add(student);
      students.add(student1);

      // persist list of students
      studentJDBCTemplate.batchUpdate(students);

      // get updated list of students
      List<Student> updatedStudents = studentJDBCTemplate.listStudents();
      System.out.println("Updated Students");
      
      // print all the students including new students	  
      for(Student student3: updatedStudents){
         System.out.print("ID : " + student3.getId() );
         System.out.println(", Age : " + student3.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Initial Students
ID : 1, Age : 11
ID : 3, Age : 15
Records updated!
Updated Students
ID : 1, Age : 10
ID : 3, Age : 10

Spring JDBC - 对象批量操作

概述

以下示例将演示如何在 Spring JDBC 中使用对象进行批量更新。我们将通过单个批量操作更新 Student 表中的可用记录。

语法

String SQL = "update Student set age = :age where id = :id";
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(students.toArray());

NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);

int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL,batch);
System.out.println("records updated!"); 

其中，

• SQL − 更新学生年龄的更新查询。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 StudentJDBCTemplate 对象。

• batch − 用于表示对象批处理的 SqlParameterSource 对象。

• updateCounts − 包含每个更新查询的已更新行数的 int 数组。

示例

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();  
   public void batchUpdate(final List<Student> students);
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   // update a batch of multiple students
   public void batchUpdate(final List<Student> students){
      String SQL = "update Student set age = :age where id = :id";
      SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(students.toArray());
      NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
      
      int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL,batch);
      System.out.println("Records updated!");
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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 all the students
      List<Student> initialStudents = studentJDBCTemplate.listStudents();
      System.out.println("Initial Students");

      // print all the students      
      for(Student student2: initialStudents){
         System.out.print("ID : " + student2.getId() );
         System.out.println(", Age : " + student2.getAge()); 
      }

      // create a new student to persist	
      Student student = new Student();
      student.setId(1);
      student.setAge(15);
      
      // create another student to persist	
      Student student1 = new Student();
      student1.setId(3);
      student1.setAge(16);
	  
      // create a list of students to persist
      List<Student> students = new ArrayList<Student>();
      students.add(student);
      students.add(student1);

      // persist list of students
      studentJDBCTemplate.batchUpdate(students);

      // get updated list of students
      List<Student> updatedStudents = studentJDBCTemplate.listStudents();
      System.out.println("Updated Students");
      
      // print all the students including new students	
      for(Student student3: updatedStudents){
         System.out.print("ID : " + student3.getId() );
         System.out.println(", Age : " + student3.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Initial Students
ID : 1, Age : 10
ID : 3, Age : 10
Records updated!
Updated Students
ID : 1, Age : 15
ID : 3, Age : 16

Spring JDBC - 对象批量操作

概述

以下示例将演示如何在 Spring JDBC 中使用对象进行批量更新。我们将通过单个批量操作更新 Student 表中的可用记录。

语法

String SQL = "update Student set age = :age where id = :id";
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(students.toArray());

NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);

int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL,batch);
System.out.println("records updated!"); 

其中，

• SQL − 更新学生年龄的更新查询。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 StudentJDBCTemplate 对象。

• batch − 用于表示对象批处理的 SqlParameterSource 对象。

• updateCounts − 包含每个更新查询的已更新行数的 int 数组。

示例

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();  
   public void batchUpdate(final List<Student> students);
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   // update a batch of multiple students
   public void batchUpdate(final List<Student> students){
      String SQL = "update Student set age = :age where id = :id";
      SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(students.toArray());
      NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
      
      int[] updateCounts = jdbcTemplateObject.batchUpdate(SQL,batch);
      System.out.println("Records updated!");
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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 all the students
      List<Student> initialStudents = studentJDBCTemplate.listStudents();
      System.out.println("Initial Students");

      // print all the students      
      for(Student student2: initialStudents){
         System.out.print("ID : " + student2.getId() );
         System.out.println(", Age : " + student2.getAge()); 
      }

      // create a new student to persist	
      Student student = new Student();
      student.setId(1);
      student.setAge(15);
      
      // create another student to persist	
      Student student1 = new Student();
      student1.setId(3);
      student1.setAge(16);
	  
      // create a list of students to persist
      List<Student> students = new ArrayList<Student>();
      students.add(student);
      students.add(student1);

      // persist list of students
      studentJDBCTemplate.batchUpdate(students);

      // get updated list of students
      List<Student> updatedStudents = studentJDBCTemplate.listStudents();
      System.out.println("Updated Students");
      
      // print all the students including new students	
      for(Student student3: updatedStudents){
         System.out.print("ID : " + student3.getId() );
         System.out.println(", Age : " + student3.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Initial Students
ID : 1, Age : 10
ID : 3, Age : 10
Records updated!
Updated Students
ID : 1, Age : 15
ID : 3, Age : 16

Spring JDBC - 多个批处理操作

概述

以下示例将演示如何在 Spring JDBC 中使用单个调用执行多个批量更新。我们将通过多个批处理操作更新 Student 表中的可用记录，其中批处理大小为 1。

语法

String SQL = "update Student set age = ? where id = ?";
int[][] updateCounts = jdbcTemplateObject.batchUpdate(SQL,students,1,
   new ParameterizedPreparedStatementSetter<Student>() {
   
   public void setValues(PreparedStatement ps, Student student)
      throws SQLException {
      ps.setInt(1, student.getAge());						
      ps.setInt(2, student.getId());	
   }
}); 

其中，

• SQL − 更新学生年龄的更新查询。

• **jdbcTemplateObject** - 用于在数据库中更新学生对象的StudentJDBCTemplate对象。

• ParameterizedPreparedStatementSetter − 批量执行器，根据学生对象列表为 PerparedStatement 设置值。

• updateCounts − 包含每个更新查询的每个批处理的已更新行数的 int[][] 数组。

示例

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();  
   public void batchUpdate(final List<Student> students);
}

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.PreparedStatement;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import java.sql.SQLException;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
   
   // update a batch of multiple students
   public void batchUpdate(final List<Student> students){
      String SQL = "update Student set age = ? where id = ?";
      int[][] updateCounts = jdbcTemplateObject.batchUpdate(
         SQL,students,1,new ParameterizedPreparedStatementSetter<Student>() {
            public void setValues(PreparedStatement ps, Student student)
               throws SQLException {
               ps.setInt(1, student.getAge());						
               ps.setInt(2, student.getId());	
            }
         }
      );
      System.out.println("Records updated!");
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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 all the students
      List<Student> initialStudents = studentJDBCTemplate.listStudents();
      System.out.println("Initial Students");

      // print all the students
      for(Student student2: initialStudents){
         System.out.print("ID : " + student2.getId() );
         System.out.println(", Age : " + student2.getAge()); 
      }
      
      // create a new student to persist
      Student student = new Student();
      student.setId(1);
      student.setAge(17);

      // create another student to persist
      Student student1 = new Student();
      student1.setId(3);
      student1.setAge(18);

      // create a list of students to persist
      List<Student> students = new ArrayList<Student>();
      students.add(student);
      students.add(student1);
	  
      // persist list of students
      studentJDBCTemplate.batchUpdate(students);

      // get updated list of students      
      List<Student> updatedStudents = studentJDBCTemplate.listStudents();
      System.out.println("Updated Students");

      // print all the students including new students
      for(Student student3: updatedStudents){
         System.out.print("ID : " + student3.getId() );
         System.out.println(", Age : " + student3.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Initial Students
ID : 1, Age : 15
ID : 3, Age : 16
records updated!
Updated Students
ID : 1, Age : 17
ID : 3, Age : 18

Spring JDBC - JdbcTemplate 类

概述

org.springframework.jdbc.core.JdbcTemplate 类是 JDBC 核心包中的核心类。它简化了 JDBC 的使用，并有助于避免常见错误。它执行核心 JDBC 工作流程，让应用程序代码提供 SQL 并提取结果。此类执行 SQL 查询或更新，启动对 ResultSet 的迭代，并捕获 JDBC 异常并将它们转换为在 org.springframework.dao 包中定义的通用、更具信息量的异常层次结构。

类声明

以下是 org.springframework.jdbc.core.JdbcTemplate 类的声明：

public class JdbcTemplate
   extends JdbcAccessor
      implements JdbcOperations

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 使用 JdbcTemplate 对象方法进行数据库操作。

示例

以下示例将演示如何使用 JdbcTemplate 类读取查询。我们将读取 Student 表中的可用记录。

语法

String selectQuery = "select * from Student";
List <Student> students = jdbcTemplateObject.query(selectQuery, new StudentMapper());

其中，

• **selectQuery** - 读取学生的Select查询。

• jdbcTemplateObject − 用于从数据库读取学生对象的 StudentJDBCTemplate 对象。

• StudentMapper − StudentMapper 是一个 RowMapper 对象，用于将每个提取的记录映射到学生对象。

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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 all the students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
	  // print all the students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15

Spring JDBC - PreparedStatementSetter 接口

概述

org.springframework.jdbc.core.PreparedStatementSetter 接口充当 JdbcTemplate 类使用的通用回调接口。此接口使用相同的 SQL 为批量中的多个更新中的每一个设置 JdbcTemplate 类提供的 PreparedStatement 上的值。

实现负责设置任何必要的参数。带有占位符的 SQL 已经提供。使用此接口比 PreparedStatementCreator 更容易。JdbcTemplate 将创建 PreparedStatement，回调只负责设置参数值。

接口声明

以下是 org.springframework.jdbc.core.PreparedStatementSetter 接口的声明：

public interface PreparedStatementSetter

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 使用 JdbcTemplate 对象方法进行数据库操作，同时传递 PreparedStatementSetter 对象以替换查询中的占位符。

示例

以下示例将演示如何使用 JdbcTemplate 类和 PreparedStatementSetter 接口读取查询。我们将读取 Student 表中学生的一条可用记录。

语法

final String SQL = "select * from Student where id = ? ";
List <Student> students = jdbcTemplateObject.query(
   SQL, new PreparedStatementSetter() {
   
   public void setValues(PreparedStatement preparedStatement) throws SQLException {
      preparedStatement.setInt(1, id);
   }
},
new StudentMapper());

其中，

• SQL − 读取学生的查询。

• **jdbcTemplateObject** - 用于从数据库读取学生对象的StudentJDBCTemplate对象。

• PreparedStatementSetter − 用于在查询中设置参数的 PreparedStatementSetter 对象。

• **StudentMapper** - StudentMapper是一个RowMapper对象，用于将每个提取的记录映射到学生对象。

为了理解上述与 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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database
   public Student getStudent(final Integer id) {
      final String SQL = "select * from Student where id = ? ";
      List <Student> students = jdbcTemplateObject.query(
         SQL, new PreparedStatementSetter() {
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
               preparedStatement.setInt(1, id);
            }
         },
         new StudentMapper()
      );
      return students.get(0);
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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
      Student student = studentJDBCTemplate.getStudent(1);
      // print the student details
      System.out.print("ID : " + student.getId() );
      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, Age : 17

Spring JDBC - ResultSetExtractor 接口

概述

org.springframework.jdbc.core.ResultSetExtractor 接口是 JdbcTemplate 的查询方法使用的回调接口。此接口的实现执行从 ResultSet 提取结果的实际工作，但无需担心异常处理。

SQLException 将被调用的 JdbcTemplate 捕获和处理。此接口主要用于 JDBC 框架本身。RowMapper 通常是 ResultSet 处理的更简单的选择，每个行映射一个结果对象，而不是为整个 ResultSet 映射一个结果对象。

接口声明

以下是 org.springframework.jdbc.core.ResultSetExtractor 接口的声明：

public interface ResultSetExtractor

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 使用 JdbcTemplate 对象方法进行数据库操作，同时使用 ResultSetExtractor 解析结果集。

示例

以下示例将演示如何使用 JdbcTemplate 类和 ResultSetExtractor 接口读取查询。我们将读取 Student 表中学生的一条可用记录。

语法

public List<Student> listStudents() {
   String SQL = "select * from Student";
   List <Student> students = jdbcTemplateObject.query(
      SQL, new ResultSetExtractor<List<Student>>(){
         public List<Student> extractData(ResultSet rs) throws SQLException, DataAccessException {
            List<Student> list = new ArrayList<Student>();  
            while(rs.next()){  
               Student student = new Student();
               student.setId(rs.getInt("id"));
               student.setName(rs.getString("name"));
               student.setAge(rs.getInt("age"));
               student.setDescription(rs.getString("description"));
               student.setImage(rs.getBytes("image"));
               list.add(student);  
            }
            return list;  
         }
      }
   );
   return students;
}

其中，

• SQL − 读取学生的查询。

• **jdbcTemplateObject** - 用于从数据库读取学生对象的StudentJDBCTemplate对象。

• ResultSetExtractor − 用于解析结果集对象的 ResultSetExtractor 对象。

为了理解上述与 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
    * all the records from the Student table.
   */
   public List<Student> listStudents();
}

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

StudentJDBCTemplate.java

以下是为已定义的DAO接口StudentDAO编写的实现类文件**StudentJDBCTemplate.java**。

package com.tutorialspoint;

import java.util.List;
import java.util.ArrayList;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database   
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, 
         new ResultSetExtractor<List<Student>>(){
         public List<Student> extractData(ResultSet rs) throws SQLException, DataAccessException {
            List<Student> list = new ArrayList<Student>();  
            while(rs.next()){  
               Student student = new Student();
               student.setId(rs.getInt("id"));
               student.setName(rs.getString("name"));
               student.setAge(rs.getInt("age"));
               student.setDescription(rs.getString("description"));
               student.setImage(rs.getBytes("image"));
               list.add(student);  
            }
            return list;  
         }    	  
      });
      return students;
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

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

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 all the students
	  List<Student> students = studentJDBCTemplate.listStudents();
      // print all the students
      for(Student student: students){
         System.out.print("ID : " + student.getId() );
         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, Age : 17
ID : 3, Age : 18

Spring JDBC - RowMapper 接口

概述

org.springframework.jdbc.core.RowMapper<T> 接口由 JdbcTemplate 用于逐行映射 ResultSet 的行。此接口的实现执行将每一行映射到结果对象的实际工作。任何抛出的 SQLException 都将被调用的 JdbcTemplate 捕获和处理。

接口声明

以下是 org.springframework.jdbc.core.RowMapper<T> 接口的声明：

public interface RowMapper<T>

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 创建一个实现 RowMapper 接口的 StudentMapper 对象。

• 步骤 3 − 使用 JdbcTemplate 对象方法进行数据库操作，同时使用 StudentMapper 对象。

示例

以下示例将演示如何使用 Spring JDBC 读取查询。我们将使用 StudentMapper 对象将从 Student 表读取的记录映射到 Student 对象。

语法

String SQL = "select * from Student";
List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());

其中

• SQL − 读取所有学生记录的读取查询。

• jdbcTemplateObject − 用于从数据库读取学生记录的 StudentJDBCTemplate 对象。

• StudentMapper − 用于将学生记录映射到学生对象的 StudentMapper 对象。

为了理解前面提到的关于Spring JDBC的概念，让我们编写一个示例，该示例将读取查询并使用StudentMapper对象映射结果。为了编写我们的示例，让我们准备好一个可用的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
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

// 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 all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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 all the students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print all the students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 17
ID : 3, Name : Ayan, Age : 18

Spring JDBC - NamedParameterJdbcTemplate 类

概述

**org.springframework.jdbc.core.NamedParameterJdbcTemplate** 类是一个模板类，它具有基本的JDBC操作集，允许使用命名参数而不是传统的“?”占位符。此类在执行时将命名参数替换为JDBC样式的“?”占位符后，委托给包装的JdbcTemplate。它还允许将值列表扩展到适当数量的占位符。

接口声明

以下是**org.springframework.jdbc.core.NamedParameterJdbcTemplate**类的声明：

public class NamedParameterJdbcTemplate
   extends Object
      implements NamedParameterJdbcOperations

语法

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

String SQL = "update Student set description = :description where id = :id";
NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
jdbcTemplateObject.update(SQL, in);

其中，

• in − 用于向更新查询传递参数的 SqlParameterSource 对象。

• SqlLobValue − 用于表示 SQL BLOB/CLOB 值参数的对象。

• jdbcTemplateObject − 用于在数据库中更新学生对象的 NamedParameterJdbcTemplate 对象。

示例

为了理解上述与 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 update
    * a record into the Student table.
   */
   public void updateDescription(Integer id, String description);
}

Student.java

以下是**Student.java**文件的内容。

package com.tutorialspoint;

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

   // 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;
   }
   public String getDescription() {
      return description;
   }
   public void setDescription(String description) {
      this.description = description;
   }
}

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"));
      student.setDescription(rs.getString("description"));
      return student;
   }
}

StudentJDBCTemplate.java

以下是为已定义的DAO接口StudentDAO编写的实现类文件**StudentJDBCTemplate.java**。

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.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import java.io.ByteArrayInputStream;
import java.sql.Types;

// 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 NamedParameterJdbcTemplate(dataSource);
   }
   // update description
   public void updateDescription(Integer id, String description) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

      String SQL = "update Student set description = :description where id = :id";
      
      jdbcTemplateObject.update(SQL, in);
      System.out.println("Updated Record with ID = " + id );
   }
}

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");
      // update student description
      studentJDBCTemplate.updateDescription(1, "This can be a very long text upto 4 GB of size."); 
   }
}

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.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

Updated Record with ID = 1

您可以通过查询数据库来检查存储的描述。

Spring JDBC - SimpleJdbcInsert 类

概述

**org.springframework.jdbc.core.SimpleJdbcInsert** 类是一个多线程的、可重用的对象，为表提供了简单的插入功能。它提供元数据处理以简化构建基本插入语句所需的代码。实际的插入操作由Spring的JdbcTemplate处理。

类声明

以下是**org.springframework.jdbc.core.SimpleJdbcInsert**类的声明：

public class SimpleJdbcInsert
   extends AbstractJdbcInsert
      implements SimpleJdbcInsertOperations

下面的示例将演示如何使用Spring JDBC插入查询。我们将使用SimpleJdbcInsert对象在Student表中插入一条记录。

语法

jdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("Student");
Map<String,Object> parameters = new HashMap<String,Object>();

parameters.put("name", name);
parameters.put("age", age);
jdbcInsert.execute(parameters);

其中，

• **jdbcInsert** - 用于在学生表中插入记录的SimpleJdbcInsert对象。

• **jdbcTemplateObject** - 用于读取数据库中学生对象的StudentJDBCTemplate对象。

示例

为了理解上述与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 create
    * a record in the Student table.
   */
   public void create(String name, Integer age);
   
   /** 
    * This is the method to be used to list down
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

// DAO instance to persist Student values
public class StudentJDBCTemplate implements StudentDao {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   SimpleJdbcInsert jdbcInsert;

   // set the datasource and jdbctemplate
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
      this.jdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("Student");
   }
   
   // create and persist a new student
   public void create(String name, Integer age) {
      Map<String,Object> parameters = new HashMap<String,Object>();
      parameters.put("name", name);
      parameters.put("age", age);
      
      jdbcInsert.execute(parameters);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }

   // get all the students from the database
   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
      return students;
   }
}

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");
      
      // Create a new student
      System.out.println("------Records Creation--------" );
      studentJDBCTemplate.create("Nuha", 2);

      // get all the students
      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
    
      // print all students	
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Records Creation--------
Created Record Name = Nuha Age = 12
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 17
ID : 3, Name : Ayan, Age : 18
ID : 4, Name : Nuha, Age : 12

Spring JDBC - SimpleJdbcCall 类

概述

**org.springframework.jdbc.core.SimpleJdbcCall** 类是一个多线程的、可重用的对象，表示对存储过程或存储函数的调用。它提供元数据处理以简化访问基本存储过程/函数所需的代码。

您只需要提供过程/函数的名称以及在执行调用时包含参数的映射。提供的参数名称将与创建存储过程时声明的输入和输出参数匹配。

类声明

以下是**org.springframework.jdbc.core.SimpleJdbcCall**类的声明：

public class SimpleJdbcCall
   extends AbstractJdbcCall
      implements SimpleJdbcCallOperations

下面的示例将演示如何使用Spring SimpleJdbcCall调用存储过程。我们将通过调用存储过程来读取Student表中的一条可用记录。我们将传递一个ID并接收学生记录。

语法

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
Map<String, Object> out = jdbcCall.execute(in);

Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));

其中，

• jdbcCall − 表示存储过程的 SimpleJdbcCall 对象。

• in − 用于向存储过程传递参数的 SqlParameterSource 对象。

• student − Student 对象。

• **out** - 用于表示存储过程调用结果输出的Map对象。

示例

为了理解上述与 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.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;

// 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 the student based on id
   public Student getStudent(Integer id) {
      SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");

      SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
      Map<String, Object> out = jdbcCall.execute(in);

      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，其中包含任何按存储过程中指定的名称作为键的输出 (out) 参数。

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 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 : 11

Spring JDBC - SqlQuery 类

概述

**org.springframework.jdbc.object.SqlQuery** 类提供了一个可重用的操作对象，表示一个SQL查询。

类声明

以下是**org.springframework.jdbc.object.SqlQuery**类的声明：

public abstract class SqlQuery<T>
   extends SqlOperation

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 创建一个实现 RowMapper 接口的 StudentMapper 对象。

• **步骤3** - 使用JdbcTemplate对象方法在使用SqlQuery对象时执行数据库操作。

下面的示例将演示如何使用SqlQuery对象读取查询。我们将使用StudentMapper对象将从Student表读取的记录映射到Student对象。

语法

String sql = "select * from Student";
SqlQuery<Student> sqlQuery = new SqlQuery<Student>() {
   @Override
   protected RowMapper<Student> newRowMapper(Object[] parameters,
      Map<?, ?> context) {
      return new StudentMapper();
   }
};
sqlQuery.setDataSource(dataSource);
sqlQuery.setSql(sql);
List <Student> students = sqlQuery.execute();

其中，

• SQL − 读取所有学生记录的读取查询。

• **jdbcTemplateObject** - 用于从数据库读取学生记录的StudentJDBCTemplate对象。

• **StudentMapper** - 用于将学生记录映射到学生对象的StudentMapper对象。

• **SqlQuery** - 用于查询学生记录并将其映射到学生对象的SqlQuery对象。

示例

为了理解前面提到的关于Spring JDBC的概念，让我们编写一个示例，该示例将读取查询并使用StudentMapper对象映射结果。为了编写我们的示例，让我们准备好一个可用的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
    * all the records from the Student table.
   */
   public List<Student> listStudents();   
}

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.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.object.SqlQuery;

// 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 all the students from the database
   public List<Student> listStudents() {
      String sql = "select * from Student";
      SqlQuery<Student> sqlQuery = new SqlQuery<Student>() {
         @Override
         protected RowMapper<Student> newRowMapper(Object[] parameters, Map<?, ?> context){
            return new StudentMapper();
         }
      };
      sqlQuery.setDataSource(dataSource);
      sqlQuery.setSql(sql);
      List <Student> students = sqlQuery.execute();
      return students;
   }
}

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");
      
      System.out.println("------Listing Multiple Records--------" );
      // get all the students
      List<Student> students = studentJDBCTemplate.listStudents();
      
      // print all the students
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.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.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 17
ID : 3, Name : Ayan, Age : 18
ID : 4, Name : Nuha, Age : 12

Spring JDBC - SqlUpdate 类

概述

**org.springframework.jdbc.object.SqlUpdate** 类提供了一个可重用的操作对象，表示一个SQL更新。

类声明

以下是**org.springframework.jdbc.object.SqlUpdate**类的声明：

public abstract class SqlUpdate<T>
   extends SqlOperation

用法

• 步骤 1 − 使用已配置的数据源创建 JdbcTemplate 对象。

• 步骤 2 − 创建一个实现 RowMapper 接口的 StudentMapper 对象。

• **步骤3** - 使用JdbcTemplate对象方法在使用SqlUpdate对象时执行数据库操作。

下面的示例将演示如何使用SqlUpdate对象更新查询。我们将使用StudentMapper对象将从Student表更新的记录映射到Student对象。

语法

String SQL = "update Student set age = ? where id = ?";

SqlUpdate sqlUpdate = new SqlUpdate(dataSource,SQL);
sqlUpdate.declareParameter(new SqlParameter("age", Types.INTEGER));
sqlUpdate.declareParameter(new SqlParameter("id", Types.INTEGER));
sqlUpdate.compile();

sqlUpdate.update(age.intValue(),id.intValue());

其中，

• **SQL** - 用于更新学生记录的更新查询。

• **jdbcTemplateObject** - 用于从数据库读取学生记录的StudentJDBCTemplate对象。

• StudentMapper − 用于将学生记录映射到学生对象的 StudentMapper 对象。

• **sqlUpdate** - 用于更新学生记录的SqlUpdate对象。

示例

为了理解前面提到的关于Spring JDBC的概念，让我们编写一个示例，该示例将读取查询并使用StudentMapper对象映射结果。为了编写我们的示例，让我们准备好一个可用的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 update
    * a record into the Student table.
   */
   public void update(Integer id, Integer age);

   /** 
    * 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 javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

// 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);
   }
   
   // update a student's age based on id
   public void update(Integer id, Integer age){
      String SQL = "update Student set age = ? where id = ?";
      
      SqlUpdate sqlUpdate = new SqlUpdate(dataSource,SQL);
      sqlUpdate.declareParameter(new SqlParameter("age", Types.INTEGER));
      sqlUpdate.declareParameter(new SqlParameter("id", Types.INTEGER));
      sqlUpdate.compile();
      sqlUpdate.update(age.intValue(),id.intValue());
      System.out.println("Updated Record with ID = " + id );
      return;
   }
   
   // get a student based on id
   public Student getStudent(Integer id) {
      String SQL = "select * from Student where id = ?";
      Student student = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new StudentMapper());
      return student;
   }
}

MainApp.java

以下是**MainApp.java**文件的内容。

package com.tutorialspoint;

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");
      
      // Update a student with id as 1
      System.out.println("----Updating Record with ID = 1 -----" );
      studentJDBCTemplate.update(1, 10);
      
      // get a student of id 1
      System.out.println("----Listing Record with ID = 1 -----" );
      Student student = studentJDBCTemplate.getStudent(1);
      
      // print student details
      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.jdbc.cj.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配置文件后，让我们运行应用程序。如果您的应用程序一切正常，它将打印以下消息。

----Updating Record with ID = 1 -----
Updated Record with ID = 1
----Listing Record with ID = 1 -----
ID : 1, Name : Zara, Age : 10

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，其中包含任何按存储过程中指定的名称作为键的输出 (out) 参数。

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