Manish Bhayana

Data Interface Testing Using Automation

Data Interface Testing

Say, you got a DB comprising of huge data with billions of records. You have to showcase it on UI only after making sure that everything you want to represent on UI is accurate and as expected. Incorrect data could impact your business in unknown and serious ways that can lie undetected for months.

So, here you might need to plan a new strategy, which should lead to answers of all your questions.

One of the finest approach among this strategy should be- to make sure that everything you are showing is validated and verified. This leads to a special type of testing called as Data Interface Testing.

What is Data Interface Testing ??

Before we go ahead with Data Interface Testing, let’s first discuss about data interface. Lots of application in the market are nowadays based on Data Mining or Big Data concept. This helps to streamline the big data and showcase on UI in an adequate manner.

Now, as many people say that there is always some pros and cons for each process. Similarly, even this one has few. One of the biggest one is showcasing huge data. But I have a solution.

There is always a challenge to show the huge data on UI where everything is placed at their respective place with correct data set and correct orientation (if you’re showing the data in graphical representation).

So, the interaction between database and User Interface brings the term Data Interface. And to make sure that it works well both ways i.e. request and response results, we call it as Data Interface testing.



Big Question !! 

Can I test this much of data and all of that manually??

Answer is yes, it is possible. But practically not a good way to do the same.

So what …?? Automation ??


But what if I don’t have any good knowledge for it ?

bulbDon’t you worry, we got a cheat for you !!!  A tool to test data interface automatically, with a very basic knowledge for Automation/coding.

Some Info Regarding this tool
This tool is made to test validation and verification of data between database and user interface. To make this tool useful, one can easily use it on it’s own working environment, by customizing the details in provided file and coding as per their requirements. .

How this tool works ?

With it’s main class, it reads multiple files which further executes the methods written in those properties.

For reference, the source code is mentioned below:

Representing Main Class of Tool

    * @author Jyothi Venugopalan
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Map;

public class test_class 

	public static void main(String[] args) throws Throwable 

		String PROPERTY_PATH = args[0];
		String PROPERTY_PATH_COLUMN = args[3];
		String RESULT_FILE_PATH = args[4];
			// read all queries (actual and expected)
			Enumeration<String> keys = property_reader.bundle_query_actual.getKeys();
			while (keys.hasMoreElements()) 
				String key = keys.nextElement();
				String actual_query = property_reader.bundle_query_actual.getString(key);
				String expected_query = property_reader.bundle_query_expected.getString(key);
				String[] columns = property_reader.bundle_query_column.getString(key).split(",");

				Map<String, String> actual_result = property_reader.dbVerification(actual_query, columns);
				Map<String, String> expected_result = property_reader.dbVerification(expected_query, columns);
				for (String col : columns) 
					if (expected_result.get(col).equals(actual_result.get(col))) 
						System.out.println("[Passed]  " + col + "  Actual["
								+ actual_result.get(col) + "]" + "\t"
								+ "  Expected[" + expected_result.get(col)
								+ "]");
						System.out.println("[Failed]  " + col + "  Actual["
								+ actual_result.get(col) + "]" + "\t"
								+ "  Expected[" + expected_result.get(col)
								+ "]");
		catch (SQLException ex) 
			System.err.println("Cannot connect to database server (SQLException)"+ ex);

Method written in above class is dependent upon various files. One of them is called as Property_Reader file.

This is a custom made file, which executes multiple methods and brings result for main class.

1. Property_Reader_Method

package com.test.tool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

public class property_reader {

	static Connection con = null;
	static Statement stm = null;

	public static InputStream PropertyFile = null;
	public static FileOutputStream file_result = null;
	static File file = null;
	static PrintStream result = null;
	public static InputStream PropertyFile_Query_Actual = null;
	public static InputStream PropertyFile_Query_Expected = null;
	public static InputStream PropertyFile_Query_Column = null;
	public static ResourceBundle bundle = null;
	public static ResourceBundle bundle_query_actual = null;
	public static ResourceBundle bundle_query_expected = null;
	public static ResourceBundle bundle_query_column = null;
	// DB properties
	public static String Url = null;
	public static String UserName = null;
	public static String Password = null;
	public static String ClassName = null;

	/* Basic properties supported by this configuration builder. */

