2023-06-04 18:53

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

(相关资料图)

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

UpgradeSqlPlugin是JF插件：

packagecom.momathink.common.plugin.activerecord;importcom.jfinal.kit.LogKit;importcom.jfinal.kit.PathKit;importcom.jfinal.log.Log;importcom.jfinal.plugin.IPlugin;importcom.jfinal.plugin.activerecord.*;importcom.jfinal.template.Engine;importcom.jfinal.template.Template;importcom.momathink.common.plugin.activerecord.directive.*;importjava.io.File;importjava.util.*;/***项目启动时数据库升级插件*@author杜福忠*/publicclassUpgradeSqlPluginimplementsIPlugin{privateLoglog=Log.getLog(UpgradeSqlPlugin.class);privateActiveRecordPluginarp;privateDbProdb;privateConfigconfig;privateEngineengine;privateStringtableName="upgrade_sql_config_log";privateStringfolder="/upgrade_sql";privateArrayListexisting=null;privatebooleanimmediatelyStop=true;publicUpgradeSqlPlugin(IDataSourceProviderdataSource){this.arp=newActiveRecordPlugin(getName(),dataSource);}@SuppressWarnings("unused")publicUpgradeSqlPlugin(IDataSourceProviderdataSource,inttransactionLevel){this.arp=newActiveRecordPlugin(getName(),dataSource,transactionLevel);}privatestaticintcount=0;protectedsynchronizedstaticStringgetName(){count++;return"UpgradeSqlPlugin_"+count;}@Overridepublicbooleanstop(){db=null;config=null;engine=null;existing=null;if(this.arp!=null){this.arp.stop();this.arp=null;}SqlParaDirective.remove();log=null;returntrue;}@Overridepublicbooleanstart(){Objects.requireNonNull(this.arp,"UpgradeSqlPlugin设计上只支持start一次");this.arp.start();try{configDb();configSql();}catch(Exceptione){LogKit.error("升级数据库脚本异常：",e);returnfalse;}//执行完升级SQL立即注销节约内存if(this.immediatelyStop){stop();}returntrue;}@SuppressWarnings("unused")publicUpgradeSqlPluginsetImmediatelyStop(booleanimmediatelyStop){this.immediatelyStop=immediatelyStop;returnthis;}privatevoidconfigSql(){Filefiles=getFolderPath();if(!files.isDirectory()){return;}File[]listFiles=files.listFiles();if(listFiles==null||listFiles.length==0){return;}//名称排序Arrays.sort(listFiles);for(Filefile:listFiles){//名称统一小写，避免平台问题StringsqlName=file.getName().toLowerCase();if(file.isFile()&&sqlName.endsWith(".sql")&&!existing.contains(sqlName)){runSql(file,sqlName);}}}privateFilegetFolderPath(){returnnewFile(PathKit.getRootClassPath()+folder);}privatevoidrunSql(Filefile,StringsqlName){Templatetemplate=engine.getTemplate(file.getName());log.info("数据库执行升级文件："+sqlName);longt=System.currentTimeMillis();finalString[]logStr={null};db.tx(()->{logStr[0]=template.renderToString();returntrue;});saveRecord(sqlName,t,logStr[0]);}protectedvoidsaveRecord(StringsqlName,longt,StringlogStr){if(logStr!=null){logStr=logStr.trim();}Recordr=newRecord();r.set("sqlName",sqlName);r.set("runDate",newDate());r.set("duration",System.currentTimeMillis()-t);r.set("log",logStr);db.save(tableName,r);}privatevoidconfigDb(){this.config=arp.getConfig();Objects.requireNonNull(config,"ActiveRecordPlugin需要先启动");this.db=Db.use(config.getName());SqlParaDirective.setDb(this.db);configEngine();try{Listlist=db.find("SELECTsqlNameFROM".concat(tableName));this.existing=newArrayList<>(list.size());for(Recordr:list){this.existing.add(r.getStr("sqlName"));}}catch(ActiveRecordExceptione){//建表initTable();this.existing=newArrayList<>(0);}}protectedvoidinitTable(){db.update("CREATETABLE`"+tableName+"`("+//"`id`int(11)NOTNULLAUTO_INCREMENT,"+//"`sqlName`varchar(255)NOTNULL,"+//"`runDate`datetimeNULL,"+//"`duration`int(11)NULL,"+//"`log`longtextNULL,"+//"PRIMARYKEY(`id`)"+//")");}privatevoidconfigEngine(){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);}publicvoidsetTableName(StringtableName){Objects.requireNonNull(tableName,"tableName不能为null");this.tableName=tableName;}publicvoidsetFolder(Stringfolder){Objects.requireNonNull(folder,"folder不能为null");if(!folder.startsWith("/")){folder="/"+folder;}this.folder=folder;}publicDbProgetDb(){returndb;}}

使用姿势：

注册插件：/doc/2-5

publicvoidconfigPlugin(Pluginsme){DruidPlugindp=newDruidPlugin(jdbcUrl,userName,password);me.add(dp);//自动检查升级数据库me.add(newUpgradeSqlPlugin(dp));ActiveRecordPluginarp=newActiveRecordPlugin(DbKit.MAIN_CONFIG_NAME,dp);arp.addMapping("user",User.class);me.add(arp);}

数据库连接账户需要使用 有建表 修改表 等权限的账户，一般升级脚本里面就是要建表和改变表结构的。如果担心业务数据账户安全 ，可以使用独立的DruidPlugin，和业务数据库账户分开，高权限的账户只做升级操作，升级完立即stop掉就可以了。* 注意大家一般使用ActiveRecordPlugin为主对象时，也就是业务里面直接写 Db.findXXX 等写法时，注意configName 加上main，也就是如下：

newActiveRecordPlugin(DbKit.MAIN_CONFIG_NAME,dp);

因为UpgradeSqlPlugin里面会自建一个ActiveRecordPlugin用于SQL模板里面执行SQL，并且一般都是先启动的，因为要在业务ActiveRecordPlugin之前启动，业务ActiveRecordPlugin里面一般会做数据库表关系映射等等业务。所以要把configName 加上main 就不会被替掉了。

编写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()ALTERTABLExxxx#end

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

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

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

packagecom.momathink.common.plugin.activerecord.directive;importcom.jfinal.plugin.activerecord.SqlPara;/***#find指令用于需要查询结果后代码处理的*
*定义：*#find(返回结果的变量名默认是list)*在此是SQL语句*#end*
*/publicclassFindDirectiveextendsSqlParaDirective{@OverrideprotectedObjectgetData(SqlParasqlPara){setDefaultName("list");returndb().find(sqlPara);}}

packagecom.momathink.common.plugin.activerecord.directive;importcom.jfinal.plugin.activerecord.SqlPara;/***#findFirst指令用于需要查询结果后代码处理的*
*定义：*#findFirst(返回结果的变量名默认是record)*在此是SQL语句*#end*
*/publicclassFindFirstDirectiveextendsSqlParaDirective{@OverrideprotectedObjectgetData(SqlParasqlPara){setDefaultName("record");returndb().findFirst(sqlPara);}}

packagecom.momathink.common.plugin.activerecord.directive;importcom.jfinal.kit.StrKit;importcom.jfinal.log.Log;importcom.jfinal.plugin.activerecord.ActiveRecordException;importcom.jfinal.plugin.activerecord.SqlPara;importcom.jfinal.template.expr.ast.Const;importjava.util.ArrayList;importjava.util.Arrays;importjava.util.List;/***#sqlPara指令方便定义SqlParaSqlPara*
*定义：*#runSql(受影响行数返回结果的变量名默认是result,true)特殊场景：给true参数时代表运行异常时可继续执行*在此是SQL语句，多条用;\n分号加回车符隔开*#end*
*/publicclassRunSqlDirectiveextendsSqlParaDirective{privatestaticfinalLoglog=Log.getLog(RunSqlDirective.class);privatebooleanisRuntimeException=false;privatevoidinitIsRuntimeException(){intk0=0;if(exprArray==null||exprArray.length==k0){return;}if(exprArray[k0]instanceofConst){isRuntimeException=((Const)exprArray[k0]).isTrue();return;}intk1=1;if(exprArray.length==k1){return;}if(exprArray[k1]instanceofConst){isRuntimeException=((Const)exprArray[k1]).isTrue();}}privatestaticfinalObject[]NULL_PARA_ARRAY=newObject[0];@OverrideprotectedObjectgetData(SqlParasqlPara){setDefaultName("result");initIsRuntimeException();Stringsql=sqlPara.getSql();Object[]paras=sqlPara.getPara();//单行SQLif(!sql.contains(";")){returnupdate(sql,paras);}String[]sqlArr=sql.replaceAll(";\r\n",";\n").split(";\n");if(sqlArr.length==1){returnupdate(sql,paras);}//多行SQLListret=newArrayList<>(sqlArr.length);intfrom=0;intto;Object[]para;for(Strings:sqlArr){if(StrKit.isBlank(s)){continue;}intcount=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));}returnret;}privatestaticintcount(Strings){intto=0;intlength=s.length();for(inti=0;i
packagecom.momathink.common.plugin.activerecord.directive;importcom.jfinal.plugin.activerecord.DbPro;importcom.jfinal.plugin.activerecord.SqlPara;importcom.jfinal.template.Directive;importcom.jfinal.template.Env;importcom.jfinal.template.expr.ast.Expr;importcom.jfinal.template.expr.ast.ExprList;importcom.jfinal.template.expr.ast.Id;importcom.jfinal.template.io.CharWriter;importcom.jfinal.template.io.FastStringWriter;importcom.jfinal.template.io.Writer;importcom.jfinal.template.stat.Scope;publicabstractclassSqlParaDirectiveextendsDirective{protectedExpr[]exprArray;protectedStringname;//SqlKit.SQL_PARA_KEYprotectedstaticfinalStringSQL_PARA_KEY="_SQL_PARA_";privatestaticfinalThreadLocalTL=newThreadLocal<>();@OverridepublicvoidsetExprList(ExprListexprList){exprArray=exprList.getExprArray();if(exprArray.length==0){return;}if((exprArray[0]instanceofId)){this.name=((Id)exprArray[0]).getId();}}@Overridepublicvoidexec(Envenv,Scopescope,Writerwriter){SqlParasqlPara=newSqlPara();//放入数据域中scope.setLocal(SQL_PARA_KEY,sqlPara);//渲染SQLCharWritercharWriter=newCharWriter(64);FastStringWriterfsw=newFastStringWriter();charWriter.init(fsw);try{stat.exec(env,scope,charWriter);}finally{charWriter.close();}//移除sqlPara对象，避免污染Mapscope.removeLocal(SQL_PARA_KEY);sqlPara.setSql(fsw.toString());Objectdata=getData(sqlPara);//放置返回结果scope.set(name,data);}@OverridepublicbooleanhasEnd(){returntrue;}protectedabstractObjectgetData(SqlParasqlPara);protectedvoidsetDefaultName(Stringname){if(this.name==null){this.name=name;}}publicstaticDbProdb(){returnTL.get();}publicstaticvoidsetDb(DbProdb){TL.set(db);}publicstaticvoidremove(){TL.remove();}}
packagecom.momathink.common.plugin.activerecord.directive;importcom.jfinal.template.Directive;importcom.jfinal.template.Env;importcom.jfinal.template.expr.ast.Expr;importcom.jfinal.template.expr.ast.ExprList;importcom.jfinal.template.expr.ast.Id;importcom.jfinal.template.io.Writer;importcom.jfinal.template.stat.Scope;/***#try(默认值exception可设置)*包裹代码*#end*#if(exception)*#(exception.getMessage())*#end*/publicclassTryDirectiveextendsDirective{protectedStringexceptionName="exception";protectedStringname;@OverridepublicvoidsetExprList(ExprListexprList){Expr[]exprArray=exprList.getExprArray();if(exprArray.length==0){return;}if((exprArray[0]instanceofId)){this.name=((Id)exprArray[0]).getId();}}@Overridepublicvoidexec(Envenv,Scopescope,Writerwriter){try{stat.exec(env,scope,writer);}catch(Exceptione){setDefaultName(exceptionName);scope.set(this.name,e);}}@OverridepublicbooleanhasEnd(){returntrue;}protectedvoidsetDefaultName(Stringname){if(this.name==null){this.name=name;}}}
						  

关键词：