Test Automation Framework (Selenium with Java) — Magic or JIRA, Selenide, Complex SQL, Java Objects with Equals & HashCode (with Exercises)

S01E08 of the Test Automation Framework series about everything you’ll need to set up the nice, simple, yet sophisticated framework.

Covered with clear explanations and pretty illustrations.

Sounds like fun? Cool. Now, please, fasten your seatbelts because you’re here for a ride.

S01E01 — What To Automate?

S01E02 — Test Automation Environment and Tools

S01E03 — The First Selenium Test Case

S01E04 — Selenium Foundations Revisited

S01E05 — Page Factory and Elements Related Exceptions

S01E06 — Page Loading Strategies and Waits

S01E07 — Translating JIRA with Selenide (with Exercises)

S01E09 — Code Review and Refactoring (Part 1)

S01E10 — Code Review and Refactoring (Part 2)

S01E11 — Allure in Action

This article is a continuation of the previous episode, so please complete the steps from S01E07 — Translating JIRA with Selenide (with Exercises) or simply clone the repository from https://github.com/n4bik/test-automation-framework/tree/Alchemy-ClientTests to follow the steps described below.

USD-8 Test Case Implementation

This task is one of the most complicated yet. In the real world, it should’ve been split into five different Test Cases such as:

  1. Verify that the Article title is visible
  2. Verify that the Article author full name is visible
  3. Verify that the Article publish date is visible
  4. Verify that the Categories assigned to the Article are displayed as badges
  5. Verify that when the User hover over a Category badge Category title is displayed within the tooltip

I’ve decided to not do that, for the sake of keeping it concise, but I just wanted to pinpoint that it’s not the way how one should write Test Cases. Keep in mind the S from the SOLID principle which stands for the Single Responsibility Principle — can also be applied for the creation of Test Cases.

To test this task, I’ve come up with a simple Java Object represented in a form of POJO (Plain Old Java Object) to adapt the Article row from the Ultimate Stack Developer app into the Java ecosystem.

Article row from the Ultimate Stack Developer application

So, what do we see here?

  • Author’s full name
  • Publish date
  • Title
  • Category tag
  • Category title

Exactly what we’ve seen in the JIRA ticket’s Acceptance Criteria. Let’s create a Java Object out of it then.

Create a new package models.article within the pl.tomaszbuga.tests and create a new class Article.java. Paste the code that you can find below into that class.

Let’s add Setters, shall we? Right-click below the constructor and select Generate (or simply use CMD+N/CTRL+N).

Select Setter option

Select every available field to create Setters for each of them.

Once we’ve got our Article POJO, let’s create a new SQL Query within the DbDataProvider class to check if our Article object can be populated as expected.

One more thing before we move further — you may be wondering why I didn’t want to create Getters as well. It’s just because we won’t be needing them. In the end, we’ll be comparing objects but we’ll be using Equals & HashCode methods to do so. Getters would be unused, therefore there is no need to create them in the first place. It’s how you keep yourself restrained from overengineering. Don’t create stuff that you predict will be useful in the future if it will be necessary you can always add those methods later.

Below you can find an overview of the Ultimate Stack Developer database, so you can get an idea of what are we talking about. Again, I’m using JetBrains DataGrip app for database management.

article table in USD Database
article_category table in USD Database
category table in USD Database

There are plenty of ways to achieve the same result, but I wanted to get all the information that we require for the Article object in just one SQL query. Prepare for something more complex, as we’re going to use some SQL magic right now. Let’s do it step-by-step, so it’s easier to comprehend.

As you can see in the picture below, the Article list is based on the categoryId (as the URL is: localhost:4200/articles?categoryId=19)

How can we retrieve an Article based on a categoryId if there is no categoryId anywhere in the article table?

article table in USD Database

A many-to-many relationship in PostgreSQL is being held within the special table — article_category (you can learn more about that relationship type in this StackOverflow thread).

article_category table in USD Database

So, how can we retrieve all those information? The naive way would be getting all the data from the article and article_category tables and combining them within the Java code. But that’s a terrible way to handle database calls, as getting all the information from multiple tables can have a severe impact on the performance (just take a look at the PostgreSQL database capacity and imagine selecting everything from multiple tables like that)

“What’s the best practice, then?” one might ask.

Subqueries are the answer! Let me show you how to use them and how can we obtain the Article title for the specific categoryId.

What’s going on here?

We’re selecting the article.title (title column from article table)

This is the subquery I’ve mentioned previously. We’re selecting the article_id (and storing it as aid) from the article_category table for each row where the category_id equals 19. We’re saving the results as a new ac table (ac is an abbreviation for article_category).

At last, we’re using the INNER JOIN to get the article table and select only those articles where the ID is the same as the ID in the ac table.

