相信大多数金融行业的都有遇到过,想通过最简洁的方式去执行响应的脚本文件,比如测试人员、运维人员可以通过Jenkins进行构建SQL执行器,就能将SQL文件进行执行,并且输出相应的日志。
通过这种方式,开发人员只需将SQL文件,放在指定的目录,测试人员只需要在Jenkins中进行构建执行就行。
这套方案我在网上找了很多文章,都没有给出完整的Demo,都是一些代码块。我相信有些同学是不动ant,并且不懂build.xml的相关节点,那么今天我把我的搭建经验记录下来,并且与大家分享,不喜勿喷。
涉及的相关JAR包如下:
ant-contrib-1.0b3.jar
mysql-connector-java-8.0.15.jar
apache-ant-1.9.16-bin.zip
下载地址:阿里云盘分享
Ant下载地址:Apache Ant - Binary Distributions
1)编写build.xml
新建一个build.xml
<?xml version="1.0"?>
<project xmlns='antlib:org.apache.tools.ant'>
<taskdef resource="net/sf/antcontrib/antlib.xml" classpath="ant-contrib-1.0b3.jar"/>
<path id="project.class.path">
<fileset dir="/dclife/mobile_team_sql/map/ant-script/lib" includes="*.jar" />
</path>
<property name="encoding" value="UTF-8" /><!--UTF-8-->
<property file="build.properties" /><!--引入文件-->
<target name="runSqlInFolder">
<echo>Run the SQL at Folder: ${sqlfolder}</echo>
<echo>DB URL: ${db.url}</echo>
<echo>DB User: ${db.user}</echo>
<trycatch property="errMsg">
<try>
<for param="folder">
<path>
<sort xmlns:rcmp="antlib:org.apache.tools.ant.types.resources.comparators">
<dirset dir="${sqlfolder}" includes="*" />
</sort>
</path>
<sequential>
<echo>SQL Folder: @{folder}</echo>
<for param="file">
<path>
<sort xmlns:rcmp="antlib:org.apache.tools.ant.types.resources.comparators">
<fileset dir="@{folder}" includes="*.sql" casesensitive="false"/>
</sort>
</path>
<sequential>
<echo>SQL: @{file}</echo>
<execsql
dbUrl="${db.url}"
dbuser="${db.user}"
dbpwd="${db.pwd}"
sqlfile="@{file}"
logfile="${sqllogdir}/${sqllogfile}_info.log"/>
</sequential>
</for>
<move file="@{folder}" todir="${sqlbakdir}"/>
</sequential>
</for>
<echo>Finished running all SQL</echo>
<echo>File moved to backup folder:</echo>
<echo>${sqlbakdir}</echo>
</try>
<catch>
<echo>Error found when running SQL</echo>
<echo>Log file can be found in:</echo>
<echo>${sqllogdir}</echo>
<move file="${sqllogfile}_info.log" todir="${sqllogdir}"/>
<fail>Error Occur</fail>
</catch>
<finally>
</finally>
</trycatch>
</target>
<macrodef name="execsql" description="Run single SQL file.">
<attribute name="dbUrl" description="Host Name/ IP of the DB"/>
<attribute name="dbuser" description="DB User name"/>
<attribute name="dbpwd" description="DB Password"/>
<attribute name="sqlfile" description="SQL file to be run"/>
<attribute name="logfile" default="sql.log" description="Log file"/>
<sequential>
<echo>Log file @{logfile}</echo>
<record name="@{logfile}" action="start"/>
<sql driver="${driver}"
url="@{dbUrl}"
userid="@{dbuser}"
password="@{dbpwd}"
encoding="${encoding}"
src="@{sqlfile}"
classpathref="project.class.path"
>
</sql>
<record name="@{logfile}" action="stop"/>
</sequential>
</macrodef>
</project>
2)创建build.properties
db.url=jdbc:mysql://IP地址:3306/库名?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
db.user=数据库用户
db.pwd=数据库密码
driver=com.mysql.cj.jdbc.Driver
Sqllogfile=logs/info.log
3)Linux安装Ant
将下载好的apache-ant-1.9.16-bin.zip的压缩包,上传至linux服务器,并且解压该文件夹
unzip apache-ant-1.9.16-bin.zip
4)jenkins创建项目