背景： 因为项目需要部署到项目现场，新的电脑环境虽然安装了MySQL，但是不一定配置了MySQL环境变量，加上部署人员不一定会执行数据库脚本。每次通过Navicat等软件也比较麻烦。所以想着在项目启动前检查是否存在数据库，如果不存在就创建数据库并且执行数据库脚本。如果存在就跳过。
项目配置文件application.yml
#环境配置
database:
#初始化数据名称
customDatabaseName: aaaaa
#初始化数据文件名称
initDatabaseFileName: bbbbbb
spring:
application:
name: xxxxx
profiles:
# 配置当前项目环境 值在常量类中统一管理
active: ssssss
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
driverClassName: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/xxxxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
validationQuery: select 1
validationQueryTimeout: 5
testOnBorrow: false
testOnReturn: false
testWhileIdle: true
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
filters: stat
webStatFilter:
enabled: true
urlPattern: /api/*
sessionStatEnable: true
sessionStatMaxCount: 1000
profileEnable: true
statViewServlet:
enabled: true
urlPattern: /druid/*
resetEnable: false
loginUsername: admin
loginPassword: admin
filter:
stat:
enabled: true
mergeSql: true
检查数据库执行类：
@Configuration 知识点
用于定义配置类，可替换XML配置文件，被注解的类内部包含一个或多个@Bean注解方法。可以被AnnotationConfigApplicationContext或者AnnotationConfigWebApplicationContext 进行扫描。用于构建bean定义以及初始化Spring容器。
@PostConstruct说明
被@PostConstruct修饰的方法会在服务器加载Servlet的时候运行，并且只会被服务器调用一次，类似于Serclet的inti()方法。被@PostConstruct修饰的方法会在构造函数之后，init()方法之前运行。
@PreConstruct说明
被@PreConstruct修饰的方法会在服务器卸载Servlet的时候运行，并且只会被服务器调用一次，类似于Servlet的destroy()方法。被@PreConstruct修饰的方法会在destroy()方法之后运行，在Servlet被彻底卸载之前
3.程序实践（检查是否存在指定的数据库–不存在执行初始化脚本-否则跳过）
package com.xxx.xxxxx.common.base.config;
import com.xxx.xxxxxx.common.base.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.URI;
import java.net.URISyntaxException;
import java.sql.*;
/**
* Package: com.xxx.xxxxxx.common.base.config
*
* @author: yxs
* @created Date: 2021/1/15
* @decription:
*/
@Configuration
public class DatabaseInitConfig {
private static final Logger LOG = LoggerFactory.getLogger(DatabaseInitConfig.class);
private static final String SCHEMA_NAME = "schema_name";
/**
* com.mysql.cj.jdbc.Driver
*/
@Value("${spring.datasource.druid.driverClassName}")
private String driver;
/**
* jdbc_url
*/
@Value("${spring.datasource.druid.url}")
private String url;
/**
* 账号名称
*/
@Value("${spring.datasource.druid.username}")
private String username;
/**
* 账号密码
*/
@Value("${spring.datasource.druid.password}")
private String password;
/**
* 需要创建的数据名称
*/
@Value("${database.customDatabaseName}")
private String customDatabaseName;
/**
* 创建的数据库文件名
*/
@Value("${database.initDatabaseFileName}")
private String initDatabaseFileName;
@PostConstruct
public void init() {
URI uri = null;
try {
Class.forName(driver);
uri = new URI(url.replace("jdbc:", ""));
} catch (ClassNotFoundException | URISyntaxException e) {
LOG.error("JDBC URL解析错误", e);
}
String host = uri.getHost();
int port = uri.getPort();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port +
"?useUnicode=true&characterEncoding=UTF-8&useSSL=false", username, password);
Statement statement = connection.createStatement()) {
String sal = "select schema_name from information_schema.schemata where schema_name = " + "'" + customDatabaseName + "'";
//查询返回的结果集
ResultSet resultSet = statement.executeQuery(sal);
if (!resultSet.next()) {
//查不到数据库，执行数据库初始化脚本
LOG.warn("查不到中控数据库({})", customDatabaseName);
InputStream inputStream = this.getClass().getResourceAsStream("/database/" + initDatabaseFileName + ".sql");
ByteArrayOutputStream result = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int length;
String createDb = "CREATE DATABASE IF NOT EXISTS " + customDatabaseName;
connection.setAutoCommit(false);
statement.execute(createDb);
connection.commit();
LOG.info("创建中控数据库（{}）成功", initDatabaseFileName);
try (
Connection connection2 = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + customDatabaseName +
"?useUnicode=true&characterEncoding=UTF-8&useSSL=false", username, password);
Statement statement2 = connection2.createStatement();
) {
while ((length = inputStream.read(buffer)) != -1) {
result.write(buffer, 0, length);
}
String initSql = result.toString("UTF-8");
String[] sqlSplit = initSql.split(";");
for (String sql : sqlSplit) {
if (StringUtils.isNotBlank(sql)) {
statement2.execute(sql);
}
}
LOG.info("执行数据库文件脚本（{}）成功", initDatabaseFileName);
} catch (Exception e) {
LOG.error("初始化系统数据库失败", e);
}
} else {
String databaseName = resultSet.getString(SCHEMA_NAME);
LOG.warn("已经存在数据库({})", databaseName);
}
if (resultSet.isClosed()) {
resultSet.close();
}
} catch (SQLException e) {
LOG.error("启动项目检查数据库是否创建", e);
}
}
}
配置数据源类：
控制数据源初始化在检查数据库完成之后：
@DependsOn 控制bean初始化顺序
可能有些场景中，bean A 间接依赖 bean B。如Bean B应该需要更新一些全局缓存，可能通过单例模式实现且没有在spring容器注册，bean A需要使用该缓存；因此，如果bean B没有准备好，bean A无法访问。
另一个场景中，bean A是事件发布者（或JMS发布者），bean B (或一些) 负责监听这些事件，典型的如观察者模式。我们不想B 错过任何事件，那么B需要首先被初始化。
简言之，有很多场景需要bean B应该被先于bean A被初始化，从而避免各种负面影响。我们可以在bean A上使用@DependsOn注解，告诉容器bean B应该先被初始化。下面通过示例来说明。
package com.ssss.sssss.common.base.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Scope;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* @ClassName DruidConfig
* @Description Druid关联spring监控配置
* Druid连接池监控平台 http://127.0.0.1:8089/druid/index.html
* @Date 2020/7/11
* @Author sss
* @Version 1.0
**/
@Configuration
public class DruidConfig {
private static final Logger LOG = LoggerFactory.getLogger(DruidConfig.class);
/**
* spring监控，druid的拦截器
*
* @return
*/
@Bean
public DruidStatInterceptor druidStatInterceptor() {
return new DruidStatInterceptor();
}
@Bean
@Scope("prototype")
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
pointcut.setPatterns("com.xx.xxxx.*.service.*.service.*", "com.xxxx.xxxx.*.service.*.mapper.*");
return pointcut;
}
/**
* aop配置
*
* @param druidStatInterceptor
* @param druidStatPointcut
* @return
*/
@Bean
public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
DefaultPointcutAdvisor advisor = new DefaultPointcutAdvisor();
advisor.setAdvice(druidStatInterceptor);
advisor.setPointcut(druidStatPointcut);
return advisor;
}
private DruidDataSource dataSource;
@Bean
@DependsOn("databaseInitConfig")
public DataSource init(
@Value("${spring.datasource.druid.driverClassName}")
String driverClassName,
@Value("${spring.datasource.druid.username}")
String username,
@Value("${spring.datasource.druid.password}")
String password,
@Value("${spring.datasource.druid.url}")
String url,
@Value("${spring.datasource.druid.initialSize}")
Integer initialSize,
@Value("${spring.datasource.druid.minIdle}")
Integer minIdle,
@Value("${spring.datasource.druid.maxActive}")
Integer maxActive,
@Value("${spring.datasource.druid.maxWait}")
Integer maxWait,
@Value("${spring.datasource.druid.validationQuery}")
String validationQuery,
@Value("${spring.datasource.druid.validationQueryTimeout}")
Integer validationQueryTimeout,
@Value("${spring.datasource.druid.testOnBorrow}")
Boolean testOnBorrow,
@Value("${spring.datasource.druid.testOnReturn}")
Boolean testOnReturn,
@Value("${spring.datasource.druid.testWhileIdle}")
Boolean testWhileIdle,
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
Integer timeBetweenEvictionRunsMillis,
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
Integer minEvictableIdleTimeMillis,
@Value("${spring.datasource.druid.filters}")
String filters
) {
if (dataSource != null) {
return dataSource;
}
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setUrl(url);
druidDataSource.setInitialSize(initialSize);
druidDataSource.setMinIdle(minIdle);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setMaxWait(maxWait);
druidDataSource.setValidationQuery(validationQuery);
druidDataSource.setValidationQueryTimeout(validationQueryTimeout);
druidDataSource.setTestOnBorrow(testOnBorrow);
druidDataSource.setTestOnReturn(testOnReturn);
druidDataSource.setTestWhileIdle(testWhileIdle);
druidDataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
try {
druidDataSource.setFilters(filters);
} catch (SQLException e) {
LOG.error("初始化数据源出错", e);
}
return druidDataSource;
}
}
最后一步
在启动类排除自动加载数据源操作
@SpringBootApplication(scanBasePackages = "com.xxx",exclude = {
DataSourceAutoConfiguration.class
})
public class iiiApp { }
如果存在错误之处，希望指出，大家共同进步！！！
另外：继承ApplicationContextInitializer接口也可以实现
参考文章：
https://www.jianshu.com/p/721c76c1529c
https://www.cnblogs.com/YuyuanNo1/p/8184003.html
https://www.jb51.net/article/190698.htm
异常参考博客https://blog.csdn.net/bedweather/article/details/6743951
版权声明：本文为xixisi888原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。