springboot jar druib 启动建表 初始化数据

1.启动时执行

package com.hongshan.visual.api.config;

import com.hongshan.visual.common.constant.BdpConstant;
import com.hongshan.visual.common.utils.SpringUtil;
import com.hongshan.visual.dao.mapper.DebugLogMapper;
import com.hongshan.visual.dao.mapper.UserInfoMapper;
import com.hongshan.visual.service.ClusterManagerService;
import com.hongshan.visual.service.WebSocketService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;

@Component
public class InitConfig implements CommandLineRunner {
    private static final Logger log = LoggerFactory.getLogger(InitConfig.class);

    @Value("${hive.datasource.druid.driver-class-name}")
    public String driver;

    @Autowired
    private DataSource datasource;

    private final UserInfoMapper userInfoMapper;
    private final ClusterManagerService clusterManagerService;
    private final DebugLogMapper debugLogMapper;

    @Autowired
    public InitConfig(UserInfoMapper userInfoMapper, ClusterManagerService clusterManagerService, DebugLogMapper debugLogMapper) {
        this.userInfoMapper = userInfoMapper;
        this.clusterManagerService = clusterManagerService;
        this.debugLogMapper = debugLogMapper;
    }

    @Override
    public void run(String... strings) {

        BdpConstant.HIVE_DRIVER = driver;
        try {
            executeSqlFile();

            WebSocketService webSocketService = new WebSocketService(userInfoMapper, clusterManagerService);
            webSocketService.clearClusterThriftServer();
        }catch (Exception e){
            log.error("ThriftServer Init Failed: " + e);
        }

        debugLogMapper.initStatus();

        log.info("iniLogTable");
    }

    /**
     * 执行sql文件
     */
    private void executeSqlFile(){
        Connection connection = null;
        try {
            connection = this.datasource.getConnection();
            String ddlSqlRelativePath = "classpath:" + "sql/ddl.sql";
            String dmlSqlRelativePath = "classpath:" + "sql/dml.sql";
            Resource ddlResource = SpringUtil.getApplicationContext().getResource(ddlSqlRelativePath);

            Resource dmlResource = SpringUtil.getApplicationContext().getResource(dmlSqlRelativePath);

            ScriptUtils.executeSqlScript(connection, new EncodedResource(ddlResource,"UTF-8"));

            ScriptUtils.executeSqlScript(connection, new EncodedResource(dmlResource, "UTF-8"),false, false, ScriptUtils.DEFAULT_COMMENT_PREFIX, ";;", ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER, ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);

            DataSourceUtils.releaseConnection(connection, this.datasource);
            log.info("init tables success!");
        }catch (Exception es){
            log.warn(es.getMessage());
            DataSourceUtils.releaseConnection(connection, this.datasource);
        }
    }
}

2.建表脚本

3.初始化数据脚本

--存储过程
create or replace function p1() RETURNS integer AS $total$

declare
total integer;
begin
    select count(*) into total from xxx;

    if total = 0 then
        INSERT INTO xxx;
    end if;

    return total;

    --异常处理
    EXCEPTION
    WHEN others THEN
    RAISE EXCEPTION '(%)', SQLERRM;
end;

$total$ language plpgsql;;

select * from p1();;

4.自动建数据库

    /**
     *  一开始默认数据库2，当创建数据库后改为连接数据库1
     *   1. 连接错误可能经常出现，所以单独抽出来，提示
     *   2. 创建数据库也可能错误，可能已经存在，不抛出异常
     */
    private void createDb() {
        Connection conn = null;

        try {
            conn = datasource.getConnection();
        }catch (SQLException e){
            e.printStackTrace();
            DataSourceUtils.releaseConnection(conn, this.datasource);
            throw new VisualException(e.getMessage());
        }

        try {
            Statement stat = conn.createStatement();
            String createSql = "create database " + BdpConstant.DB_VISUAL;
            stat.execute(createSql);
            stat.close();
            conn.close();
            log.info("create db success!");
        }catch (Exception es){
            log.warn("数据库" + BdpConstant.DB_VISUAL + "已经创建！");
        }
        DataSourceUtils.releaseConnection(conn, this.datasource);
        dynamicDataSource.setDataSource2(DataSourceNames.ONE);
    }

package com.hongshan.visual.api.config.datasource;

import com.hongshan.visual.common.constant.DataSourceNames;
import com.hongshan.visual.common.utils.SpringUtil;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

/**

• 动态数据源

• @author luoliyin

• @date 2020-03-03
  */
  public class DynamicDataSource extends AbstractRoutingDataSource {
  private static final ThreadLocal contextHolder = new ThreadLocal<>();

  private static Object datasoure = null;

  private DataSource defaultDatasource;

  public DataSource getDefaultDatasource() {
  return defaultDatasource;
  }

  public void setDefaultDatasource(DataSource defaultDatasource) {
  this.defaultDatasource = defaultDatasource;
  }

  /**

  • 配置DataSource, defaultTargetDataSource为主数据库
    */
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
    super.setDefaultTargetDataSource(defaultTargetDataSource);
    super.setTargetDataSources(targetDataSources);
    this.defaultDatasource = (DataSource) targetDataSources.get(DataSourceNames.ONE);
    super.afterPropertiesSet();
    }

  @Override
  protected Object determineCurrentLookupKey() {
  datasoure = getDataSource();
  return datasoure;
  }

  public static void setDataSource(String dataSource) {
  contextHolder.set(dataSource);
  }

  public static String getDataSource() {
  return contextHolder.get();
  }

  public static void clearDataSource() {
  contextHolder.remove();
  }

  /**

  • 这个方法非常关键
  • @param dataSource
    */
    public void setDataSource2(String dataSource) {
    contextHolder.set(dataSource);
    super.setDefaultTargetDataSource(defaultDatasource);
    super.afterPropertiesSet();

  }

}
@Configuration
public class DynamicDataSourceConfig {

/**
 * 创建 DataSource Bean
 * */

@Bean
@ConfigurationProperties("spring.datasource.druid.one")
public DataSource oneDataSource(){
    DataSource dataSource = DruidDataSourceBuilder.create().build();
    return dataSource;
}

@Bean
@ConfigurationProperties("spring.datasource.druid.two")
public DataSource twoDataSource(){
    DataSource dataSource = DruidDataSourceBuilder.create().build();
    return dataSource;
}

/**
 * 如果还有数据源,在这继续添加 DataSource Bean
 * */

@Bean
@Primary
public DynamicDataSource dataSource(DataSource oneDataSource, DataSource twoDataSource) {
    Map<Object, Object> targetDataSources = new HashMap<>(2);
    targetDataSources.put(DataSourceNames.ONE, oneDataSource);
    targetDataSources.put(DataSourceNames.TWO, twoDataSource);
    // 还有数据源,在targetDataSources中继续添加
    System.out.println("DataSources:" + targetDataSources);
    return new DynamicDataSource(twoDataSource, targetDataSources);
}

}