Spring Boot 2.5.0 was released a few days ago, and it mentioned some changes to the Datasource initialization mechanism. So today, I will talk about the redesigned configuration content in detail, and give my understanding and practical suggestions based on the actual situation.

Deprecated content

Let’s correct one misconception. There were some problems with the presentation of the previous version update. Some readers think this update is a tweak to the Datasource initialization itself, but it’s not. This redesign is just a redesign of the initialization mechanism for the Datasource script.

Let’s look at the abandoned with part of the content (in the org. Springframework. Boot. Autoconfigure. JDBC. DataSourceProperties), if you used the configuration content, so it is easy to understand the new configuration.

	/** * 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;
Copy the code

The attributes that correspond to the configuration file are as follows:

spring.datasource.schema=
spring.datasource.schema-username=
spring.datasource.schema-password=
.
Copy the code

These configurations are used to specify which users to use after the data source is initialized, which scripts to execute, and whether to continue if an error occurs.

The new design

Spring Boot 2.5.0, enable the new way of configuration, we can from this kind of org. Springframework. Boot. Autoconfigure. SQL. The init. SqlInitializationProperties see details.

Let’s go through a simple example to see what this feature can do.

  • Create a base application for Spring Boot and introduce mysql dependencies 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>
Copy the code
  • Add the data source and initialize the data source configuration 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
The name of the user to execute the initialization script
spring.sql.init.username=root
The password of the user to execute the initialization script
spring.sql.init.password=
The location of the initialized schema script
spring.sql.init.schema-locations=classpath*:schema-all.sql
Copy the code
  • According to the configuration definition above, the next step is inresourceCreate a script file in the directoryschema-all.sqlAnd write some scripts that initialize the table structure
create table test.user_info
(
    id          int unsigned auto_increment comment 'user id'
        primary key,
    open_id     varchar(255)     default ' ' null comment 'wechat small program OpenID',
    nick_name   varchar(255)     default ' ' null comment 'Wechat name',
    head_img    varchar(255)     default ' ' null comment 'wechat Profile picture',
    sex         varchar(255)     default ' ' null comment 'gender',
    phone       varchar(255)     default ' ' null comment 'mobile phone',
    province    varchar(255)     default ' ' null comment 'Registered Address: Province',
    city        varchar(255)     default ' ' null comment 'Registered Address: City',
    country     varchar(255)     default ' ' null comment 'Registered Address: County/District',
    status      tinyint unsigned default 0  not null comment 'Marked for deletion 0: No 1: yes',
    create_time datetime                    not null comment 'Creation time',
    update_time datetime                    not null comment 'Update Time'
)
comment 'User table';
Copy the code
  • After completing the above steps, start the application. Then open the MySQL client and you can see in thetestUnder the library, there’s one moreuser_infotable

From the above example, it is not hard to imagine that such a function can be used to manage the automatic execution of application startup and database configuration, so as to reduce the manual execution during application deployment and reduce the execution steps of application deployment.

The configuration,

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

  • spring.sql.init.enabled: Indicates whether to enable the initialization switch. The default value is true. If you do not want to execute the initialization script, set it to false. Command line arguments via -d are easier to control.
  • spring.sql.init.usernameandspring.sql.init.password: Configures the user name and password for executing the initialization script. This is necessary because security management requires that users assigned to service applications do not have permissions on commands such as creating and deleting tables. This can be managed separately from the users in the datasource.
  • spring.sql.init.schema-locations: Configures SQL scripts related to schema changes. Multiple SQL scripts can be configured;Division)
  • spring.sql.init.data-locations: used to configure SQL scripts related to data. Multiple SQL scripts can be configured (default;Division)
  • spring.sql.init.encoding: Sets the code of the script file
  • spring.sql.init.separator: Configures separators for multiple SQL files. The default value is;
  • Spring.sql.init. continue-on-error: Whether to continue if an error occurs during script executionFalse `; Therefore, the second execution of the above example will report an error and fail to start because the table already exists at the time of the first execution.

Application Suggestions

As for the application of these configurations, you would be wise to associate them with database versioning (since scripts can be executed automatically).

Can these configurations be used to automate database initialization when business applications are deployed?

In my opinion, the configuration described above has some automatic execution capability. However, due to the lack of judgment of the current environment, it is far from enough to deal with the actual deployment scenarios.

If you want to automate managing database table structures and initializing data, my recommendations are:

  1. The initialization function provided by default can be used for unit testing only, automatically creating database structures and initializing data, and then destroying it after use. You can easily control the consistency of the execution environment for each unit test.
  2. When the application is deployed in the environment, it will be implemented using Flyway as described earlier. How to use visible sharing before: Manage database versions using Flyway.
  3. Use it in conjunction with Flyway, throughorg.springframework.jdbc.datasource.init.DataSourceInitializerTo define more complex execution logic.

More free tutorials in this series”Click to go to summary directory”

Code sample

For an example of this article, see the chapter3-13 directory in the repository below:

  • Github:github.com/dyc87112/Sp…
  • Gitee:gitee.com/didispace/S…

Original is not easy, if you think this article is good, welcomeStarSupport, your attention is my motivation!

Welcome to pay attention to my public account: program ape DD, share the outside can not see the dry goods and thinking!