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.

Up and running with: Selendroid

This is the second article in our series on new, popular or otherwise interesting tools used in test automation. You can read all posts within this series by clicking here.

What is Selendroid?
Selendroid is a test automation framework for testing Android native and hybrid applications. Selendroid tests are written using the Selenium Webdriver client API, which allows for full integration with existing Selenium frameworks.

Where can I get Selendroid?
Selendroid can be downloaded from the Selendroid website.

How do I install and configure Selendroid?
Before you can start setting up Selendroid and writing tests, you need to download and install the latest Android SDK first. Clear instructions on how to do this can be found here. Make sure you also create at least one Android virtual device (AVD) and test whether it can be run properly, as we are going to need this virtual device to deploy our app under test and run our Selendroid tests on it.

NOTE: The instructions specify the creation of an AVD with the latest Android version (KitKat, API level 19). This is all wonderful, but if you’re (like me) working on a virtual machine or any other machine with limited resources, you’re probably better off creating an AVD based on Android Gingerbread (API level 10). You will need to install the correct SDK components for that API level using the Android SDK Manager. Oh, and it’s probably wise as well to use GPU emulation by enabling ‘Use host GPU’ in the AVD settings while you’re at it. This will make your test run much faster. Or, in my case, it will make your test run at all.

After you have successfully installed the Android SDK, you can install Selendroid following these instructions. When you’ve done so, start Selendroid and load the test app (the .apk file) they provide here. When you point your browser to

http://localhost:4444/wd/hub/status

you’ll see something similar to this:

Selendroid is running
As you can see, Selendroid is running and it has detected our newly created AVD.

Creating a first test script
Now we’re done setting up our environment we can go to the interesting part. To start mobile testing using Selendroid, create a new Java project, add the Selendroid and Selenium .jar files as dependencies and create the following script:

public static void runSelendroidTest() throws Exception {
		
	// specify test capabilities (your 'test environment')
	SelendroidCapabilities capa = new SelendroidCapabilities("io.selendroid.testapp:0.9.0");
		
	// explicitly state that we want to run our test on an Android API level 10 device
	capa.setPlatformVersion(DeviceTargetPlatform.ANDROID10);
		
	// explicitly state that we use an emulator (an AVD) for test execution rather than a physical device
	capa.setEmulator(true);

	// start a new WebDriver
	WebDriver driver = new SelendroidDriver(capa);
		
	// execute a very simple test
	WebElement inputField = driver.findElement(By.id("my_text_field"));
	Assert.assertEquals("true", inputField.getAttribute("enabled"));
	inputField.sendKeys("Selendroid");
	Assert.assertEquals("Selendroid", inputField.getText());
		
	// quit testing
	driver.quit();
		
}

The comments in the code really explain it all. You can instantly see that Selendroid tests are indeed very similar to regular Selenium tests, with the exception that they are run on an app that is deployed on an AVD rather than on a website that you access using a browser.

Running your test
Running your Selendroid tests is done just like you’d run Selenium tests, so there’s really no need to go into more detail on that here.

When you run your tests, you’ll see that an Android emulator (the AVD) is started, the app is loaded, tests are executed and the emulator is closed again. Selendroid can also be used to execute tests on apps that are deployed on physical Android devices connected to your machine, something I haven’t tried myself.

A screenshot of the test app

Useful features
Selendroid offers a lot of to the tester that wants to create more useful and more complex tests. For example, you can very accurately emulate a user swipe action from right to left using the following code snippet:

WebElement pages = driver.findElement(By.id("some_id"));
TouchActions flick = new TouchActions(driver).flick(pages, -100, 0, 0);
flick.perform();

Using the instructions presented here, just try and see for yourself what you can do with Selendroid. And of course, please share your experiences here.

Further reading
Again, the official Selendroid site can be found here. It offers a lot of information on the possibilities and features of Selendroid.

Happy mobile testing!