AJ-Report 禁用flyway以及切换底层数据库方案

禁用flyway

为了解决之前有很多小伙伴因为数据库的版本问题，存在一些sql脚本语法不支持，导致数据库初始化失败

配置文件bootstrap.yml，将spring.flyway.enabled改为false即可。

spring:
  flyway:
    enabled: true    #是否开启flyway，默认true.
    baseline-on-migrate: true
    #数据库连接配置
    url: ${spring.datasource.url}
    user: ${spring.datasource.username}
    password: ${spring.datasource.password}
    placeholder-replacement: false
    init-sqls:
      - CREATE DATABASE IF NOT EXISTS `aj_report` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

在开启的状态下，首次导入，会执行spring.flyway.init-sqls创建数据库aj_report
创建数据库需要获取数据库连接，而spring.datasource.url中的数据库aj_report此时并未创建，导致启动报错
错误信息如下：

Parsed mapper file: 'file [D:\anji-code\gitee\aj-report-p\report-core\target\classes\mapper\ReportShareMapper.xml]'
 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
09-26 10:34:36.078 | main |-INFO  o.s.s.c.ThreadPoolTaskExecutor:181 - Initializing ExecutorService 'applicationTaskExecutor'
09-26 10:34:36.336 | main |-INFO  o.f.c.i.license.VersionPrinter:49 - Flyway Community Edition 5.2.1 by Boxfuse
09-26 10:34:36.427 | main |-WARN  o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext:559 - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.exception.FlywaySqlException: 
Unable to obtain connection from database (jdbc:mysql://127.0.0.1:3306/aj_report?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false) for user 'root': Unknown database 'aj_report'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL State  : 42000
Error Code : 1049
Message    : Unknown database 'aj_report'

09-26 10:34:36.428 | main |-INFO  o.s.s.c.ThreadPoolTaskExecutor:218 - Shutting down ExecutorService 'applicationTaskExecutor'
09-26 10:34:36.430 | main |-INFO  o.a.catalina.core.StandardService:173 - Stopping service [Tomcat]
09-26 10:34:36.434 | main |-WARN  o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [__DEFAULT__] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 java.lang.Object.wait(Native Method)
 java.lang.Object.wait(Object.java:502)
 java.util.TimerThread.mainLoop(Timer.java:526)
 java.util.TimerThread.run(Timer.java:505)
09-26 10:34:36.435 | main |-WARN  o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [report%0043ache.data] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 sun.misc.Unsafe.park(Native Method)
 java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
 java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
 java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 java.lang.Thread.run(Thread.java:748)
09-26 10:34:36.435 | main |-WARN  o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [Statistics Thread-__DEFAULT__-1] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 sun.misc.Unsafe.park(Native Method)
 java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
 java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
 java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 java.lang.Thread.run(Thread.java:748)
09-26 10:34:36.444 | main |-INFO  o.s.b.a.l.ConditionEvaluationReportLoggingListener:136 - 

解决方法,通过DatabaseInitializer类在加载数据源之间拼接出flyway所需要的数据源

@Slf4j
@Component
@AllArgsConstructor
@ConditionalOnProperty(value = {"spring.flyway.enabled"})
public class DatabaseInitializer {

    private final FlywayProperties flywayProperties;
    private final DataSourceProperties dataSourceProperties;

    @PostConstruct
    public void init() throws SQLException {
        log.info("DatabaseInitializer uses flyway init-sqls to initiate database");
        String url = dataSourceProperties.getUrl();
        // jdbc url最后一个 '/' 用于分割具体 schema?参数
        int lastSplitIndex = url.lastIndexOf('?');
        // 获取spring.datasource.url具体数据库schema前的jdbc url
        String addressUrl = url.substring(0, lastSplitIndex);
        String addresslast = url.substring(lastSplitIndex);
        addressUrl = addressUrl.substring(0, addressUrl.lastIndexOf("/"));
        // 直连数据库地址:jdbc:mysql://yourIp:port
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(addressUrl.concat(addresslast));
        dataSource.setUsername(dataSourceProperties.getUsername());
        dataSource.setPassword(dataSourceProperties.getPassword());
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        for (String sql : flywayProperties.getInitSqls()) {
            // 通过flyway的init-sqls配置进行建库与数据库配置
            // executeUpdate:执行给定的SQL语句，该语句可以是INSERT，UPDATE或DELETE语句或不返回任何内容的SQL语句，例如SQL DDL语句。
            statement.executeUpdate(sql);
        }
        statement.close();
        connection.close();
        dataSource.close();
        log.info("DatabaseInitializer initialize completed");
    }

}

更换底层数据库

如需更换底层数据源，如SqlServer等，只要关闭上方的flyway，再去Navicat等工具中将mysql数据库脚本导出为你所需要的数据源版本即可，可能启动后会存在一些sql兼容性问题，需要自行去测试，(目前只测试oracle，存在自增id问题，改动性过大，就没有后续了…),如遇问题，欢迎大家去提交issus，共同完善。