Data driven testing using a test data database

In a previous post I explained how to set up a data driven test in Selenium Webdriver using data from an Excel worksheet. However, you might have your potential test data stored in a database rather than in Excel. In this post, I will show you how to set up and run a data driven test using data from a database. In this example, I will use a REST webservice as the object to be tested and therefore won’t use Selenium Webdriver, but you can easily apply this approach to your Selenium tests as well.

First, we need a table containing our test data. For this example, I have created a simple table in a local MySQL installation, containing the following data:

The table containing our test data

As our test object, I am going to use a public REST service API that returns, amongst other data, the city and state corresponding to a US zip code in JSON format (click here for an example).

Now, let’s create a test that calls this API for all zipcodes in our test data table and verify whether the city and state returned by the service match the expected values stored in our table.

To do that, we first need to create a connection to our database table, retrieve the test data from it and call our test method for every row in the table. This is done using the following piece of code:

public static void runTest() {
		
	try {
			
		// Retrieve database connection properties from the properties file
		String driver = DBDrivenProperties.getProperty("db.driver");
		String dburl = DBDrivenProperties.getProperty("db.url");
		String dbname = DBDrivenProperties.getProperty("db.dbname");
		String dbquery = DBDrivenProperties.getProperty("db.query");
		String dbuser = DBDrivenProperties.getProperty("db.username");
		String dbpassword = DBDrivenProperties.getProperty("db.password");
			
		// Load the MySQL JDBC driver
		Class.forName(driver);
			
		// Create a connection to the MySQL database
		Connection conn = DriverManager.getConnection(dburl + dbname, dbuser, dbpassword);
			
		// Create a statement to be executed
		Statement stmt = conn.createStatement();
			
		// Execute the query
		ResultSet rs = stmt.executeQuery(dbquery);
			
		// Loop through the query results and run the REST service test for every row
		while (rs.next()) {
			String zipcode = rs.getString("zipcode");
			String city = rs.getString("city");
			String state = rs.getString("state");
			try {
				testService(zipcode,city,state);
			} catch (IOException | JSONException e) {
				System.out.println(e.toString());
			}
		}
			
		// Close the database connection
		conn.close();
			
	} catch (ClassNotFoundException | SQLException e) {
		System.out.println(e.toString());
	}		
}

For this method to run, we need to add a MySQL JDBC driver to the classpath of our project, otherwise we get an error when we try to load the driver. You can get yours here.

For clarity, I have put all the configuration data that is needed to connect to the database and get the results from it in a separate properties file that looks like this:

The properties file

You can find the code that I use to retrieve the property values from this file in the project files. See the end of this post for a link to it.

Now that we have retrieved our test data from the database, let’s write the actual test method. This is very similar to the one I used in a previous post:

public static void testService(String zipcode, String city, String state) throws IOException, JSONException {
		
	System.out.println("Validating response for " + DBDrivenProperties.getProperty("rest.url") + zipcode + "...");
		
	// Retrieve the base URL for the REST service and append the zipcode parameter
	String restURL = DBDrivenProperties.getProperty("rest.url") + zipcode;
		
	// Call the REST service and store the response
	HttpUriRequest request = new HttpGet(restURL);
	HttpResponse httpResponse = HttpClientBuilder.create().build().execute(request);

	// Convert the response to a String format
	String result = EntityUtils.toString(httpResponse.getEntity());

	// Convert the result as a String to a JSON object
	JSONObject jo = new JSONObject(result);
		
	// Get the array containing the places that correspond to the requested zipcode
	JSONArray ja = jo.getJSONArray("places");
		
	// Assert that the values returned by the REST service match the expected values in our database
	Assert.assertEquals(city, ja.getJSONObject(0).getString("place name"));
	Assert.assertEquals(state, ja.getJSONObject(0).getString("state"));	
}

The only thing that is new compared to the code in my previous post on testing REST webservices is that in this response, the elements I am interested in (being the city and state corresponding to the zip code in the request) are stored within a result array called places. To retrieve these, I need to dig one level deeper into my JSON response object using the JSONArray object. Other than that, the test method is pretty straightforward.

One warning I need to address before you go ahead and create your own tests using test data from a database is that I used a very broad query in this example (a simple SELECT * FROM table). This potentially generates a lot of results and subsequently a lot of test iterations. Even though this gives great test coverage, it also takes more time to execute all test cases. Especially when you use this approach in combination with Selenium Webdriver, you might want to use a narrower query (or limit the number of results returned) to prevent your test from taking too long to finish.

The Eclipse project files including all code needed to get this to work can be downloaded here.

Testing RESTful webservices

With the world of IT systems and applications becoming more and more distributed, testing the interfaces between system components becomes ever more important. In a previous post I have introduced a very basic way to test a SOAP web service interface. However, nowadays, in the world of APIs, the REpresentational State Transfer or REST standard becomes increasingly popular due to its lightweight nature. This article introduces a way to test RESTful APIs in a simple yet effective manner, and in a way that integrates smoothly with your existing Java-based testing frameworks, such as those based on Selenium Webdriver.

RESTful APIs come in several varieties. In this post, we are going to see two of them: one returning XML responses (I used this one) and one returning JSON responses (I used this one). You’re welcome to click on either link to see the difference in response format.

Also, I have used some external libraries to get the code to work:

