SpringBoot启动自动执行SQL脚本

648 阅读1分钟

说明

主要解决服务启动的时候需要创建表,创建初始化数据的情况,相比liqubase,这种写法更加方便,只是后面的更新可能支持不强大

sql脚本如下

CREATE TABLE IF NOT EXISTS`user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一、按照数据库配置创建数据库

@Configuration
public class DataSourcePreTask {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    @PostConstruct
    public void init() {
        try {
            URI uri = new URI(url.replace("jdbc:", ""));
            String host = uri.getHost();
            String path = uri.getPath();
            int port = uri.getPort();
            Connection connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port, username, password);
            Statement statement = connection.createStatement();
            statement.executeUpdate("CREATE DATABASE IF NOT EXISTS `" + path.replace("/", "") + "` DEFAULT CHARACTER SET = `utf8mb4` COLLATE `utf8mb4_unicode_ci`;");
            statement.close();
            connection.close();
        } catch (SQLException | URI.MalformedURIException throwables) {
            log.error(throwables.getMessage());
        }
    }
}

二、开始配置数据库并执行SQL脚本

@Configuration
public class DataSourceConfig {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.hikari.maximumPoolSize}")
    private int maximumPoolSize;
    @Value("${spring.datasource.hikari.minimumIdle}")
    private int minimumIdle;
    @Value("${spring.datasource.hikari.idleTimeout}")
    private int idleTimeout;
    @Value("${spring.datasource.hikari.connectionTimeout}")
    private int connectionTimeout;
    @Value("${spring.datasource.hikari.maxLifetime}")
    private int maxLifetime;

    private HikariDataSource dataSource;

    public HikariDataSource instance() {
        if (dataSource != null) {
            return dataSource;
        }
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setMinimumIdle(minimumIdle);
        config.setMaximumPoolSize(maximumPoolSize);
        config.setConnectionTimeout(connectionTimeout);
        config.setMaxLifetime(maxLifetime);
        config.setIdleTimeout(idleTimeout);

        dataSource = new HikariDataSource(config);
        return dataSource;
    }

    @Bean(name = "dataSource")
    @DependsOn("dataSourcePreTask")
    public DataSource dataSource() {
        return instance();
    }
}
@Configuration
public class StartInitDataSourceInitializer {
    /**
     * 构建Resource对象
     */
    @Value("classpath:sql/schema.sql")
    private Resource schema;

    /**
     * 自定义Bean实现业务的特殊需求
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        final DataSourceInitializer initializer = new DataSourceInitializer();
        // 设置数据源
        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(databasePopulator());
        return initializer;
    }

    private DatabasePopulator databasePopulator() {
        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScripts(schema);
        return populator;
    }
}