Springboot2.x 自动创建表并且执行初始化数据

1.使用springboot jdbc初始化数据库

项目结构

Springboot2.x 自动创建表并且执行初始化数据

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为不执行初始化。

 1 /*
 2  * Copyright 2012-2017 the original author or authors.
 3  *
 4  * Licensed under the Apache License, Version 2.0 (the "License");
 5  * you may not use this file except in compliance with the License.
 6  * You may obtain a copy of the License at
 7  *
 8  *      http://www.apache.org/licenses/LICENSE-2.0
 9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16  
17 package org.springframework.boot.jdbc;
18  
19 /**
20  * Supported {@link javax.sql.DataSource} initialization modes.
21  *
22  * @author Vedran Pavic
23  * @author Stephane Nicoll
24  * @since 2.0.0
25  * @see AbstractDataSourceInitializer
26  */
27 public enum DataSourceInitializationMode {
28  
29     /**
30      * Always initialize the datasource.
31      */
32     ALWAYS,
33  
34     /**
35      * Only initialize an embedded datasource.
36      */
37     EMBEDDED,
38  
39     /**
40      * Do not initialize the datasource.
41      */
42     NEVER
43  
44 }

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初始化数据库

1 spring:
2   jpa:
3     show-sql: true
4     #启动时是否初始化数据库-hibernate
5     generate-ddl: false
6     hibernate:
7       ddl-auto: update

generate-ddl: 为true时,执行schema创建,会检测classpath下的import.sql文件,当然spring.jpa.hibernate.ddl-auto: 必须为create/update/create-drop,none和validate是不行的,因为这个创建时hibernate的,所以建议用spring的

转载至Springboot(二十)启动时数据库初始化spring.datasource/spring.jpa













上一篇:UidGenerator springboot2集成篇


下一篇:SpringBoot2.x服务器端主动推送技术之SSE