How SpringBoot starts and initializes the execution of sql scripts

SpringBoot starts and initializes the execution of sql scripts

What if we want to execute some sql scripts when the project starts, SpringBoot provides us with With this function, you can execute the script when starting the SpringBoot project. Let's take a look.

Let’s take a look at the source code first

boolean createSchema() {
	//会从application.properties或application.yml中获取sql脚本列表
	List<Resource> scripts = this.getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
    if (!scripts.isEmpty()) {
    	if (!this.isEnabled()) {
        	logger.debug("Initialization disabled (not running DDL scripts)");
            return false;
        }

        String username = this.properties.getSchemaUsername();
        String password = this.properties.getSchemaPassword();
        //运行sql脚本
        this.runScripts(scripts, username, password);
    }
    return !scripts.isEmpty();
}
private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
	if (resources != null) {
		//如果配置文件中配置，则加载配置文件
    	return this.getResources(propertyName, resources, true);
   	} else {
   		//指定schema要使用的Platform(mysql、oracle)，默认为all
    	String platform = this.properties.getPlatform();
        List<String> fallbackResources = new ArrayList();
        //如果配置文件中没配置，则会去类路径下找名称为schema或schema-platform的文件
        fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
       	fallbackResources.add("classpath*:" + fallback + ".sql");
       	return this.getResources(propertyName, fallbackResources, false);
    }
}
private List<Resource> getResources(String propertyName, List<String> locations, boolean validate) {
	List<Resource> resources = new ArrayList();
	Iterator var5 = locations.iterator();

    while(var5.hasNext()) {
    	String location = (String)var5.next();
        Resource[] var7 = this.doGetResources(location);
        int var8 = var7.length;

        for(int var9 = 0; var9 < var8; ++var9) {
        	Resource resource = var7[var9];
        	//验证文件是否存在
        	if (resource.exists()) {
            	resources.add(resource);
           	} else if (validate) {
            	throw new InvalidConfigurationPropertyValueException(propertyName, resource, "The specified resource does not exist.");
            }
        }
    }
    return resources;
}

From the source code, we roughly know what it means. SpringBoot will find the script file from the class path by default, but Only script files with specified names of schema or schema-platform can be placed under the class path. If we want to divide multiple script files, then this method is not suitable, then we need to add them in application.properties or application.yml Go to the configuration script list. Can this initialization script operation be controlled in the configuration file? Yes, there is an initialization-mode attribute, which can be set to three values. Always means initialization is always performed, and embedded only initializes the memory database (default value) , such as h3, etc., never means no initialization is performed.

spring:
  datasource:
    username: root
    password: liuzhenyu199577
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always

Let’s verify these two methods

1. Place the script file of schema or schema-platform by default

CREATE TABLE IF NOT EXISTS department (ID VARCHAR(40) NOT NULL, NAME VARCHAR(100), PRIMARY KEY (ID));

View The database does not have the department table. Next, we start the program

After starting, we take a look again and execute

2. Specify multiple sql scripts in the configuration file

spring:
  datasource:
    username: root
    password: liuzhenyu199577
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always
    schema:
      - classpath:department.sql
      - classpath:department2.sql
      - classpath:department3.sql

The three sql scripts are all insert statements

INSERT INTO department (ID,NAME) VALUES ('1','2')
INSERT INTO department (ID,NAME) VALUES ('2','3')
INSERT INTO department (ID,NAME) VALUES ('3','4')

There is no data in the table now. Next we start the program

#After starting, we take a look again and there are three pieces of data in the table

It is the steps for SpringBoot to start and initialize the sql script

The SpringBoot project executes the specified sql file at startup

1. Execute at startup

When the project is started, execute the specified file first When the SQL statement is required, you can add the SQL file that needs to be executed in the resources folder. The SQL statement in the file can be a DDL script or a DML script, and then add the corresponding configuration to the configuration, as follows:

spring:
  datasource:
    schema: classpath:schema.sql # schema.sql中一般存放的是DDL脚本，即通常为创建或更新库表的脚本 data: classpath:data.sql # data.sql中一般是DML脚本，即通常为数据插入脚本

2. Execute multiple sql files

spring.datasource.schema and spring.datasource.data both support receiving a list, so when you need to execute multiple sql files, you can use the following configuration:

