Springboot2.x 自動建立表并且執行初始化資料

1.使用springboot jdbc初始化資料庫

項目結構：

schema.sql

drop table  if exists user;
 create table user (id bigint(20) not null auto_increment,
 username varchar(40) DEFAULT NULL,
 name varchar(20) DEFAULT NULL,
 age int(3) DEFAULT NULL,
 balance decimal(10,2) DEFAULT NULL,
 primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;　
           

data.sql

insert into user (id, username, name, age, balance) values (1,'account1','張三', 20, 100.00);
insert into user (id, username, name, age, balance) values (2,'account2','李四', 28, 180.00);
insert into user (id, username, name, age, balance) values (3,'account3','王五', 32, 280.00);
           

在SpringBoot1.x中, 運作schema.sql不需要配置便可之間運作,但是在SpringBoot2.x中,我們需要在配置檔案中配置一下:

spring.datasource.initialization-mode: always

資料庫配置：

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test-xc?useSSL=false&useUnicode=true&characterEncoding=UTF8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.separator=;
spring.datasource.schema=classpath:schema.sql
spring.datasource.data=classpath:data.sql
spring.datasource.initialization-mode=always

#顯示SQL語句
spring.jpa.show-sql=true
#不加下面這句則不會預設建立MyISAM引擎的資料庫
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
           

spring.datasource下有兩個屬性 schme、data，其中schema為表初始化語句，data為資料初始化，預設加載schema.sql與data.sql。腳本位置可以通過spring.datasource.schema 與spring.datasource.data 來改變，源碼如下：

/**
     * Create the schema if necessary.
     * @return {@code true} if the schema was created
     * @see DataSourceProperties#getSchema()
     */
    public boolean createSchema() {
        List<Resource> scripts = getScripts("spring.datasource.schema",
                this.properties.getSchema(), "schema");
        if (!scripts.isEmpty()) {
            if (!isEnabled()) {
                logger.debug("Initialization disabled (not running DDL scripts)");
                return false;
            }
            String username = this.properties.getSchemaUsername();
            String password = this.properties.getSchemaPassword();
            runScripts(scripts, username, password);
        }
        return !scripts.isEmpty();
    }
 
    /**
     * Initialize the schema if necessary.
     * @see DataSourceProperties#getData()
     */
    public void initSchema() {
        List<Resource> scripts = getScripts("spring.datasource.data",
                this.properties.getData(), "data");
        if (!scripts.isEmpty()) {
            if (!isEnabled()) {
                logger.debug("Initialization disabled (not running data scripts)");
                return;
            }
            String username = this.properties.getDataUsername();
            String password = this.properties.getDataPassword();
            runScripts(scripts, username, password);
        }
    }
           

看getScripts源碼，它還會加載schema-${platform}.sql檔案，或者data-${platform}.sql檔案，其中platform就是spring.datasource.platform的值

private List<Resource> getScripts(String propertyName, List<String> resources,
            String fallback) {
        if (resources != null) {
            return getResources(propertyName, resources, true);
        }
        String platform = this.properties.getPlatform();
        List<String> fallbackResources = new ArrayList<>();
        fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
        fallbackResources.add("classpath*:" + fallback + ".sql");
        return getResources(propertyName, fallbackResources, false);
    }
           

spring.datasource.initialization-mode 初始化模式（springboot2.0），其中有三個值，always為始終執行初始化，embedded隻初始化記憶體資料庫（預設值）,如h2等，never為不執行初始化。

/*
 * Copyright 2012-2017 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.boot.jdbc;

/**
 * Supported {@link javax.sql.DataSource} initialization modes.
 *
 * @author Vedran Pavic
 * @author Stephane Nicoll
 * @since 2.0.0
 * @see AbstractDataSourceInitializer
 */
public enum DataSourceInitializationMode {

    /**
     * Always initialize the datasource.
     */
    ALWAYS,

    /**
     * Only initialize an embedded datasource.
     */
    EMBEDDED,

    /**
     * Do not initialize the datasource.
     */
    NEVER

}
           

spring.datasouce.data-passwork:

spring.datasouce.data-username:

spring.datasouce.schema-password:

spring.datasouce.schema-username:

這四個值為執行schema.sql或者data.sql時，用的使用者

spring.datasource.sql-script-encoding: utf-8 為檔案的編碼

spring.datasource.separator: ; 為sql腳本中語句分隔符

spring.datasource.continue-on-error: false 遇到語句錯誤時是否繼續，若已經執行過某些語句，再執行可能會報錯，可以忽略，不會影響程式啟動

2.使用hibernate初始化資料庫

spring:
  jpa:
    show-sql: true
    #啟動時是否初始化資料庫-hibernate
    generate-ddl: false
    hibernate:
      ddl-auto: update
           

generate-ddl: 為true時，執行schema建立，會檢測classpath下的import.sql檔案，當然spring.jpa.hibernate.ddl-auto: 必須為create/update/create-drop，none和validate是不行的，因為這個建立時hibernate的，是以建議用spring的