Below you can find a simple comparison of different SQL join types.

Okay, let’s get the author's full name now. We could’ve done something like below, but again it’s a naive approach and we would need to combine first name and last name inside Java.

Fortunately, PostgreSQL has had a CONCAT function since the 9.1 version which enables us exactly what we need to do — concatenate strings. Let’s add a line below into the SELECT statement to get the author’s full name.

CONCAT(author_first_name, ' ', author_last_name) as author_full_name

Let’s add also publish date, as it’s as simple as adding article.publish_date to the SELECT statement.

Now, how are we supposed to get the Category tags and titles? The first thought you might have is to add a category table to the FROM statement. Then add category.tag and category.title to the SELECT statement. You can see the result of such an approach in the picture below.

It worked, but as you can see it’s not entirely what we wanted. We would like to merge all the tags and titles into one row, instead of having them assigned one category at a time. We will use one of the cool features that PostgreSQL provides us — the string_agg() function.

Here’s an implementation of the string_agg() function in our query.

SELECT article.title,
concat(author_first_name, ' ', author_last_name) AS author_full_name,
string_agg(category.tag, ', ') AS category_tag_list,
string_agg(category.title, ', ') AS category_title_list
FROM category,
SELECT article_id AS aid
FROM article_category
WHERE category_id = 19
) AS ac
INNER JOIN article ON article.id = ac.aid
GROUP BY 1, 2, 3;

Usage is quite simple and intuitive if you’re familiar with the Excel built-in functions. Just type string_agg() and in the parentheses type column that you’d like to aggregate add a comma and using single-quote marks set the delimiter of your choice and you have to provide column name that you’d like to assign to the new column.

Example: string_agg(column, ‘delimiter ’) as new_column_name

Also, if you’re using the string_agg() function you have to use the GROUP BY statement at the end of your query with all the columns that are not used by the aggregate function — otherwise, you’ll get an error like the one below.

Here’s the result of the query:

We almost have it, but at the moment it retrieves every possible category tag and title from the category table.

Happily enough, it’s not tough to fix. Simply add the article_category table to the FROM statement, so that we can filter results by the article.id and category.id in the WHERE statement.

SELECT article.title,
concat(author_first_name, ' ', author_last_name) AS author_full_name,
string_agg(category.tag, ', ') AS category_tag_list,
string_agg(category.title, ', ') AS category_title_list
FROM category,
SELECT article_id AS aid
FROM article_category
WHERE category_id = 19
) AS ac
INNER JOIN article ON article.id = ac.aid
WHERE article.id = article_category.article_id
AND category.id = article_category.category_id
GROUP BY 1, 2, 3;

As you can see we’ve added the WHERE statement with AND concatenation so that we can filter by both article.id and category.id. Here’s the final result of our query:

Now, once we’ve got everything we need in our SQL query we can go back to the IntelliJ and create a new method in the DbDataProvider class to utilize that query inside Java.

Open the DbDataProvider class and add the new method’s code from the snippet.

