JFinal使用技巧-项目启动时自动检查升级数据库内容插件

如题，该插件支持项目启动时自动检查数据库是否需要执行SQL脚本。做该插件的原因是该客户有运维人员，并且是客户自己运营系统。我们是碰不到客户机器的，系统也是多租户，一个租户是一个数据仓库，人工执行肯定不行的。所以数据库连接启动时就需要执行升级SQL。网上有类似插件，但是不是复杂就是过于简单，想要SQL脚本支持Enjoy SQL模板语法，最好还是自己撸一个比较好用和方便定制。

分享内容我简单的整理剔除了一些业务代码，尽量保持简洁易读。废话不多说了，直接上🐴！

UpgradeSqlPlugin是JF插件：

package com.momathink.common.plugin.activerecord;

import cn.yunjiaowu.table.plugin.activerecord.directive.*;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Log;
import com.jfinal.plugin.IPlugin;
import com.jfinal.plugin.activerecord.*;
import com.jfinal.template.Engine;
import com.jfinal.template.Template;

import java.io.File;
import java.util.*;

/**
 * 项目启动时 数据库升级插件
 * @author  杜福忠
 */
public class UpgradeSqlPlugin implements IPlugin {
    private Log log = Log.getLog(UpgradeSqlPlugin.class);
    private ActiveRecordPlugin arp;
    private DbPro db;
    private Config config;
    private Engine engine;
    private String tableName = "upgrade_sql_config_log";
    private String folder = "/upgrade_sql";
    private ArrayList<String> existing = null;
    private boolean immediatelyStop = true;

    public UpgradeSqlPlugin(IDataSourceProvider dataSource) {
        this.arp = new ActiveRecordPlugin(getName(), dataSource);
    }

    @SuppressWarnings("unused")
    public UpgradeSqlPlugin(IDataSourceProvider dataSource, int transactionLevel) {
        this.arp = new ActiveRecordPlugin(getName(), dataSource, transactionLevel);
    }

    private static int count = 0;
    protected synchronized static String getName() {
        count ++;
        return "UpgradeSqlPlugin_" + count;
    }

    @Override
    public boolean stop() {
        db = null;
        config = null;
        engine = null;
        existing = null;
        if (this.arp != null){
            this.arp.stop();
            this.arp = null;
        }
        SqlParaDirective.remove();
        log = null;
        return true;
    }

    @Override
    public boolean start() {
        Objects.requireNonNull(this.arp, "UpgradeSqlPlugin设计上只支持start一次");
        this.arp.start();

        try {
            configDb();

            configSql();
        }catch (Exception e) {
            log.error("升级数据库运行异常：", e);
            return false;
        }
        //执行完升级SQL 立即注销 节约内存
        if (this.immediatelyStop){
            stop();
        }
        return true;
    }

    @SuppressWarnings("unused")
    public UpgradeSqlPlugin setImmediatelyStop(boolean immediatelyStop) {
        this.immediatelyStop = immediatelyStop;
        return this;
    }

    private void configSql() {
        File files = getFolderPath();
        if (! files.isDirectory()){
            return;
        }
        File[] listFiles = files.listFiles();
        if (listFiles == null || listFiles.length == 0) {
            return;
        }
        //名称排序
        Arrays.sort(listFiles);

        for (File file : listFiles) {
            //名称统一小写，避免平台问题
            String sqlName = file.getName().toLowerCase();
            if (file.isFile() && sqlName.endsWith(".sql") && !existing.contains(sqlName)){
                runSql(file, sqlName);
            }
        }
    }

    private File getFolderPath() {
        return new File(PathKit.getRootClassPath() + folder);
    }

    private void runSql(File file, String sqlName) {
        Template template = engine.getTemplate(file.getName());
        log.info("数据库执行升级文件：" + sqlName);

        long t = System.currentTimeMillis();

        final String[] ret = {"ok", null};
        db.tx(() -> {
            try{
                ret[1] = template.renderToString();
                return true;
            }catch (Exception e){
                ret[0] = "fail";
                ret[1] = e.getMessage();
                return false;
            }
        });
        saveRecord(sqlName, ret[0], t, ret[1]);
    }

    protected void saveRecord(String sqlName, String status, long t, String logStr) {
        if (logStr != null) {
            logStr = logStr.trim();
        }
        Record r = new Record();
        r.set("sqlName", sqlName);
        r.set("status", status);
        r.set("runDate", new Date());
        r.set("duration", System.currentTimeMillis() - t);
        r.set("log", logStr);
        db.save(tableName, r);
    }

