背景：项目开发或发布阶段修改表结构，项目更新时需要手动执行脚本，需要优化为项目启动时自动检查版本并执行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_version
where 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{
@Override
public 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方法内容如下：
@Override
public 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 {
@Autowired
HdCommonDao hdCommonDao;
private List<SchemaData> schema = new ArrayList<>();
@Override
public 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 ) BEGIN
DECLARE
Rows1 INT;
SET Rows1 = 0;
SELECT
COUNT(*) INTO Rows1
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = DATABASE ()
AND upper( table_name )= TableName
AND upper( column_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, ' DROP COLUMN ', ColumnName );
ELSE
SET SqlStr := '';
END IF;
IF
( SqlStr <> '' ) THEN
SET @SQL1 = SqlStr;
PREPARE stmt1
FROM
@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 )) BEGIN
DECLARE
Rows1 int;
DECLARE
SqlStr VARCHAR(4000);
DECLARE
target_database VARCHAR ( 100 );
SELECT DATABASE
() INTO target_database;
SET Rows1 = 0;
SELECT
COUNT(*) INTO Rows1
FROM
information_schema.statistics
WHERE
table_schema = DATABASE ()
AND upper( table_name )= upper(TableName)
AND upper( index_name )= upper(idx_name);
IF Rows1<=0 THEN
SET SqlStr := CONCAT( 'alter table ', TableName, ' ADD INDEX ', idx_name, '(', ColumnNames, ') USING ', idx_type );
END IF;
IF
( SqlStr <> '' ) THEN
SET @SQL1 = SqlStr;
PREPARE stmt1
FROM
@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');