SpringBoot 1.x
启动时初始化脚本
- 注: 案列中使用的是
JPA
,在application.yml
文件中增加了相关配置;使用的是H2
数据库(可根据实际情况修改数据库连接配置)
Maven
依赖及配置
项目结构
├─java
│ │ └─com
│ │ └─learning
│ │ │ ProviderApplication.java
│ │
│ └─resources
│ application.yml
│ data.sql
│ schema.sql
版本信息
SpringBoot
版本:<version>1.5.12.RELEASE</version>
项目依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
application.yml
文件内容
server:
port: 2001
spring:
application:
name: microsevice-provider-user
datasource: # 指定数据源;默认H2建表脚本(根目录/schema.sql);默认H2的insert脚本(classpath:data.sql)
url: jdbc:h2:mem:~/example-app; # 指定数据库,默认
platform: h2
username: sa
password:
driver-class-name: org.h2.Driver
h2:
console:
enabled: true # 是否启用H2数据库控制台
settings:
web-allow-others: true # 允许远程浏览器访问H2 数据库控制台
trace: false
jpa:
show-sql: true
hibernate:
ddl-auto: none
generate-ddl: false
database-platform: org.hibernate.dialect.H2Dialect
脚本内容
脚本名称说明(机翻自官网)
Spring Boot可以自动创建数据源的架构(DDL脚本)并对其进行初始化(DML脚本)。它从标准根类路径位置(分别为schema.sql和data.sql)加载SQL。另外,Spring Boot处理schema-$ {platform} .sql和data-$ {platform} .sql文件(如果存在),其中platform是spring.datasource.platform的值。这使您可以在必要时切换到特定于数据库的脚本。例如,您可以选择将其设置为数据库的供应商名称(hsqldb,h2,oracle,mysql,postgresql等)。
data.sql
drop table if exists tbl_account;
create table tbl_account(
id number(12) not null ,
name varchar not null,
age int(2) not null ,
sex char(2) not null ,
password varchar(80) not null ,
role varchar(10) not null
);
schema.sql
insert into tbl_account (id,name,age,sex,password,role) values (1001,'admin',18,'男','1','ROLE_ADMIN');
insert into tbl_account (id,name,age,sex,password,role) values (1002,'zy',25,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1003,'lb',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1004,'lk',22,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1005,'zj',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1006,'lc',21,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1007,'wdd',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1008,'ln',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1009,'gy',20,'女','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1010,'dew',35,'男','1','ROLE_USER');
SpringBoot 2.x
启动时初始化脚本
- 注: 需要在
SpringBoot 1.x
的application.yaml
中增加属性,示例如下
spring.datasource.initialization-mode=always
application.yml
完整内容
server:
port: 2002
spring:
application:
name: sofa-account
datasource: # 指定数据源;默认H2建表脚本(根目录/schema-h2.sql);默认H2的insert脚本(classpath:data-h2.sql)
url: jdbc:h2:mem:~/example-app; # 指定数据库,默认
platform: h2
username: sa
password:
driver-class-name: org.h2.Driver
initialization-mode: always # SpringBoot 2.0 中需要添加的属性配置
h2:
console:
enabled: true # 是否启用H2数据库控制台
settings:
web-allow-others: true # 允许远程浏览器访问H2 数据库控制台
trace: false
jpa:
show-sql: true
hibernate:
ddl-auto: none
generate-ddl: false
database-platform: org.hibernate.dialect.H2Dialect
SpringBoot 2.0
使用Druid
连接池初始化脚本
Maven
依赖及配置
SpringBoot
默认的数据库连接池为Hikari
,本文中使用的是Druid
而已;配置和Hikari
应该是一样的,如果有不同烦请告知。
项目结构
├─main
│ ├─java
│ │ └─com
│ │ └─dew
│ │ │ Application.java
│ │ │
│ │ ├─config
│ │ │ DruidDataSourceConfig.java
│ │
│ └─resources
│ application.yaml
│ bootstrap.yaml
│ data.sql
│ schema.sql
│
└─test
└─java
└─com
└─dew
├─config
│ DruidDataSourceConfigTest.java
版本信息
SpringBoot
版本:<version>2.2.1.RELEASE</version>
项目依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
application.yml
文件内容
server:
port: 2001
spring:
application:
name: sofa-authentication
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 5
min-idle: 5
max-active: 20
stat-view-servlet:
login-username: admin
login-password: admin
max-wait: 60000
time-between-eviction-runs-millis: 60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接.单位是毫秒
min-evictable-idle-time-millis: 300000 # 配置一个连接池中最小生存的时间,单位是毫秒
## 配置监控统计拦截的filters,去掉后监控界面SQL无法进行统计,`wall`用于防火墙(https://blog.csdn.net/garyond/article/details/80189939)
filters: config,stat,wall,log4j
web-stat-filter:
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
## 多数据源的标识,若该属性存在则为多数据源环境,不存在则为但数据源环境
data-sources:
primary:
url: jdbc:h2:mem:~/example-app # 指定数据库,默认
username: sa
password:
secondary:
url: jdbc:h2:mem:~/example-app # 指定数据库,默认
username: sa
password:
async-init: true
initialization-mode: always
h2:
console:
enabled: true # 是否启用H2数据库控制台
settings:
web-allow-others: true # 允许远程浏览器访问H2 数据库控制台
trace: false
脚本内容
- 与上述一致,为方便查看copy了一下
脚本名称说明(机翻自官网)
Spring Boot可以自动创建数据源的架构(DDL脚本)并对其进行初始化(DML脚本)。它从标准根类路径位置(分别为schema.sql和data.sql)加载SQL。另外,Spring Boot处理schema-$ {platform} .sql和data-$ {platform} .sql文件(如果存在),其中platform是spring.datasource.platform的值。这使您可以在必要时切换到特定于数据库的脚本。例如,您可以选择将其设置为数据库的供应商名称(hsqldb,h2,oracle,mysql,postgresql等)。
data.sql
drop table if exists tbl_account;
create table tbl_account(
id number(12) not null ,
name varchar not null,
age int(2) not null ,
sex char(2) not null ,
password varchar(80) not null ,
role varchar(10) not null
);
schema.sql
insert into tbl_account (id,name,age,sex,password,role) values (1001,'admin',18,'男','1','ROLE_ADMIN');
insert into tbl_account (id,name,age,sex,password,role) values (1002,'zy',25,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1003,'lb',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1004,'lk',22,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1005,'zj',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1006,'lc',21,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1007,'wdd',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1008,'ln',18,'男','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1009,'gy',20,'女','1','ROLE_USER');
insert into tbl_account (id,name,age,sex,password,role) values (1010,'dew',35,'男','1','ROLE_USER');
数据源配置
DruidDataSourceConfig
package com.dew.config;
import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* @ClassName DruidDataSourceConfig
* @Description <br/> Druid 连接池配置
* @Author Dew
* @Date 2019/12/4
**/
@Configuration
public class DruidDataSourceConfig {
@Primary//必需注解,缺少该注解将启动异常.可自定义某个数据源为主数据源
@Bean(name = "primaryDataSource")
@Qualifier(value = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.data-sources.primary")
public DataSource primaryDataSource() {
return new DruidDataSource();
}
@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "secondaryDataSource")
@Qualifier(value = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.data-sources.secondary")
public DataSource secondaryDataSource() {
return new DruidDataSource();
}
@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
DruidDataSourcesConfigTest
package com.dew.config;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.dew.entity.UserPO;
import com.dew.repository.UserRepository;
import java.util.List;
import java.util.Map;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
/**
* @author :Dew
* @date :Created in 2019/12/4
*/
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
public class DruidDataSourceConfigTest {
@Autowired
private JdbcTemplate primaryJdbcTemplate;
@Autowired
private JdbcTemplate secondaryJdbcTemplate;
@Test
public void primaryDataSource() {
String sql = "select * from TBL_ACCOUNT where NAME='admin' and PASSWORD = '1'";
List<Map<String, Object>> result = primaryJdbcTemplate.queryForList(sql);
log.info("primary datasource test:{}", JSONArray.toJSONString(result));
}
@Test
public void secondaryDataSource() {
String sql = "SELECT * FROM TBL_ACCOUNT";
List<Map<String, Object>> result = primaryJdbcTemplate.queryForList(sql);
log.info(JSONArray.toJSONString(result));
}
}
- 小提示: 若对数据库连接池配置有不明白的,可以查看另一篇博客SpringBoot配置多数据源