    private void configDb() {
        this.config = arp.getConfig();
        Objects.requireNonNull(config, "ActiveRecordPlugin 需要先启动");
        this.db = Db.use(config.getName());
        SqlParaDirective.setDb(this.db);

        configEngine();

        try{
            List<Record> list = db.find("SELECT sqlName FROM " + tableName + " WHERE status = 'ok'");
            this.existing = new ArrayList<>(list.size());
            for (Record r : list) {
                this.existing.add(r.getStr("sqlName"));
            }
        }catch (ActiveRecordException e) {
            //建表
            initTable();
            this.existing = new ArrayList<>(0);
        }
    }

    protected void initTable() {
        db.update("CREATE TABLE `" + tableName + "`  ( " +//
                "  `id` int(11) NOT NULL AUTO_INCREMENT, " +//
                "  `sqlName` varchar(255) NOT NULL, " +//
                "  `status` varchar(10) NULL, " +//
                "  `runDate` datetime NULL, " +//
                "  `duration` int(11) NULL, " +//
                "  `log` longtext NULL, " +//
                "  PRIMARY KEY (`id`) " +//
                ") COMMENT='数据库升级记录'");
    }

    private void configEngine() {
        this.engine = config.getSqlKit().getEngine();
        engine.setBaseTemplatePath(folder);
        engine.addDirective("try", TryDirective.class, true);
        engine.addDirective("find", FindDirective.class, true);
        engine.addDirective("findFirst", FindFirstDirective.class, true);
        engine.addDirective("runSql", RunSqlDirective.class, true);
    }

    @SuppressWarnings("unused")
    public void setTableName(String tableName) {
        Objects.requireNonNull(tableName, "tableName 不能为null");
        this.tableName = tableName;
    }

    public void setFolder(String folder) {
        Objects.requireNonNull(folder, "folder 不能为null");
        if (! folder.startsWith("/")) {
            folder = "/" + folder;
        }
        this.folder = folder;
    }

    public DbPro getDb() {
        return db;
    }


}

使用姿势：

1、注册插件：https://jfinal.com/doc/2-5

public void configPlugin(Plugins me) {
    DruidPlugin dp = new DruidPlugin(jdbcUrl, userName, password);
    me.add(dp);
    
    //自动检查升级数据库
    me.add(new UpgradeSqlPlugin(dp));
    
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dp);
    arp.addMapping("user", User.class);
    me.add(arp);
}

数据库连接账户需要使用有建表修改表等权限的账户，一般升级脚本里面就是要建表和改变表结构的。
如果担心业务数据账户安全，可以使用独立的DruidPlugin，和业务数据库账户分开，高权限的账户只做升级操作，升级完立即stop掉就可以了。

2、编写SQL脚本：

在配置文件目录》建一个upgrade_sql目录，里面编写模板SQL文件即可，比如：

SQL文件除了支持官方的#para 指令，还增加了几个常用指令：

#runSql、#find、#findFirst、#try

执行多行SQL：
#runSql(受影响行数返回结果的变量名默认是result, true) 特殊场景：给true参数时代表运行异常时可继续执行
    在此是SQL语句，多条用 ;\n 分号加回车符 隔开
#end

查询list集合的，一般用于需要先查出结果再根据数据进行修改的，比如迁移历史数据，之前是一对一，后面改成了一对多等等业务
#find(返回结果的变量名默认是list)
    在此是SQL语句
#end

查一条数据的：
#findFirst(返回结果的变量名默认是record)
    在此是SQL语句
#end

以及吃住异常再处理的指令：

#try(默认值exception可设置)
    包裹代码或指令
#end
#if(exception)
    #(exception.getMessage())
#end

-----------------------------------------------------
例子：
#runSql()
    ALTER TABLE xxxx
#end

项目启动升级后，数据库会自动建一个upgrade_sql_config_log记录升级情况。

业务系统做一个界面可查看升级记录就OK了。

PS：附加上面的Enjoy指令代码：

package com.momathink.common.plugin.activerecord.directive;

import com.jfinal.plugin.activerecord.SqlPara;

/**
 * #find 指令 用于需要查询结果后代码处理的
 * <p>
 * 定义：
 * #find(返回结果的变量名默认是list)
 * 在此是SQL语句
 * #end
 * <p>
 */
