Quick Guide on Loading Initial Data with Spring Boot – 用Spring Boot加载初始数据的快速指南

最后修改: 2017年 11月 24日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

Spring Boot makes it really easy to manage our database changes. If we leave the default configuration, it’ll search for entities in our packages and create the respective tables automatically.

Spring Boot使管理我们的数据库变化变得非常容易。如果我们保持默认配置，它将在我们的包中搜索实体，并自动创建相应的表。

But we’ll sometimes need more fine-grained control over the database alterations. And that’s when we can use the data.sql and schema.sql files in Spring.

但有时我们需要对数据库的改动进行更细粒度的控制。这时我们可以使用Spring中的data.sql和schema.sql文件。

2. The data.sql File

2、data.sql文件

Let’s also make the assumption here that we’re working with JPA and define a simple Country entity in our project:

让我们在此假设我们是在使用JPA，并在我们的项目中定义一个简单的Country实体。

@Entity
public class Country {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Integer id;
    
    @Column(nullable = false)
    private String name;

    //...
}

If we run our application, Spring Boot will create an empty table for us but won’t populate it with anything.

如果我们运行我们的应用程序，Spring Boot将为我们创建一个空表，但不会用任何东西填充它。

An easy way to do this is to create a file named data.sql:

一个简单的方法是创建一个名为data.sql的文件。

INSERT INTO country (name) VALUES ('India');
INSERT INTO country (name) VALUES ('Brazil');
INSERT INTO country (name) VALUES ('USA');
INSERT INTO country (name) VALUES ('Italy');

When we run the project with this file on the classpath, Spring will pick it up and use it for populating the database.

当我们在classpath上运行这个文件的项目时，Spring会接收它并使用它来填充数据库。

3. The schema.sql File

3、schema.sql文件

Sometimes, we don’t want to rely on the default schema creation mechanism.

有时，我们不想依赖默认的模式创建机制。

In such cases, we can create a custom schema.sql file:

在这种情况下，我们可以创建一个自定义的schema.sql文件。

