目录
1. 创建数据版本表，结构如下：
2. 创建HdVersion对象
3. 创建执行sql的dao
4. 创建dao对应的xml
5.创建sql执行器，实现ApplicationRunner
6. 结语
背景：项目开发或发布阶段修改表结构，项目更新时需要手动执行脚本，需要优化为项目启动时自动检查版本并执行sql语句。
开发环境：jdk1.8
开发工具：IDEA
框架：springboot+mybatisplus
数据库：mysql 5.7
SpringBoot本身提供了丰富的组件供开发者调用，本次优化通过ApplicationRunner类实现。
在SpringBoot中，提供了一个接口：ApplicationRunner。
该接口中，只有一个run方法，他执行的时机是：spring容器启动完成之后，就会紧接着执行这个接口实现类的run方法。
mybaits默认不能批量执行sql，yml配置文件中连接数据库url配置添加以下参数：
allowMultiQueries=true
如：
url: jdbc:mysql://192.168.100.xx:3306/xxx?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
让我们开始吧！
1. 创建数据版本表，结构如下：
CREATE TABLE `hd_version` (`id` varchar(64) NOT NULL,`version` varchar(64) DEFAULT NULL COMMENT '版本号',`created` datetime DEFAULT NULL COMMENT '创建时间',`remark` varchar(500) DEFAULT NULL COMMENT '备注',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据版本';2. 创建HdVersion对象
@Data
public class HdVersionEntity {private String id;private String version;private String remark;private Date created;}3. 创建执行sql的dao
@Mapper
public interface HdCommonDao {//查询版本号是否存在int selectVersion(@Param("version") String version);//查询版本表是否存在int selectTableExist(@Param("tableName") String tableName);//新增版本int insertVersion(HdVersionEntity entity);//执行sql@Update("${sql}")void updateSql(@Param("sql") String sql);}4. 创建dao对应的xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.hdkj.hdiot.configure.dao.HdCommonDao"><select id="selectVersion" resultType="int">selecT count(1) from hd_versionwhere version = #{version}</select><select id="selectTableExist" resultType="int">select count(*) count from information_schema.TABLES where TABLE_NAME = #{tableName} and table_schema = (select database())</select><insert id="insertVersion">insert into hd_version(id,version, remark, created) values (uuid(),#{version}, #{remark}, #{created})</insert></mapper>5.创建sql执行器，实现ApplicationRunner
@Order(1)
@Component
@Slf4j
public class HdSchemaExecutor implements ApplicationRunner{@Overridepublic void run(ApplicationArguments applicationArguments) throws Exception {//do something}}约定一个存放sql文件的目录：
sql文件命名规则不进行约束
HdSchemaExecutor 新建一个全局变量，存放脚本列表：
private List<SchemaData> schema = new ArrayList<>();SchemaData对象如下：
@Data
public class SchemaData {/*** 版本号*/public String version;/*** 文件名*/public String fileName;public SchemaData(String version, String fileName) {this.version = version;this.fileName = fileName;}
}HdSchemaExecutor 新增方法，给schema赋值：
public void buildSchemas(){schema.add(new SchemaData("v2.1","schema_v2.1.sql"));schema.add(new SchemaData("v2.2","schema_v2.2.sql"));schema.add(new SchemaData("v2.3","schema_v2.3.sql"));}run方法内容如下：
@Overridepublic void run(ApplicationArguments args) throws Exception {//初始版本列表buildSchemas();//定义sql文件路径String basePath = "schemas/";//非版本控制，初始化脚本ClassLoader loader = this.getClass().getClassLoader();//通过流的方式获取项目路径下的文件InputStream inputStream = loader.getResourceAsStream(basePath + "init.sql");//获取文件内容String sql = IoUtil.readUtf8(inputStream);try {//判断版本表是否存在int count = hdCommonDao.selectTableExist("hd_version");if (count == 0) {hdCommonDao.updateSql(sql);}for (SchemaData schemaData : schema) {//查询版本记录是否存在count = hdCommonDao.selectVersion(schemaData.getVersion());if (count == 0) {log.info("--------------执行数据脚本，版本：" + schemaData.getVersion());//获取对应sql脚本inputStream = loader.getResourceAsStream(basePath + schemaData.getFileName());sql = IoUtil.readUtf8(inputStream);hdCommonDao.updateSql(sql);HdVersionEntity entity = new HdVersionEntity();entity.setId(UUID.randomUUID().toString());entity.setVersion(schemaData.getVersion());entity.setCreated(new Date());entity.setRemark(schemaData.getFileName());//写入版本记录hdCommonDao.insertVersion(entity);}}} catch (IORuntimeException e) {e.printStackTrace();} finally {//关闭流inputStream.close();}}完整代码如下：
package com.hdkj.hdiot.configure.config;import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.UUID;
import com.hdkj.hdiot.configure.common.SchemaData;
import com.hdkj.hdiot.configure.dao.HdCommonDao;
import com.hdkj.hdiot.configure.entity.HdVersionEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** @Author: 刘成辉* @Date: 2022/7/27 8:45* @Description:*/
@Order(1)
@Component
@Slf4j
public class HdSchemaExecutor implements ApplicationRunner {@AutowiredHdCommonDao hdCommonDao;private List<SchemaData> schema = new ArrayList<>();@Overridepublic void run(ApplicationArguments args) throws Exception {//初始版本列表buildSchemas();//定义sql文件路径String basePath = "schemas/";//非版本控制，初始化脚本ClassLoader loader = this.getClass().getClassLoader();//通过流的方式获取项目路径下的文件InputStream inputStream = loader.getResourceAsStream(basePath + "init.sql");//获取文件内容String sql = IoUtil.readUtf8(inputStream);try {//判断版本表是否存在int count = hdCommonDao.selectTableExist("hd_version");if (count == 0) {hdCommonDao.updateSql(sql);}for (SchemaData schemaData : schema) {//查询版本记录是否存在count = hdCommonDao.selectVersion(schemaData.getVersion());if (count == 0) {log.info("--------------执行数据脚本，版本：" + schemaData.getVersion());//获取对应sql脚本inputStream = loader.getResourceAsStream(basePath + schemaData.getFileName());sql = IoUtil.readUtf8(inputStream);hdCommonDao.updateSql(sql);HdVersionEntity entity = new HdVersionEntity();entity.setId(UUID.randomUUID().toString());entity.setVersion(schemaData.getVersion());entity.setCreated(new Date());entity.setRemark(schemaData.getFileName());//写入版本记录hdCommonDao.insertVersion(entity);}}} catch (IORuntimeException e) {e.printStackTrace();} finally {//关闭流inputStream.close();}}public void buildSchemas() {schema.add(new SchemaData("v2.1", "schema_v2.1.sql"));schema.add(new SchemaData("v2.2", "schema_v2.2.sql"));schema.add(new SchemaData("v2.3", "schema_v2.3.sql"));}
}
6. 结语
每次发布版本是放脚本到对应目录下，初始化方法中新增版本对应关系
附初始化脚本文件：
/*==============================================================*/
/* Table: hd_version */
/*==============================================================*/
create table if not exists hd_version
(id varchar(64) not null,version varchar(64) comment '版本号',created datetime comment '创建时间',remark varchar(500) comment '备注',primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据版本';/* 创建函数Pro_Temp_ColumnWork操作表字段 */DROP PROCEDURE IF EXISTS Pro_Temp_ColumnWork;
CREATE PROCEDURE `Pro_Temp_ColumnWork` ( TableName VARCHAR ( 50 ), ColumnName VARCHAR ( 50 ), SqlStr VARCHAR ( 4000 ), CType INT ) BEGINDECLARERows1 INT;SET Rows1 = 0;SELECTCOUNT(*) INTO Rows1FROMINFORMATION_SCHEMA.COLUMNSWHEREtable_schema = DATABASE ()AND upper( table_name )= TableNameAND upper( column_name )= ColumnName;IF( CType = 1 AND Rows1 <= 0 ) THENSET SqlStr := CONCAT( 'ALTER TABLE ', TableName, ' ADD COLUMN ', ColumnName, ' ', SqlStr );ELSEIF ( CType = 2 AND Rows1 > 0 ) THENSET SqlStr := CONCAT( 'ALTER TABLE ', TableName, ' MODIFY ', ColumnName, ' ', SqlStr );ELSEIF ( CType = 3 AND Rows1 > 0 ) THENSET SqlStr := CONCAT( 'ALTER TABLE ', TableName, ' DROP COLUMN ', ColumnName );ELSESET SqlStr := '';END IF;IF( SqlStr <> '' ) THENSET @SQL1 = SqlStr;PREPARE stmt1FROM@SQL1;EXECUTE stmt1;END IF;END;
/** 函数创建结束 **//*创建定义普通索引函数*/
DROP PROCEDURE IF EXISTS Modify_index;CREATE PROCEDURE Modify_index (TableName VARCHAR ( 50 ),ColumnNames VARCHAR ( 500 ),idx_name VARCHAR ( 50 ),idx_type VARCHAR ( 50 )) BEGINDECLARERows1 int;DECLARESqlStr VARCHAR(4000);DECLAREtarget_database VARCHAR ( 100 );SELECT DATABASE() INTO target_database;SET Rows1 = 0;SELECTCOUNT(*) INTO Rows1FROMinformation_schema.statisticsWHEREtable_schema = DATABASE ()AND upper( table_name )= upper(TableName)AND upper( index_name )= upper(idx_name);IF Rows1<=0 THENSET SqlStr := CONCAT( 'alter table ', TableName, ' ADD INDEX ', idx_name, '(', ColumnNames, ') USING ', idx_type );END IF;IF( SqlStr <> '' ) THENSET @SQL1 = SqlStr;PREPARE stmt1FROM@SQL1;EXECUTE stmt1;END IF;END;
/*创建定义普通索引函数结束*/Pro_Temp_ColumnWork：维护表字段
exp：
CALL Pro_Temp_ColumnWork ('table_name','column_name','int(1) ', 1);
Modify_index：维护表索引
exp：
call Modify_index('table_name','column_names','idx_tid_target','BTREE');
