SpringBoot 自动建表

1.在启动类中添加 @EnableScheduling 注解

@SpringBootApplication
@EnableDiscoveryClient
@EnableFeignClients
@MapperScan("com.gactoyota.system.mapper")
@EnableScheduling
public class SystemApplication {

    public static void main(String[] args) {
        SpringApplication.run(SystemApplication.class, args);
    }

}

2 编写config 实现 SchedulingConfigurer

/**
 * 自动分表配置
 * @Author LB
 * @Date 2022/4/2
 */
@Component
@Slf4j
public class ScheduledConfig implements SchedulingConfigurer {


    @Value("${spring.datasource.driver-class-name}")
    private String driverName;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Resource
    private IDeviceDataTabsService deviceDataTabsService;


    @SneakyThrows
    @Override
    public void configureTasks(ScheduledTaskRegistrar scheduledTaskRegistrar) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        // 查询这个当前月份的表是否已经创建
        // 获取当前月份
        Date date = new Date();
        String dataForStr = dateFormat.format(date);
        String[] split = dataForStr.split("-");
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < split.length; i++) {
            if (i <= 1) {
                stringBuilder.append(split[i]);
            }
        }
        stringBuilder.append("01");
        String thisSysDate = stringBuilder.toString();
        QueryWrapper<DeviceDataTabs> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("table_name", thisSysDate);
        //  本月表未创建则自动创建并保存
        List<DeviceDataTabs> list = deviceDataTabsService.list(queryWrapper);
        if (list.size() == 0) {
            // 建表
            String tableName = autoTable(thisSysDate);
            log.info("表创建成功！");
            // 新增数据库数据
            DeviceDataTabs deviceDataTabs = new DeviceDataTabs();
            deviceDataTabs.setCreateDate(date);
            deviceDataTabs.setTableName(tableName);
            if (deviceDataTabsService.save(deviceDataTabs)) {
                log.info("当前月份表初始化成功！！【" + tableName + "】");
            }
        }


        // 已经创建则进入定时任务
        scheduledTaskRegistrar.addTriggerTask(
                // 添加任务内容(Runnable)
                () -> {

                    // 获取当前时间
                    Date dateOne = new Date();
                    String thisDate = dateFormat.format(dateOne);

                    String thisDateDay = thisDate.replace("-", "");
                    // 调用创建表格时间
                    try {
                        thisDateDay = autoTable(thisDateDay);
                        log.info("表创建成功！");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    log.info("thisDateDay : " + thisDateDay);
                    DeviceDataTabs deviceDataTabs = new DeviceDataTabs();
                    deviceDataTabs.setCreateDate(date);
                    deviceDataTabs.setTableName(thisDateDay);
                    deviceDataTabsService.save(deviceDataTabs);
                }

                // 设置执行周期(Trigger)
                , triggerContext -> {
                    // 每月1号 凌晨0点调用定时任务
                    String cron = "0 0 0 1 * ?";
                    log.info("定时创建表任务启动！");
                    //返回执行周期(Date)
                    return new CronTrigger(cron).nextExecutionTime(triggerContext);
                }
        );
    }

    /**
     * 创建数据表
     * @param tabName 表名
     * @throws Exception
     */
    public String autoTable(String tabName) throws Exception {
        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement stat = conn.createStatement();
        String tableName = "device_data_" + tabName;
        //创建表
        stat.executeUpdate("CREATE TABLE " + tableName + " (" +
                "  `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键Id'," +
                "  `numerial_number` decimal(6, 3) NULL DEFAULT NULL COMMENT '数值'," +
                "  `picture_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图片名称'," +
                "  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间'," +
                "  `voltage` decimal(3, 1) NULL DEFAULT NULL COMMENT '电压'," +
                "  `device_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备Id'," +
                "  `data_status` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据状态'," +
                "  `is_deleted` int NULL DEFAULT 0 COMMENT '是否已删除'," +
                "  PRIMARY KEY (`id`) USING BTREE" +
                ") ");

        //查询数据
        ResultSet result = stat.executeQuery("select * from " + tableName);
        while (result.next()) {
            System.out.println(result.getInt("id") + " " + result.getString("name"));
        }

        //关闭数据库
        result.close();
        stat.close();
        conn.close();
        return tableName;
    }

}