Spring JdbcTemplate示例

Spring JdbcTemplate is the most important class in Spring JDBC package.

Spring JdbcTemplate是Spring JDBC包中最重要的类。

SpringJdbcTemplate (Spring JdbcTemplate)

• JDBC produces a lot of boiler plate code, such as opening/closing a connection to a database, handling sql exceptions etc. It makes the code extremely cumbersome and difficult to read.
  JDBC产生了许多样板代码，例如打开/关闭与数据库的连接，处理sql异常等。这使代码极其繁琐且难以阅读。
• Implementing JDBC in the Spring Framework takes care of working with many low-level operations (opening/closing connections, executing SQL queries, etc.).
  在Spring Framework中实现JDBC需要处理许多底层操作（打开/关闭连接，执行SQL查询等）。
• Thanks to this, when working with the database in the Spring Framework, we only need to define the connection parameters from the database and register the SQL query, the rest of the work for us is performed by Spring.
  因此，当在Spring Framework中使用数据库时，我们只需要从数据库中定义连接参数并注册SQL查询，剩下的工作就由Spring完成。
• JDBC in Spring has several classes (several approaches) for interacting with the database. The most common of these is using the JdbcTemplate class. This is the base class that manages the processing of all events and database connections.
  Spring中的JDBC具有几种与数据库进行交互的类（几种方法）。 其中最常见的是使用JdbcTemplate类。 这是管理所有事件和数据库连接的处理的基类。
• The JdbcTemplate class executes SQL queries, iterates over the ResultSet, and retrieves the called values, updates the instructions and procedure calls, “catches” the exceptions, and translates them into the exceptions defined in the org.springframwork.dao package.
  JdbcTemplate类执行SQL查询，遍历ResultSet ，并检索调用的值，更新指令和过程调用，“捕获”异常，并将其转换为org.springframwork.dao包中定义的异常。
• Instances of the JdbcTemplate class are thread-safe. This means that by configuring a single instance of the JdbcTemplate class, we can then use it for several DAO objects.
  JdbcTemplate类的实例是线程安全的。 这意味着通过配置JdbcTemplate类的单个实例，我们可以将其用于多个DAO对象。
• When using JdbcTemplate, most often, it is configured in the Spring configuration file. After that, it is implemented using bean in DAO classes.
  使用JdbcTemplate时，通常是在Spring配置文件中进行配置。 之后，它在DAO类中使用bean实现。

Spring JdbcTemplate示例 (Spring JdbcTemplate Example)

Let’s look at Spring JdbcTemplate example program. I am using Postgresql database here, but you can use any other relational database too, such as MySQL and Oracle. All you need is to change the database configurations and it should work.

让我们看一下Spring JdbcTemplate示例程序。 我在这里使用Postgresql数据库，但是您也可以使用任何其他关系数据库，例如MySQL和Oracle。 您所需要做的就是更改数据库配置，它应该可以工作。

First of all we need some sample data to work on. Below SQL queries will create a table and populate it with some data for us to use.

首先，我们需要一些样本数据进行处理。 下面SQL查询将创建一个表，并在其中填充一些数据供我们使用。

create table people (
id serial not null primary key,
first_name varchar(20) not null,
last_name varchar(20) not null,
age integer not null
);

insert into people (id, first_name, last_name, age) values
(1, 'Vlad', 'Boyarskiy', 21),
(2,'Oksi', ' Bahatskaya', 30),
(3,'Vadim', ' Vadimich', 32);

Below image shows the final project structure in Eclipse.

下图显示了Eclipse中的最终项目结构。

Spring JDBC Maven依赖关系 (Spring JDBC Maven Dependencies)

We need following dependencies – spring-core, spring-context, spring-jdbc and postgresql. If you are using any other relational database such as MySQL, then add it’s corresponding java driver dependencies. Here is our final pom.xml file.

我们需要以下依赖项spring-core ， spring-context ， spring-jdbc和postgresql 。 如果您正在使用其他任何关系数据库（例如MySQL），请添加其对应的Java驱动程序依赖项。 这是我们最终的pom.xml文件。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.journaldev.spring</groupId>
	<artifactId>JdbcTemplate</artifactId>
	<version>1.0-SNAPSHOT</version>
	<properties>
		<spring.framework>4.3.0.RELEASE</spring.framework>
		<postgres.version>42.1.4</postgres.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>${postgres.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.framework}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.framework}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.framework}</version>
		</dependency>
	</dependencies>