public static List<Article> getArticlesListByCategoryId(String categoryId) {
List<Article> articleList = new ArrayList<>();

try (Connection connection = getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT article.title,\n" +
" CONCAT(author_first_name, ' ', author_last_name) as author_full_name,\n" +
" article.publish_date,\n" +
" string_agg(category.tag, ', ') as category_tag_list,\n" +
" string_agg(category.title, ', ') as category_title_list\n" +
"FROM category,\n" +
" article_category,\n" +
" (\n" +
" SELECT article_id as aid\n" +
" FROM article_category\n" +
" WHERE category_id = " + categoryId + "\n" +
" ) as ac\n" +
" JOIN article\n" +
" ON article.id = ac.aid\n" +
"WHERE article.id = article_category.article_id\n" +
" AND category.id = article_category.category_id\n" +
"GROUP BY 1, 2, 3;")){

while (resultSet.next()) {
Article article = new Article();


} catch (SQLException e) {

return articleList;

It’s a lot, I know. Let’s just split this code to make sure we understand everything that’s going on there.

We’re creating a new method getArticlesListByCategoryId with the categoryId parameter that will be passed from the page URL and eventually it will return a List of Article objects.

This line initializes the empty ArrayList called articleList.

try (Connection connection = getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT article.title,\n" +
" concat(author_first_name, ' ', author_last_name) AS author_full_name,\n" +
" article.publish_date,\n" +
" string_agg(category.tag, ', ') AS category_tag_list,\n" +
" string_agg(category.title, ', ') AS category_title_list\n" +
"FROM category,\n" +
" article_category,\n" +
" (\n" +
" SELECT article_id AS aid\n" +
" FROM article_category\n" +
" WHERE category_id = " + categoryId + "\n" +
" ) as ac\n" +
" JOIN article\n" +
" ON article.id = ac.aid\n" +
"WHERE article.id = article_category.article_id\n" +
" AND category.id = article_category.category_id\n" +
"GROUP BY 1, 2, 3;")){

This is the beginning of the try-with-resources statement and we’re using the SQL query that we’ve been working on in the previous part of this article.

You can learn more about the try-with-resources in the previous article: S01E07 — Translating JIRA with Selenide (with Exercises) — in the Categories Page Test Cases Preparing JDBC section.

This is where we’re translating the query results into the actual Article object and adding it to the articleList.

} catch (SQLException e) {

In the case of SQLException, we’re going to print the Stack Trace.

At last, we’re returning the articleList filled with Article objects.

We have everything that we’d need to create an actual Test Case implementation. Let’s add a new Java class in the pl.tomaszbuga.tests.client package and name it ArticlesPageTests.

A quick recap of what are we supposed to do:

And here’s my solution to that requirements. Please, keep in mind that the code is not yet refactored, as we’ll be doing that in the next episode of the Test Automation Framework.

package pl.tomaszbuga.tests.client;

import com.codeborne.selenide.WebDriverRunner;
import org.apache.commons.collections4.CollectionUtils;
import org.testng.Assert;
import org.testng.annotations.Test;
import pl.tomaszbuga.tests.models.article.Article;

import java.util.ArrayList;
import java.util.List;

import static com.codeborne.selenide.CollectionCondition.sizeGreaterThan;
import static com.codeborne.selenide.Condition.exactText;
import static com.codeborne.selenide.Condition.visible;
import static com.codeborne.selenide.Selenide.*;
import static pl.tomaszbuga.utils.DbDataProvider.getArticlesListByCategoryId;

public class ArticlesPageTests {
public void verifyArticlesListWithDataBase() {
$(".subtitle-content").shouldHave(exactText("Please select category"));
$(".subtitle-content").shouldHave(exactText("Please select article"));
String categoryIdFromUrl = WebDriverRunner.url().split("=")[1];
List<Article> articlesList = getArticlesListByCategoryId(categoryIdFromUrl);
List<Article> articlesListFromPage = new ArrayList<>();

.forEach(row -> {
List<String> categoryTitlesList = new ArrayList<>();
List<String> categoryTagsList = new ArrayList<>();

row.$$(".badge-tag").forEach(badge -> {

Article article = new Article();
article.setCategoryTagList(String.join(", ", categoryTagsList));
article.setCategoryTitleList(String.join(", ", categoryTitlesList));


Assert.assertTrue(CollectionUtils.isEqualCollection(articlesList, articlesListFromPage));


This part of the code is a copy-paste of the verifyThatClickOnCategoryRedirectsToArticlesPage method from CategoriesPageTests class and it navigates to the Articles page.

This line is responsible for getting the categoryId from the URL. We’re using Selenide’s WebDriverRunner.url() method to get the address. Then we’re using the .split(“=”) method to slice the URL string at the equals sign. Then we’re using the [1] to retrieve the index of 1 to get the value of the categoryId.

URL after using the .split() method

Once we extracted the categoryId from the URL we can pass it as a parameter to the getArticlesListByCategoryId which we created in the previous section, so that we can perform a SQL query. We store the results as an articlesList variable.

This line initiates a new ArrayList that we’ll be using to store the articles list from the page.

This is quite complex if you’re not familiar with Java, but please stay with me. We’re using Selenide’s $$() static method to get the Collection of the Elements that can be found by the “article-row” class. We’re using the cssSelector, so we use a dot sign to indicate that it’s a class named “article-row”.

Secondly, we’re making sure that articles are loaded, so we’re expecting the size of the ElementsCollection to be greater than zero with the .shouldBe(sizeGreaterThan(0)) method.

At last, we’re using the .forEach() method so that we can iterate through each Element in the Collection. Also, we’re using the Lambda Expression which detailed description is out of the scope of this article series, but it’s quite an important asset to have in your skillset, so I’d recommend learning more about this topic by yourself.

We’re creating two lists in the Lambda scope, to have a place to store the category titles and tags from the page.

It’s kinda cool to see how easy it is to use Selenide compared to the vanilla Selenium. Here we’re accessing the row element (it’s the parameter from the Lambda expression and we can use it within Lambda scope wherever we find it useful) and we can perform the $$() method to find the ElementsCollection without any hassle. We’re skipping the .shouldBe() method as we are sure that article rows are loaded and we perform the .forEach() method to iterate through every badge available within the article row.

We’re adding category tag from the badge with .getText() method.

To display the category title, we have to hover over a badge before getting the text value. We’re using the .hover() method to do so. Then we’re looking for the “.badge-title” class element with the $() method. We have to make sure that it’s visible before getting the value, so we’re using the .shouldBe(visible) method and eventually, we’re using the .getText() method to get the category title.

At last, we’re can create a new Article object to translate the Article row into Java understandable nomenclature. We’re using the row.$().getText() to search for each element of the Article within the page and we’re setting the proper values with Setters that we have generated in the first section of this article. As for the Category tags and titles, we’re using the String.join() method to concatenate all the elements from the List and transform them into a single String with a comma as the delimiter.

In the end, we’re adding a new article to the articleListFromPage.

At last, we can compare articlesList (with articles from the database) with articlesListFromPage. We’re using the CollectionUtils.isEqualCollection() method from the Apache Commons Collections.

But assertion will fail at this point. 😱

To understand why this assertion fails we have to understand how Objects are compared in Java. Code below is a default equals() method implementation which is inherited by every Object (in our case Article).

As you can see it’s quite simple and it doesn’t check the fields of the particular Object, hence it will return true only if we’re comparing the same Object reference. When we’re trying to compare articlesList and articlesListFromPage it has to fail as we’re creating two different Objects (even though they contain the same values) stored at different memory addresses.

How to compare fields then? By simply overriding the default equals method within our Article class.

Before we do that, we have to also remember to override the hashCode() method as well, because it’s connected to the equals() with the so-called contract. Below you can find the documentation from the Object.java class.

If you’d like to learn more about the topic of equals() and hashCode() methods here are some useful StackOverflow threads and an article from Baeldung:




Fortunately, we’re living in the future, so we don’t need to put much effort to set up equals() and hashCode() methods. Go to Article.java and right-click to invoke context menu, and select Generate.

Click on equals() and hashCode() option.

Set Template to IntelliJ Default and click Next

Select all fields to be included in equals()

Select all fields to be included in hashCode()

Set all fields as non-null fields

Done! Now you have your equals() and hashCode() methods ready to go.

If you’d run the code now NullPointerException will be thrown. That’s because we assumed that every field will be non-null, and yet some articles have the empty publishDate entry in the database, hence the value is null.

Let’s fix that. Navigate to Article.java and replace the setPublishDate() method with the code from below.

This way we make sure that publishDate is filled with the actual publish date and in case if it’s null it will be replaced with an empty String (“”).

Now, if you’d try to run the code it will run successfully, but the assertion will fail.

That’s because the categoryTagList and categoryTitleList are not sorted, and when the equals() method compares them, they’re not the same, even though they contain the same categories.

Navigate to Articles.java once more. I promise it’s the last time. Replace default setCategoryTagList and setCategoryTitleList with this code. I know, it’s not the prettiest solution, but I couldn’t figure out anything else.

We’re getting the List, we’re slicing it to receive tag/titles, we’re using .sorted() method to sort them, and then we’re returning the result as a new List. At last, we’re using the String.join() method to make the single String out of the List and we’re assigning it to the actual tag/title List field.

That’s it. Now the test passes and we’re done with yet another ticket.

I’ve prepared four more tickets for you to practice. When in doubt — remember that Google and StackOverflow are your friends. In case you’d be stuck for a couple of days — feel free to check the solution I made (link can be found in the Sources section at the bottom of this article).

Have fun and happy coding!

My doghter Judy’s reviewing the article

USD-9 Test Case

USD-10 Test Case

USD-11 Test Case

USD-12 Test Case — Verify that Article Details are displayed correctly

Tips for the USD-12:

  • Modify Article object to store summary and content
  • Modify the equals and hashCode methods as required
  • Get the articleId from the URL
  • Remember that you’ll be comparing two Objects instead of Lists
  • Add a new method to DbDataProvider with SQL query from the picture
SELECT article.title,
CONCAT(author_first_name, ' ', author_last_name) as author_full_name,
FROM article
WHERE article.id = 27;

What we’ve learned in this episode?

  • How to create a Java Object to represent objects from the page
  • How to create a complex PostgreSQL query
  • How to populate Java Object with data from the Database
  • How to create a test to compare data from the page with data from Database
  • Why we need equals() and hashCode() methods
  • How to use the IntelliJ Generate option to quickly build our Java Objects

In case of any questions (I believe there can be one or two of those) — feel free to post them in the comments section below.

All the best,

Tomasz Buga, SDET



GitHub Repository with all tickets solved available at: https://github.com/n4bik/test-automation-framework/tree/Magic-ClientTest

All illustrations made by Tomasz Buga



Software Development Engineer in Tests. Passionate about programming. Experienced, former employee of the insurance industry. Graphic designer by choice.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tomasz Buga

Tomasz Buga


Software Development Engineer in Tests. Passionate about programming. Experienced, former employee of the insurance industry. Graphic designer by choice.