sql升级更新解决方案

1.问题

随着项目版本的迭代，有时候需要更新数据库如修改字段、更新数据、删除数据等等，但是如果系统已经部署到生产环境了，让开发人员连接生产环境的数据库手动处理是不太实际的，生产环境数据库一般不允许外部连接，而且这么处理效率太低也容易出错。

2.解决方案

在项目启动时进行数据库自动更新，更新后记录执行的sql方法名

2.1创建sql升级记录表

CREATE TABLE `sys_sql_upgrade_record` (
  `id` varchar(100) NOT NULL COMMENT 'id',
  `sqlKey` varchar(100) NOT NULL COMMENT 'sqlKey',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='sql升级记录';

2.2对sql升级记录表生成代码

我用的是Mybatis Plus框架，用mybatis-plus-generator工具对sys_sql_upgrade_record表生成controller, entity, mapper, service代码

2.3配置sql升级

项目启动时执行sql升级操作，使用反射机制获取SqlUpgradeRecordMapper的全部自有方法，然后查询sys_sql_upgrade_record表所有的sqlKey，判断每个方法是否存在表中，不存在则用invoke执行，然后保存方法名到sys_sql_upgrade_record表

@Component
@Slf4j
public class CustomCommandLineRunner implements CommandLineRunner {
    @Autowired
    private SqlUpgradeRecordService sqlUpgradeRecordService;

    @Override
    public void run(String... args) throws Exception {
        log.info("初始化");
        try {
            sqlUpgradeRecordService.executeSqlUpgrade();
        } catch (Exception e) {
            log.error("初始化出错", e);
        }
        log.info("初始化结束");
    }
}

SqlUpgradeRecordServiceImpl.java

@Service
@Slf4j
public class SqlUpgradeRecordServiceImpl extends ServiceImpl<SqlUpgradeRecordMapper, SqlUpgradeRecord> implements SqlUpgradeRecordService {
    @Autowired
    private SqlUpgradeRecordMapper sqlUpgradeRecordMapper;

    @Override
    public void executeSqlUpgrade() {
        log.info("执行sql升级");
        Class<SqlUpgradeRecordMapper> mapperClass = SqlUpgradeRecordMapper.class;
        Method[] declaredMethods = mapperClass.getDeclaredMethods();
        if (declaredMethods.length > 0) {
            QueryWrapper<SqlUpgradeRecord> wrapper = new QueryWrapper<>();
            List<String> list = sqlUpgradeRecordMapper.selectList(wrapper).stream().map(SqlUpgradeRecord::getSqlKey).collect(Collectors.toList());
            for (Method method : declaredMethods) {
                String methodName = method.getName();
                log.info("sqlKey={}", methodName);
                if (!list.contains(methodName)) {
                    try {
                        log.info("执行sql:{}", methodName);
                        method.invoke(sqlUpgradeRecordMapper);
                        log.info("sql执行成功:{}", methodName);
                        SqlUpgradeRecord record = SqlUpgradeRecord.def();
                        record.setSqlKey(methodName);
                        sqlUpgradeRecordMapper.insert(record);
                    } catch (Exception e) {
                        log.error("sql执行失败", e);
                    }
                }
            }
        }
        log.info("sql升级结束");
    }
}

SqlUpgradeRecord.java

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_sql_upgrade_record")
public class SqlUpgradeRecord implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @TableId(value = "id", type = IdType.ASSIGN_UUID)
    private String id;

    /**
     * sqlKey
     */
    @TableField("sqlKey")
    private String sqlKey;

    /**
     * 创建时间
     */
    @TableField("createTime")
    private LocalDateTime createTime;

    public static SqlUpgradeRecord def() {
        SqlUpgradeRecord entity = new SqlUpgradeRecord();
        entity.setCreateTime(LocalDateTime.now());
        return entity;
    }


}

SqlUpgradeRecordMapper.java

@Mapper
public interface SqlUpgradeRecordMapper extends BaseMapper<SqlUpgradeRecord> {
    /**
     * 更新所有管理员的状态=1（测试sql更新）
     */
    void updateManagerStatus();
}

SqlUpgradeRecordMapper.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.hhf.takeaway.service.user.module.sys.sqlupgraderecord.mapper.SqlUpgradeRecordMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hhf.takeaway.service.user.module.sys.sqlupgraderecord.entity.SqlUpgradeRecord">
        <id column="id" property="id" />
        <result column="sqlKey" property="sqlKey" />
        <result column="createTime" property="createTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, sqlKey, createTime
    </sql>

    <update id="updateManagerStatus">
        update usr_manager set status = 1
    </update>

</mapper>

3.总结

通过这种方案，使得sql升级实现自动化，并且记录了执行哪些sql，方便后续查看，如果希望再次执行某个sql，也可以去记录表手动删掉记录，重启系统后可以再次执行。

想到的另一个方案：

数据库建个temp表，key字段为sql_version，value字段为当前最大版本

自行定义dtd文件，然后根据dtd在resources目录下定义xml，xml里面配置sql语句和对应版本，格式如下

<sql version="1">
    update ...
</sql>

系统启动时扫描resources目录下的xml文件，用jsoup解析xml文件的元素，获取sql版本和语句，并查询temp表的sql_version，比较sql版本和当前最大版本，执行那些大于最大版本的sql语句，最后更新最大版本到temp表。

此方案工作量就比较大了，需要定义dtd和xml，还要扫描解析xml执行sql语句，相比之下方案一借助Mybatis省去了很多工作量。