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
		// 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 ( {
			String zipcode = rs.getString("zipcode");
			String city = rs.getString("city");
			String state = rs.getString("state");
			try {
			} catch (IOException | JSONException e) {
		// Close the database connection
	} catch (ClassNotFoundException | SQLException e) {

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);

	// 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.

7 thoughts on “Data driven testing using a test data database

  1. Hi Bas,
    I am little confused on database testing topic.Actually what we should test here?i mean if data is stored as json or in a big array or something we just verify it by a select query?
    ok lets make it simple any way if the entered username and password is successfully taking us to home page then username and password is what is the point in verifying it against database.
    can you provide a simple example?

  2. Hi Bas,
    I think i have found a good example of database testing from a video.suppose we are registering in a website (lot of text boxes and a submit button).The moment we fill the text boxes and hit the submit button contents gets entered in the database.So to verify if the contents we entered are the same which is stored in the database if we write a select query program in eclipse after we enter information using ‘sendkeys’ then we can verify correct?

    • Hi ,

      I want some knowledge on the topic you mentioned. Can you please share the link of the video you found useful. It will be of great help.

      Thanks in advance !!

  3. Hi Bas,
    From my little knowledge i still believe just because we have so many text boxes to fill or do the process repeatedly many times we have to automate may depend on various thing and should be a wise decision correct?
    suppose if i want to automate a search.So the logic would be to assert the search result with the entered search item.sounds simple right?

  4. Hi Bas,
    so even if we do not use a testng or junit or extent report
    suppose i read the input from an excel file and write the out put to another excel file and if i use any of the frameworks that is data driven or keyword driven or hybrid to make the program work,then also we can call it a framework oriented automated script right?

    • Hi Sherin,

      a script is not framework oriented, a framework in the context of test automation is a piece of code that executes test scripts. This piece of code can be JUnit or TestNG, or any other stock or self-built piece of software. ExtentReports also is some sort of framework, but it doesn’t do test execution, just reporting.

Leave a Reply