</project>

Spring DataSource配置 (Spring DataSource Configuration)

Next step is to create spring configuration class to define DataSource bean. I am using java based configuration, you can also do this using spring bean configuration xml file.

下一步是创建spring配置类来定义DataSource bean。 我正在使用基于Java的配置，您也可以使用spring bean配置xml文件来执行此操作。

package com.journaldev.spring.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@ComponentScan("com.journaldev.spring")
@PropertySource("classpath:database.properties")
public class AppConfig {

	@Autowired
	Environment environment;

	private final String URL = "url";
	private final String USER = "dbuser";
	private final String DRIVER = "driver";
	private final String PASSWORD = "dbpassword";

	@Bean
	DataSource dataSource() {
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		driverManagerDataSource.setUrl(environment.getProperty(URL));
		driverManagerDataSource.setUsername(environment.getProperty(USER));
		driverManagerDataSource.setPassword(environment.getProperty(PASSWORD));
		driverManagerDataSource.setDriverClassName(environment.getProperty(DRIVER));
		return driverManagerDataSource;
	}
}

• @Configuration – says that this class is configuration for Spring context.
  @Configuration –表示该类是Spring上下文的配置。
• @ComponentScan(“com.journaldev.spring”)- specifies the package to scan for component classes.
  @ComponentScan（“ com.journaldev.spring”）-指定要扫描组件类的包。
• @PropertySource(“classpath:database.properties”)- says that properties will be read from database.properties file.
  @PropertySource（“ classpath：database.properties”）-表示将从数据库.properties文件读取属性。

Content of database.properties file is shown below.

下面显示了database.properties文件的内容。

driver=org.postgresql.Driver
url=jdbc:postgresql://127.0.0.1:5432/school
dbuser=postgres
dbpassword=postgres

If you are using MySQL or some other relational database, change above configurations accordingly.

如果您使用的是MySQL或其他关系数据库，请相应地更改上述配置。

Spring JDBC模型类 (Spring JDBC Model Classes)

Next step is to create model classes to map our database table.

下一步是创建模型类以映射我们的数据库表。

package com.journaldev.model;

public class Person {
	private Long id;
	private Integer age;
	private String firstName;
	private String lastName;

	public Person() {
	}

	public Person(Long id, Integer age, String firstName, String lastName) {
		this.id = id;
		this.age = age;
		this.firstName = firstName;
		this.lastName = lastName;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	@Override
	public String toString() {
		return "Person{" + "id=" + id + ", age=" + age + ", firstName='" + firstName + '\'' + ", lastName='" + lastName
				+ '\'' + '}';
	}
}

For fetching data from database we need to implement interface RowMapper. This interface has only one method mapRow(ResultSet resultSet, int i), which will return one instance of our model class (i.e. Person).

为了从数据库中获取数据，我们需要实现接口RowMapper 。 此接口只有一个方法mapRow(ResultSet resultSet, int i) ，该方法将返回我们的模型类的一个实例（即Person）。

package com.journaldev.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class PersonMapper implements RowMapper<Person> {

	public Person mapRow(ResultSet resultSet, int i) throws SQLException {

		Person person = new Person();
		person.setId(resultSet.getLong("id"));
		person.setFirstName(resultSet.getString("first_name"));
		person.setLastName(resultSet.getString("last_name"));
		person.setAge(resultSet.getInt("age"));
		return person;
	}
}

Spring JDBC DAO类 (Spring JDBC DAO Classes)

Final step is to create DAO classes to map our model class to database table using sql queries. We will also configure DataSource using @Autowired annotation and expose some APIs.

最后一步是创建DAO类，以使用sql查询将我们的模型类映射到数据库表。 我们还将使用@Autowired批注配置DataSource并公开一些API。

package com.journaldev.spring.dao;

import java.util.List;

import com.journaldev.model.Person;

public interface PersonDAO {
	Person getPersonById(Long id);

	List<Person> getAllPersons();

	boolean deletePerson(Person person);

