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