logo logo

Using Java Objects for Comparing API and DB Test Data

main post image

Automated tests deal with a lot of information comparison, where expected values are compared against actual ones. In certain situations, a test must compare a very large number of values, which leads to an assertion for each of these values. This, in turn, leads to a very large number of code lines, which are difficult to read, understand and maintain. For more clarity and maintainability, Java Objects can be used.

In this article I will show you how to model the test data you receive from various sources, like an API and a DB, into plain Java Objects. Then I will show you what a test assertion looks like when using these objects.

The Example

Let’s assume you are working on an API that retrieves hotel information. By calling a particular endpoint of the API, you will receive the hotel related data that you need to compare to the values stored in the DB, corresponding to the same hotel. The DB is the source of truth, therefore the values from the DB are the ‘expected’ ones. The data returned by the API will be the ‘actual’ values you will use in the test assertions.

Let’s assume you are interested in a hotel whose id is 1. The id is the unique identifier of the hotel in the ‘hotel’ DB table. You need to pass the same id to the API in order to retrieve the information for this hotel.

Your task is to compare the information stored in the DB for the hotel with id ‘1’ to the information retrieved from the API when calling it with the value ‘1’ for the id.

This example assumes you are using RestAssured for calling the API, and Spring’s JdbcTemplate to query the database. The hotel information retrieved from the API will be stored in a JsonPath variable, whereas the information from the DB will be stored into a Map. The challenge will be to compare these values represented by different types (JsonPath versus Map). The solution will be to bring these values to the same denominator. Simply put, to translate them to the same Java type, namely Object. Well, a Hotel Object. Read on.

The Test Data

For each hotel, the information that is of interest in your test is: the name of the hotel; the chain the hotel is part of; the address (represented by city, country, street and zipcode); the total number of rooms; the number of rooms for each room category (single, double, triple); the number of available rooms for each room category (at the time the DB/API are queried) and the price per night for each room category.

For example, for the hotel with id ‘1’, this data is: name of the hotel – Little Hotel; hotel chain – Little Hotels; country – Littlenia; city – Littleville; street – Little Street no 1; zipcode – 000001; total number of rooms – 40; number of single rooms – 10; the number of  double rooms – 25; number of triple rooms – 5; number of available rooms is 0 for each type of room except for the triple one, where there are 2 available rooms; then, the price for single rooms is 25, the price for double rooms is 50, and the price for a triple room is 75.

Confused already? 😵 Just imagine the number of assertions needed for checking this data. And this is only for one hotel right now. How about needing to test 100 hotels? Don’t worry, I have you covered. It will be as easy as pie. But first, let’s take a look at the DB structure used to store this information. Just so we know where from and how we need to gather the information.

DB Structure

The following diagram represents the conceptual structure of the DB that would be used for storing the information presented above. Some parts are missing from the city and country tables, as only the required fields are depicted.

The database structure

In order to query the DB for the hotel information, I recommend using Spring’s JdbcTemplate, because it is very easy and simple to work with. Please refer to the official JdbcTemplate documentation. In our case, getting the information from the DB can be done either with one very intricate query, or with two simpler ones. I will exemplify the latter option, only to make it more clear what happens.

Hotel Information from DB

So, let’s start by gathering the information from the ‘hotel’ table. The corresponding SQL query is:

select * from hotel where id=1;

This statement should be passed to the ‘queryForMap()’ method (from the JdbcTemplate class). This returns all the information corresponding to our hotel, including the id. Of course, for the city and country, the only information that is returned by this query is the id of the city, as it appears in the ‘city’ table. You should store the result of the select statement to a variable of type Map<String, Object>. Let’s name it ‘hotelInfoFromDB’. The values in this Map, at this point, are the following:

hotelInfoFromDB = {number_of_single_rooms=10, chain=Little Hotels, price_double_room=50,
number_of_double_rooms=25, number_of_triple_rooms=5, available_double_rooms=0, 
available_triple_rooms=2, zipcode=000001, total_number_of_rooms=40, 
price_single_room=25, street=Little Street no 1, name=Little Hotel, 
available_single_rooms=0, price_triple_room=75, id=1, city_id=1}

The keys of the map are equal to the column name where each value is stored.

You can now retrieve the city and country information from the city and country tables by using the following SQL statement, which you should also pass to the ‘queryForMap()’ method:

select city.name as city, country.name as country from city, country 
where city.id=1 and city.country_id=country.id;

Let’s say you store the result of this query to a Map named ‘cityAndCountry’. The result of this SQL statement should be added to the same Map that stores the result of the first statement (the one made against the ‘hotel’ table). You can do that by using the ‘putAll()’ method as follows:

hotelInfoFromDB.putAll(cityAndCountry);