CREATE TABLE country (
    id   INTEGER      NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

Spring will pick this file up and use it for creating a schema.

Spring会接收这个文件并使用它来创建一个模式。

Please note that script-based initialization i.e. through schema.sql and data.sql and Hibernate initialization together can cause some issues.

请注意，基于脚本的初始化，即通过schema.sql和data.sql和Hibernate的初始化一起进行，会导致一些问题。

Either we disable Hibernate automatic schema creation:

要么我们禁用Hibernate的自动模式创建。

spring.jpa.hibernate.ddl-auto=none

spring.jpa.hibernate.ddl-auto=none

This will ensure that script-based initialization is performed using schema.sql and data.sql directly.

这将确保直接使用schema.sql和data.sql进行基于脚本的初始化。

If we still want to have both Hibernate automatic schema generation in conjugation with script-based schema creation and data population, we’ll have to use:

如果我们仍然想让Hibernate的自动模式生成与基于脚本的模式创建和数据人口结合在一起，我们就必须使用。

spring.jpa.defer-datasource-initialization=true

This will ensure, that after Hibernate schema creation is performed then additionally schema.sql is read for any additional schema changes and data.sql is executed to populate the database. 

这将确保在执行Hibernate模式创建后，另外schema.sql被读取以获得任何额外的模式变化，data.sql被执行以填满数据库。

Also, script-based initialization is performed by default only for embedded databases, to always initialize a database using scripts, we’ll have to use:

另外，基于脚本的初始化默认只对嵌入式数据库执行，要想总是使用脚本初始化数据库，我们必须使用。

spring.sql.init.mode=always

Please refer to official Spring documentation on initializing databases using SQL scripts.

请参考Spring官方文档使用SQL脚本初始化数据库。

4. Controlling Database Creation Using Hibernate

4.使用Hibernate控制数据库的创建

Spring provides a JPA-specific property that Hibernate uses for DDL generation: spring.jpa.hibernate.ddl-auto.

Spring提供了一个JPA专用的属性，Hibernate使用该属性来生成DDL。spring.jpa.hibernate.ddl-auto。

The standard Hibernate property values are create, update, create-drop, validate and none:

标准的Hibernate属性值是create、update、creat-drop、validate和none。

  • create – Hibernate first drops existing tables and then creates new tables.
  • update – The object model created based on the mappings (annotations or XML) is compared with the existing schema, and then Hibernate updates the schema according to the diff. It never deletes the existing tables or columns even if they are no longer required by the application.
  • create-drop – similar to create, with the addition that Hibernate will drop the database after all operations are completed; typically used for unit testing
  • validate – Hibernate only validates whether the tables and columns exist; otherwise, it throws an exception.
  • none – This value effectively turns off the DDL generation.

Spring Boot internally defaults this parameter value to create-drop if no schema manager has been detected, otherwise none for all other cases.

如果没有检测到模式管理器，Spring Boot内部将此参数值默认为create-drop，否则在所有其他情况下为none。

We have to set the value carefully or use one of the other mechanisms to initialize the database.

我们必须仔细设置该值，或者使用其他机制来初始化数据库。

5. Customizing Database Schema Creation

5.定制数据库模式的创建

By default, Spring Boot automatically creates the schema of an embedded DataSource.

默认情况下，Spring Boot会自动创建嵌入式DataSource的模式。

If we need to control or customize this behavior, we can use the property spring.sql.init.mode. This property takes one of three values:

如果我们需要控制或定制这种行为，我们可以使用属性spring.sql.init.mode。这个属性取三个值之一。

  • always – always initialize the database
  • embedded – always initialize if an embedded database is in use. This is the default if the property value is not specified.
  • never – never initialize the database

Notably, if we are using a non-embedded database, let’s say MySQL or PostGreSQL, and want to initialize its schema, we’ll have to set this property to always.

值得注意的是，如果我们使用的是非嵌入式数据库，比方说MySQL或PostGreSQL，并且想要初始化其模式，我们必须将这个属性设置为always。

This property was introduced in Spring Boot 2.5.0; we need to use spring.datasource.initialization-mode if we are using previous versions of Spring Boot.

这个属性是在Spring Boot 2.5.0中引入的；如果我们使用以前版本的Spring Boot，我们需要使用spring.datasource.initialization-mode 。

6. @Sql

6.@Sql

Spring also provides the @Sql annotation — a declarative way to initialize and populate our test schema.

Spring还提供了@Sql注解–一种声明性的方式来初始化和填充我们的测试模式。

Let’s see how to use the @Sql annotation to create a new table and also load the table with initial data for our integration test:

让我们看看如何使用@Sql注解来创建一个新的表，同时为我们的集成测试加载带有初始数据的表。

@Sql({"/employees_schema.sql", "/import_employees.sql"})
public class SpringBootInitialLoadIntegrationTest {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    public void testLoadDataForTestClass() {
        assertEquals(3, employeeRepository.findAll().size());
    }
}

Here are the attributes of the @Sql annotation:

下面是@Sql注释的属性。

  • config – local configuration for the SQL scripts. We describe this in detail in the next section.
  • executionPhase – We can also specify when to execute the scripts, either BEFORE_TEST_METHOD or AFTER_TEST_METHOD.
  • statements – We can declare inline SQL statements to execute.
  • scripts – We can declare the paths to SQL script files to execute. This is an alias for the value attribute.

The @Sql annotation can be used at the class level or the method level.

@Sql注解可以在类级别或方法级别使用。

We’ll load additional data required for a particular test case by annotating that method:

我们将通过注解该方法来加载某个特定测试案例所需的额外数据。

@Test
@Sql({"/import_senior_employees.sql"})
public void testLoadDataForTestCase() {
    assertEquals(5, employeeRepository.findAll().size());
}

7. @SqlConfig 

7. @SqlConfig

We can configure the way we parse and run the SQL scripts by using the @SqlConfig annotation.

我们可以通过使用@SqlConfig注释来配置我们解析和运行SQL脚本的方式。

@SqlConfig can be declared at the class level, where it serves as a global configuration. Or we can use it to configure a particular @Sql annotation.

@SqlConfig可以在类的层面上声明，在那里它作为一个全局配置。或者我们可以用它来配置一个特定的@Sql注解。

Let’s see an example where we specify the encoding of our SQL scripts as well as the transaction mode for executing the scripts:

让我们看一个例子，我们指定SQL脚本的编码，以及执行脚本的事务模式。

@Test
@Sql(scripts = {"/import_senior_employees.sql"}, 
  config = @SqlConfig(encoding = "utf-8", transactionMode = TransactionMode.ISOLATED))
public void testLoadDataForTestCase() {
    assertEquals(5, employeeRepository.findAll().size());
}

And let’s look at the various attributes of @SqlConfig:

我们再来看看@SqlConfig的各种属性。

  • blockCommentStartDelimiter – delimiter to identify the start of block comments in SQL script files
  • blockCommentEndDelimiter – delimiter to denote the end of block comments in SQL script files
  • commentPrefix – prefix to identify single-line comments in SQL script files
  • dataSource – name of the javax.sql.DataSource bean against which the scripts and statements will be run
  • encoding – encoding for the SQL script files; default is platform encoding
  • errorMode – mode that will be used when an error is encountered running the scripts
  • separator – string used to separate individual statements; default is “–“
  • transactionManager – bean name of the PlatformTransactionManager that will be used for transactions
  • transactionMode – the mode that will be used when executing scripts in transaction

8. @SqlGroup 

8.@SqlGroup

Java 8 and above allow the use of repeated annotations. We can utilize this feature for @Sql annotations as well. For Java 7 and below, there is a container annotation — @SqlGroup.

Java 8及以上版本允许使用重复注解。我们也可以对@Sql注解利用这一特性。对于Java 7及以下版本，有一个容器注解–@SqlGroup。

Using the @SqlGroup annotation, we’ll declare multiple @Sql annotations:

使用@SqlGroup注解，我们将声明多个@Sql注解。

@SqlGroup({
  @Sql(scripts = "/employees_schema.sql", 
    config = @SqlConfig(transactionMode = TransactionMode.ISOLATED)),
  @Sql("/import_employees.sql")})
public class SpringBootSqlGroupAnnotationIntegrationTest {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    public void testLoadDataForTestCase() {
        assertEquals(3, employeeRepository.findAll().size());
    }
}

9. Conclusion

9.结论

In this quick article, we saw how we can leverage schema.sql and data.sql files for setting up an initial schema and populating it with data.

在这篇快速文章中，我们看到了如何利用schema.sql和data.sql文件来设置一个初始模式并将其填充到数据中。

We also looked at how to use @Sql, @SqlConfig and @SqlGroup annotations to load test data for tests.

我们还研究了如何使用@Sql,@SqlConfig和@SqlGroup注解来加载测试数据进行测试。

Keep in mind that this approach is more suited for basic and simple scenarios, and any advanced database handling would require more advanced and refined tooling like Liquibase or Flyway.

请记住，这种方法更适合于基本和简单的场景，任何高级的数据库处理都需要更高级、更完善的工具，如Liquibase或Flyway。

Code snippets, as always, can be found over on GitHub.

像往常一样，可以在GitHub上找到代码片段。