Spring Boot 简明教程

Spring Boot - Database Handling

Spring Boot 提供了非常好的支持来创建用于数据库的数据源。我们不需要编写任何额外的代码在 Spring Boot 中创建一个数据源。只需添加依赖并进行配置详细信息就足以创建一个数据源并连接数据库。

Spring Boot provides a very good support to create a DataSource for Database. We need not write any extra code to create a DataSource in Spring Boot. Just adding the dependencies and doing the configuration details is enough to create a DataSource and connect the Database.

在本章中，我们将使用 Spring Boot JDBC 驱动程序连接连接数据库。

In this chapter, we are going to use Spring Boot JDBC driver connection to connect the database.

首先，我们需要在构建配置文件中添加 Spring Boot Starter JDBC 依赖。

First, we need to add the Spring Boot Starter JDBC dependency in our build configuration file.

Maven 用户可以在 pom.xml 文件中添加以下依赖项。

Maven users can add the following dependencies in the pom.xml file.

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Gradle 用户可以在 build.gradle 文件中添加以下依赖项。

Gradle users can add the following dependencies in the build.gradle file.

compile('org.springframework.boot:spring-boot-starter-jdbc')

Connect to H2 database

要连接 H2 数据库，我们需要在构建配置文件中添加 H2 数据库依赖项。

To connect the H2 database, we need to add the H2 database dependency in our build configuration file.

对于 Maven 用户，在 pom.xml 文件中添加以下依赖：

For Maven users, add the below dependency in your pom.xml file.

<dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
</dependency>

对于 Gradle 用户，在 build.gradle 文件中添加以下依赖：

For Gradle users, add the below dependency in your build.gradle file.

compile('com.h2database:h2')

我们需要在 classpath src/main/resources 目录下创建 schema.sql 文件和 data.sql 文件，以连接 H2 数据库。

We need to create the schema.sql file and data.sql file under the classpath src/main/resources directory to connect the H2 database.

下面给出了 schema.sql 文件。

The schema.sql file is given below.

CREATE TABLE PRODUCT (ID INT PRIMARY KEY, PRODUCT_NAME VARCHAR(25));

下面给出了 data.sql 文件。

The data.sql file is given below.

INSERT INTO PRODUCT (ID,PRODUCT_NAME) VALUES (1,'Honey');
INSERT INTO PRODUCT (ID,PRODUCT_NAME) VALUES (2,'Almond');

Connect MySQL

为了连接 MySQL 数据库，我们需要在构建配置文件中添加 MySQL 依赖。

To connect the MySQL database, we need to add the MySQL dependency into our build configuration file.

对于 Maven 用户，在 pom.xml 文件中添加以下依赖：

For Maven users, add the following dependency in your pom.xml file.

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>

对于 Gradle 用户，在 build.gradle 文件中添加以下依赖：

For Gradle users, add the following dependency in your build.gradle file.

compile('mysql:mysql-connector-java')

现在，在 MySQL 中创建数据库和表，如下所示：

Now, create database and tables in MySQL as shown −

对于属性文件用户，在 application.properties 文件中添加以下属性。

For properties file users, add the following properties in the application.properties file.

spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/PRODUCTSERVICE?autoreconnect = true
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.testOnBorrow = true
spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.minEvictableIdleTimeMillis = 30000
spring.datasource.validationQuery = SELECT 1
spring.datasource.max-active = 15
spring.datasource.max-idle = 10
spring.datasource.max-wait = 8000

对于 YAML 用户，在 application.yml 文件中添加以下属性：

For YAML users, add the following properties in the application.yml file.

spring:
   datasource:
      driverClassName: com.mysql.jdbc.Driver
      url: "jdbc:mysql://localhost:3306/PRODUCTSERVICE?autoreconnect=true"
      username: "root"
      password: "root"
      testOnBorrow: true
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: SELECT 1
      max-active: 15
      max-idle: 10
      max-wait: 8000

Connect Redis

Redis 是一个用于存储内存内数据结构的开源数据库。为了在 Spring Boot 应用程序中连接 Redis 数据库，我们需要在构建配置文件中添加 Redis 依赖。

Redis is an open source database used to store the in-memory data structure. To connect the Redis database in Spring Boot application, we need to add the Redis dependency in our build configuration file.

Maven 用户应在 pom.xml 文件中添加以下依赖：

Maven users should add the following dependency in your pom.xml file.

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-redis</artifactId>
</dependency>

Gradle 用户应在 build.gradle 文件中添加以下依赖：

Gradle users should add the following dependency in your build.gradle file.

compile('org.springframework.boot:spring-boot-starter-data-redis')

对于 Redis 连接，我们需要使用 RedisTemplate。对于 RedisTemplate，我们需要提供 JedisConnectionFactory 详细信息。

For Redis connection, we need to use RedisTemplate. For RedisTemplate we need to provide the JedisConnectionFactory details.

@Bean
JedisConnectionFactory jedisConnectionFactory() {
   JedisConnectionFactory jedisConFactory = new JedisConnectionFactory();
   jedisConFactory.setHostName("localhost");
   jedisConFactory.setPort(6000);
   jedisConFactory.setUsePool(true);
   return jedisConFactory;
}
@Bean
public RedisTemplate<String, Object> redisTemplate() {
   RedisTemplate<String, Object> template = new RedisTemplate<>();
   template.setConnectionFactory(jedisConnectionFactory());
   template.setKeySerializer(new StringRedisSerializer());
   template.setHashKeySerializer(new StringRedisSerializer());
   template.setHashValueSerializer(new StringRedisSerializer());
   template.setValueSerializer(new StringRedisSerializer());
   return template;
}