At this point, the ‘hotelInfoFromDB’ Map variable holds all the hotel information we have in all our tables:

hotelInfoFromDB = {number_of_single_rooms=10, country=Littlenia, chain=Little Hotels, 
city=Littleville, price_double_room=50, number_of_double_rooms=25, number_of_triple_rooms=5,
available_double_rooms=0, available_triple_rooms=2, zipcode=000001, 
total_number_of_rooms=40, price_single_room=25, street=Little Street no 1,
name=Little Hotel, available_single_rooms=0, price_triple_room=75, id=1, city_id=1}

Hotel Information from the API

For the purpose of this example, let’s assume that you will use RestAssured to make a request to the API. This request will return a JSON representing the information corresponding to the hotel with id 1. The result of the API request will be stored in the test into a variable of type JsonPath. Please refer to the RestAssured documentation for further details.

The JSON returned by the API in this example will be the following:

{
  "name": "Little Hotel",
  "chain": "Little Hotels",
  "address": {
    "country": "Littlenia",
    "city": "Littleville",
    "street": "Little Street no 1",
    "zipcode": "000001"
  },
  "no_of_total_rooms" : 40,
  "no_of_rooms" : {
    "no_of_single_rooms" : 10,
    "no_of_double_rooms" : 25,
    "no_of_triple_rooms" : 5
  },
  "no_of_available_rooms" : {
    "no_of_available_single_rooms" : 0,
    "no_of_available_double_rooms" : 0,
    "no_of_available_triple_rooms" : 2
  },
  "price" : {
    "price_per_single_room" : 25,
    "price_per_double_room" : 50,
    "price_per_triple_room" : 75
  }
}

As you can see, we have all the required hotel information in this JSON. Getting each hotel detail from this API response will be done by specifying the path in the JSON to the information we are interested in. Let’s assume that the JsonPath variable which you will use to store the API’s JSON response is named ‘hotelInfoFromAPI’.

The path for getting the name of the hotel from the JSON is: name. Now, to get the name of the hotel, as a Java String, from the ‘hotelInfoFromAPI’ variable, the following code should be used:

hotelInfoFromAPI.get("name").toString()

Similarly, to get, for example, the number of available triple rooms, you need to use the path: no_of_available_rooms.no_of_available_triple_rooms.  Getting this information as a Java int value should be done as follows:

Integer.parseInt(hotelInfoFromAPI.get("no_of_available_rooms.no_of_available_triple_rooms").
toString())

As you might have noticed, some of the hotel information, when translated into Java types, should be represented by Strings. Other information is more suited to be an int. The prices look best as Double values. You will see how this is relevant in the next section of this article.

The Java Object

As I mentioned at the beginning of the article, the purpose of this test is to check that the data gathered from the DB and the data gathered from the API, for the hotel with id 1, is identical. At this time, this data is represented in two different ways: a JsonPath variable and a Map. Comparing the data at this point can be quite difficult due to the different representations of the expected and actual values.

The object properties

The solution is to bring both the expected data and the actual data to a common form. Given that we are talking about a hotel, and its’ properties, we can consider creating a Java Object called Hotel, which will represent the data we are interested in. This Object will have some String properties, like the hotel name, chain name or the street where the hotel is located. Some of the Hotel properties will be int, namely those that represent the number of rooms the hotel offers or the number of currently available rooms. Double will be used for the price per room properties. The Hotel class properties will be as follows, considering that the naming of the properties reflects the Java camel case convention:

public class Hotel {

    private String name;
    private String chain;
    private String country;
    private String city;
    private String street;
    private String zipcode;
    private int noOfTotalRooms;
    private int noOfSingleRooms;
    private int noOfDoubleRooms;
    private int noOfTripleRooms;
    private int noOfAvailableSingleRooms;
    private int noOfAvailableDoubleRooms;
    private int noOfAvailableTripleRooms;
    private Double pricePerSingleRoom;
    private Double pricePerDoubleRoom;
    private Double pricePerTripleRoom;
}

The ‘expected’ constructor (for the DB data)

Now it’s time to write the constructor which generates a Hotel Object based on the hotel information retrieved from the DB. It will take as a parameter the Map<String, Object> which stores the required information. It will extract the information from the map as follows: each Object property will receive a value from the Map (by specifying what is its’ corresponding key), in the ‘get()’ method. For example, if you want to get the hotel name (if the name of the Map parameter passed to the constructor is simply ‘map’), you can use the following code: map.get(“name”).toString().

The constructor which extracts the information from the Map is:

