springboot项目初始化sql脚本可重放执行方案

已于 2024-04-19 22:27:09 修改
阅读量539 收藏 2
点赞数 8
文章标签： spring boot sql 后端
于 2024-04-19 22:14:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_42144712/article/details/137978460
版权
本文介绍了在SpringBoot项目中如何处理版本迭代中数据库表结构变化的问题，通过使用`init.sql`文件和自定义存储过程`Pro_ColumnWork`实现SQL脚本的可重放执行，确保部署时的库表变更无缝进行。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

背景

        springboot中小项目经常会将初始化的sql脚本放在工程中在项目启动时自动执行。

        在进行中小项目版本迭代时，经常面临不同版本之间库表的改动，包括库表字段的增加、字段类型的修改、字段的删除等，导致部署上线经常出现疏漏。

        下面是一种springboot项目中支持mysql初始化脚本可重放执行的方案

可重放方案

        sql初始化脚本都放在init.sql文件中


CREATE TABLE if not exists `con_package` (
                               `id` int NOT NULL AUTO_INCREMENT COMMENT 'Id',
                               `k8s_version_id` varchar(100) DEFAULT NULL COMMENT '版本号（k8s）',
                               `k8s_app_id` varchar(100) DEFAULT NULL COMMENT '应用id（k8s）',
                               `status` int DEFAULT NULL COMMENT '部署包状态 0-成功 1-执行中 2-失败',
                               `version_id` int DEFAULT NULL COMMENT '版本号（容器平台）',
                               `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                               `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
                               `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除',
                               `task_id` varchar(200) DEFAULT NULL COMMENT '任务id',
                               `execute_type` int DEFAULT NULL COMMENT '执行类型 0-执行 1-转产',
                               PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部署包表';

CALL container.Pro_ColumnWork('con_package','tool_name',1,"varchar(255) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '工具名称'");

CALL container.Pro_ColumnWork('con_package','k8s_app_id',2,"varchar(255) character set utf8mb4 collate utf8mb4_0900_ai_ci default null comment '应用id（k8s）'");

        上述sql都是可重放的，其中建表语句通过 if not exists即可保证可重放，而对库表字段的修改操作则通过 Pro_ColumnWork存储过程实现，其中第三个入参1表示增加字段，2表示修改字段类型。下面是Pro_ColumnWork存储过程的定义：

CREATE DEFINER=`root`@`%` PROCEDURE if not EXISTS `container`.`Pro_ColumnWork`(TableName VARCHAR(50),ColumnName VARCHAR(50),CType INT,SqlStr VARCHAR(4000))
BEGIN
DECLARE Rows1 INT;
DECLARE Rows2 INT;
SET Rows1=0;
SET Rows2=0;
SELECT COUNT(*) into Rows1  FROM INFORMATION_SCHEMA.Columns
WHERE table_schema= DATABASE() AND table_name=TableName AND column_name=ColumnName;
SELECT COUNT(*) into Rows2 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name=TableName and constraint_name =ColumnName;
-- 新增列
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD COLUMN ',ColumnName,' ',SqlStr);
-- 修改列类型
ELSEIF (CType=2 AND Rows1>0)  THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' MODIFY  ',ColumnName,' ',SqlStr);
-- 修改列名称
ELSEIF (CType=3 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' CHANGE  ',ColumnName,' ',SqlStr);
-- 删除列
ELSEIF (CType=4 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' DROP COLUMN  ',ColumnName);
-- 新增约束
ELSEIF (CType=5 AND Rows2<0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' ADD CONSTRAINT ',ColumnName,' ',SqlStr);
ELSE  SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
END IF;
end;

        这样就能实现sql脚本的可重放

整合到springboot项目中

        这里有一个坑，就是springboot在执行初始化sql是默认的sql语句的分隔符是“;”执行上述创建存储过程的sql会报错，因为当“;”出现时sql语句并没有结束，这里需要手动指定执行sql时的分隔符。在springboot启动类做一下配置即可：

package com.jzt.container;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.openfeign.EnableFeignClients;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.ScriptException;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.SQLException;

@RestController
@RequestMapping()
@SpringBootApplication
@MapperScan(basePackages = "com.jzt.container.model.entity.mapper")
@EnableFeignClients
public class ContainerWebApplication {

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


    @Bean
    public DataSourceInitializer initProcedureInitializer(DataSource dataSource) {
        return createInitializer(dataSource, "sql/procedure.sql", "$$");
    }

    @Bean
    public DataSourceInitializer initSqlInitializer(DataSource dataSource) {
        return createInitializer(dataSource, "sql/init.sql", ";");
    }

    private DataSourceInitializer createInitializer(DataSource dataSource, String scriptName, String delimiter) {
        String commentPrefix = "--";
        String separator = ";";
        String blockCommentStartDelimiter = "/*";
        String blockCommentEndDelimiter = "*/";
        return new DataSourceInitializer() {
            {
                setDataSource(dataSource);
                setDatabasePopulator(connection -> {
                    try {
                        ScriptUtils.executeSqlScript(connection,
                                new EncodedResource(new ClassPathResource(scriptName)),
                                false,
                                false,
                                commentPrefix,
                                delimiter,
                                blockCommentStartDelimiter,
                                blockCommentEndDelimiter);
                    } catch (ScriptException e) {
                        throw new RuntimeException("Failed to execute " + scriptName, e);
                    }
                });
            }
        };
    }
}

        这里将存储过程的创建存储过程的语句和初始化库表的sql分开执行，存储过程创建在前，从而保证初始化库表的init.sql中可以正常调用存储过程。同时将存储过程的分隔符设置为//，所以要将创建存储过程的sql语句的最后一个“;”替换成//，即procedure.sql如下：

CREATE DEFINER=`root`@`%` PROCEDURE if not EXISTS `container`.`Pro_ColumnWork`(TableName VARCHAR(50),ColumnName VARCHAR(50),CType INT,SqlStr VARCHAR(4000))
BEGIN
DECLARE Rows1 INT;
DECLARE Rows2 INT;
SET Rows1=0;
SET Rows2=0;
SELECT COUNT(*) into Rows1  FROM INFORMATION_SCHEMA.Columns
WHERE table_schema= DATABASE() AND table_name=TableName AND column_name=ColumnName;
SELECT COUNT(*) into Rows2 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name=TableName and constraint_name =ColumnName;
-- 新增列
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD COLUMN ',ColumnName,' ',SqlStr);
-- 修改列类型
ELSEIF (CType=2 AND Rows1>0)  THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' MODIFY  ',ColumnName,' ',SqlStr);
-- 修改列名称
ELSEIF (CType=3 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' CHANGE  ',ColumnName,' ',SqlStr);
-- 删除列
ELSEIF (CType=4 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' DROP COLUMN  ',ColumnName);
-- 新增约束
ELSEIF (CType=5 AND Rows2<0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' ADD CONSTRAINT ',ColumnName,' ',SqlStr);
ELSE  SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
END IF;
end//

        每次重启springboot项目初始化sql均可成功执行，且有可重放效果 

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值