SpringBoot Project: Create specific table on project start

Some days ago, my leader tell me to complete a mission that automatically creating a table when starting the SpringBoot project. After struggling for some time, I find some ways that can achieve that goal.

1.1 Config application.propertys and Add .sql scripts.

This is a built-in functionality of SpringBoot Framework. What you just need to do is to write a sql script and put it in src/java/resources/ folder. For details about sql DDL information, search yourself.
In this case(filename: schema.sql),

CREATE TABLE IF NOT EXISTS model_version
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    model_name VARCHAR(32) COMMENT '模型名称',
    biz_id INT COMMENT '业务id',
    version INT COMMENT '版本号',
    CONSTRAINT model_version_model_name_biz_id_uindex UNIQUE(model_name, biz_id)
) COMMENT='模型版本';

ATT: You can’t write more than one statement which means that the scripts can only include one delimiter(in the case, semicolon for example).
The following is wrong:

CREATE TABLE model_version
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    model_name VARCHAR(32) COMMENT '模型名称',
    biz_id INT COMMENT '业务id',
    version INT COMMENT '版本号'
);
CREATE UNIQUE INDEX model_version_model_name_biz_id_uindex ON model_version (model_name, biz_id);
ALTER TABLE model_version COMMENT = '模型版本';

In the application.properties, add the following statements:

spring.datasource.initialization-mode=always
spring.datasource.schema=classpath:schema.sql

ATT: classpath is a solid term, while schema.sql is your filename.

1.2 Make use of JPA.

1.2.1 Inject dependency.

The project is built by gradle. So you just need add some statements in build.gradle.

dependencies {
	compile("org.springframework.boot:spring-boot-starter-data-jpa")
}

This will also bring in Hibernate dependencies.

1.2.2 Config application.properties

hibernate.show_sql=true
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Other options about configurations, search yourself!

1.2.3 Write your entity class.

Because this method is creating tables by entity classes, So you need to define your own needed classes corresponding to your table info.
In this case, See following:

import lombok.Data;
import javax.persistence.*;

@Data
@Entity
@Table(name = "model_version", uniqueConstraints =
		                    @UniqueConstraint(columnNames = {"model_name", "biz_id"}))
public class ModelVersion {

		    @Id
		    @GeneratedValue(strategy = GenerationType.AUTO)
		    private Integer id;

		    @Column(name = "model_name")
		    private String modelName;

		    @Column(name = "biz_id")
		    private Integer bizId;

		    private Integer version;

		}

ATT: Take care of @Entity and @Id annotations.

1.3 Add statements in a Bean of the Application main class

The code:

@Bean
	public SubProject subProjectLoader() {
		SubProject loader = new SubProject();
		for (var root: subprojectRoot.split(",")) {
			loader.loadAll(root);
		}
		var a = DataSourceFactory.getMapDataSource();
        String createTable = "CREATE TABLE IF NOT EXISTS model_version\n" +
                "\t(\n" +
                "\t    id INT PRIMARY KEY AUTO_INCREMENT,\n" +
                "\t    model_name VARCHAR(32) COMMENT '模型名称',\n" +
                "\t    biz_id INT COMMENT '业务id',\n" +
                "\t    version INT COMMENT '版本号',\n" +
                "\t    CONSTRAINT model_version_model_name_biz_id_uindex UNIQUE(model_name, biz_id)\n" +
                "\t) COMMENT='模型版本';";
        a.remove("mssql_finance");
        a.remove("hrs_general_order");
        a.keySet().forEach(it -> DbUtils.execute(it, createTable));
		return loader;
	}

ATT: The project starts by load @Bean subProjectLoader, which compose of DataSource connection and other resources. The key is to retrieve all the need DataSource connections then create specfic table in each datasource.

Happing ending!