public Hotel(Map<String, Object> map) {
    this.name = map.get("name").toString();
    this.chain = map.get("chain").toString();
    this.country = map.get("country").toString();
    this.city = map.get("city").toString();
    this.street = map.get("street").toString();
    this.zipcode = map.get("zipcode").toString();
    this.noOfTotalRooms = Integer.parseInt(map.get("total_number_of_rooms").toString());
    this.noOfSingleRooms = Integer.parseInt(map.get("number_of_single_rooms").toString());
    this.noOfDoubleRooms = Integer.parseInt(map.get("number_of_double_rooms").toString());
    this.noOfTripleRooms = Integer.parseInt(map.get("number_of_triple_rooms").toString());
    this.noOfAvailableSingleRooms = Integer.parseInt(
map.get("available_single_rooms").toString());
    this.noOfAvailableDoubleRooms = Integer.parseInt(
map.get("available_double_rooms").toString());
    this.noOfAvailableTripleRooms = Integer.parseInt(
map.get("available_triple_rooms").toString());
    this.pricePerSingleRoom = Double.parseDouble(map.get("price_single_room").toString());
    this.pricePerDoubleRoom = Double.parseDouble(map.get("price_double_room").toString());
    this.pricePerTripleRoom = Double.parseDouble(map.get("price_triple_room").toString());
}

If you call this constructor by passing the ‘hotelInfoFromDB’ Map as a parameter to it, the resulting Object will look like:

Hotel{name='Little Hotel', chain='Little Hotels', country='Littlenia', 
city='Littleville', street='Little Street no 1', zipcode='000001', 
noOfTotalRooms=40, noOfSingleRooms=10, noOfDoubleRooms=25, noOfTripleRooms=5, 
noOfAvailableSingleRooms=0, noOfAvailableDoubleRooms=0, noOfAvailableTripleRooms=2,
pricePerSingleRoom=25.0, pricePerDoubleRoom=50.0, pricePerTripleRoom=75.0}

The ‘actual’ constructor (for the API response)

In order to transform the data gathered from the API into the Hotel Object representation, you can write a new constructor in the Hotel class, which takes as parameter a JsonPath. This is basically the response of the API request you make for id 1. Populating each property of the Hotel object happens by extracting the desired values from the JsonPath parameter by specifying the path corresponding to each property, in the JSON. The constructor you need for generating the ‘actual’ Hotel information is:

public Hotel(JsonPath jsonPath) {
    this.name = jsonPath.get("name").toString();
    this.chain = jsonPath.get("chain").toString();
    this.country = jsonPath.get("address.country").toString();
    this.city = jsonPath.get("address.city").toString();
    this.street = jsonPath.get("address.street").toString();
    this.zipcode = jsonPath.get("address.zipcode").toString();
    this.noOfTotalRooms = Integer.parseInt(jsonPath.get("no_of_total_rooms").
toString());
    this.noOfSingleRooms = Integer.parseInt(jsonPath.
get("no_of_rooms.no_of_single_rooms").toString());
    this.noOfDoubleRooms = Integer.parseInt(jsonPath.
get("no_of_rooms.no_of_double_rooms").toString());
    this.noOfTripleRooms = Integer.parseInt(jsonPath.
get("no_of_rooms.no_of_triple_rooms").toString());
    this.noOfAvailableSingleRooms = Integer.parseInt(jsonPath.
get("no_of_available_rooms.no_of_available_single_rooms").toString());
    this.noOfAvailableDoubleRooms = Integer.parseInt(jsonPath.
get("no_of_available_rooms.no_of_available_double_rooms").toString());
    this.noOfAvailableTripleRooms = Integer.parseInt(jsonPath.
get("no_of_available_rooms.no_of_available_triple_rooms").toString());
    this.pricePerSingleRoom = Double.parseDouble(jsonPath.
get("price.price_per_single_room").toString());
    this.pricePerDoubleRoom = Double.parseDouble(jsonPath.
get("price.price_per_double_room").toString());
    this.pricePerTripleRoom = Double.parseDouble(jsonPath.
get("price.price_per_triple_room").toString());
}

If you call this constructor by passing the ‘hotelInfoFromAPI’ JsonPath as a parameter to it, the resulting Object will look like:

Hotel{name='Little Hotel', chain='Little Hotels', country='Littlenia', city='Littleville', 
street='Little Street no 1', zipcode='000001', noOfTotalRooms=40, noOfSingleRooms=10, 
noOfDoubleRooms=25, noOfTripleRooms=5, noOfAvailableSingleRooms=0, 
noOfAvailableDoubleRooms=0, noOfAvailableTripleRooms=2, pricePerSingleRoom=25.0,
pricePerDoubleRoom=50.0, pricePerTripleRoom=75.0}

The equals(), hashCode() and toString() methods

