Jenkins+ant+mysql 自动化构建脚本文件输出日志

        相信大多数金融行业的都有遇到过，想通过最简洁的方式去执行响应的脚本文件，比如测试人员、运维人员可以通过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创建项目

        

5）添加Invoke Ant

         6）执行结果