平时在用SpringBoot开发时我们需用连接数据库，首先我们都是先创好数据库名和表结构，然后在YML配置好数据库名，这样十分的不方便，有没有一种方式在SpringBoot启动时就把库表创建好呢？

今天为大家介绍一种方式：

1.先创建数据库，自定义一个配置类(DataSourceConfig)，里面获取YML里面的数据库配置信息，然后通过Druid连接数据库，运行建库语句

@Configuration
@Primary //在同样的DataSource中，首先使用被标注的DataSource
public class DataSourceConfig {

    private Logger log = LoggerFactory.getLogger(DataSourceConfig.class);

    /**jdbc:mysql://127.0.0.1:3306/insight?useUnicode=true&characterEncoding=utf8&failOverReadOnly=false&allowMultiQueries=true
     */
    @Value("${spring.datasource.url}")
    private String datasourceUrl;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    @Bean     //声明其为Bean实例
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(datasourceUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        try {
            Class.forName(driverClassName);
            String url01 = datasourceUrl.substring(0,datasourceUrl.indexOf("?"));
            String url02 = url01.substring(0,url01.lastIndexOf("/"));
            String datasourceName = url01.substring(url01.lastIndexOf("/")+1);
            // 连接已经存在的数据库，如：mysql
            Connection connection = DriverManager.getConnection(url02, username, password);
            Statement statement = connection.createStatement();

            // 创建数据库
            statement.executeUpdate("create database if not exists `" + datasourceName + "` default character set utf8 COLLATE utf8_general_ci");

            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }


        return datasource;
    }

}

2.建表结构,在yml里面加入如下配置就可以实现自动创建表结构

 

#自动运行表结构文件
spring:
  datasource:
    sql-script-encoding: utf-8
    schema:  classpath:sql/table.sql
    initialization-mode: always
    platform: mysql
    url: jdbc:mysql://localhost:3306/move_task?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false
    driverClassName: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: ******

 

3.table.sql文件内容

CREATE TABLE IF NOT EXISTS `agv_task_queue`
(
    `auto_id`     bigint                                                        NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `task_id`     varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务id',
    `node_num`    int                                                           NOT NULL COMMENT '排序标识',
    `exec_status` int DEFAULT NULL COMMENT '执行状态',
    PRIMARY KEY (`auto_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci;

4.测试。

启动项目，就能看到创建好的库和表