What is the use of Spring Boot 2.5.0's redesigned spring.sql.init configuration?

A few days ago, Spring Boot 2.5.0 released, which mentioned the adjustment of the Datasource initialization mechanism. Some readers want to know what adjustments have been made in this regard. So today, I will talk about the redesigned configuration content in detail, and talk about my understanding and practical suggestions based on the actual situation.

Deprecated content

Let me correct a misunderstanding first. There were some presentation problems during the introduction of the version update. Some readers think that this update is an adjustment of the initialization of Datasource itself, but it is not. This redesign is only a redesign of the Datasource script initialization mechanism.

Let's take a look at the content of this deprecated part (located at org.springframework.boot.autoconfigure.jdbc.DataSourceProperties ). If you have used these configuration content, then the new configuration will be easy to understand.

    /**
     * Mode to apply when determining if DataSource initialization should be performed
     * using the available DDL and DML scripts.
     */
    @Deprecated
    private DataSourceInitializationMode initializationMode = DataSourceInitializationMode.EMBEDDED;

    /**
     * Platform to use in the DDL or DML scripts (such as schema-${platform}.sql or
     * data-${platform}.sql).
     */
    @Deprecated
    private String platform = "all";

    /**
     * Schema (DDL) script resource references.
     */
    private List<String> schema;

    /**
     * Username of the database to execute DDL scripts (if different).
     */
    @Deprecated
    private String schemaUsername;

    /**
     * Password of the database to execute DDL scripts (if different).
     */
    @Deprecated
    private String schemaPassword;

    /**
     * Data (DML) script resource references.
     */
    @Deprecated
    private List<String> data;

    /**
     * Username of the database to execute DML scripts (if different).
     */
    @Deprecated
    private String dataUsername;

    /**
     * Password of the database to execute DML scripts (if different).
     */
    @Deprecated
    private String dataPassword;

    /**
     * Whether to stop if an error occurs while initializing the database.
     */
    @Deprecated
    private boolean continueOnError = false;

    /**
     * Statement separator in SQL initialization scripts.
     */
    @Deprecated
    private String separator = ";";

    /**
     * SQL scripts encoding.
     */
    @Deprecated
    private Charset sqlScriptEncoding;

The attributes corresponding to the configuration file are as follows (only some are listed here, not all of them are listed, mainly corresponding to the attributes in the source code above):

spring.datasource.schema=
spring.datasource.schema-username=
spring.datasource.schema-password=
...

These configurations are mainly used to specify which users to use after the data source is initialized, which scripts to execute, and whether to continue when errors are encountered.

New design

Starting from Spring Boot 2.5.0, a new configuration method is enabled. We can see the details org.springframework.boot.autoconfigure.sql.init.SqlInitializationProperties

Below we use a simple example to experience the role of this feature.

• Create a Spring Boot basic application and introduce dependency with mysql in pom.xml:

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

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

• Add the data source and initialize the configuration of the data source in the configuration file, as follows:

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Spring Boot 2.5.0 init schema & data
# 执行初始化脚本的用户名称
spring.sql.init.username=root
# 执行初始化脚本的用户密码
spring.sql.init.password=
# 初始化的schema脚本位置
spring.sql.init.schema-locations=classpath*:schema-all.sql

• According to the definition of the above configuration, next, create the script file schema-all.sql resource directory, and write some scripts to initialize the table structure

create table test.user_info
(
    id          int unsigned auto_increment comment '用户id'
        primary key,
    open_id     varchar(255)     default '' null comment '微信小程序openid',
    nick_name   varchar(255)     default '' null comment '微信名',
    head_img    varchar(255)     default '' null comment '微信头像',
    sex         varchar(255)     default '' null comment '性别',
    phone       varchar(255)     default '' null comment '手机',
    province    varchar(255)     default '' null comment '注册地址：省',
    city        varchar(255)     default '' null comment '注册地址：城市',
    country     varchar(255)     default '' null comment '注册地址：县/区',
    status      tinyint unsigned default 0  not null comment '是否标记删除 0：否 1：是',
    create_time datetime                    not null comment '创建时间',
    update_time datetime                    not null comment '更新时间'
)
comment '用户表';

• After completing the above steps, start the application. Then open the MySQL client, you can see that there is an user_info table test

Through the above example, it is not difficult to think that such a function can be mainly used to manage the automatic execution of application startup and database configuration, so as to reduce the content of manual execution in the application deployment process and reduce the execution steps of application deployment.

Detailed configuration

In addition to the configuration properties used above, there are some other configurations, which are explained in detail below.

• spring.sql.init.enabled : Whether to start the initialization switch, the default is true. If you don't want to execute the initialization script, just set it to false. It will be easier to control through the -D command line parameter.
• spring.sql.init.username and spring.sql.init.password : Configure the user name and password for executing the initialization script. This is very necessary, because of the security management requirements, usually users assigned to business applications do not have permissions to some commands such as creating tables and deleting tables. This can be managed separately from the users in the datasource.
• spring.sql.init.schema-locations : Configure SQL scripts related to schema changes, multiple configurations can be configured (default ; split)
• spring.sql.init.data-locations : Used to configure data-related sql scripts, multiple configurations can be configured (default is divided ;
• spring.sql.init.encoding : configure the encoding of the script file
• spring.sql.init.separator : Configure the separator for multiple sql files, the default is ;
• spring.sql.init.continue-on-error: If an error is encountered during the execution of the script, the default is false`; therefore, the above example will report an error and fail to start the second time it is executed, because the first time The table already exists at the time of execution.

Application Suggestion

Regarding the application of these configurations, I believe you will be wise to associate it with the version management of the database (because the script can be executed automatically).

So relying on these configurations, is it capable of automating database initialization during business application deployment?

I personally think that the configuration described above has a certain degree of automatic execution capability. However, due to the lack of ability to judge the current environment, it is far from enough to deal with actual deployment scenarios.

If you want to automatically manage the database table structure and initialize the data, my suggestion is:

1. The initialization function provided by default can and is only used for unit testing, automatically creating database structure and initializing data, and destroying it after use. You can easily control the execution environment of each unit test to be consistent.
2. When the application is deployed in the environment, it is still necessary to use the Flyway introduced before, how to use the previous sharing: Use Flyway to manage the database version .
3. Used in conjunction with Flyway, through org.springframework.jdbc.datasource.init.DataSourceInitializer to define more complex execution logic.

More free tutorials in this series are serialized "Click to enter the summary catalog"

Code example

For related examples in this article, you can view the chapter3-13 directory in the following warehouse:

• Github：https://github.com/dyc87112/SpringBoot-Learning/
• Gitee：https://gitee.com/didispace/SpringBoot-Learning/

original is not easy, if you think this article is good, welcome to Star , your attention is my motivation for persistence!

Welcome to pay attention to my public account: Program Ape DD, share knowledge and thoughts that can’t be seen elsewhere