Before you can consider the Hotel object complete and ready to be used in tests, you still need to define 3 methods: the equals(), hashCode() and toString() methods. Luckily, if you are using IntelliJ as your code editor, by using the ‘Alt + Ins’ shortcut (or equivalent in MacOs) in the class, these will be created for you automatically. These methods are necessary for Object comparison and output of the Object’s properties. For the sake of giving you a complete example, here are these 3 methods you need to add to your Hotel class:

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    Hotel hotel = (Hotel) o;
    return noOfTotalRooms == hotel.noOfTotalRooms &&
            noOfSingleRooms == hotel.noOfSingleRooms &&
            noOfDoubleRooms == hotel.noOfDoubleRooms &&
            noOfTripleRooms == hotel.noOfTripleRooms &&
            noOfAvailableSingleRooms == hotel.noOfAvailableSingleRooms &&
            noOfAvailableDoubleRooms == hotel.noOfAvailableDoubleRooms &&
            noOfAvailableTripleRooms == hotel.noOfAvailableTripleRooms &&
            Objects.equals(name, hotel.name) &&
            Objects.equals(chain, hotel.chain) &&
            Objects.equals(country, hotel.country) &&
            Objects.equals(city, hotel.city) &&
            Objects.equals(street, hotel.street) &&
            Objects.equals(zipcode, hotel.zipcode) &&
            Objects.equals(pricePerSingleRoom, hotel.pricePerSingleRoom) &&
            Objects.equals(pricePerDoubleRoom, hotel.pricePerDoubleRoom) &&
            Objects.equals(pricePerTripleRoom, hotel.pricePerTripleRoom);
}

@Override
public int hashCode() {
    return Objects.hash(name, chain, country, city, street, zipcode, noOfTotalRooms, 
noOfSingleRooms, noOfDoubleRooms, noOfTripleRooms, noOfAvailableSingleRooms, 
noOfAvailableDoubleRooms, noOfAvailableTripleRooms, pricePerSingleRoom, pricePerDoubleRoom, 
pricePerTripleRoom);
}

@Override
public String toString() {
    return "Hotel{" +
            "name='" + name + '\'' +
            ", chain='" + chain + '\'' +
            ", country='" + country + '\'' +
            ", city='" + city + '\'' +
            ", street='" + street + '\'' +
            ", zipcode='" + zipcode+ '\'' +
            ", noOfTotalRooms=" + noOfTotalRooms +
            ", noOfSingleRooms=" + noOfSingleRooms +
            ", noOfDoubleRooms=" + noOfDoubleRooms +
            ", noOfTripleRooms=" + noOfTripleRooms +
            ", noOfAvailableSingleRooms=" + noOfAvailableSingleRooms +
            ", noOfAvailableDoubleRooms=" + noOfAvailableDoubleRooms +
            ", noOfAvailableTripleRooms=" + noOfAvailableTripleRooms +
            ", pricePerSingleRoom=" + pricePerSingleRoom +
            ", pricePerDoubleRoom=" + pricePerDoubleRoom +
            ", pricePerTripleRoom=" + pricePerTripleRoom +
            '}';
}

The Test

At this point, most of the work is done: you collected the data from various sources, and converted it to the same entity (namely a Hotel Object), via the corresponding constructors. The test itself only involves passing the two constructor calls to an assertion method, as follows:

assertEquals(new Hotel(hotelInfoFromAPI), new Hotel(hotelInfoFromDB));

If the information retrieved from the API (via ‘new Hotel(hotelInfoFromAPI)’) equals the information retrieved from the DB (via ‘new Hotel(hotelInfoFromDB)’), this assertion will pass successfully.

And that’s it! A very short test, with only one assertion, which checks, at the same time, multiple values. And the beauty of this approach is that you can use these constructors to generate Objects representing any hotels you are interested in. You only need to change the needed hotel id when querying the API or the DB to the desired values, generate the corresponding Objects and add them to the assertion.

 

Reading References

 

Avatar

About the author

Corina Pip

Corina is a Test & Automation Lead, with focus on testing by means of Java, Selenium, TestNG, Spring, Maven, and other cool frameworks and tools. Previous endeavours from her 11+ years testing career include working on navigation devices, in the online gaming industry, in the aviation software and automotive industries.
Apart from work, Corina is a testing blogger (https://imalittletester.com/) and a GitHub contributor (https://github.com/iamalittletester).
She is the creator of a wait based library for Selenium testing (https://github.com/iamalittletester/thewaiter) and creator of “The Little Tester” comic series (https://imalittletester.com/category/comics/). She also tweets at @imalittletester.

Join TestProject Community

Get full access to the world's first cloud-based, open source friendly testing community. Enjoy TestProject's end-to-end test automation Platform, Forum, Blog and Docs - All for FREE.

Join Us Now  

Leave a Reply

Join TestProject Newsletter

Join a 20K community of readers! Always stay up-to-date with all the latest test automation trends, best practice and tips shared by leading software testing community experts across the globe!

FacebookLinkedInTwitterEmail