SpringBoot2.x系列教程40--使用SQL数据库
作者：一一哥
从本章节开始，我将带领大家学习Spring Boot中如何整合SQL数据库的相关知识点。
一. Spring对SQL的支持
1. SQL支持
Spring框架为使用SQL数据库提供了广泛支持，我们可以使用JdbcTemplate直接访问JDBC，可以利用完全的‘对象关系映射’框架，比如Hibernate，Mybatis，JPA等。而Spring Data则提供了更的功能，直接从接口创建Repository实现，并根据约定从方法名生成查询。
2. DataSource简介
其中Java的javax.sql.DataSource接口提供了一个标准的使用数据库连接的方法。通常，DataSource使用URL和相应的凭证去初始化数据库连接。
DataSource的作用
- 通过DataSource可以获取数据库连接Connection对象；
- 通过DataSource创建JdbcTemplate对象来操作数据库。
3. 数据库连接池
在实际项目中，我们配置数据源的时候一般都是通过数据库连接池来实现。比较流行的数据库连接池有Hikari(Spring默认的数据库连接池)、Druid、C3p0、Dbcp2等。
数据库连接池的好处在于，可以使得应用程序在操作数据库的时候，直接从数据库连接池获取连接对象，而不需要每次创建新的连接对象。因为应用程序每次创建和销毁连接对象的代价是很大的，使用数据库连接池可以很好的复用连接对象，节省开销，方便管理，简化开发。
二. Spring Boot中支持的数据库类型
1. 对内存数据库的支持
我们在开发应用的时候，可以使用内存数据库来进行开发环境级别的代码编写，这是比较方便的。
当然内存数据库不提供持久化存储，它会在应用启动时填充数据库，在应用结束前预先清除数据。
Spring Boot可以自动配置的内存数据库包括H2, HSQL和Derby。内存数据库的使用，不需要我们提供任何连接URL，只需要添加我们想使用的内存数据库依赖包就可以了，使用起来很方便。
内存数据库的使用
如果我们想使用内存数据库，首先要添加如下依赖：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<scope>runtime</scope>
</dependency>其中spring-boot-starter-data-jpa已包含了spring-jdbc依赖；
hsqldb是内存数据库的依赖包。
注意：
在使用内存数据库的时候，如果我们想明确的配置内嵌数据库的连接URL，一定要确保数据库的自动关闭功能是被禁用的。
因为禁用数据库的自动关闭功能可以让Spring Boot自动控制何时关闭数据库，因此在数据库不需要时可以确保关闭只发生一次。
- 如果使用的是H2数据库，需要设置DB_CLOSE_ON_EXIT=FALSE；
- 如果使用的是HSQLDB数据库，需要确保shutdown=false。
2. 对生产环境数据库的支持
2.1 相关依赖包
如果我们想使用生产环境数据库，首先要添加如下依赖：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>或者
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
可以看到jpa的依赖中包含了jdbc的依赖。
注意：
在以上两个依赖包中，都会默认包含tomcat-jdbc依赖，所以默认的spring.datasource.type=tomcat-jdbc。
2.2 数据库连接池类型
生产环境的数据库连接对象可以通过池化的DataSource进行自动配置，以下是几个常用的数据库连接池：
- Tomcat数据库连接池，各种性能尤其是并发性能都较，推荐使用；
- HikariCP数据库连接池，Spring中目前默认的连接池；
- Druid数据库连接池，alibaba出品，国内较常用；
- Commons DBCP2数据库连接池，较常用；
- Commons DBCP数据库连接池，生产环境不推荐。
2.3 spring.datasource.*属性
我们在使用数据源的时候，对DataSource一般都需要配置如下属性：
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver- 其中spring.datasource.url属性一般都是必须要设置的，如果不指定，Spring Boot会尝试自动去连接配置内存数据库；
- 我们可以不去指定driver-class-name，因为Spring boot可以从url中推断出所使用的的大部分数据库。
其他可选配置属性：
#例如制定数据源类型
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.tomcat.max-wait=10000
# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=50
# Validate the connection before borrowing it from the pool.
spring.datasource.tomcat.test-on-borrow=truespring.datasource.type可用类型：
2.4 DataSourceProperties可选配置
我们在开发的时候，还有一些其他可选配置，可以通过DataSourceProperties来进行配置。
有些标准配置是跟实现无关的，对于实现相关的配置可以通过相应前缀进行设置:
spring.datasource.tomcat.*
spring.datasource.hikari.*
spring.datasource.dbcp.*
spring.datasource.dbcp2.*DataSourceProperties源码：
/*
* Copyright 2012-2016 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.boot.autoconfigure.jdbc;
import java.nio.charset.Charset;
import java.util.LinkedHashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.BeanClassLoaderAware;
import org.springframework.beans.factory.BeanCreationException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.context.EnvironmentAware;
import org.springframework.core.env.Environment;
import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
/**
* Base class for configuration of a data source.
*
* @author Dave Syer
* @author Maciej Walkowiak
* @author Stephane Nicoll
* @author Benedikt Ritter
* @author Eddú Meléndez
* @since 1.1.0
*/
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties
implements BeanClassLoaderAware, EnvironmentAware, InitializingBean {
private ClassLoader classLoader;
private Environment environment;
/**
* Name of the datasource.
*/
private String name = "testdb";
/**
* Fully qualified name of the connection pool implementation to use. By default, it
* is auto-detected from the classpath.
*/
private Class<? extends DataSource> type;
/**
* Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
*/
private String driverClassName;
/**
* JDBC url of the database.
*/
private String url;
/**
* Login user of the database.
*/
private String username;
/**
* Login password of the database.
*/
private String password;
/**
* JNDI location of the datasource. Class, url, username & password are ignored when
* set.
*/
private String jndiName;
/**
* Populate the database using 'data.sql'.
*/
private boolean initialize = true;
/**
* Platform to use in the schema resource (schema-${platform}.sql).
*/
private String platform = "all";
/**
* Schema (DDL) script resource reference.
*/
private String schema;
/**
* User of the database to execute DDL scripts (if different).
*/
private String schemaUsername;
/**
* Password of the database to execute DDL scripts (if different).
*/
private String schemaPassword;
/**
* Data (DML) script resource reference.
*/
private String data;
/**
* User of the database to execute DML scripts.
*/
private String dataUsername;
/**
* Password of the database to execute DML scripts.
*/
private String dataPassword;
/**
* Do not stop if an error occurs while initializing the database.
*/
private boolean continueOnError = false;
/**
* Statement separator in SQL initialization scripts.
*/
private String separator = ";";
/**
* SQL scripts encoding.
*/
private Charset sqlScriptEncoding;
private EmbeddedDatabaseConnection embeddedDatabaseConnection = EmbeddedDatabaseConnection.NONE;
private Xa xa = new Xa();
@Override
public void setBeanClassLoader(ClassLoader classLoader) {
this.classLoader = classLoader;
}
@Override
public void setEnvironment(Environment environment) {
this.environment = environment;
}
@Override
public void afterPropertiesSet() throws Exception {
this.embeddedDatabaseConnection = EmbeddedDatabaseConnection
.get(this.classLoader);
}
/**
* Initialize a {@link DataSourceBuilder} with the state of this instance.
* @return a {@link DataSourceBuilder} initialized with the customizations defined on
* this instance
*/
public DataSourceBuilder initializeDataSourceBuilder() {
return DataSourceBuilder.create(getClassLoader()).type(getType())
.driverClassName(determineDriverClassName()).url(determineUrl())
.username(determineUsername()).password(determinePassword());
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Class<? extends DataSource> getType() {
return this.type;
}
public void setType(Class<? extends DataSource> type) {
this.type = type;
}
/**
* Return the configured driver or {@code null} if none was configured.
* @return the configured driver
* @see #determineDriverClassName()
*/
public String getDriverClassName() {
return this.driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
/**
* Determine the driver to use based on this configuration and the environment.
* @return the driver to use
* @since 1.4.0
*/
public String determineDriverClassName() {
if (StringUtils.hasText(this.driverClassName)) {
Assert.state(driverClassIsLoadable(),
"Cannot load driver class: " + this.driverClassName);
return this.driverClassName;
}
String driverClassName = null;
if (StringUtils.hasText(this.url)) {
driverClassName = DatabaseDriver.fromJdbcUrl(this.url).getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
driverClassName = this.embeddedDatabaseConnection.getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
throw new DataSourceBeanCreationException(this.embeddedDatabaseConnection,
this.environment, "driver class");
}
return driverClassName;
}
private boolean driverClassIsLoadable() {
try {
ClassUtils.forName(this.driverClassName, null);
return true;
}
catch (UnsupportedClassVersionError ex) {
// Driver library has been compiled with a later JDK, propagate error
throw ex;
}
catch (Throwable ex) {
return false;
}
}
/**
* Return the configured url or {@code null} if none was configured.
* @return the configured url
* @see #determineUrl()
*/
public String getUrl() {
return this.url;
}
public void setUrl(String url) {
this.url = url;
}
/**
* Determine the url to use based on this configuration and the environment.
* @return the url to use
* @since 1.4.0
*/
public String determineUrl() {
if (StringUtils.hasText(this.url)) {
return this.url;
}
String url = this.embeddedDatabaseConnection.getUrl(this.name);
if (!StringUtils.hasText(url)) {
throw new DataSourceBeanCreationException(this.embeddedDatabaseConnection,
this.environment, "url");
}
return url;
}
/**
* Return the configured username or {@code null} if none was configured.
* @return the configured username
* @see #determineUsername()
*/
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
/**
* Determine the username to use based on this configuration and the environment.
* @return the username to use
* @since 1.4.0
*/
public String determineUsername() {
if (StringUtils.hasText(this.username)) {
return this.username;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "sa";
}
return null;
}
/**
* Return the configured password or {@code null} if none was configured.
* @return the configured password
* @see #determinePassword()
*/
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
/**
* Determine the password to use based on this configuration and the environment.
* @return the password to use
* @since 1.4.0
*/
public String determinePassword() {
if (StringUtils.hasText(this.password)) {
return this.password;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "";
}
return null;
}
public String getJndiName() {
return this.jndiName;
}
/**
* Allows the DataSource to be managed by the container and obtained via JNDI. The
* {@code URL}, {@code driverClassName}, {@code username} and {@code password} fields
* will be ignored when using JNDI lookups.
* @param jndiName the JNDI name
*/
public void setJndiName(String jndiName) {
this.jndiName = jndiName;
}
public boolean isInitialize() {
return this.initialize;
}
public void setInitialize(boolean initialize) {
this.initialize = initialize;
}
public String getPlatform() {
return this.platform;
}
public void setPlatform(String platform) {
this.platform = platform;
}
public String getSchema() {
return this.schema;
}
public void setSchema(String schema) {
this.schema = schema;
}
public String getSchemaUsername() {
return this.schemaUsername;
}
public void setSchemaUsername(String schemaUsername) {
this.schemaUsername = schemaUsername;
}
public String getSchemaPassword() {
return this.schemaPassword;
}
public void setSchemaPassword(String schemaPassword) {
this.schemaPassword = schemaPassword;
}
public String getData() {
return this.data;
}
public void setData(String script) {
this.data = script;
}
public String getDataUsername() {
return this.dataUsername;
}
public void setDataUsername(String dataUsername) {
this.dataUsername = dataUsername;
}
public String getDataPassword() {
return this.dataPassword;
}
public void setDataPassword(String dataPassword) {
this.dataPassword = dataPassword;
}
public boolean isContinueOnError() {
return this.continueOnError;
}
public void setContinueOnError(boolean continueOnError) {
this.continueOnError = continueOnError;
}
public String getSeparator() {
return this.separator;
}
public void setSeparator(String separator) {
this.separator = separator;
}
public Charset getSqlScriptEncoding() {
return this.sqlScriptEncoding;
}
public void setSqlScriptEncoding(Charset sqlScriptEncoding) {
this.sqlScriptEncoding = sqlScriptEncoding;
}
public ClassLoader getClassLoader() {
return this.classLoader;
}
public Xa getXa() {
return this.xa;
}
public void setXa(Xa xa) {
this.xa = xa;
}
/**
* XA Specific datasource settings.
*/
public static class Xa {
/**
* XA datasource fully qualified name.
*/
private String dataSourceClassName;
/**
* Properties to pass to the XA data source.
*/
private Map<String, String> properties = new LinkedHashMap<String, String>();
public String getDataSourceClassName() {
return this.dataSourceClassName;
}
public void setDataSourceClassName(String dataSourceClassName) {
this.dataSourceClassName = dataSourceClassName;
}
public Map<String, String> getProperties() {
return this.properties;
}
public void setProperties(Map<String, String> properties) {
this.properties = properties;
}
}
static class DataSourceBeanCreationException extends BeanCreationException {
DataSourceBeanCreationException(EmbeddedDatabaseConnection connection,
Environment environment, String property) {
super(getMessage(connection, environment, property));
}
private static String getMessage(EmbeddedDatabaseConnection connection,
Environment environment, String property) {
StringBuilder message = new StringBuilder();
message.append("Cannot determine embedded database " + property
+ " for database type " + connection + ". ");
message.append("If you want an embedded database please put a supported "
+ "one on the classpath. ");
message.append("If you have database settings to be loaded from a "
+ "particular profile you may need to active it");
if (environment != null) {
String[] profiles = environment.getActiveProfiles();
if (ObjectUtils.isEmpty(profiles)) {
message.append(" (no profiles are currently active)");
}
else {
message.append(" (the profiles \""
+ StringUtils.arrayToCommaDelimitedString(
environment.getActiveProfiles())
+ "\" are currently active)");
}
}
message.append(".");
return message.toString();
}
}
}