public class FindDirective extends SqlParaDirective {
    @Override
    protected Object getData(SqlPara sqlPara) {
        setDefaultName("list");
        return db().find(sqlPara);
    }
}

package com.momathink.common.plugin.activerecord.directive;


import com.jfinal.plugin.activerecord.SqlPara;

/**
 * #findFirst 指令 用于需要查询结果后代码处理的
 * <p>
 * 定义：
 * #findFirst(返回结果的变量名默认是record)
 * 在此是SQL语句
 * #end
 * <p>
 */
public class FindFirstDirective extends SqlParaDirective {
    @Override
    protected Object getData(SqlPara sqlPara) {
        setDefaultName("record");
        return db().findFirst(sqlPara);
    }
}

package com.momathink.common.plugin.activerecord.directive;

import com.jfinal.kit.StrKit;
import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.ActiveRecordException;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.template.expr.ast.Const;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * #sqlPara 指令方便定义SqlPara  <a href="https://www.jfinal.com/doc/5-13">SqlPara</a>
 * <p>
 * 定义：
 * #runSql(受影响行数返回结果的变量名默认是result, true) 特殊场景：给true参数时代表运行异常时可继续执行
 * 在此是SQL语句，多条用 ;\n 分号加回车符 隔开
 * #end
 * <p>
 */
public class RunSqlDirective extends SqlParaDirective {
    private boolean isRuntimeException = false;

    private void initIsRuntimeException() {
        int k0 = 0;
        if (exprArray == null || exprArray.length == k0) {
            return;
        }
        if (exprArray[k0] instanceof Const) {
            isRuntimeException = ((Const) exprArray[k0]).isTrue();
            return;
        }
        int k1 = 1;
        if (exprArray.length == k1) {
            return;
        }
        if (exprArray[k1] instanceof Const) {
            isRuntimeException = ((Const) exprArray[k1]).isTrue();
        }
    }

    private static final Object[] NULL_PARA_ARRAY = new Object[0];
    @Override
    protected Object getData(SqlPara sqlPara) {
        setDefaultName("result");
        initIsRuntimeException();

        String sql = sqlPara.getSql();
        Object[] paras = sqlPara.getPara();
        //单行SQL
        if (!sql.contains(";")){
            return update(sql, paras);
        }
        String[] sqlArr = sql.replaceAll(";\r\n", ";\n").split(";\n");
        if (sqlArr.length == 1){
            return update(sql, paras);
        }
        //多行SQL
        List<Integer> ret = new ArrayList<>(sqlArr.length);
        int from = 0;
        int to;
        Object[] para;
        for (String s : sqlArr) {
            if (StrKit.isBlank(s)) {
                continue;
            }
            int count = count(s);
            if (count == 0) {
                para = NULL_PARA_ARRAY;
            } else {
                to = from + count;
                para = Arrays.copyOfRange(paras, from, to);
                from = to;
            }
            ret.add(update(s, para));
        }
        return ret;
    }

    private static int count(String s) {
        int to = 0;
        int length = s.length();
        for (int i = 0; i < length; i++) {
            if (s.charAt(i) == '?') {
                to++;
            }
        }
        return to;
    }

    private int update(String sql, Object[] para) {
        if (StrKit.isBlank(sql)){
            return 0;
        }
        try {
            return db().update(sql, para);
        }catch (ActiveRecordException e){
            if (isRuntimeException){
                write(sql);
                write(" 执行失败:");
                write(e.getMessage());
                write(" 。跳过继续执行。\n");
            }else {
                throw e;
            }
        }
        return 0;
    }
}

package com.momathink.common.plugin.activerecord.directive;

import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.template.Directive;
import com.jfinal.template.Env;
import com.jfinal.template.TemplateException;
import com.jfinal.template.expr.ast.Expr;
import com.jfinal.template.expr.ast.ExprList;
import com.jfinal.template.expr.ast.Id;
import com.jfinal.template.io.CharWriter;
import com.jfinal.template.io.FastStringWriter;
import com.jfinal.template.io.Writer;
import com.jfinal.template.stat.Scope;

import java.io.IOException;

public abstract class SqlParaDirective extends Directive {
    protected Expr[] exprArray;
    protected String name;
    private Writer writer;

    //SqlKit.SQL_PARA_KEY
    protected static final String SQL_PARA_KEY = "_SQL_PARA_";
    private static final ThreadLocal<DbPro> TL = new ThreadLocal<>();