Verify the HTTP status code
The first thing we are going to test for our services is whether they respond to our requests at all. This means we are going to validate whether the HTTP response code for the REST service is correct. There way to do this is equal for both XML and JSON REST APIs:

public static void testStatusCode(String restURL) throws ClientProtocolException, IOException {

	HttpUriRequest request = new HttpGet(restURL);
	HttpResponse httpResponse = HttpClientBuilder.create().build().execute(request);
		
	Assert.assertEquals(httpResponse.getStatusLine().getStatusCode(),HttpStatus.SC_OK);
}

Pretty simple, right? As you probably know, calling a RESTful API is done by either sending a HTTP POST or a HTTP GET to a specific URL. This in contrast with SOAP web services, where you send a specific XML message to an endpoint. Then, retrieve the HTTP status code from the response and check whether this is OK (i.e., equal to HTTP 200).

Verify the response content type
Next, we are going to verify whether our web service sends us the content type we expect (either XML or JSON). Again, this can be done with just a couple of lines of code:

public static void testMimeType(String restURL, String expectedMimeType) throws ClientProtocolException, IOException {
		
	HttpUriRequest request = new HttpGet(restURL);
	HttpResponse httpResponse = HttpClientBuilder.create().build().execute(request);
		
	Assert.assertEquals(expectedMimeType,ContentType.getOrDefault(httpResponse.getEntity()).getMimeType());
}

Again, just call the RESTful API URL and retrieve the information you need from the response. In this case, we are interested in the MIME type, which should be either ‘application/xml’ or ‘application/json’, depending on the URL we use.

Verify the response content
Now that we have verified whether our services return correct responses with the correct MIME type, it’s time to look at the actual content of the response. This is where testing XML RESTful APIs differs from testing JSON RESTful APIs, since we need to parse the response to extract the elements and element values we’re interested in.

For XML, we can do this using the following piece of code:

public static void testContent(String restURL, String element, String expectedValue) throws ClientProtocolException, IOException, SAXException, ParserConfigurationException {
		
	Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(restURL);
	NodeList nodelist = doc.getElementsByTagName(element);
		
	Assert.assertEquals(expectedValue,nodelist.item(0).getTextContent());		
}

Note that we use a different way to retrieve the result from the REST URL as we need to parse it as actual XML. After we’ve done that, we simply look for the XML element we’re interested in and compare its value to the value we expect.

For JSON, we can verify element values as follows:

public static void testContentJSON(String restURL, String element, String expectedValue) throws ClientProtocolException, IOException, SAXException, ParserConfigurationException, JSONException {

	HttpUriRequest request = new HttpGet(restURL);
	HttpResponse httpResponse = HttpClientBuilder.create().build().execute(request);

	// Convert the response to a String format
	String result = EntityUtils.toString(httpResponse.getEntity());

	// Convert the result as a String to a JSON object
	JSONObject jo = new JSONObject(result);

	Assert.assertEquals(expectedValue, jo.getString(element));
}

Here, we need to take an intermediate step to parse the response to a sensible format. First, we convert the HttpResponse to a String, then we convert this String to a JSONObject. From this JSONObject we can then extract the value for a specific element and compare it to an expected value.

The above examples should get you started nicely when you’re asked to include testing RESTful web services in your automated testing solutions.

Again, happy testing!

An example Eclipse project where I use the code above can be downloaded here.

How to compare WSDL versions in your automated test framework

A colleague of mine posed an interesting question last week. He had a test setup with three different machines on which his application under test was installed and deployed. He wanted to make sure in his test that the web service interface offered by these deployments was exactly the same by comparing the WSDLs associated with each installation. However, the tool he used (Parasoft SOAtest) only supports regression testing of single WSDL instances, i.e., it can validate whether a certain WSDL has been changed over time, but it cannot compare two or more different WSDL instances.

Luckily, SOAtest supports extension of its functionality using scripting, and I found a nice Java API that would do exactly what he asked. In this post, I’ll show you how this is done in Java. In SOAtest, I did it with a Jython script that imported and used the appropriate Java classes, but apart from syntax the solution is the same.

The Java API I used can be found here. The piece of code that executes the comparison is very straightforward:

private static void compareWSDL(){
		
	// configure the log4j logger
	BasicConfigurator.configure();
		
	// create a new wsdl parser
	WSDLParser parser = new WSDLParser();

	// parse both wsdl documents
	Definitions wsdl1 = parser.parse("Z:\\Documents\\Bas\\wsdlcompare\\ParaBank.wsdl");
	Definitions wsdl2 = parser.parse("Z:\\Documents\\Bas\\wsdlcompare\\ParaBank2.wsdl");

	// compare the wsdl documents
	WsdlDiffGenerator diffGen = new WsdlDiffGenerator(wsdl1, wsdl2);
	List<Difference> lst = diffGen.compare();
		
	// write the differences to the console
	for (Difference diff : lst) {
		System.out.println(diff.dump());
	}
}

For this example, I used two locally stored copies of a WSDL document where I changed the definition of a single element (I removed the minOccurs=”0″ attribute). The API uses Log4J as the logging engine, so we need to initialize that in our code and add a log4j.properties file to our project:
Log4J properties file
When we run our code, we can see that the WSDL documents are compared successfully, and that the difference I injected by hand is detected nicely by the WSDL compare tool:
Console output for the WSDL comparison
A nice and clean answer to yet another automated testing question, just as it should be.

An example Eclipse project using the pattern described above can be downloaded here.