Spring JDBC 简介 - 《Java Tutorial Network 中文系列教程》

原文： https://javatutorial.net/introduction-to-jdbc-in-spring

在本教程中，您将学习什么是 JDBC 模块，并希望在阅读完之后可以找到用例。

Spring JDBC 简介 - 图1

现在，让我们创建一个代表员工的非常简单的表。

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

Spring JDBC Frameworks 处理底层细节，例如初始化连接，执行 SQL 查询，关闭连接等，因此为我们节省了很多时间和精力。在这种情况下，您可能想知道为我们保留的内容去做。好了，我们必须定义连接参数，并指定要执行的 SQL 查询，最后，我们必须在从数据库中获取数据的同时编写所有迭代的逻辑。

有许多方法和已经编写好的类可以帮助我们实现 JDBC。在我们的例子中，我们将坚持经典的 JDBC 模板类。它将管理所有数据库通信。

您需要了解有关 JDBC 模板类的哪些知识？简而言之，它可以捕获 JDBC 异常，执行 SQL 查询和更新语句。还需要注意的是，JDBC 模板类的所有实例都是线程安全的，这意味着我们可以配置 JDBC 模板类的实例，并将其作为共享引用安全地注入到多个 DAO 中。

如果您不熟悉什么是 DAO，则可以查看我关于该主题的文章。

现在到有趣的部分。让我们来看看如何实现所有理论。我们将使用该教程开头显示的表格。

EmployeeDAO.java

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

这是我们的 DAO 界面。它包含所有方法声明，并且所有这些方法声明都与 CRUD 功能有关。

setDataSource()：建立数据库连接。
create()：将用于在数据库中创建新的Employee条目。
getEmployee()：将根据提供的 ID 返回一名员工。
getEmployees()：将返回数据库中所有雇员的列表。
delete()：将根据提供的 ID 删除员工。
update()：将更新现有员工。

Employee.java

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

EmployeeMapper.java

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

EmployeeJDBCTemplate.java

package com.javatutorial;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeJDBCTemplate implements EmployeeDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   public void create(String name, Integer age, String department) {
      String SQL = "insert into Employee (name, age, department) values (?, ?)";
      jdbcTemplateObject.update( SQL, name, age, department);
   }
   public Employee getEmployee(Integer id) {
      String SQL = "select * from Employee where id = ?";
      Employee employee = jdbcTemplateObject.queryForObject(SQL, 
         new Object[]{id}, new EmployeeMapper());
      return employee;
   }
   public List<Employee> getEmployees() {
      String SQL = "select * from Employee";
      List <Employee> employees = jdbcTemplateObject.query(SQL, new EmployeeMapper());
      return employees;
   }
   public void delete(Integer id) {
      String SQL = "delete from Employee where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
   }
   public void update(Integer id, Integer age){
      String SQL = "update Employee set age = ? where id = ?";
      jdbcTemplateObject.update(SQL, age, id);
   }
}

JDBC 类定义了我们在上面定义的接口中的所有方法声明。

Main.java

package com.javatutorial;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.EmployeeJDBCTemplate;
public class Main {
   public static void main(String[] args) {
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
      EmployeeJDBCTemplate employeeJDBCTemplate = (EmployeeJDBCTemplate)context.getBean("employeeJDBCTemplate");
      System.out.println("Creating records..." );
      employeeJDBCTemplate.create("Jack", 11, "Software engineering");
      employeeJDBCTemplate.create("Joanna", 2, "Finance");
      employeeJDBCTemplate.create("Derek", 15, "Hardware engineering");
      System.out.println("Listing employee entries from the database..");
      List<Employee> employees = employeeJDBCTemplate.getEmployees();
      for (Employee employee : employees) {
         System.out.print("ID: " + employee.getId());
         System.out.print("Name: " + employee.getName());
         System.out.println("Age: " + employee.getAge());
         System.out.println("Age: " + employee.getDepartment());
      }
      System.out.println("Updating a record with an id of 1");
      employeeJDBCTemplate.update(1, 29, "Marketing");
      System.out.println("Displaying information about record with an id of 1");
      Employee employee = employeeJDBCTemplate.getEmployee(1);
      System.out.print("ID: " + employee.getId());
      System.out.print("Name : " + employee.getName() );
      System.out.println("Age : " + employee.getAge());
      ystem.out.println("Department : " + employee.getDepartment());
   }
}

在这里，我们将调用在接口中定义的所有方法。

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 ">
   <bean id="dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/DEMO"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "admin123"/>
   </bean>
   <bean id = "employeeJDBCTemplate" 
      class = "com.javatutorial.EmployeeJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>
</beans>