spring:
  datasource:
    schema: classpath:schema_1.sql, classpath:schema_2.sql data: classpath:data_1.sql, classpath:data_2.sql 或 spring: datasource: schema: - classpath:schema_1.sql - classpath:schema_2.sql data: - classpath:data_1.sql - classpath:data_2.sql

3. Different running environments execute different scripts

Generally, there will be multiple running environments, such as development, testing, production, etc. The SQL that usually needs to be executed in different operating environments will be different. To solve this problem, wildcards can be used.

Create folders for different environments

Create folders corresponding to different environments in the resources folder, such as dev/, sit/, prod/.

Configuration

application.yml

spring:
  datasource:
    schema: classpath:${spring.profiles.active:dev}/schema.sql 
    data: classpath:${spring.profiles.active:dev}/data.sql

Note: ${} wildcard supports default values. For example, ${spring.profiles.active:dev} in the above configuration, before the semicolon is to take the value of the attribute spring.profiles.active, and when the value of the attribute does not exist, the value after the semicolon is used, that is dev.

bootstrap.yml

spring:
  profiles:
    active: dev # dev/sit/prod等。分别对应开发、测试、生产等不同运行环境。

Reminder: The spring.profiles.active property is generally configured in bootstrap.yml or bootstrap.properties.

4. Support different databases

Because the syntax of different databases is different, to achieve the same function, the SQL statements of different databases may be different, so there may be multiple copies. sql file. When you need to support different databases, you can use the following configuration:

spring:
  datasource:
    schema: classpath:${spring.profiles.active:dev}/schema-${spring.datasource.platform}.sql
    data: classpath:${spring.profiles.active:dev}/data-${spring.datasource.platform}.sql
    platform: mysql

Reminder: The default value of the platform attribute is 'all', so only use the above configuration when switching between different databases, because the default value In this case, spring boot will automatically detect the currently used database.

Note: At this time, taking the dev allowed environment as an example, the following files must exist in the resources/dev/ folder: schema-mysql.sql and data-mysql.sql.

5. Avoid Pitfalls

5.1 Pitfalls

When there are stored procedures or functions in the executed sql file, an error will be reported when starting the project .

For example, there is such a requirement now: when the project starts, scan a certain table. When the number of records in the table is 0, insert multiple records; when it is greater than 0, skip it.

The schema.sql file script is as follows:

-- 当存储过程`p1`存在时，删除。
drop procedure if exists p1;
 
-- 创建存储过程`p1`
create procedure p1() 
begin declare row_num int; select count(*) into row_num from `t_user`; if row_num = 0 then INSERT INTO `t_user`(`username`, `password`) VALUES ('zhangsan', '123456'); end if; end; -- 调用存储过程`p1` call p1(); drop procedure if exists p1;

Start the project and report an error for the following reasons:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create procedure p1() begin declare row_num int' at line 1

大致的意思是：'create procedure p1() begin declare row_num int'这一句出现语法错误。刚看到这一句，我一开始是懵逼的，吓得我赶紧去比对mysql存储过程的写法，对了好久都发现没错，最后看到一篇讲解spring boot配置启动时执行sql脚本的文章，发现其中多了一项配置：spring.datasource.separator=$$。然后看源码发现，spring boot在解析sql脚本时，默认是以';'作为断句的分隔符的。看到这里，不难看出报错的原因，即：spring boot把'create procedure p1() begin declare row_num int'当成是一条普通的sql语句。而我们需要的是创建一个存储过程。

5.2 解决方案

修改sql脚本的断句分隔符。如：spring.datasource.separator=$$。然后把脚本改成：

-- 当存储过程`p1`存在时，删除。
drop procedure if exists p1;$$
 
-- 创建存储过程`p1`
create procedure p1() 
begin declare row_num int; select count(*) into row_num from `t_user`; if row_num = 0 then INSERT INTO `t_user`(`username`, `password`) VALUES ('zhangsan', '123456'); end if; end;$$ -- 调用存储过程`p1` call p1();$$ drop procedure if exists p1;$$

5.3 不足

因为sql脚本的断句分隔符从';'变成'$$'，所以可能需要在DDL、DML语句的';'后加'$$'，不然可能会出现将整个脚本当成一条sql语句来执行的情况。比如：

-- DDL
CREATE TABLE `table_name` (
  -- 字段定义
  ... 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;$$
 
-- DML
INSERT INTO `table_name` VALUE(...);$$

The above is the detailed content of How SpringBoot starts and initializes the execution of sql scripts. For more information, please follow other related articles on the PHP Chinese website!