现在自动连接 RedisTemplate 类并从 Redis 数据库访问数据。

Now auto wire the RedisTemplate class and access the data from Redis database.

@Autowired

RedisTemplate<String, Object> redis;
Map<Object,Object> datalist = redis.opsForHash().entries(“Redis_code_index_key”);

JDBCTemplate

为了使用 JdbcTemplate 在 Spring Boot 应用程序中访问关系数据库，我们需要在构建配置文件中添加 Spring Boot Starter JDBC 依赖。

To access the Relational Database by using JdbcTemplate in Spring Boot application, we need to add the Spring Boot Starter JDBC dependency in our build configuration file.

然后，如果你 @Autowired 了 JdbcTemplate 类，Spring Boot 会自动连接数据库，并为 JdbcTemplate 对象设置数据源。

Then, if you @Autowired the JdbcTemplate class, Spring Boot automatically connects the Database and sets the Datasource for the JdbcTemplate object.

@Autowired
JdbcTemplate jdbcTemplate;
Collection<Map<String, Object>> rows = jdbc.queryForList("SELECT QUERY");

应该向 class 文件添加 @Repository 注解。@Repository 注解用于为 Spring Boot 应用程序创建数据库存储库。

The @Repository annotation should be added into the class file. The @Repository annotation is used to create database repository for your Spring Boot application.

@Repository
public class ProductServiceDAO {
}

Multiple DataSource

我们可以在单个 Spring Boot 应用程序中保留“n”个数据源。这里给出的示例显示了如何在 Spring Boot 应用程序中创建多个数据源。现在，在应用程序属性文件中添加两个数据源配置详细信息。

We can keep ‘n’ number Datasources in a single Spring Boot application. The example given here shows how to create more than 1 data source in Spring Boot application. Now, add the two data source configuration details in the application properties file.

对于属性文件用户，添加以下属性到您的 application.properties 文件中。

For properties file users, add the following properties into your application.properties file.

spring.dbProductService.driverClassName = com.mysql.jdbc.Driver
spring.dbProductService.url = jdbc:mysql://localhost:3306/PRODUCTSERVICE?autoreconnect = true
spring.dbProductService.username = root
spring.dbProductService.password = root
spring.dbProductService.testOnBorrow = true
spring.dbProductService.testWhileIdle = true
spring.dbProductService.timeBetweenEvictionRunsMillis = 60000
spring.dbProductService.minEvictableIdleTimeMillis = 30000
spring.dbProductService.validationQuery = SELECT 1
spring.dbProductService.max-active = 15
spring.dbProductService.max-idle = 10
spring.dbProductService.max-wait = 8000

spring.dbUserService.driverClassName = com.mysql.jdbc.Driver
spring.dbUserService.url = jdbc:mysql://localhost:3306/USERSERVICE?autoreconnect = true
spring.dbUserService.username = root
spring.dbUserService.password = root
spring.dbUserService.testOnBorrow = true
spring.dbUserService.testWhileIdle = true
spring.dbUserService.timeBetweenEvictionRunsMillis = 60000
spring.dbUserService.minEvictableIdleTimeMillis = 30000
spring.dbUserService.validationQuery = SELECT 1
spring.dbUserService.max-active = 15
spring.dbUserService.max-idle = 10
spring.dbUserService.max-wait = 8000

Yaml 用户应添加以下属性到您的 application.yml 文件中。

Yaml users should add the following properties in your application.yml file.

spring:
   dbProductService:
      driverClassName: com.mysql.jdbc.Driver
      url: "jdbc:mysql://localhost:3306/PRODUCTSERVICE?autoreconnect=true"
      password: "root"
      username: "root"
      testOnBorrow: true
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: SELECT 1
      max-active: 15
      max-idle: 10
      max-wait: 8000
   dbUserService:
      driverClassName: com.mysql.jdbc.Driver
      url: "jdbc:mysql://localhost:3306/USERSERVICE?autoreconnect=true"
      password: "root"
      username: "root"
      testOnBorrow: true
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: SELECT 1
      max-active: 15
      max-idle: 10
      max-wait: 8000

现在，创建一个 Configuration 类来创建针对多个数据源的 DataSource 和 JdbcTemplate。

Now, create a Configuration class to create a DataSource and JdbcTemplate for multiple data sources.

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class DatabaseConfig {
   @Bean(name = "dbProductService")
   @ConfigurationProperties(prefix = "spring.dbProductService")
   @Primary
   public DataSource createProductServiceDataSource() {
      return DataSourceBuilder.create().build();
   }
   @Bean(name = "dbUserService")
   @ConfigurationProperties(prefix = "spring.dbUserService")
   public DataSource createUserServiceDataSource() {
      return DataSourceBuilder.create().build();
   }
   @Bean(name = "jdbcProductService")
   @Autowired
   public JdbcTemplate createJdbcTemplate_ProductService(@Qualifier("dbProductService") DataSource productServiceDS) {
      return new JdbcTemplate(productServiceDS);
   }
   @Bean(name = "jdbcUserService")
   @Autowired
   public JdbcTemplate createJdbcTemplate_UserService(@Qualifier("dbUserService") DataSource userServiceDS) {
      return new JdbcTemplate(userServiceDS);
   }
}

然后，通过使用 @Qualifier 注解自动植入 JDBCTemplate 对象。

Then, auto wire the JDBCTemplate object by using @Qualifier annotation.

@Qualifier("jdbcProductService")
@Autowired
JdbcTemplate jdbcTemplate;

@Qualifier("jdbcUserService")
@Autowired
JdbcTemplate jdbcTemplate;