    @Override
    public void setExprList(ExprList exprList) {
        exprArray = exprList.getExprArray();
        if (exprArray.length == 0) {
            return;
        }
        if ((exprArray[0] instanceof Id)) {
            this.name = ((Id) exprArray[0]).getId();
        }
    }

    @Override
    public void exec(Env env, Scope scope, Writer writer) {
        this.writer = writer;
        SqlPara sqlPara = new SqlPara();
        //放入数据域中
        scope.setLocal(SQL_PARA_KEY, sqlPara);

        //渲染SQL
        CharWriter charWriter = new CharWriter(64);
        FastStringWriter fsw = new FastStringWriter();
        charWriter.init(fsw);
        try {
            stat.exec(env, scope, charWriter);
        } finally {
            charWriter.close();
        }

        //移除sqlPara对象，避免污染Map
        scope.removeLocal(SQL_PARA_KEY);
        sqlPara.setSql(fsw.toString());
        Object data = getData(sqlPara);
        //放置返回结果
        scope.set(name, data);
    }

    @Override
    public boolean hasEnd() {
        return true;
    }

    protected abstract Object getData(SqlPara sqlPara);

    protected void setDefaultName(String name) {
        if (this.name == null) {
            this.name = name;
        }
    }

    protected void write(String str) {
        try {
            writer.write(str, 0, str.length());
        } catch (IOException e) {
            throw new TemplateException(e.getMessage(), location, e);
        }
    }

    public static DbPro db(){
        return TL.get();
    }

    public static void setDb(DbPro db){
        TL.set(db);
    }

    public static void remove(){
        TL.remove();
    }
}

package com.momathink.common.plugin.activerecord.directive;

import com.jfinal.template.Directive;
import com.jfinal.template.Env;
import com.jfinal.template.expr.ast.Expr;
import com.jfinal.template.expr.ast.ExprList;
import com.jfinal.template.expr.ast.Id;
import com.jfinal.template.io.Writer;
import com.jfinal.template.stat.Scope;

/**
 * #try(默认值exception可设置)
 * 包裹代码
 * #end
 * #if(exception)
 * #(exception.getMessage())
 * #end
 */
public class TryDirective extends Directive {
    protected String exceptionName = "exception";
    protected String name;

    @Override
    public void setExprList(ExprList exprList) {
        Expr[] exprArray = exprList.getExprArray();
        if (exprArray.length == 0) {
            return;
        }
        if ((exprArray[0] instanceof Id)) {
            this.name = ((Id) exprArray[0]).getId();
        }
    }

    @Override
    public void exec(Env env, Scope scope, Writer writer) {
        try {
            stat.exec(env, scope, writer);
        } catch (Exception e) {
            setDefaultName(exceptionName);
            scope.set(this.name, e);
        }
    }

    @Override
    public boolean hasEnd() {
        return true;
    }

    protected void setDefaultName(String name) {
        if (this.name == null) {
            this.name = name;
        }
    }
}

再附加一个 UpgradeSqlKit 工具类是我们SaaS系统在用的，这个与 UpgradeSqlPlugin 选一个用就可以了，大部分业务应该只有一两个数据源。

package com.momathink.common.plugin.activerecord;

import com.jfinal.kit.PathKit;
import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.ActiveRecordException;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.sql.SqlKit;
import com.jfinal.template.Engine;
import com.jfinal.template.Template;
import com.momathink.common.plugin.activerecord.directive.*;

import java.io.File;
import java.util.*;

/**
 * 数据库升级工具<br/>
 *
 * 该工具与 UpgradeSqlPlugin 的区别：<br/>
 *
 * UpgradeSqlKit 适合大量数据源,重复利用创建的SQL模板。例：
 *      static UpgradeSqlKit usk = new UpgradeSqlKit();
 *      usk.run(db);
 *      该DbPro对象可被高度定制，比如指向不同的数据源等业务<br/>
 *
 * UpgradeSqlPlugin 适合只有一两个数据源的时候，项目启动时当做插件配置后即可使用<br/>
 *
 * @author  杜福忠
 */
public class UpgradeSqlKit {
    private static final Log log = Log.getLog(UpgradeSqlKit.class);
    private Engine engine;
    private String tableName = "upgrade_sql_config_log";
    private String folder = "/upgrade_sql";
    private final LinkedHashMap<String, Template> sqlTpl = new LinkedHashMap<>();

    public UpgradeSqlKit() {
        configEngine();
        configSql();
    }

