SpringJDBCTemplate是SpringJDBC包裹。
目录
Spring JDBC模板
- JDBC产生了大量的代码，如打开/关闭与数据库的连接、处理sql异常等，这使得代码非常繁琐且难以阅读。
- 在中实现JDBCSpring框架负责处理许多低级操作（打开/关闭连接、执行SQL查询等）。
- 正因为如此，在Spring框架下使用数据库时，我们只需要从数据库中定义连接参数并注册SQL查询，剩下的工作由Spring完成。
- Spring中的JDBC有几个类（几种方法）用于与数据库交互。其中最常见的是使用
JdbcTemplateclass. This is the base class that manages the processing of all events and database connections. - JdbcTemplate类执行SQL查询，在
ResultSet, and retrieves the called values, updates the instructions and procedure calls, “catches” the exceptions, and translates them into the exceptions defined in theorg.springframwork.daopackage. - JdbcTemplate类的实例是线程安全的。这意味着，通过配置JdbcTemplate类的单个实例，我们可以将其用于多个实例刀物体。
- 在使用JdbcTemplate时，它通常是在Spring配置文件中配置的。然后，在DAO类中使用bean实现它。
Spring JdbcTemplate示例
让我们看看SpringJDBCTemplate示例程序。我在这里使用的是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中的最终项目结构。
maven依赖项
我们需要以下依赖项；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.
<?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数据源配置
下一步是创建spring配置类来定义DataSource bean. I am using java based configuration, you can also do this using spring bean configuration xml file.
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上下文的配置。
- @组件扫描；com.journaldev.spring&8221网站；）-指定要扫描组件类的包。
- @财产来源；类路径：database.properties&8221；）-表示将从数据库.properties文件。
内容database.properties file is shown below.
driver=org.postgresql.Driver
url=jdbc:postgresql://127.0.0.1:5432/school
dbuser=postgres
dbpassword=postgres
如果您使用的是MySQL或其他一些关系数据库，请相应地更改上述配置。
模型类
下一步是创建模型类来映射数据库表。
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
+ """ + "}";
}
}
为了从数据库中获取数据，我们需要实现接口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;
}
}
SpringJDBCDAO类
最后一步是用sql表映射数据库查询。我们还将使用@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.
当调用此类的构造函数时DataSource will be injected into it and we can create an instance of JdbcTemplate. After that we can use in in our methods.
Spring jdbc模板测试程序
我们的template示例项目已经准备好了，让我们用一个测试类来测试它。
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("
Get person with ID 2");
Person personById = personDAO.getPersonById(2L);
System.out.println(personById);
System.out.println("
Creating person: ");
Person person = new Person(4L, 36, "Sergey", "Emets");
System.out.println(person);
personDAO.createPerson(person);
System.out.println("
List of person is:");
for (Person p : personDAO.getAllPersons()) {
System.out.println(p);
}
System.out.println("
Deleting person with ID 2");
personDAO.deletePerson(personById);
System.out.println("
Update person with ID 4");
Person pperson = personDAO.getPersonById(4L);
pperson.setLastName("CHANGED");
personDAO.updatePerson(pperson);
System.out.println("
List of person is:");
for (Person p : personDAO.getAllPersons()) {
System.out.println(p);
}
context.close();
}
}
下图显示了我们执行上述程序时产生的输出。输出将根据示例数据和多次执行而有所不同，我们的想法是通过示例程序学习如何使用template。
这是关于template的，您可以从下面的链接下载最终的项目。
参考文献：API文件
