Spring JdbcTemplate 示例

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

春季 JdbcTemplate

• JDBC 产生大量的样板代码，例如打开/关闭与数据库的连接、处理 SQL 异常等。这使得代码非常繁琐且难以阅读。
• 在Spring 框架中实现 JDBC负责处理许多低级操作（打开/关闭连接、执行 SQL 查询等）。
• 由于这一点，在 Spring 框架中使用数据库时，我们只需要定义从数据库的连接参数并注册 SQL 查询，其余的工作都由 Spring 完成。
• Spring 中的 JDBC 有几个类（几种方法）用于与数据库交互。其中最常见的是使用JdbcTemplate类。这是管理所有事件和数据库连接的处理的基类。
• JdbcTemplate 类执行 SQL 查询，迭代ResultSet，并检索调用的值，更新指令和过程调用，“捕获”异常，并将它们转换为包中定义的异常org.springframwork.dao。
• JdbcTemplate 类的实例是线程安全的。这意味着，通过配置 JdbcTemplate 类的单个实例，我们可以将其用于多个DAO对象。
• 使用JdbcTemplate时，最常见的是在Spring配置文件中进行配置，之后在DAO类中使用bean来实现。

Spring JdbcTemplate 示例

让我们看一下 Spring JdbcTemplate 示例程序。我在这里使用 Postgresql 数据库，但您也可以使用任何其他关系数据库，例如 MySQL 和 Oracle。您只需更改数据库配置即可。首先，我们需要一些示例数据来处理。下面的 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);

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

Spring JDBC Maven 依赖项

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

<?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 配置类来定义DataSourcebean。我使用基于 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 – 表示此类是 Spring 上下文的配置。
• @ComponentScan (“com.journaldev.spring”)-指定要扫描组件类的包。
• @PropertySource (“classpath:database.properties”)-表示将从 database.properties 文件中读取属性。

文件内容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.

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

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

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

Spring JdbcTemplate Test Program

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

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. That’s all about Spring JdbcTemplate, you can download the final project from below link.

Download Spring JdbcTemplate Example Project

Reference: API Doc