    public void run(DbPro db){
        DbPro dbPro = SqlParaDirective.db();
        SqlParaDirective.setDb(db);
        try{
            ArrayList<String> existing = existing(db);
            for (Map.Entry<String, Template> en : sqlTpl.entrySet()) {
                if (! existing.contains(en.getKey())){
                    run(db, en.getKey(), en.getValue());
                }
            }
        }finally {
            if (dbPro != null){
                SqlParaDirective.setDb(dbPro);
            }else {
                SqlParaDirective.remove();
            }
        }
    }

    private void run(DbPro db, String sqlName, Template template) {
        log.info("数据库执行升级文件：" + sqlName);
        long t = System.currentTimeMillis();

        final String[] ret = {"ok", null};
        db.tx(() -> {
            try{
                ret[1] = template.renderToString();
                return true;
            }catch (Exception e){
                ret[0] = "fail";
                ret[1] = e.getMessage();
                return false;
            }
        });
        saveRecord(db, sqlName, ret[0], t, ret[1]);
    }

    protected void saveRecord(DbPro db, String sqlName, String status, long t, String logStr) {
        if (logStr != null) {
            logStr = logStr.trim();
        }
        Record r = new Record();
        r.set("sqlName", sqlName);
        r.set("status", status);
        r.set("runDate", new Date());
        r.set("duration", System.currentTimeMillis() - t);
        r.set("log", logStr);
        db.save(tableName, r);
    }

    private ArrayList<String> existing(DbPro db) {
        ArrayList<String> existing;
        try{
            List<Record> list = db.find("SELECT sqlName FROM " + tableName + " WHERE state = 'ok'");
            existing = new ArrayList<>(list.size());
            for (Record r : list) {
                existing.add(r.getStr("sqlName"));
            }
        }catch (ActiveRecordException e) {
            //建表
            initTable(db);
            existing = new ArrayList<>(0);
        }
        return existing;
    }

    protected void initTable(DbPro db) {
        db.update("CREATE TABLE `" + tableName + "`  ( " +//
                "  `id` int(11) NOT NULL AUTO_INCREMENT, " +//
                "  `sqlName` varchar(255) NOT NULL, " +//
                "  `status` varchar(10) NULL, " +//
                "  `runDate` datetime NULL, " +//
                "  `duration` int(11) NULL, " +//
                "  `log` longtext NULL, " +//
                "  PRIMARY KEY (`id`) " +//
                ")");
    }

    private void configSql() {
        File files = getFolderPath();
        if (! files.isDirectory()){
            return;
        }
        File[] listFiles = files.listFiles();
        if (listFiles == null || listFiles.length == 0) {
            return;
        }
        //名称排序
        Arrays.sort(listFiles);

        for (File file : listFiles) {
            //名称统一小写，避免平台问题
            String sqlName = file.getName().toLowerCase();
            if (file.isFile() && sqlName.endsWith(".sql")){
                sqlTpl.put(sqlName, engine.getTemplate(file.getName()));
            }
        }
    }

    private void configEngine() {
        SqlKit sqlKit = new SqlKit("UpgradeSqlKit");
        this.engine = sqlKit.getEngine();
        engine.setBaseTemplatePath(folder);
        engine.addDirective("try", TryDirective.class, true);
        engine.addDirective("find", FindDirective.class, true);
        engine.addDirective("findFirst", FindFirstDirective.class, true);
        engine.addDirective("runSql", RunSqlDirective.class, true);
    }

    public void setTableName(String tableName) {
        Objects.requireNonNull(tableName, "tableName 不能为null");
        this.tableName = tableName;
    }

    public void setFolder(String folder) {
        Objects.requireNonNull(folder, "folder 不能为null");
        if (! folder.startsWith("/")) {
            folder = "/" + folder;
        }
        this.folder = folder;
    }

    private File getFolderPath() {
        return new File(PathKit.getRootClassPath() + folder);
    }
}

有社友反馈分享代码集成到项目有困难，我把该代码加入到了官网的 JFinal demo for maven 5.0.0 项目中了，可以方便参考和复制下载：
https://gitee.com/bean80/jfinal-upgrade-sql-plugin

好了，分享完毕，有参考价值就点个赞呗~ 有特殊业务自行改造吧~

项目：JFinal

33 25

JFinal使用技巧-项目启动时自动检查升级数据库内容插件

评论区

JFinal

杜福忠

JFinal

北流家园网

天晴

天晴

小徐同学

graphics

热门分享

扫码入社