17-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant
Problem
You wish to automatically run your unit tests for PL/SQL code and Java stored procedures each day and to write the results of the unit test to a file.
Solution
Use Apache's Ant build system to perform unit testing on your PL/SQL code. At the same time, Ant can build and compile any Java code that you will be using for your stored procedures. To do so, develop an Ant build script that will execute some SQL statements, automate your unit tests, and compile Java source into a directory. For example, the following build.xml file is an example of such a build that can be used to compile Java sources and execute unit tests on PL/SQL within a single Ant run.
<project name="MyPLSQLProject" default="unitTest" basedir=".">
<description>
PLSQL Unit Test and Application Builder
</description>
<!-- set global properties for this build -->
<property name="src" location="src"/>
<property name="build" location="build" value=”build”/>
<property name="user" value="myuser"/>
<property name="db_password" value="mypassword"/>
<property name="database.jdbc.url" value="jdbc:oracle:thin:@hostname:1521:database"/>
<target name="init">
<!-- Create the time stamp -->
<tstamp/>
<mkdir dir="${build}"/>
</target>
<target name="compile" depends="init"
description="compile the source " >
<!-- Compile the java code from ${src} into ${build} -->
<!-- This is where you place the code for your java stored procedures -->
<javac srcdir="${src}" destdir="${build}"/>
</target>
<target name="unitTest" depends="compile"
description="Execute PLSQL Unit Tests" >
<sql
driver = "oracle.jdbc.driver.OracleDriver"
url = "${database.jdbc.url}"
userid = "${user}"
password = "${db_password}"
print="true"
>
call utconfig.setfiledir('FILE_SYSTEM'),
call utconfig.setreporter('File'),
call utPLSQL.test('calc_quarter_hour'),
-- Closes the fle
call utfilereporter.close();
-- Returns output redirection to DBMS_OUTPUT
call utconfig.setreporter('Output'),
</sql>
</target>
</project>
This build script can be executed by issuing the ant command from within the terminal or command prompt. The results will resemble the following output.
juneau$ ant
Buildfile: /Users/juneau/Documents/PLSQL_Recipes/sources/17/build.xml
init:
compile:
[javac] /Users/juneau/Documents/PLSQL_Recipes/sources/17/build.xml:22: warning:
'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for
repeatable builds
unitTest:
[sql] Executing commands
[sql] 0 rows affected
[sql] 0 rows affected
[sql] 0 rows affected
[sql] 0 rows affected
[sql] 0 rows affected
[sql] 5 of 5 SQL statements executed successfully
BUILD SUCCESSFUL
Total time: 4 seconds
How It Works
Automating unit tests can be very helpful, especially if you are working on a project where there may be more than one developer contributing code. The Apache Ant build system is useful for automating builds and unit tests for Java projects. However, it can also be used to perform a myriad of other tasks, including issuing SQL statements, as seen in the solution to this recipe. Ant provides an entire build and unit test solution that is easy to use. To set up a build, all you need to do is install Ant on your machine and then create a build.xml file that consists of targets that Ant will use to build the project. Once you have created a build file, then simply open a command prompt or terminal and traverse into the directory containing your build file. Once in the directory, issue the ant command and it will automatically look for a file named build.xml that will provide Ant the sequence used for the build.
Ant uses simple logic to determine the order of sequence that will be used to execute the targets that are listed within the build.xml file. In the solution to this recipe, the build file contains three targets, init, compile, and unitTest. Ant will start the build by executing the target listed within the <project> tag as the default. In this case, the default target is unitTest.
<project name="MyPLSQLProject" default="unitTest" basedir=".">
The unitTest target contains a depends attribute, which lists the compile target. This tells Ant that the compile target should be executed first because unitTest depends upon its outcome.
<target name="unitTest" depends="compile"
description="Execute PLSQL Unit Tests" >
Consequently, the compile target depends upon the init target, so init will be executed before compile.
<target name="compile" depends="init"
description="compile the source " >
The order of target execution for the solution to this recipe will be the init target first, followed by the compile target, and lastly the unitTest target. The project tag also contains an attribute named basedir. This attribute tells Ant where the build files should be located. In the solution to this recipe, basedir contains a period “.” that tells Ant to use the current directory.
At the top of the build file, you can see that there is a <description> tag. This is used to provide a brief description of the tasks completed by the build file. There are also several <property> tags. These tags are used to define the variables that will be used within the build file. Each <property> tag contains a name attribute and either a value or location attribute.
<property name="src" location="src"/>
<property name="build" location="build" value=”build”/>
<property name="user" value="myuser"/>
<property name="db_password" value="mypassword"/>
<property name="database.jdbc.url" value="jdbc:oracle:thin:@hostname:1521:database"/>
The properties that use a value attribute are used to assign values to the property name, whereas the properties that contain location attributes are used to assign a location to the property name. Properties can be referenced within the build file by using the following syntax: “${property_name}”. As you can see from the solution to this recipe, each target within the build file consists of a number of tasks in the form of XML tags. The init target creates a timestamp by using the <tstamp/> tag, and it creates a directory using the <mkdir/> tag and passing the name of a directory to be created. In this case, the directory name will be named the same as the value that is assigned to the <property> tag that is named build.
<target name="init">
<!-- Create the time stamp -->
<tstamp/>
<mkdir dir="${build}"/>
</target>
The compile target is used to compile all of the Java sources contained in the project. All of the sources should reside within a named directory that is located in the base directory of the Ant project. The compile target contains a single task using the <javac> tag. This tag contains a src attribute that defines the location of the sources to be compiled, and a destdir attribute that tells Ant where to place the resulting Java class files. An Ant project that builds a Java project may contain only this task, but can build several hundred Java class files. In the solution to this recipe, and for most Ant uses with PL/SQL projects, however, the project will probably contain no Java source files or only a few at most. If a project contains no Java source files, then the target will be executed, but the <javac> task will do nothing since there are not any sources to be compiled.
<target name="compile" depends="init"
description="compile the source " >
<!-- Compile the java code from ${src} into ${build} -->
<!-- This is where you place the code for your java stored procedures -->
<javac srcdir="${src}" destdir="${build}"/>
</target>
The most important target in the solution to this recipe is the unitTest target. It consists of a single task using the <sql> tag. The sole purpose of the <sql> task is to execute SQL within a designated database. The <sql> tag contains a driver attribute that is used to list the JDBC driver for the target database, a url attribute used to define the JDBC URL for the target database, a userid and password attribute for defining the database username and password, and a print attribute that tells Ant whether to print the result sets from the SQL statements. In the solution to this recipe, the SQL that is required to execute the unit tests is contained within the <sql> opening and closing tags. This causes the unit tests to be executed as if you were issuing these statements at the SQL*Plus command prompt.
<target name="unitTest" depends="compile"
description="Execute PLSQL Unit Tests" >
<sql
driver = "oracle.jdbc.driver.OracleDriver"
url = "${database.jdbc.url}"
userid = "${user}"
password = "${db_password}"
print="true"
>
call utconfig.setfiledir('FILE_SYSTEM'),
call utconfig.setreporter('File'),
call utPLSQL.test('calc_quarter_hour'),
-- Closes the fle
call utfilereporter.close();
-- Returns output redirection to DBMS_OUTPUT
call utconfig.setreporter('Output'),
</sql>
</target>
To automate your Ant build, you will need to set up an operating system task that starts the Ant build. The task is very simple and needs to contain only very few lines. The following lines of code contain batch script for the Windows operating system that can be used to invoke the Ant build. This assumes that the java.exe executable is contained within the PATH environment variable.
cd C:/path_to_project_directory
ant
You will also need to ensure that the JDBC driver for the Oracle database is contained within your CLASSPATH. If you do not include the JDBC driver in the CLASSPATH, then you will receive an error when you try to execute the build. When the Ant build is executed, a file will be placed onto the database server in the location designated by the FILE_SYSTEM database directory. The file will contain the results of the unit test execution.
Ant is a complex build system that can be used for configuration and preparation of your builds and unit tests. It is a widely used build system, especially for organizations that do lots of Java development. As you can see, it is easy to use, but does contain complexity in that there are a number of different tasks and attributes that can be used. This recipe does not even scratch the surface of everything that Ant can do. However, there are lots of sources for documentation on Ant that can be found online as well as in book format. To learn more about Ant, you can start by reading the online documentation that can be found at: http://ant.apache.org/manual/.