springboot + mybatis启动时执行sql脚本

目录

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_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');