	public static void initServerProperty(String PROPERTY_PATH,
			throws Exception {
		if (PropertyFile != null && bundle != null) {
		if (PropertyFile_Query_Actual != null && bundle_query_actual != null) {
		if (PropertyFile_Query_Expected != null
				&& bundle_query_expected != null) {
		if (PropertyFile_Query_Column != null && bundle_query_column != null) {

		// get db properties

		PropertyFile = new FileInputStream(PROPERTY_PATH); // PropertyFile
		bundle = new PropertyResourceBundle(PropertyFile);
		Url = bundle.getString("Url");
		UserName = bundle.getString("UserName");
		Password = bundle.getString("Password");
		ClassName = bundle.getString("ClassName");
		//output file
		file = new File(RESULT_FILE_PATH);
		file_result = new FileOutputStream(file);
		result = new PrintStream(file_result);
		if (!file.exists()) {

		// get actual queries

		PropertyFile_Query_Actual = new FileInputStream(
		bundle_query_actual = new PropertyResourceBundle(
		// get expected queries
		PropertyFile_Query_Expected = new FileInputStream(
		bundle_query_expected = new PropertyResourceBundle(
		// get column name
		PropertyFile_Query_Column = new FileInputStream(PROPERTY_PATH_COLUMN);
		bundle_query_column = new PropertyResourceBundle(

		con = DriverManager.getConnection(Url,UserName, Password);
		stm = con.createStatement();
		System.out.println("Database connection established");


	public static void connection_close() {

		if (con != null) {
			try {
			} catch (SQLException e) {

	public static Map<String, String> dbVerification(String query,
			String[] columns) throws Throwable {
		Map<String, String> result = new HashMap<String, String>();

		try {
			String value = null;
			// Send query to db
			ResultSet rs = stm.executeQuery(query);
			while ( {
				for (String col : columns) {
					value = rs.getString(col);
					result.put(col, value);
		} catch (Throwable e) {
			throw e;
		} finally {

		return result;



2. db_property

This file comprises of all properties, which helps in setting up connection with server/DB.

Following are the properties used in this file.

  1. Url=jdbc:presto://
  2. UserName=root
  3. Password= 12345
  4. ClassName=com.facebook.presto.jdbc.PrestoDriver
  • You can change your URL and credentials as per available server(s).
  • Password can be null too. Depends upon the server details.
  • For current we are using presto as DB.

3. query_column

This comprises of column name for which data needs to be fetched from DB. For every query there should be a unique query name which must be identical in all property files for that query.

For below mentioned example. “testA_count” is the name of query which is unique from rest of the two but same in other property files for queries with same conditions.

Apart from that, irrespective of number of columns available in expected and actual query, it will only bring data for “Column A” column in the result set.

Same case for others too.

Following is an example to illustrate the same:



4. query_actual

This property file contains the queries created by developers or fetched through server logs file which are created while accessing the application through UI.

testA_count = SELECT count(employee_count) employee FROM Employer WHERE condition ??
 testB_count =  SELECT sum(salary) salary_account FROM Employer trip WHERE condition ??
 test_total_count = SELECT sum(salary) salary_account, sum(employee_count-1) employee, sum(residence) Address FROM Employer WHERE condition ??


5. query_expected

This property file contains the queries created by testers.

 testA_count = SELECT count(employee_count) employee FROM Employer WHERE condition ??
 testB_count =  SELECT sum(salary) salary_account FROM Employer trip WHERE condition ??
 test_total_count = SELECT sum(salary) salary_account, sum(employee_count) employee, sum(residence) Address FROM Employer WHERE condition ??

By running the above mentioned code for main class, it will create a new result file every time. This file will comprise of end result for executed queries, having expected and actual result with numbers and pass/fail result.

For above case it will return the following result set:

[Passed]  employee_count  Actual[142448]	  Expected[142448]
[Passed]  salary Actual[122099]	  Expected[122099]
[Passed]  employee_count  Actual[142448]	  Expected[142448]
[Passed]  salary Actual[122099]	  Expected[122099]
[Passed]  residence Actual[126617]	  Expected[126617]

For Failed Case:

Let’s change the actual query to-

testA_count = SELECT count(employee_count-1) employee FROM Employer WHERE condition ??

Now after executing the same main_class method again, it will return the following result set:

[Failed]  employee_count  Actual[-1019851]	  Expected[142448]
[Passed]  salary Actual[122099]	  Expected[122099]
[Failed]  employee_count  Actual[-1019851]	  Expected[142448]
[Passed]  salary Actual[122099]	  Expected[122099]
[Passed]  residence Actual[126617]	  Expected[126617]


Points to be considered:

  • Make sure that the query name should always be the same in all expected, actual and column property file.
  • For every query there should be a unique query name.

Benefits of using this tool are:

  • Any Structured DB can be used for this eg. Presto, MySQL, MS-SQL etc.
  • Platform independent. Can be run on Windows/Ubuntu/Linux.
  • Can be run on a project written in any language.
  • Doesn’t require any prior coding skills or automation knowledge.
  • One can easily put the expected and actual test case in respective property files and can have the result set, with complete information.
  • Can be easily customized as per available resources/requirements.

I hope this was useful.

Thank you for reading. 🙂