	boolean updatePerson(Person person);

	boolean createPerson(Person person);
}

package com.journaldev.spring.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.journaldev.model.Person;
import com.journaldev.model.PersonMapper;

@Component
public class PersonDAOImpl implements PersonDAO {

	JdbcTemplate jdbcTemplate;

	private final String SQL_FIND_PERSON = "select * from people where id = ?";
	private final String SQL_DELETE_PERSON = "delete from people where id = ?";
	private final String SQL_UPDATE_PERSON = "update people set first_name = ?, last_name = ?, age  = ? where id = ?";
	private final String SQL_GET_ALL = "select * from people";
	private final String SQL_INSERT_PERSON = "insert into people(id, first_name, last_name, age) values(?,?,?,?)";

	@Autowired
	public PersonDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public Person getPersonById(Long id) {
		return jdbcTemplate.queryForObject(SQL_FIND_PERSON, new Object[] { id }, new PersonMapper());
	}

	public List<Person> getAllPersons() {
		return jdbcTemplate.query(SQL_GET_ALL, new PersonMapper());
	}

	public boolean deletePerson(Person person) {
		return jdbcTemplate.update(SQL_DELETE_PERSON, person.getId()) > 0;
	}

	public boolean updatePerson(Person person) {
		return jdbcTemplate.update(SQL_UPDATE_PERSON, person.getFirstName(), person.getLastName(), person.getAge(),
				person.getId()) > 0;
	}

	public boolean createPerson(Person person) {
		return jdbcTemplate.update(SQL_INSERT_PERSON, person.getId(), person.getFirstName(), person.getLastName(),
				person.getAge()) > 0;
	}
}

PersonDAOImpl class is annotated with @Component annotation and in this class we have field with type JdbcTemplate.

PersonDAOImpl类使用@Component注释进行注释，并且在此类中，我们使用JdbcTemplate类型的字段。

When constructor of this class will be invoked, an instance of DataSource will be injected into it and we can create an instance of JdbcTemplate. After that we can use in in our methods.

当此类的构造函数被调用时，将向其中注入DataSource的实例，我们可以创建JdbcTemplate的实例。 之后，我们可以在我们的方法中使用。

Spring JdbcTemplate测试程序 (Spring JdbcTemplate Test Program)

Our Spring JdbcTemplate example project is ready, let’s test this with a test class.

我们的Spring JdbcTemplate示例项目已经准备就绪，让我们使用测试类对其进行测试。

package com.journaldev;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.journaldev.model.Person;
import com.journaldev.spring.config.AppConfig;
import com.journaldev.spring.dao.PersonDAO;

public class Main {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);

		PersonDAO personDAO = context.getBean(PersonDAO.class);

		System.out.println("List of person is:");

		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		System.out.println("\nGet person with ID 2");

		Person personById = personDAO.getPersonById(2L);
		System.out.println(personById);

		System.out.println("\nCreating person: ");
		Person person = new Person(4L, 36, "Sergey", "Emets");
		System.out.println(person);
		personDAO.createPerson(person);
		System.out.println("\nList of person is:");

		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		System.out.println("\nDeleting person with ID 2");
		personDAO.deletePerson(personById);

		System.out.println("\nUpdate person with ID 4");

		Person pperson = personDAO.getPersonById(4L);
		pperson.setLastName("CHANGED");
		personDAO.updatePerson(pperson);

		System.out.println("\nList of person is:");
		for (Person p : personDAO.getAllPersons()) {
			System.out.println(p);
		}

		context.close();
	}
}

Below image shows the output produced when we execute above program. Output will vary based on sample data and on multiple executions, idea is to learn here how to use Spring JdbcTemplate through example program.

下图显示了当我们执行以上程序时产生的输出。 输出将根据示例数据和多次执行而有所不同，其目的是在此处学习如何通过示例程序使用Spring JdbcTemplate。

That’s all about Spring JdbcTemplate, you can download the final project from below link.

关于Spring JdbcTemplate的全部内容，您可以从下面的链接下载最终项目。

Download Spring JdbcTemplate Example Project 下载Spring JdbcTemplate示例项目

Reference: API Doc

参考： API文档

翻译自: https://www.journaldev.com/17053/spring-jdbctemplate-example