Managing the test-database with SOAP UI

As I blogged a few posts ago, we are using the free edition of soapUI to test our webservice and are very pleased about it.
In this post I will show you one way to solve a common problem when you are testing your software. It is managing the initial the state of the database you are using for your tests. Since you want to run your tests with a simple push of a button you don’t want to have to set the state of your database correctly manually. So what we did was we created a Java class that calls our sql scripts that sets the initial state of our database. For performing the actual scripts the class uses the SQLExec task of Ant.

Here is the code for the Java class:

package net.pascalalma.test.soapui;

import java.io.File;

import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;

import com.eviware.soapui.SoapUI;

/**
 * This class is a Helper class for the soapui scripts. Methods in this class are called via the Groovy scripts like
 * this : net.pascalalma.test.soapui.TestDataBase.cleanByHost(dbHost); (the SQL script are in the
 * lib dir !!). It contains methods for executing the SQL files (using the ant library) in order
 * to get a fresh database.
 *
 * We use the ant SQLExec task to do the actual sql work for us.
 */
public class TestDatabase {

   /**
    * Cleans the database.
    *
    * @param host the host name
    */
   public static void cleanByHost(String host) {
      clean("org.postgresql.Driver", "jdbc:postgresql://" + host + ":5432/my_db", "my_db", "db_passw", "../lib");
   }

  /**
    * Cleans the database.
    *
    * @param driver the JDBC driver classname
    * @param dbUrl the database URL
    * @param username the username
    * @param password the password
    * @param path the path where the SQL files are located
    */
   public static void clean(String driver, String dbUrl, String username, String password, String path) {

      try {
         SoapUI.log("Running DB clean on " + dbUrl + " using SQL scripts from " + path);
         SQLExec sql = new SQLExec();
         sql.setProject(new Project()); // set a dummy project or else log() will fail !
         sql.setDriver(driver);
         sql.setUrl(dbUrl);
         sql.setUserid(username);
         sql.setPassword(password);

         // conf/drop_schema.sql
         try {
            SoapUI.log(" - drop_schema.sql");
            sql.setSrc(new File(path + "/drop_schema.sql"));
            sql.execute();
         } catch (Exception e) {
            // continue anyway
            SoapUI.log(e.getMessage());
            SoapUI.log("continuing anyway");
         }

         SoapUI.log(" - install_schema.sql");
         sql.setSrc(new File(path + "/install_schema.sql"));
         sql.execute();

         SoapUI.log(" - schema_update_1.1.0.sql");
         sql.setSrc(new File(path + "/schema_update_1.1.0.sql"));
         sql.execute();

         SoapUI.log(" - load_data.sql");
         sql.setSrc(new File(path + "/load_data.sql"));
         sql.execute();

         SoapUI.log("Ready!");
      } catch (Exception e) {
         SoapUI.log("Got an exception! ");
         SoapUI.log(e.getMessage());
      }

   }
}

(I am sure you can think of much more/better ways to do this, but I only want to show you how to do this with soapUI)

After compiling this class you can add the TestDatabase.class file to the SOAP UI in the folder ‘$SOAP_UI_HOME$/bin/ext’, together with the necessary jar files, in this case the database driver (postgres-8.1.-404.jdbc3.jar’ and the Ant lib (ant-1.6.5.jar). We are using a PostgreSQL database for our application and have putted our sql-scripts in the ‘$SOAP_UI_HOME$/lib/’ folder.

When this is all in place we can add a test step (initialization) to our test run. We insert a Test Step: ‘Groovy script’ and add the following statement:
net.pascalalma.test.soapui.TestDatabase.cleanByHost("localhost");

If you now run this step you should see the output of it in the Groovy Test Log window. One thing you have to remind is that you put the correct version of your sql-scripts in the SOAP lib directory!

About Pascal Alma

Pascal is a senior IT consultant and has been working in IT since 1997. He is monitoring the latest development in new technologies (Mobile, Cloud, Big Data) closely and particularly interested in Java open source tool stacks, cloud related technologies like AWS and mobile development like building iOS apps with Swift. Specialties: Java/JEE/Spring Amazon AWS API/REST Big Data Continuous Delivery Swift/iOS
This entry was posted in SoapUI and tagged , . Bookmark the permalink.

3 Responses to Managing the test-database with SOAP UI

  1. Ole Matzura says:

    Cool stuff, Pascal.. Thank You!

    kind regards,

    /Ole
    eviware.com

  2. smougenot says:

    A good idea would be to have a look at dbunit. It can :
    -set your database
    -compare with an expected state
    -clean up after your test execution

    On big benefit of it is the data file (XML) used both for setup and clean operations. This mean you do not need sql writing.

    http://dbunit.sourceforge.net/

  3. Pascal Alma says:

    Yes, you’re right about DBUnit. I have used it some years ago at a project and we were able to load the test database based on data in MS Excel sheets. That was really cool!

Comments are closed.